9istatspack使用指南

内容包括
 statspack安装
 statspack报告的详细说明
  本文来自：中国建站网 (www.136z.com) 原文链接： http://edu.136z.com/DataBase/32554_7.html
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%~100%,如果这个值太低，就浪费内存，如果太高，会使共享池外部的组件老化，如果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          118           16.0                            1.1     0.00      0.11           4041890278
delete smReq where isDel = '1' and regType = '0'
 
          1,842           56           32.9                             1.1     0.00      0.27           1728016048
begin smsc_util.OriginDone(:p1, :p2); end;
 
          1,679           23           73.0                             1.0     0.00      0.05            156420249
select Refrence, orig_addr, MMType from Refence_tabwhere SrvMsgID=:p1 and dest_addr=:p2
 
            930          198            4.7                               0.5     0.00      0.06           3744392743
UPDATE origininfo set sendnum=:b3, MsgList=:b2 where sub_isdn=:b1
 
            717          220            3.3                               0.4     0.00      0.13           3466225112
SELECT sendnum, MsgList from origininfo where sub_isdn=:b1
 
            640           40           16.0                               0.4     0.00      0.03           776298407
select count(*) from smReq
 
            527           42           12.5                               0.3     0.00      0.12            3721822251
INSERT INTO destinfo     VALUES(:b1, :b2, :b3, :b4, :b5, :b6, :b7, :b8, :b9,:b10, :b11, :b12, :b13, :b14)
 
            187           48            3.9                                0.1     0.00      0.23         4009906312
select bMonthBillState, bMsgStatus from monthbillinfo where wYear=:p1 and bMonth=:p2 and tBillAddr=:p3 and tVaspId=:p4 and tVasId=:p5 and tServiceCode=:p6 and tDestAddr=:p7
 
            171           12           14.3                              0.1     0.00      0.08           664582079
begin smsc_util.Auto_OpenSubServ(:p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8,:p9,:p10,:p11,:p12,:p13,:p14,:p15,:p16,:p17,:p18,:p19,:p20,:p21,:p22); end;
 
            140           20            7.0                               0.1     0.00      0.01           2872798779
select count(*) from myuser.sys_var
 
            139           22            6.3                              0.1     0.00      0.01             714875384
INSERT into origininfo values(:b1, :b3, :b2)
 
            114           57            2.0                              0.1     0.00      0.02              2963598673
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (next_date < :2))    or ((last_date is null) and (next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5) ) and (this_date is null) order by next_date, job
 
            110           12            9.2                              0.1     0.00      0.02            1315078830
INSERT INTO userinfo VALUES(:b22 ,:b21 ,:b20 ,:b19 ,:b18
 
          -------------------------------------------------------------
说明：
就是通过执行了多少个逻辑IO来排序
 
--------------------------9.按物理IO进行排序的SQL------------------------------------------------
SQL ordered by Reads for DB: MYDB Instance: mydb Snaps: 1 -2
-> End Disk Reads Threshold:    1000
 
                       &nb

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8203880/viewspace-364827/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8203880/viewspace-364827/