解决方案二：在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 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 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 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 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
----------------------------------------------------------
55 recursive calls
0 db block gets
78 consistent gets
4 physical reads
0 redo size
1033 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
1 rows processed
8.7查看Buffer Pool Advisory并把Buffer cache的大小设置为推荐的大小
05:19:01~ 05:34:01时间段的Buffer PoolAdvisory
Est
Phys Estimated Est
Size for Size Buffers Read Phys Reads Est Phys % dbtime
P Est (M) Factr (thousands) Factr (thousands) Read Time for 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 48 1.0 6 1.0 32 33 .5
D 52 1.1 6 1.0 32 33 .5
D 56 1.2 7 1.0 32 33 .5
D 60 1.3 7 1.0 32 33 .5
D 64 1.3 8 1.0 32 33 .5
D 68 1.4 8 1.0 32 33 .5
D 72 1.5 9 1.0 32 33 .5
D 76 1.6 9 1.0 32 33 .5
D 80 1.7 10 1.0 32 33 .5
05:34:01 ~05:49:00时间段的Buffer PoolAdvisory
Est
Phys Estimated Est
Size for Size Buffers Read Phys Reads Est Phys % dbtime
P Est (M) Factr (thousands) Factr (thousands) Read Time for 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 48 1.0 6 1.0 35 36 .5
D 52 1.1 6 1.0 35 36 .5
D 56 1.2 7 1.0 35 36 .5
D 60 1.3 7 1.0 35 36 .5
D 64 1.3 8 1.0 35 36 .5
D 68 1.4 8 1.0 35 36 .5
D 72 1.5 9 1.0 35 36 .5
D 76 1.6 9 1.0 35 36 .5
D 80 1.7 10 1.0 35 36 .5
05:49:00 ~ 06:04:05时间段的Buffer PoolAdvisory
Est
Phys Estimated Est
Size for Size Buffers Read Phys Reads Est Phys % dbtime
P Est (M) Factr (thousands) Factr (thousands) Read Time for 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 48 1.0 6 1.0 40 46 .6
D 52 1.1 6 1.0 40 46 .6
D 56 1.2 7 1.0 40 46 .6
D 60 1.3 7 1.0 40 46 .6
D 64 1.3 8 1.0 40 46 .6
D 68 1.4 8 1.0 40 46 .6
D 72 1.5 9 1.0 40 46 .6
D 76 1.6 9 1.0 40 46 .6
D 80 1.7 10 1.0 40 46 .6
06:04:05 ~06:13:00时间段的Buffer PoolAdvisory
Est
Phys Estimated Est
Size for Size Buffers Read Phys Reads Est Phys % dbtime
P Est (M) Factr (thousands) Factr (thousands) Read Time for 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 48 1.0 6 1.0 45 53 .7
D 52 1.1 6 1.0 45 53 .7
D 56 1.2 7 1.0 45 53 .7
D 60 1.3 7 1.0 45 53 .7
D 64 1.3 8 1.0 45 53 .7
D 68 1.4 8 1.0 45 53 .7
D 72 1.5 9 1.0 45 53 .7
D 76 1.6 9 1.0 45 53 .7
D 80 1.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次数上来了。
