部署statspack工具（二）之解决方案1-CSDN博客

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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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，如需转载请自行联系原作者

部署statspack工具（二）之解决方案1

“相关推荐”对你有帮助么？