9istatspack使用手册

最新推荐文章于 2021-04-06 06:20:55 发布

转载最新推荐文章于 2021-04-06 06:20:55 发布 · 93 阅读

内容包括
  statspack安装
  statspack报告的详细说明

Statspack  使用说明

一．statspack系统安装 1
二．Statspack介绍 2
三．Statspack使用 4
四．Statspack报告说明 5

一．statspack系统安装
statspack的安装程序在@?/rdbms/admin/下:
spcreate.sql  安装
spdrop.sql 　卸载
spauto.sql:　生成自动采集数据任务
spreport.sql 　生成分析报告
spuexp.sql 　参数文件
sppurge.sql　清除不在需要的数据
sptrunc.sql 　清除所有的数据
sprepsql.sql:用于根据给定的SQL HASH值生成SQL报告

为了能顺利地运行Statspack 工具，则需要设置以下参数：
job_queue_processes>0  (如果不采用自动采集数据则可不设)
timed_statistics=true (如果不采用自动采集数据则可不设)

安装
1.建一个单独的表空间用于Statspack 或建在tools表空间上(>100MB)
$sqlplus “/ as sysdba”
SQL>create tablespace perfstat
SQL>datafile ‘/oracle/oradata/perfstat.dbf’
SQL>size 500M extent management local;
2.建用户perfstat及表
　SQL>@?/rdbms/admin/spcreate.sql
要求输入表空间及临时表空间.
建完后会在本目录下生成：
　　  spauto.lis
          spcpkg.lis
          spctab.lis
          spcusr.lis
spdtab.lis
spdusr.lis
grep –I “ora-“ *.lis 查看是否有错。

3．删除statspack表
SQL>@?/rdbms/admin/spdrop.sql
4.测试statspack
采样数据
SQL> exec statspack.snap
  后隔几分钟后再次采样数据
SQL> exec statspack.snap
           生成报表　
SQL>@?/rdbms/admin/spreport.sql

二．Statspack介绍
Oracle9i调优顺序一般采用自顶向下的顺序：

Statspack源于utlbstat和utlestat工具，在执行快照时，statspack会从 SGA内部的RAW内存结构中来采样数据，并将结果存入相应表中。
RAW　v$内存结构表    à    statspack   stats$内存结构表
　　V$sysstat stats$sysstat
        V$sgastat                               stats$sgastat
        V$parameter                          stats$parameter
         V$librarycache                      stats$librarycache
1．外部环境
内存
vmstat
bash-2.03$ vmstat 2 5
procs     memory            page            disk          faults      cpu
r b w   swap  free  re  mf pi po fr de sr s0 s1 s1 --   in   sy   cs us sy id
0 0 0 9868032 2120968 14 3 159 8  8  0  0  0  0 24  0  306  455 1204  1  2 97
0 1 0 9813224 2154792 29 6  0 20 20  0  0  0  0 39  0  388 9587 2353  3  3 94
0 0 0 9813224 2154296 39 0  0 40 40  0  0  0  1 46  0  393 9529 2363  5  3 92
0 1 0 9813224 2153736 36 3  0 20 20  0  0  0  0 48  0  397 9387 2364  3  3 93
0 0 0 9813224 2153232 37 3  0 20 20  0  0  0  0 48  0  397 9483 2360  4  3 93
其中：
CPU瓶颈:
proc下：
     r:正在运行的任务队列，当r>CPU数量时，则会出现CPU等待瓶颈　
查看CPU个数：
psrinfo –v|grep –I “status of processor” | wc -l
RAM瓶颈:
  Page下：
Pi:页导入次数：如果RAW短缺时，系统会产生pi操作
查看内存容量
　　prtconf|grep –i  “mem”

2．系统IO
   sar –d
bash-2.03$ sar -d 2 2

SunOS mydb001 5.8 Generic_108528-13 sun4us    01/09/04

08:35:11   device        %busy   avque   r+w/s  blks/s  avwait  avserv

