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: 61
Enter value for end_snap: 71
Enter value for report_name:
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 71
Enter value for end_snap: 81
Enter value for report_name:
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 81
Enter value for end_snap: 91
Enter value for report_name:
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 91
Enter value for end_snap: 101
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 |
33 |
6 |
|
| 03:47:04~04:02:02 |
direct path read |
13,876,758 |
355 |
| log file parallel write |
3,156 |
18 |
|
| log file sync |
771 |
17 |
|
| os thread startup |
38 |
6 |
|
| 04:02:02~04:17:00 |
direct path read |
13,577,002 |
356 |
| log file parallel write |
3,116 |
3,116 |
|
| log file sync |
789 |
18 |
|
| os thread startup |
35 |
8 |
|
| 04:17:00~04:20:02 |
direct path read |
2,707,590 |
71 |
| log file parallel write |
629 |
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 |
16.5 |
select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket |
| 1,714 |
10.8 |
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 |
通过对比各时间段最消耗资源的SQL语句，发现有相同或相似的执行计划，应该使用绑定变量。
生成语句的执行计划: set autotrace traceonly select * from scott.emp2
sys@TESTDB12>select* from scott.emp2;
20000000 rowsselected.
Execution Plan
----------------------------------------------------------
Plan hash value:2941272003
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20M| 1892M| 42422 (1)| 00:08:30 |
| 1 | TABLE ACCESS FULL| EMP2 | 20M| 1892M| 42422 (1)| 00:08:30 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement(level=2)
Statistics
----------------------------------------------------------
95 recursive calls
2 db block gets
1473014 consistent gets
147539 physical reads
0 redo size
1315677753 bytes sent via SQL*Net to client
14667186 bytes received via SQL*Net from client
1333335 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
20000000 rows processed
7.5查看Buffer Pool Advisory并把Buffer cache的大小设置为推荐的大小
03:32:02~ 03:47:04时间段的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 12.5 205 82 4.5
D 8 .2 1 1.3 21 7 .4
D 12 .3 1 1.2 20 7 .4
D 16 .3 2 1.1 18 6 .3
D 20 .4 2 1.0 17 6 .3
D 24 .5 3 1.0 17 5 .3
D 28 .6 3 1.0 17 5 .3
D 32 .7 4 1.0 17 5 .3
D 36 .8 4 1.0 17 5 .3
D 40 .8 5 1.0 17 5 .3
D 44 .9 5 1.0 16 5 .3
D 48 1.0 6 1.0 16 5 .3
D 52 1.1 6 1.0 16 5 .3
D 56 1.2 7 1.0 16 5 .3
D 60 1.3 7 1.0 16 5 .3
D 64 1.3 8 1.0 16 5 .3
D 68 1.4 8 1.0 16 5 .3
D 72 1.5 9 1.0 16 5 .3
D 76 1.6 9 1.0 16 5 .3
D 80 1.7 10 1.0 16 5 .3
03:47:04 ~04:02:02时间段的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 12.7 232 90 3.4
D 8 .2 1 1.3 24 8 .3
D 12 .3 1 1.2 22 7 .3
D 16 .3 2 1.1 20 7 .3
D 20 .4 2 1.0 19 6 .2
D 24 .5 3 1.0 19 6 .2
D 28 .6 3 1.0 19 6 .2
D 32 .7 4 1.0 19 6 .2
D 36 .8 4 1.0 18 6 .2
D 40 .8 5 1.0 18 6 .2
D 44 .9 5 1.0 18 6 .2
D 48 1.0 6 1.0 18 6 .2
D 52 1.1 6 1.0 18 6 .2
D 56 1.2 7 1.0 18 6 .2
D 60 1.3 7 1.0 18 6 .2
D 64 1.3 8 1.0 18 6 .2
D 68 1.4 8 1.0 18 6 .2
D 72 1.5 9 1.0 18 6 .2
D 76 1.6 9 1.0 18 6 .2
D 80 1.7 10 1.0 18 6 .2
04:02:02~ 04:17: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 12.7 275 98 2.7
D 8 .2 1 1.3 29 9 .3
D 12 .3 1 1.2 27 8 .2
D 16 .3 2 1.1 24 7 .2
D 20 .4 2 1.0 23 6 .2
D 24 .5 3 1.0 22 6 .2
D 28 .6 3 1.0 22 6 .2
D 32 .7 4 1.0 22 6 .2
D 36 .8 4 1.0 22 6 .2
D 40 .8 5 1.0 22 6 .2
D 44 .9 5 1.0 22 6 .2
D 48 1.0 6 1.0 22 6 .2
D 52 1.1 6 1.0 22 6 .2
D 56 1.2 7 1.0 21 6 .2
D 60 1.3 7 1.0 21 6 .2
D 64 1.3 8 1.0 21 6 .2
D 68 1.4 8 1.0 21 6 .2
D 72 1.5 9 1.0 21 6 .2
D 76 1.6 9 1.0 21 6 .2
D 80 1.7 10 1.0 21 6 .2
04:17:00~ 04:20:02时间段的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 12.7 289 113 3.0
D 8 .2 1 1.3 30 10 .3
D 12 .3 1 1.2 28 9 .2
D 16 .3 2 1.1 25 8 .2
D 20 .4 2 1.1 24 7 .2
D 24 .5 3 1.0 23 7 .2
D 28 .6 3 1.0 23 7 .2
D 32 .7 4 1.0 23 7 .2
D 36 .8 4 1.0 23 7 .2
D 40 .8 5 1.0 23 7 .2
D 44 .9 5 1.0 23 7 .2
D 48 1.0 6 1.0 23 7 .2
D 52 1.1 6 1.0 23 7 .2
D 56 1.2 7 1.0 22 7 .2
D 60 1.3 7 1.0 22 7 .2
D 64 1.3 8 1.0 22 7 .2
D 68 1.4 8 1.0 22 7 .2
D 72 1.5 9 1.0 22 7 .2
D 76 1.6 9 1.0 22 7 .2
D 80 1.7 10 1.0 22 7 .2
通过以上4个时间段中Buffer Pool Advisory建议可以看的出来，和之前64的相比差别并不大
7.6查看Time Model System Stats
| 03:32:02~ 03:47:04时间段Time Model System Stats |
| Statistic Time (s) % DB time ----------------------------------- -------------------- --------- DB CPU 855.6 97.0 sql execute elapsed time 853.6 96.8 parse time elapsed 28.4 3.2 hard parse elapsed time 26.5 3.0 connection management call elapsed 18.6 2.1 hard parse (sharing criteria) elaps 2.5 .3 hard parse (bind mismatch) elapsed 2.0 .2 PL/SQL execution elapsed time 1.3 .1 repeated bind elapsed time 0.6 .1 PL/SQL compilation elapsed time 0.3 .0 sequence load elapsed time 0.1 .0 DB time 881.9 background elapsed time 30.1 background cpu time 2.7 03:47:04 ~04:02:02时间段Time Model System Stats |
| Statistic Time (s) % DB time ----------------------------------- -------------------- --------- DB CPU 848.3 97.8 sql execute elapsed time 839.7 96.8 parse time elapsed 22.1 2.5 hard parse elapsed time 20.2 2.3 connection management call elapsed 18.6 2.1 hard parse (sharing criteria) elaps 1.7 .2 hard parse (bind mismatch) elapsed 1.5 .2 PL/SQL execution elapsed time 0.9 .1 PL/SQL compilation elapsed time 0.3 .0 repeated bind elapsed time 0.2 .0 sequence load elapsed time 0.2 .0 DB time 867.7 background elapsed time 33.1 background cpu time 4.2 04:02:02~ 04:17:00时间段Time Model System Stats |
| Statistic Time (s) % DB time ----------------------------------- -------------------- --------- sql execute elapsed time 857.0 96.2 DB CPU 847.3 95.1 parse time elapsed 40.9 4.6 hard parse elapsed time 38.4 4.3 connection management call elapsed 20.7 2.3 hard parse (sharing criteria) elaps 3.3 .4 hard parse (bind mismatch) elapsed 2.4 .3 PL/SQL execution elapsed time 1.3 .1 PL/SQL compilation elapsed time 0.9 .1 repeated bind elapsed time 0.5 .1 sequence load elapsed time 0.1 .0 DB time 891.2 background elapsed time 34.0 background cpu time 2.8 04:17:00~ 04:20:02时间段Time Model System Stats |
| Statistic Time (s) % DB time ----------------------------------- -------------------- --------- sql execute elapsed time 176.7 96.3 DB CPU 172.3 93.9 parse time elapsed 11.7 6.4 hard parse elapsed time 11.3 6.2 connection management call elapsed 4.4 2.4 hard parse (sharing criteria) elaps 1.1 .6 hard parse (bind mismatch) elapsed 0.8 .4 PL/SQL compilation elapsed time 0.3 .2 repeated bind elapsed time 0.3 .1 PL/SQL execution elapsed time 0.2 .1 sequence load elapsed time 0.1 .0 DB time 183.5 background elapsed time 6.5 background cpu time 0.6 |
通过对比4个报告各个时间段中的Time Model System Stats，发现产生的硬解析比较少，正常。
7.7查看Latch Sleep breakdown
| 03:32:02~ 03:47:04时间段的Latch Sleep breakdown |
| Latch Name Requests Misses Sleeps Gets -------------------------- --------------- ------------ ----------- ----------- shared pool 714,647 4 4 0 shared pool simulator 41,269 1 1 0 row cache objects 888,589 1 1 0 |
| 03:47:04 ~04:02:02时间段的Latch Sleep breakdown |
| Latch Name Requests Misses Sleeps Gets -------------------------- --------------- ------------ ----------- ----------- shared pool 656,069 3 3 0 row cache objects 772,725 1 1 0 |
| 04:02:02~ 04:17:00时间段的Latch Sleep breakdown |
| Latch Name Requests Misses Sleeps Gets -------------------------- --------------- ------------ ----------- ----------- shared pool 949,238 1 1 0 |
| 04:17:00~ 04:20:02时间段的Latch Sleep breakdown |
| Get Spin Latch Name Requests Misses Sleeps Gets -------------------------- --------------- ------------ ----------- ----------- cache buffers chains 1,443,098 1 1 0 |
通过以上4个sp报告各个时间段的Latch Sleepbreakdown的内容，发现cache bufferslru chain的miss和sleep的次数并不多。
本文转自 dbapower 51CTO博客，原文链接：http://blog.51cto.com/suifu/1539796，如需转载请自行联系原作者
