ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 9istatspack使用手册

9istatspack使用手册

原创 Linux操作系统 作者:tolywang 时间:2005-01-21 00:00:00 0 删除 编辑

内容包括
  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

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

上一篇: 数据库系统原理
请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13378462