08:35:13   nfs1              0     0.0       0       0     0.0     0.0
           sd0               0     0.0       0       0     0.0     0.0
           sd1               0     0.0       0      40     0.0     6.3
           sd1,a             0     0.0       0      40     0.0     6.3
           sd1,c             0     0.0       0       0     0.0     0.0
           sd1,d             0     0.0       0       0     0.0     0.0
           sd1,e             0     0.0       0       0     0.0     0.0
           sd1,f             0     0.0       0       0     0.0     0.0
           sd16             28     0.5      51     817     0.0    10.2
           sd16,a           28     0.5      51     817     0.0    10.2
           sd16,c            0     0.0       0       0     0.0     0.0
           ohci0,bu          0     0.0       0       0     0.0     0.0
           ohci0,ct          0     0.0       0       0     0.0     0.0
           ohci0,in          0     0.0       0       0     0.0     0.0
           ohci0,is          0     0.0       0       0     0.0     0.0
           ohci0,to          0     0.0       0       0     0.0     0.0

08:35:15   nfs1              0     0.0       0       0     0.0     0.0
           sd0               0     0.0       0       0     0.0     0.0
           sd1               0     0.0       1      79     0.0     4.9
           sd1,a             0     0.0       1      79     0.0     4.9
           sd1,c             0     0.0       0       0     0.0     0.0
           sd1,d             0     0.0       0       0     0.0     0.0
           sd1,e             0     0.0       0       0     0.0     0.0
           sd1,f             0     0.0       0       0     0.0     0.0
           sd16             27     0.5      47     745     0.0    10.4
           sd16,a           27     0.5      47     745     0.0    10.4
           sd16,c            0     0.0       0       0     0.0     0.0
           ohci0,bu          0     0.0       0       0     0.0     0.0
           ohci0,ct          0     0.0       0       0     0.0     0.0
           ohci0,in          0     0.0       0       0     0.0     0.0
           ohci0,is          0     0.0       0       0     0.0     0.0
           ohci0,to          0     0.0       0       0     0.0     0.0

Average    nfs1              0     0.0       0       0     0.0     0.0
           sd0               0     0.0       0       0     0.0     0.0
           sd1               0     0.0       1      59     0.0     5.4
           sd1,a             0     0.0       1      59     0.0     5.4
           sd1,c             0     0.0       0       0     0.0     0.0
           sd1,d             0     0.0       0       0     0.0     0.0
           sd1,e             0     0.0       0       0     0.0     0.0
           sd1,f             0     0.0       0       0     0.0     0.0
           sd16             27     0.5      49     781     0.0    10.3
           sd16,a           27     0.5      49     781     0.0    10.3
           sd16,c            0     0.0       0       0     0.0     0.0
           ohci0,bu          0     0.0       0       0     0.0     0.0
           ohci0,ct          0     0.0       0       0     0.0     0.0
           ohci0,in          0     0.0       0       0     0.0     0.0
           ohci0,is          0     0.0       0       0     0.0     0.0
           ohci0,to          0     0.0       0       0     0.0     0.0
说明：
   一般%busy高些，%avque低些，文件系统的效率会较高,目前系统文件系统效率已达到

三．Statspack使用
3．手工采样数据
sqlplus perfstat/perfstat

SQL> exec statspack.snap
  后隔几分钟后再次采样数据
SQL> exec statspack.snap
           生成报表　
SQL>@?/rdbms/admin/spreport.sql

4．系统自动采样数据
定义定时任务
  修改spauto.sql内容，定义采样数据的时间间隔
dbms_job.submit(:jobno,’statspack.snap;’,trunc(sysdate+1/24,”HH”),’trunc(sysdate+1/24,”HH”),TRUE,:instno);
  一天24小时，1440分钟，则：
每小时一次： 1/24    (建议使用)
每30分钟一次：　 1/48
每10分钟一次 1/144
每5分钟一次 1/288
后执行
   SQL>＠?/rdbms/admin/spauto.sql

