部署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.shSQL>@?/rdbms/admin/spauto8.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: 131Enter value for end_snap: 141Enter value for report_name:Specify the Begin and End Snapshot Ids~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Enter value for begin_snap: 141Enter value for end_snap: 151Enter value for report_name:Specify the Begin and End Snapshot Ids~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Enter value for begin_snap: 151Enter value for end_snap: 161Enter value for report_name:Specify the Begin and End Snapshot Ids~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Enter value for begin_snap: 161Enter value for end_snap: 171Enter value for report_name:8.4通过新生成的4个statspack报告对比各个时间段的数据缓冲区的命中率和库缓冲区的命中率：时间Buffer Hit（%）Library Hit（%）05:19:01~ 05:34:0199.9989.7805:34:01 ~05:49:00 99.9989.7205:49:00 ~ 06:04:05 99.9889.4506:04:05 ~06:13:0099.9588.79在emp2的empno列上创建索引后通过对比发现数据缓冲区的命中率明显得到了改善，达到了的99%以上；而库缓冲区的命中率也得到小幅度提升8.5查看Top 5 Timed Events找出4 个报告中各个时间段跟磁盘I/O相关的等待事件时间nameWait(s)Time(s)05:19:01~ 05:34:01log file parallel write45,11054log file sync6,24046os thread startup345control file parallel write 332305:34:01 ~05:49:00log file parallel write48,41336log file sync3,56328os thread startup335db file sequential read2,018205:49:00 ~ 06:04:05log file parallel write49,56423log file sync45515db file sequential read3,9559os thread startup39606:04:05 ~06:13:00log file parallel write28,2738db file sequential read2,9285log file sync2314os thread startup213通过4个报告的对比Top 5 Timed Events中direct path read不见了，说明解决了全表扫描等待IO的问题；但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);时间ExecutionsRows per ExecSql语句05:19:01~ 05:34:0110,84016.1select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$where obj#=:1 and intcol#=:2 and row#=:3 order by bucket05:34:01 ~05:49:0012,56516.1select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$where obj#=:1 and intcol#=:2 and row#=:3 order by bucket05:49:00 ~ 06:04:0515,11216.0select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$where obj#=:1 and intcol#=:2 and row#=:3 order by bucket06:04:05 ~06:13:0020,81416.4select /*+ 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.emp2idle>select *from scott.emp2 w免费云主机域名here 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 processed8.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 % dbtimeP Est (M) Factr (thousands)Factr (thousands) Read Timefor Rds— ——– —————– —— ————– ———— ——–D 4.1 0 8.0 261 345 5.2D 8.2 1 1.1 35 37 .6D 12.3 1 1.1 34 36 .5D 16.3 2 1.0 33 35 .5D 20.4 2 1.0 33 34 .5D 24.5 3 1.0 33 34 .5D 28.6 3 1.0 33 34 .5D 32.7 4 1.0 33 33 .5D 36.8 4 1.0 33 33 .5D 40.8 5 1.0 32 33 .5D 44.9 5 1.0 32 33 .5D 481.0 6 1.0 32 33 .5D 521.1 6 1.0 32 33 .5D 561.2 7 1.0 32 33 .5D 601.3 7 1.0 32 33 .5D 641.3 8 1.0 32 33 .5D 681.4 8 1.0 32 33 .5D 721.5 9 1.0 32 33 .5D 761.6 9 1.0 32 33 .5D 801.7 10 1.0 32 33 .505:34:01 ~05:49:00时间段的Buffer PoolAdvisory Est Phys Estimated Est Size forSize Buffers ReadPhys Reads Est Phys % dbtimeP Est (M) Factr (thousands)Factr (thousands) Read Timefor Rds— ——– —————– —— ————– ———— ——–D 4.1 0 7.8 273 357 5.1D 8.2 1 1.1 37 39 .6D 12.3 1 1.0 37 38 .5D 16.3 2 1.0 36 37 .5D 20.4 2 1.0 35 37 .5D 24.5 3 1.0 35 36 .5D 28.6 3 1.0 35 36 .5D 32.7 4 1.0 35 36 .5D 36.8 4 1.0 35 36 .5D 40.8 5 1.0 35 36 .5D 44.9 5 1.0 35 36 .5D 481.0 6 1.0 35 36 .5D 521.1 6 1.0 35 36 .5D 561.2 7 1.0 35 36 .5D 601.3 7 1.0 35 36 .5D 641.3 8 1.0 35 36 .5D 681.4 8 1.0 35 36 .5D 721.5 9 1.0 35 36 .5D 761.6 9 1.0 35 36 .5D 801.7 10 1.0 35 36 .505:49:00 ~ 06:04:05时间段的Buffer PoolAdvisory Est Phys Estimated Est Size forSize Buffers ReadPhys Reads Est Phys % dbtimeP Est (M) Factr (thousands)Factr (thousands) Read Timefor Rds— ——– —————– —— ————– ———— ——–D 4.1 0 7.6 302 438 6.0D 8.2 1 1.1 42 49 .7D 12.3 1 1.0 41 48 .7D 16.3 2 1.0 40 47 .6D 20.4 2 1.0 40 46 .6D 24.5 3 1.0 40 46 .6D 28.6 3 1.0 40 46 .6D 32.7 4 1.0 40 46 .6D 36.8 4 1.0 40 46 .6D 40.8 5 1.0 40 46 .6D 44.9 5 1.0 40 46 .6D 481.0 6 1.0 40 46 .6D 521.1 6 1.0 40 46 .6D 561.2 7 1.0 40 46 .6D 601.3 7 1.0 40 46 .6D 641.3 8 1.0 40 46 .6D 681.4 8 1.0 40 46 .6D 721.5 9 1.0 40 46 .6D 761.6 9 1.0 40 46 .6D 801.7 10 1.0 40 46 .606:04:05 ~06:13:00时间段的Buffer PoolAdvisory Est Phys Estimated Est Size forSize Buffers ReadPhys Reads Est Phys % dbtimeP Est (M) Factr (thousands)Factr (thousands) Read Timefor Rds— ——– —————– —— ————– ———— ——–D 4.1 0 7.6 338 497 6.6D 8.2 1 1.0 47 56 .7D 12.3 1 1.0 46 55 .7D 16.3 2 1.0 45 54 .7D 20.4 2 1.0 45 54 .7D 24.5 3 1.0 45 54 .7D 28.6 3 1.0 45 53 .7D 32.7 4 1.0 45 53 .7D 36.8 4 1.0 45 53 .7D 40.8 5 1.0 45 53 .7D 44.9 5 1.0 45 53 .7D 481.0 6 1.0 45 53 .7D 521.1 6 1.0 45 53 .7D 561.2 7 1.0 45 53 .7D 601.3 7 1.0 45 53 .7D 641.3 8 1.0 45 53 .7D 681.4 8 1.0 45 53 .7D 721.5 9 1.0 45 53 .7D 761.6 9 1.0 45 53 .7D 801.7 10 1.0 45 53 .7通过以上4个时间段中Buffer Pool Advisory建议可以看的出来，对于增加Buffer cache的大小对性能的影响并不明显。8.8查看Time Model System Stats05:19:01~ 05:34:01时间段Time Model System StatsStatistic Time (s) % DB time———————————– ——————– ———DB CPU 440.5 119.9parse time elapsed 158.5 43.1sql execute elapsed time 145.1 39.5hard parse elapsed time 135.0 36.8connection management call elapsed 108.8 29.6PL/SQL execution elapsed time 5.7 1.6hard parse (sharing criteria) elaps 1.3 .3hard parse (bind mismatch) elapsed 1.2 .3PL/SQL compilation elapsed time 0.8 .2repeated bind elapsed time 0.4 .1sequence load elapsed time 0.1 .0DB time 367.4background elapsed time 75.1background cpu time 20.105:34:01 ~05:49:00时间段Time Model System StatsStatistic Time (s) % DB time———————————– ——————– ———DB CPU 455.9 124.3parse time elapsed 155.5 42.4sql execute elapsed time 149.9 40.9hard parse elapsed time 128.2 35.0connection management call elapsed 104.6 28.5PL/SQL execution elapsed time 6.8 1.9hard parse (sharing criteria) elaps 2.5 .7hard parse (bind mismatch) elapsed 2.4 .7PL/SQL compilation elapsed time 0.8 .2repeated bind elapsed time 0.5 .1sequence load elapsed time 0.3 .1DB time 366.8background elapsed time 54.4background cpu time 20.105:49:00 ~ 06:04:05时间段Time Model System StatsStatistic Time (s) % DB time———————————– ——————– ———DB CPU 463.3 122.2parse time elapsed 160.9 42.4sql execute elapsed time 158.6 41.9hard parse elapsed time 133.8 35.3connection management call elapsed 103.6 27.3PL/SQL execution elapsed time 7.3 1.9hard parse (sharing criteria) elaps 2.1 .6hard parse (bind mismatch) elapsed 1.9 .5PL/SQL compilation elapsed time 1.1 .3repeated bind elapsed time 0.5 .1sequence load elapsed time 0.2 .0DB time 379.0background elapsed time 52.7background cpu time 23.006:04:05 ~06:13:00时间段Time Model System StatsStatistic Time (s) % DB time———————————– ——————– ———DB CPU 269.2 119.5parse time elapsed 105.7 46.9sql execute elapsed time 102.9 45.6hard parse elapsed time 89.9 39.9connection management call elapsed 58.2 25.8PL/SQL execution elapsed time 4.0 1.8hard parse (sharing criteria) elaps 2.0 .9hard parse (bind mismatch) elapsed 1.6 .7PL/SQL compilation elapsed time 1.1 .5repeated bind elapsed time 0.6 .3sequence load elapsed time 0.1 .1DB time 225.4background elapsed time 19.6background cpu time 12.2通过对比4个报告各个时间段中的Time Model System Stats，发现产生的硬解析明显增加了。8.9查看Latch Sleep breakdown05:19:01~ 05:34:01时间段的Latch Sleep breakdownLatch Name Requests Misses Sleeps Gets————————– ————— ———— ———– ———–shared pool 3,787,761 4 4 005:34:01 ~05:49:00时间段的Latch Sleep breakdownLatch Name Requests Misses Sleeps Gets————————– ————— ———— ———– ———–shared pool 4,107,841 5 5 0JS Sh mem access 3 1 1 0enqueue hash chains 320,877 1 1 005:49:00 ~ 06:04:05时间段的Latch Sleep breakdownLatch Name Requests Misses Sleeps Gets————————– ————— ———— ———– ———–shared pool 4,257,852 8 8 0row cache objects 3,956,966 3 3 0call allocation 110,566 1 1 0redo allocation 99,927 1 1 006:04:05 ~06:13:00时间段的Latch Sleep breakdownLatch Name Requests Misses Sleeps Gets————————– ————— ———— ———– ———–shared pool 2,595,386 6 6 0row cache objects 2,500,734 1 1 0通过以上4个sp报告各个时间段的Latch Sleepbreakdown的内容，发现cache bufferslru chain已经没有了，但是shared pool次数上来了。

相关推荐: cursor pin S wait on X

A cursor wait is associated with parsing in some form. A session may wait for this event when it is trying to get a mutex pin in S…