部署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自动快照

{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

新闻名称：部署statspack工具（二）之解决方案2
URL分享：http://cdxtjz.cn/article/jieeee.html

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

其他资讯