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

最新推荐文章于 2021-08-05 07:50:00 发布

岁伏的博客

最新推荐文章于 2021-08-05 07:50:00 发布

阅读量192

点赞数

分类专栏： Oracle

本文链接：https://blog.csdn.net/carry9148/article/details/52252679

版权

Oracle 专栏收录该内容

36 篇文章 0 订阅

订阅专栏

解决方案二：在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;

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: 131

Enter value for end_snap: 141

Enter value for report_name:

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 141

Enter value for end_snap: 151

Enter value for report_name:

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 151

Enter value for end_snap: 161

Enter value for report_name:

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 161

Enter value for end_snap: 171

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	332	3
05:34:01 ~05:49:00	log file parallel write	48,413	36
	log file sync	3,563	28
	os thread startup	33	5
	db file sequential read	2,018	2
05:49:00 ~ 06:04:05	log file parallel write	49,564	23
	log file sync	455	15
	db file sequential read	3,955	9
	os thread startup	39	6
06:04:05 ~06:13:00	log file parallel write	28,273	8
	db file sequential read	2,928	5
	log file sync	231	4
	os thread startup	21	3

通过4个报告的对比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);

时间	Executions	Rows per Exec	Sql语句
05:19:01~ 05:34:01	10,840	16.1	select /+ rule / bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket


05:34:01 ~05:49:00	12,565	16.1	select /+ rule / bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket

05:49:00 ~ 06:04:05	15,112	16.0	select /+ rule / bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket

06:04:05 ~06:13:00	20,814	16.4	select /+ rule / bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket

通过对比各时间段最消耗资源的SQL语句，发现仍有相同或相似的执行计划，应该使用绑定变量，来提高执行效率。

生成语句的执行计划: set autotrace traceonly select * from scott.emp2

idle>select *from scott.emp2 where empno=1484;

Execution Plan

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

Plan hash value:2918945472

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

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

| 0 | SELECT STATEMENT | | 1 | 48 | 4 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP2 | 1 | 48 | 4 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN |IND_EMPNO | 1 | | 3 (0)| 00:00:01 |

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

PredicateInformation (identified by operation id):

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

2 - access("EMPNO"=1484)

Statistics

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

55 recursive calls

0 db block gets

78 consistent gets

4 physical reads

0 redo size

1033 bytes sent via SQL*Net to client

523 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

7 sorts (memory)

0 sorts (disk)

1 rows processed

8.7查看Buffer Pool Advisory并把Buffer cache的大小设置为推荐的大小

05:19:01~ 05:34:01时间段的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 8.0 261 345 5.2

D 8 .2 1 1.1 35 37 .6

D 12 .3 1 1.1 34 36 .5

D 16 .3 2 1.0 33 35 .5

D 20 .4 2 1.0 33 34 .5

D 24 .5 3 1.0 33 34 .5

D 28 .6 3 1.0 33 34 .5

D 32 .7 4 1.0 33 33 .5

D 36 .8 4 1.0 33 33 .5

D 40 .8 5 1.0 32 33 .5

D 44 .9 5 1.0 32 33 .5

D 48 1.0 6 1.0 32 33 .5

D 52 1.1 6 1.0 32 33 .5

D 56 1.2 7 1.0 32 33 .5

D 60 1.3 7 1.0 32 33 .5

D 64 1.3 8 1.0 32 33 .5

D 68 1.4 8 1.0 32 33 .5

D 72 1.5 9 1.0 32 33 .5

D 76 1.6 9 1.0 32 33 .5

D 80 1.7 10 1.0 32 33 .5

05:34:01 ~05:49: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 7.8 273 357 5.1

D 8 .2 1 1.1 37 39 .6

D 12 .3 1 1.0 37 38 .5

D 16 .3 2 1.0 36 37 .5

D 20 .4 2 1.0 35 37 .5

D 24 .5 3 1.0 35 36 .5

D 28 .6 3 1.0 35 36 .5

D 32 .7 4 1.0 35 36 .5

D 36 .8 4 1.0 35 36 .5

D 40 .8 5 1.0 35 36 .5

D 44 .9 5 1.0 35 36 .5

D 48 1.0 6 1.0 35 36 .5

D 52 1.1 6 1.0 35 36 .5

D 56 1.2 7 1.0 35 36 .5

D 60 1.3 7 1.0 35 36 .5

D 64 1.3 8 1.0 35 36 .5

D 68 1.4 8 1.0 35 36 .5

D 72 1.5 9 1.0 35 36 .5

D 76 1.6 9 1.0 35 36 .5

D 80 1.7 10 1.0 35 36 .5

05:49:00 ~ 06:04:05时间段的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 7.6 302 438 6.0

D 8 .2 1 1.1 42 49 .7

D 12 .3 1 1.0 41 48 .7

D 16 .3 2 1.0 40 47 .6

D 20 .4 2 1.0 40 46 .6

D 24 .5 3 1.0 40 46 .6

D 28 .6 3 1.0 40 46 .6

D 32 .7 4 1.0 40 46 .6

D 36 .8 4 1.0 40 46 .6

D 40 .8 5 1.0 40 46 .6

D 44 .9 5 1.0 40 46 .6

D 48 1.0 6 1.0 40 46 .6

D 52 1.1 6 1.0 40 46 .6

D 56 1.2 7 1.0 40 46 .6

D 60 1.3 7 1.0 40 46 .6

D 64 1.3 8 1.0 40 46 .6

D 68 1.4 8 1.0 40 46 .6

D 72 1.5 9 1.0 40 46 .6

