部署statspack工具（二）之解决方案2
解决方案二：在emp2的empno列上面创建索引，再执行share_pool_sql_1.sh脚本，查看sp报告
8.1在emp2的empno列上创建索引
sys@TESTDB12>create index ind_empno on scott.emp2(empno);
8.2 重新执行share_pool_sql_1.sh脚本并重新开启statspack自动快照
{oracle@Redhat55.cuug.net:/home/oracle/script/bin}$sh share_pool_sql_1.sh
SQL>@?/rdbms/admin/spauto
8.3生成statspack报告
perfstat@TESTDB12>selectsnap_id,snap_time,snap_level from stats$snapshot order by snap_time;
perfstat@TESTDB12>selectsnap_id,snap_time,snap_level from stats$snapshot order by snap_time;
SNAP_ID SNAP_TIME SNAP_LEVEL
---------- -------------------
1 28-JUL-14 7
11 28-JUL-14 7
21 28-JUL-14 7
31 28-JUL-14 7
41 29-JUL-14 7
51 29-JUL-14 7
61 29-JUL-14 7
71 29-JUL-14 7
81 29-JUL-14 7
91 29-JUL-14 7
101 29-JUL-14 7
111 29-JUL-14 7
121 29-JUL-14 7
131 29-JUL-14 7
141 29-JUL-14 7
151 29-JUL-14 7
161 29-JUL-14 7
171 29-JUL-14 7
181 29-JUL-14 7
191 29-JUL-14 7
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 131
Enter value for end_snap: 141
Enter value for report_name:
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 141
Enter value for end_snap: 151
Enter value for report_name:
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 151
Enter value for end_snap: 161
Enter value for report_name:
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 161
Enter value for end_snap: 171
Enter value for report_name:
8.4通过新生成的4个statspack报告对比各个时间段的数据缓冲区的命中率和库缓冲区的命中率：
时间
Buffer Hit（%）
Library Hit（%）
05:19:01~ 05:34:01
99.99
89.78
05:34:01 ~05:49:00
99.99
89.72
05:49:00 ~ 06:04:05
99.98
89.45
06:04:05 ~06:13:00
99.95
88.79
在emp2的empno列上创建索引后通过对比发现数据缓冲区的命中率明显得到了改善，达到了的99%以上；而库缓冲区的命中率也得到小幅度提升
8.5查看Top 5 Timed Events找出4 个报告中各个时间段跟磁盘I/O相关的等待事件
时间
name
Wait(s)
Time(s)
05:19:01~ 05:34:01
log file parallel write
45,110
54
log file sync
6,240
46
os thread startup
34
5
control file parallel write
332
3
05:34:01 ~05:49:00
log file parallel write
48,413
36
log file sync
3,563
28
os thread startup
33
5
db file sequential read
2,018
2
05:49:00 ~ 06:04:05
log file parallel write
49,564
23
log file sync
455
15
db file sequential read
3,955
9
os thread startup
39
6
06:04:05 ~06:13:00
log file parallel write
28,273
8
db file sequential read
2,928
5
log file sync
231
4
os thread startup
21
3
通过4个报告的对比Top 5 Timed Events中direct path read不见了，说明解决了全表扫描等待I\O的问题；但log file parallel write和log file sync的磁盘I/O都还比较大，而且新增了control fileparallel write I/O，没有什么大的耗资源的任务，说明系统性能得以提升
8.6造成物理读最大的前几个sql语句在报告中未找到，用sql语句查询得出这些语句:select sql_text from v$sqlwhere disk_reads=(select max(disk_reads) from v$sql);
时间
Executions
Rows per Exec
Sql语句
05:19:01~ 05:34:01
10,840
16.1
select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$
where obj#=:1 and intcol#=:2 and row#=:3 order by bucket
05:34:01 ~05:49:00
12,565
16.1
select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$
where obj#=:1 and intcol#=:2 and row#=:3 order by bucket
05:49:00 ~ 06:04:05
15,112
16.0
select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$
where obj#=:1 and intcol#=:2 and row#=:3 order by bucket
06:04:05 ~06:13:00
20,814
16.4
select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$
where obj#=:1 and intcol#=:2 and row#=:3 order by bucket
通过对比各时间段最消耗资源的SQL语句，发现仍有相同或相似的执行计划，应该使用绑定变量，来提高执行效率。
生成语句的执行计划: set autotrace traceonly select * from scott.emp2
idle>select *from scott.emp2 where empno=1484;
Execution Plan
----------------------------------------------------------
Plan hash value:2918945472
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |1 | 48 | 4(0)| 00:00:01 |
| 1 |TABLE ACCESS BY INDEX ROWID| EMP2| 1 | 48 |4 (0)| 00:00:01 |
|* 2 |INDEX RANGE SCAN |IND_EMPNO | 1 | |3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=1484)
Statistics
----------------------------------------------------------
55recursive calls
0db block gets
78consistent gets
4physical reads
0redo size
1033bytes sent via SQL*Net to client
523bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
7sorts (memory)
0sorts (disk)
1rows processed
8.7查看Buffer Pool Advisory并把Buffer cache的大小设置为推荐的大小
05:19:01~ 05:34:01时间段的Buffer PoolAdvisory
Est
Phys Estimated Est
Size forSize Buffers ReadPhys Reads Est Phys % dbtime
P Est (M) Factr (thousands)Factr (thousands) Read Timefor Rds
--- -------- ----------------- ------ -------------- ------------ --------
D 4.1 0 8.0 261 345 5.2
D 8.2 1 1.1 35 37 .6
D 12.3 1 1.1 34 36 .5
D 16.3 2 1.0 33 35 .5
D 20.4 2 1.0 33 34 .5
D 24.5 3 1.0 33 34 .5
D 28.6 3 1.0 33 34 .5
D 32.7 4 1.0 33 33 .5
D 36.8 4 1.0 33 33 .5
D 40.8 5 1.0 32 33 .5
D 44.9 5 1.0 32 33 .5
D 481.0 6 1.0 32 33 .5
D 521.1 6 1.0 32 33 .5
D 561.2 7 1.0 32 33 .5
D 601.3 7 1.0 32 33 .5
D 641.3 8 1.0 32 33 .5
D 681.4 8 1.0 32 33 .5
D 721.5 9 1.0 32 33 .5
D 761.6 9 1.0 32 33 .5
D 801.7 10 1.0 32 33 .5
05:34:01 ~05:49:00时间段的Buffer PoolAdvisory
Est
Phys Estimated Est
Size forSize Buffers ReadPhys Reads Est Phys % dbtime
P Est (M) Factr (thousands)Factr (thousands) Read Timefor Rds
--- -------- ----------------- ------ -------------- ------------ --------
D 4.1 0 7.8 273 357 5.1
D 8.2 1 1.1 37 39 .6
D 12.3 1 1.0 37 38 .5
D 16.3 2 1.0 36 37 .5
D 20.4 2 1.0 35 37 .5
D 24.5 3 1.0 35 36 .5
D 28.6 3 1.0 35 36 .5
D 32.7 4 1.0 35 36 .5
D 36.8 4 1.0 35 36 .5
D 40.8 5 1.0 35 36 .5
D 44.9 5 1.0 35 36 .5
D 481.0 6 1.0 35 36 .5
D 521.1 6 1.0 35 36 .5
D 561.2 7 1.0 35 36 .5
D 601.3 7 1.0 35 36 .5
D 641.3 8 1.0 35 36 .5
D 681.4 8 1.0 35 36 .5
D 721.5 9 1.0 35 36 .5
D 761.6 9 1.0 35 36 .5
D 801.7 10 1.0 35 36 .5
05:49:00 ~ 06:04:05时间段的Buffer PoolAdvisory
Est
Phys Estimated Est
Size forSize Buffers ReadPhys Reads Est Phys % dbtime
P Est (M) Factr (thousands)Factr (thousands) Read Timefor Rds
--- -------- ----------------- ------ -------------- ------------ --------
D 4.1 0 7.6 302 438 6.0
D 8.2 1 1.1 42 49 .7
D 12.3 1 1.0 41 48 .7
D 16.3 2 1.0 40 47 .6
D 20.4 2 1.0 40 46 .6
D 24.5 3 1.0 40 46 .6
D 28.6 3 1.0 40 46 .6
D 32.7 4 1.0 40 46 .6
D 36.8 4 1.0 40 46 .6
D 40.8 5 1.0 40 46 .6
D 44.9 5 1.0 40 46 .6
D 481.0 6 1.0 40 46 .6
D 521.1 6 1.0 40 46 .6
D 561.2 7 1.0 40 46 .6
D 601.3 7 1.0 40 46 .6
D 641.3 8 1.0 40 46 .6
D 681.4 8 1.0 40 46 .6
D 721.5 9 1.0 40 46 .6
D 761.6 9 1.0 40 46 .6
D 801.7 10 1.0 40 46 .6
06:04:05 ~06:13:00时间段的Buffer PoolAdvisory
Est
Phys Estimated Est
Size forSize Buffers ReadPhys Reads Est Phys % dbtime
P Est (M) Factr (thousands)Factr (thousands) Read Timefor Rds
--- -------- ----------------- ------ -------------- ------------ --------
D 4.1 0 7.6 338 497 6.6
D 8.2 1 1.0 47 56 .7
D 12.3 1 1.0 46 55 .7
D 16.3 2 1.0 45 54 .7
D 20.4 2 1.0 45 54 .7
D 24.5 3 1.0 45 54 .7
D 28.6 3 1.0 45 53 .7
D 32.7 4 1.0 45 53 .7
D 36.8 4 1.0 45 53 .7
D 40.8 5 1.0 45 53 .7
D 44.9 5 1.0 45 53 .7
D 481.0 6 1.0 45 53 .7
D 521.1 6 1.0 45 53 .7
D 561.2 7 1.0 45 53 .7
D 601.3 7 1.0 45 53 .7
D 641.3 8 1.0 45 53 .7
D 681.4 8 1.0 45 53 .7
D 721.5 9 1.0 45 53 .7
D 761.6 9 1.0 45 53 .7
D 801.7 10 1.0 45 53 .7
通过以上4个时间段中Buffer Pool Advisory建议可以看的出来，对于增加Buffer cache的大小对性能的影响并不明显。
8.8查看Time Model System Stats
05:19:01~ 05:34:01时间段Time Model System Stats
Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
DB CPU 440.5 119.9
parse time elapsed 158.5 43.1
sql execute elapsed time 145.1 39.5
hard parse elapsed time 135.0 36.8
connection management call elapsed 108.8 29.6
PL/SQL execution elapsed time 5.7 1.6
hard parse (sharing criteria) elaps 1.3 .3
hard parse (bind mismatch) elapsed 1.2 .3
PL/SQL compilation elapsed time 0.8 .2
repeated bind elapsed time 0.4 .1
sequence load elapsed time 0.1 .0
DB time 367.4
background elapsed time 75.1
background cpu time 20.1
05:34:01 ~05:49:00时间段Time Model System Stats
Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
DB CPU 455.9 124.3
parse time elapsed 155.5 42.4
sql execute elapsed time 149.9 40.9
hard parse elapsed time 128.2 35.0
connection management call elapsed 104.6 28.5
PL/SQL execution elapsed time 6.8 1.9
hard parse (sharing criteria) elaps 2.5 .7
hard parse (bind mismatch) elapsed 2.4 .7
PL/SQL compilation elapsed time 0.8 .2
repeated bind elapsed time 0.5 .1
sequence load elapsed time 0.3 .1
DB time 366.8
background elapsed time 54.4
background cpu time 20.1
05:49:00 ~ 06:04:05时间段Time Model System Stats
Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
DB CPU 463.3 122.2
parse time elapsed 160.9 42.4
sql execute elapsed time 158.6 41.9
hard parse elapsed time 133.8 35.3
connection management call elapsed 103.6 27.3
PL/SQL execution elapsed time 7.3 1.9
hard parse (sharing criteria) elaps 2.1 .6
hard parse (bind mismatch) elapsed 1.9 .5
PL/SQL compilation elapsed time 1.1 .3
repeated bind elapsed time 0.5 .1
sequence load elapsed time 0.2 .0
DB time 379.0
background elapsed time 52.7
background cpu time 23.0
06:04:05 ~06:13:00时间段Time Model System Stats
Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
DB CPU 269.2 119.5
parse time elapsed 105.7 46.9
sql execute elapsed time 102.9 45.6
hard parse elapsed time 89.9 39.9
connection management call elapsed 58.2 25.8
PL/SQL execution elapsed time 4.0 1.8
hard parse (sharing criteria) elaps 2.0 .9
hard parse (bind mismatch) elapsed 1.6 .7
PL/SQL compilation elapsed time 1.1 .5
repeated bind elapsed time 0.6 .3
sequence load elapsed time 0.1 .1
DB time 225.4
background elapsed time 19.6
background cpu time 12.2
通过对比4个报告各个时间段中的Time Model System Stats，发现产生的硬解析明显增加了。
8.9查看Latch Sleep breakdown
05:19:01~ 05:34:01时间段的Latch Sleep breakdown
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------- ------------ ----------- -----------
shared pool 3,787,761 4 4 0
05:34:01 ~05:49:00时间段的Latch Sleep breakdown
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------- ------------ ----------- -----------
shared pool 4,107,841 5 5 0
JS Sh mem access 3 1 1 0
enqueue hash chains 320,877 1 1 0
05:49:00 ~ 06:04:05时间段的Latch Sleep breakdown
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------- ------------ ----------- -----------
shared pool 4,257,852 8 8 0
row cache objects 3,956,966 3 3 0
call allocation 110,566 1 1 0
redo allocation 99,927 1 1 0
06:04:05 ~06:13:00时间段的Latch Sleep breakdown
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------- ------------ ----------- -----------
shared pool 2,595,386 6 6 0
row cache objects 2,500,734 1 1 0
通过以上4个sp报告各个时间段的Latch Sleepbreakdown的内容，发现cache bufferslru chain已经没有了，但是shared pool次数上来了。
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