生成分析报告
SQL>＠?/rdbms/admin/spreport.sql

停止定时任务
sqlplus perfstat/perfstat
SQL>select job,interval from user_jobs;
SQL>exec dbms_job.remove(‘:job_no’);

删除历史数据
　delete from stats$snapshot where snap_id   删除全部数据
　　SQL>＠?/rdbms/admin/sptrunc.sql

四．Statspack报告说明
Statspack报告分为几个部分：
5．数据库总体信息
6．每秒每事务的资源消耗情况
7．实例的各组件的命中率
8．共享池总体情况
9．前5个等待事件
10． DB所有等待事件
11．后台进程等待事件
12．根据BufferGets进行排序的SQL
13．按物理IO进行排序的SQL
14．按执行次数排序的SQL
15．按分析次数排序的SQL
16．实例的当前活动的统计数据
17． tablespace IO统计数据
18．表空间文件 IO统计数据
19． buffer池统计数据
20．实例恢复统计数据
21． Buffer池的参考数据
22． Buffer等待统计数据
23． PGA总体统计数据 1
24． PGA总体统计数据2
25． PGA内存参考数据
26．回滚段统计
27．回滚段存储统计
28． undo段总体情况
29． undo段统计
30．锁存器的当前情况
31．锁存器睡眠等待统计
32．锁存器失败情况
33．数据字典cache性能统计
34．库cache 性能统计
35．共享池性能统计
36． SGA区总体情况
37． SGA各组件的活动情况
38．系统配置参数

STATSPACK report for
------------------------------------1.DB的总体信息----------------------------------------------------
DB Name         DB Id           Instance     Inst Num  Release  Cluster Host
------------ -----------       ------------         --------     ----------- ------- ------------
MYDB      2125240762  mydb          1      9.2.0.1.0    NO      VCS-SERVER1

                     Snap Id     Snap Time            Sessions Curs/Sess Comment
                    -------    ------------------             -------     --------- -------------------
Begin Snap:     1        09-Aug-04 19:28:12         32        2.7
  End Snap:      2       09-Aug-04 19:33:06         32        3.0
   Elapsed:                4.90 (mins)  (本次报告的间隔时间)

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
                Buffer Cache:     1,536M        Std Block Size:         8K
           Shared Pool Size:       112M          Log Buffer:    16,000K

--------------------------2.每秒每事务的资源消耗情况---------------------
Load Profile
~~~~~~~~~~~~                      Per Second (每秒)      Per Transaction(每事务)
                                                  ---------------            ---------------
                    Redo size:                 38,498.93            6,733.30 –每秒/每事务产生的redo大小
              Logical reads:                    593.28                103.76 –每秒/每事务逻辑读
              Block changes:                    77.60                 13.57 –每秒/每事务修改的块数
             Physical reads:                        2.65                  0.46 --  每秒/每事务物理读
            Physical writes:                       8.17                  1.43 —每秒/每事务物理写
                    User calls:                     38.32                  6.70
                         Parses:                        6.52                  1.14  --SQL分析的次数
                Hard parses:                        0.05                  0.01 –SQL硬分析的次数
                          Sorts:                        0.73                  0.13--
                      Logons:                       0.01                  0.00
                    Executes:                     39.64                  6.93
              Transactions:                       5.72

  % Blocks changed per Read:   13.08    Recursive Call %:    24.84
Rollback per transaction %:    0.00       Rows per Sort:   138.04
说明：
硬分析：就是之前不存在此SQL，是第一次解析。如果SQL重用度很高，则硬解析应保持很低。
% Blocks changed per Read：表示逻辑读用于只读而不是修改的块的比例
Recursive Call %:递归调用SQL的比例，在PL/SQL上执行的SQL称为递归的SQL
Rollback per transaction %:    事务的回滚率
Rows per Sort:   每次排序的记录行数

