解决方案二：在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:
Enter value for end_snap:
Enter value for report_name:
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:
Enter value for end_snap:
Enter value for report_name:
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:
Enter value for end_snap:
Enter value for report_name:
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:
Enter value for end_snap:
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 个报告的对比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);
|