7.解决方案一：调整buffer cache
sys@TESTDB12>alter system set sga_max_size=804m scope=spfile; //重启数据库
sys@TESTDB12>alter system set db_cache_size=64m; //buffer cache改为64M
7.1重新生成新的statspack报告
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
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:
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:
Enter value for end_snap:
Enter value for report_name:
7.2通过新生成的4个statspack报告对比各个时间段的数据缓冲区的命中率和库缓冲区的命中率：
时间 | Buffer Hit（%） | Library Hit（%） |
03:32:02~ 03:47:04 | 99.98 | 85.84 |
03:47:04 ~04:02:02 | 99.94 | 85.63 |
04:02:02~ 04:17:00 | 99.91 | 85.45 |
04:17:00~ 04:20:02 | 99.87 | 85.33 |
7.3查看Top 5 Timed Events找出4 个报告中各个时间段跟磁盘I/O相关的等待事件
时间 | name | Wait(s) | Time(s) |
03:32:02~ 03:47:04 | direct path read | 13,919,074 | 357 |
log file parallel write | 3,152 | 17 | |
log file sync | 776 | 16 | |
os thread startup | 4 | ||
log file sync | 157 | 3 | |
os thread startup | 4 | 1 |
直接读的等待数目下降明显，说明调整buffer cache的大小时起一定作用的。
7.4造成物理读最大的前几个sql语句在报告中未找到，用sql语句查询得出这些语句:select sql_text from v$sql where disk_reads=(select max(disk_reads)from v$sql);
时间 | Executions | Rows per Exec | Sql语句 | |
03:32:02~ 03:47:04 | 25,242 | 16.3 | select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket | |
3,392 | 11.3 | select name,intcol#,segcol#,type#,length,nvl(precision#,0),decod e(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180 ,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedsto rage,nvl(deflength,0),default$,rowid,col#,property, nvl(charseti | ||
5,272 | 2.2 | select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$ whe re obj#=:1 | ||
03:47:04 ~04:02:02 | 18,202 | 16.2 | select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket | |
2,640 | 11.3 | select name,intcol#,segcol#,type#,length,nvl(precision#,0),decod e(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180 ,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedsto rage,nvl(deflength,0),default$,rowid,col#,property, nvl(charseti | ||
04:02:02~ 04:17:00 | 45,327 | 16.4 | select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket | |
3,954 | 10.3 | select name,intcol#,segcol#,type#,length,nvl(precision#,0),decod e(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180 ,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedsto rage,nvl(deflength,0),default$,rowid,col#,property, nvl(charseti | ||
04:17:00~ 04:20:02 | 15,422 | 个时间段中Buffer Pool Advisory建议可以看的出来，和之前64的相比差别并不大 7.6查看Time Model System Stats
|