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
|