-----------------------------------3.实例的各组件的命中率-----------------------------------
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00                Redo NoWait %:  100.00
               Buffer  Hit   %:   99.55              In-memory Sort %:  100.00
              Library Hit   %:   99.33                        Soft Parse %:   99.16
        Execute to Parse %:   83.56                         Latch Hit %:   99.99
Parse CPU to Parse Elapsd %:                   % Non-Parse CPU:
说明：
   Execute to Parse %:   是语句执行与分析的比例，如果要SQL重用率高，则这个比例会很高。
  Soft Parse %:软分析：即在共享池中重复使用的SQL，系统应保持较高的软分析率，否则说明系统的SQL没有绑定变量。
Parse CPU to Parse Elapsd %:  用于分析每个CPU 花费的秒数，应该处于较高比例。如果=100%,说明CPU没有等待。

---------------------------------4.共享池总体情况---------------------------------------------------------
Shared Pool Statistics                   Begin     End
                                                     ------      ------
             Memory Usage %:            89.91     90.55
    % SQL with executions>1:       32.14      32.67
  % Memory for SQL w/exec>1:   31.30     33.38
说明：
  Memory Usage %:   正在使用的共享池的%,这个值应保持在75%~90%,如果这个值太低，就浪费内存，如果太高，会使共享池外部的组件老化，如果SQL语句被再次执行，则就会发生硬分析。
% SQL with executions>1:共享池中有多少执行次数大于一次的SQL语句的度量。
% Memory for SQL w/exec>1:   频繁使用的SQL语句消耗内存多少的比例。

------------------------------5.前5个等待事件------------------------------------------------
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                      % Total
Event                                                 Waits        Time (s)    Ela Time
-------------------------------------------- ------------ ----------- --------
db file sequential read                         623           3              46.70
log file sync                                       1,682           2             32.30
control file parallel write                      95             1             8.16
db file parallel write                            190             0          6.09
log file parallel write                             1,674           0         5.77
          -------------------------------------------------------------
说明：
log file sync:当一个用户的会话提交时，会话的重写信息需要刷新到重做日志文件中，这个用户会话将发送LGWR将日志缓冲写到重做日志文件，当LGWR已经完成写入操作时，它将发送这个用户会话。
Wait Time:等待时间包括日志缓冲的写入和发送操作。

--------------------------------------6.DB所有等待事件-------------------------------------------------
Wait Events for DB: MYDB  Instance: mydb  Snaps: 1 -2
-> s  - second
-> cs - centisecond -     100th of a second
-> ms - millisecond -    1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)

                                                                                                            Avg
                                                                                        Total Wait   wait    Waits
Event                                                Waits     Timeouts   Time (s)   (ms)     /txn
----------------------------                 ------------   ---------- ---------- ------ --------
db file sequential read                          623          0               3            5          0.4
log file sync                                        1,682         0               2            1         1.0
control file parallel write                       95           0               1            6         0.1
db file parallel write                            190           95             0            2          0.1
log file parallel write                         1,674        1,664          0             0         1.0
db file scattered read                            25            0               0             2          0.0
control file sequential read                   78            0               0            0           0.0
LGWR wait for redo copy                    13            0               0            0          0.0
SQL*Net break/reset to clien                 4             0              0            0           0.0
buffer busy waits                                   2              0               0           0          0.0
latch free                                                1              0               0           0           0.0
SQL*Net message from client            10,830        0           4,364       403       6.4
SQL*Net more data from clien           1,596          0              0            0          0.9
SQL*Net message to client               10,830          0              0            0        6.4
          -------------------------------------------------------------

--------------------------------7.后台进程等待事件-----------------------------------------
Background Wait Events for DB: MYDB  Instance: mydb  Snaps: 1 -2
-> ordered by wait time desc, waits desc (idle events last)

                                                                                                     Avg
                                                                                 Total Wait   wait    Waits
