部署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;

 

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

 

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

| Id  | Operation                   | Name      | Rows | Bytes | Cost (%CPU)| Time     |

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

|   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     &n

本文名称:部署statspack工具(二)之解决方案2
本文网址:https://www.cdcxhl.com/article44/jieeee.html

成都网站建设公司_创新互联,为您提供微信公众号软件开发网站建设面包屑导航服务器托管

广告

声明:本网站发布的内容(图片、视频和文字)以用户投稿、用户转载内容为主,如果涉及侵权请尽快告知,我们将会在第一时间删除。文章观点不代表本网站立场,如需处理请联系客服。电话:028-86922220;邮箱:631063699@qq.com。内容未经允许不得转载,或转载时需注明来源: 创新互联