D 76 1.6 9 1.0 40 46 .6

D 80 1.7 10 1.0 40 46 .6

06:04:05 ~06:13: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 7.6 338 497 6.6

D 8 .2 1 1.0 47 56 .7

D 12 .3 1 1.0 46 55 .7

D 16 .3 2 1.0 45 54 .7

D 20 .4 2 1.0 45 54 .7

D 24 .5 3 1.0 45 54 .7

D 28 .6 3 1.0 45 53 .7

D 32 .7 4 1.0 45 53 .7

D 36 .8 4 1.0 45 53 .7

D 40 .8 5 1.0 45 53 .7

D 44 .9 5 1.0 45 53 .7

D 48 1.0 6 1.0 45 53 .7

D 52 1.1 6 1.0 45 53 .7

D 56 1.2 7 1.0 45 53 .7

D 60 1.3 7 1.0 45 53 .7

D 64 1.3 8 1.0 45 53 .7

D 68 1.4 8 1.0 45 53 .7

D 72 1.5 9 1.0 45 53 .7

D 76 1.6 9 1.0 45 53 .7

D 80 1.7 10 1.0 45 53 .7

通过以上4个时间段中Buffer Pool Advisory建议可以看的出来，对于增加Buffer cache的大小对性能的影响并不明显。

8.8查看Time Model System Stats

05:19:01~ 05:34:01时间段Time Model System Stats

Statistic Time (s) % DB time

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

DB CPU 440.5 119.9

parse time elapsed 158.5 43.1

sql execute elapsed time 145.1 39.5

hard parse elapsed time 135.0 36.8

connection management call elapsed 108.8 29.6

PL/SQL execution elapsed time 5.7 1.6

hard parse (sharing criteria) elaps 1.3 .3

hard parse (bind mismatch) elapsed 1.2 .3

PL/SQL compilation elapsed time 0.8 .2

repeated bind elapsed time 0.4 .1

sequence load elapsed time 0.1 .0

DB time 367.4

background elapsed time 75.1

background cpu time 20.1

05:34:01 ~05:49:00时间段Time Model System Stats

Statistic Time (s) % DB time

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

DB CPU 455.9 124.3

parse time elapsed 155.5 42.4

sql execute elapsed time 149.9 40.9

hard parse elapsed time 128.2 35.0

connection management call elapsed 104.6 28.5

PL/SQL execution elapsed time 6.8 1.9

hard parse (sharing criteria) elaps 2.5 .7

hard parse (bind mismatch) elapsed 2.4 .7

PL/SQL compilation elapsed time 0.8 .2

repeated bind elapsed time 0.5 .1

sequence load elapsed time 0.3 .1

DB time 366.8

background elapsed time 54.4

background cpu time 20.1

05:49:00 ~ 06:04:05时间段Time Model System Stats

Statistic Time (s) % DB time

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

DB CPU 463.3 122.2

parse time elapsed 160.9 42.4

sql execute elapsed time 158.6 41.9

hard parse elapsed time 133.8 35.3

connection management call elapsed 103.6 27.3

PL/SQL execution elapsed time 7.3 1.9

hard parse (sharing criteria) elaps 2.1 .6

hard parse (bind mismatch) elapsed 1.9 .5

PL/SQL compilation elapsed time 1.1 .3

repeated bind elapsed time 0.5 .1

sequence load elapsed time 0.2 .0

DB time 379.0

background elapsed time 52.7

background cpu time 23.0

06:04:05 ~06:13:00时间段Time Model System Stats

Statistic Time (s) % DB time

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

DB CPU 269.2 119.5

parse time elapsed 105.7 46.9

sql execute elapsed time 102.9 45.6

hard parse elapsed time 89.9 39.9

connection management call elapsed 58.2 25.8

PL/SQL execution elapsed time 4.0 1.8

hard parse (sharing criteria) elaps 2.0 .9

hard parse (bind mismatch) elapsed 1.6 .7

PL/SQL compilation elapsed time 1.1 .5

repeated bind elapsed time 0.6 .3

sequence load elapsed time 0.1 .1

DB time 225.4

background elapsed time 19.6

background cpu time 12.2

通过对比4个报告各个时间段中的Time Model System Stats，发现产生的硬解析明显增加了。

8.9查看Latch Sleep breakdown

05:19:01~ 05:34:01时间段的Latch Sleep breakdown

Latch Name Requests Misses Sleeps Gets

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

shared pool 3,787,761 4 4 0

05:34:01 ~05:49:00时间段的Latch Sleep breakdown

Latch Name Requests Misses Sleeps Gets

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

shared pool 4,107,841 5 5 0

JS Sh mem access 3 1 1 0

enqueue hash chains 320,877 1 1 0

05:49:00 ~ 06:04:05时间段的Latch Sleep breakdown

Latch Name Requests Misses Sleeps Gets

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

shared pool 4,257,852 8 8 0

row cache objects 3,956,966 3 3 0

call allocation 110,566 1 1 0

redo allocation 99,927 1 1 0

06:04:05 ~06:13:00时间段的Latch Sleep breakdown

Latch Name Requests Misses Sleeps Gets

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

shared pool 2,595,386 6 6 0

row cache objects 2,500,734 1 1 0

通过以上4个sp报告各个时间段的Latch Sleepbreakdown的内容，发现cache bufferslru chain已经没有了，但是shared pool次数上来了。

岁伏的博客

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
部署statspack工具（二）之解决方案2

解决方案二：在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自动快照 {[email protected]
复制链接

扫一扫