Event                                           Waits   Timeouts   Time (s)     (ms)     /txn
----------------------------            ------------ ----------  ----------      ------ --------
control file parallel write                95          0                1               6      0.1
db file parallel write                      190         95              0               2      0.1
log file parallel write                    1,674      1,664          0               0      1.0
control file sequential read             36          0                0               0      0.0
LGWR wait for redo copy              13          0                0               0      0.0
rdbms ipc message                        5,352      3,687      1,148         214    3.2
smon timer                                      1            1              281     ######      0.0
          -------------------------------------------------------------

------------------8.根据BufferGets进行排序的SQL-----------------------------------
SQL ordered by Gets for DB: MYDB  Instance: mydb  Snaps: 1 -2
-> End Buffer Gets Threshold:   10000
-> Note that resources reported for PL/SQL includes the resources used by
   all SQL statements called within the PL/SQL code.  As individual SQL
   statements are also reported, it is possible and valid for the summed
   total % to exceed 100

                                                                                              CPU    Elapsd
  Buffer Gets     Executions   Gets per Exec   %Total Time (s)  Time (s)  Hash Value
---------------        ------------        --------------        ------     --------    ---------        ----------
         74,380           20                    3,719.0             42.6     0.00       5.03         1027916473
select count(*) from myuser.userbaseinfo

         10,920        1,291                   8.5                   6.3       0.00        0.71         1385081364
insert into Refence_tabvalues(:p1, :p2, :p3, :p4, :p5,:p6)

         10,629          132                 80.5                    6.1      0.00         0.49        2785281485
update msginfo set Orig_Addr=:p1,Dest_Addr=:p2,service_type=:p3
,sub_serv_type=:p4,TransactionID=:p5,Priority=:p6,state=:p7,Send
Count=:p8,errorCode=:p9,finalDate=:p10,smFlag=:p11,tVaspId=:p12,
tVasId=:p13,tServiceCode=:p14,DateTime=:p15,DeliveryTime=:p16,Re
adReply=:p17,bAdaptations=:p18,ContentType=:p19,bMsgDistributeIn

          9,751        2,435                  4.0                     5.6     0.00          0.70        2271041384
select * from msginfo where msg_id=:p1

          9,625        2,907                  3.3                     5.5     0.00         0.93         1077832894
select * from userinfo where sub_isdn=:p1

          5,824        1,962                  3.0                     3.3     0.00         1.72        2431777133
select * from destinfo where sub_isdn=:p1

          5,787        1,156                 5.0                      3.3     0.00        0.55          3134087587
select * from msginfo_all where msg_id=:p1

          4,648           90                  51.6                     2.7     0.00        0.51         1112211039
begin smsc_util.modify_destinfo(:p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8,:p9,:p10,:p11,:p12,:p13,:p14); end;

          4,031           90                  44.8                      2.3     0.00      0.23           3842824015
UPDATE destinfo SET  sub_isdn  = :b14,  NonMmsEmailAddr = :b13, sub_state= :b12,
          local_time = :b11, desiredTime =:b10,  dest_inf  = :b9,   msg_num  = :b8,             done_msg_info   = :b7,    mailN

          3,293           67                49.1                        1.9     0.00      0.16            2175688974
insert into msginfo values(:p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8,:p9,
:p10,:p11,:p12,:p13,:p14,:p15,:p16,:p17,:p18,:p19,:p20,:p21,:p22
,:p23,:p24,:p25,:p26,:p27,:p28,:p29,:p30,:p31,:p32,:p33,:p34,:p3
5,:p36,:p37,:p38,:p39,:p40,:p41,:p42,:p43,:p44,:p45,:p46,:p47,:p
48,:p49,:p50,:p51,:p52,:p53,:p54,:p55,:p56,:p57,:p58,:p59,:p60,:

          2,149           28           76.8                              1.2     0.00      0.21             3752979796
delete from Refence_tabwhere SrvMsgID=:p1 and dest_addr=:p2

          1,888          118           16.0                             1.1     0.00      0.08              2500993063
select ISDN,pass from smReq where isDel = '0'

          1,888     &nb