一份statspack报告, 求高手指点

coolingverse

我现在手头有一份statspack报告, 不知道有什么问题, 小弟刚刚入行, 求大师们指点一二

Maclean Liu(刘相兵

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file scattered read                            103,683       8,796    31.70
buffer busy waits                                 370,997       6,134    22.10
log buffer space                                  100,178       3,676    13.25
db file sequential read                           411,273       3,003    10.82
log file sync                                      16,119       2,888    10.41

主要等待事件 db file scattered read     

                                                                   Avg
                                                     Total Wait   wait    Waits
Event                               Waits   Timeouts   Time (s)   (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file scattered read            103,683          0      8,796     85      9.7
buffer busy waits                 370,997        324      6,134     17     34.9
log buffer space                  100,178        432      3,676     37      9.4
db file sequential read           411,273          0      3,003      7     38.6
log file sync                      16,119        312      2,888    179      1.5

db file scattered read   平均等待为 85/ms每次       => 很慢 IO 有瓶颈 调优减少物理读写 或者优化存储
log file sync                    平均等待为179/ms每次    => 很慢 IO 有瓶颈 减少commit


Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:          5,315,215.45            640,243.00
              Logical reads:             25,778.93              3,105.20
              Block changes:             16,496.03              1,987.03
             Physical reads:              1,509.26                181.80
            Physical writes:                193.20                 23.27


平均每秒的物理 读=   1509 * db_block_size= 1509 * 32k= 47M
                    物理 写=    193 * db_block_size=  6M


table scans (long tables)                         58            0.1          0.0
table scans (short tables)                       700            0.6          0.1


存在对 table scans( long tables) =58 说明存在对 大表的全表扫描


物理读高的 SQL语句， 这2句似乎都是监控程序产生的

1. SQL ordered by Reads for DB: ARCSIGHT  Instance: arcsight  Snaps: 5 -6
   
2. -> End Disk Reads Threshold:      1000
   
3. 
   
4.                                                      CPU      Elapsd
   
5. Physical Reads  Executions  Reads per Exec %Total Time (s)  Time (s) Hash Value
   
6. --------------- ------------ -------------- ------ -------- --------- ----------
   
7.          48,673           15        3,244.9    2.5    11.15   1134.02   78113761
   
8. SELECT tablespace_name,  sum(bytes) as free_space  FROM dba_free
   
9. _space  GROUP BY tablespace_name
   
10. 
    
11.          17,380            4        4,345.0    0.9     3.67    391.17 3220315133
    
12. Module: java@V490a-Arcsight (TNS V1-V3)
    
13. select  total.tablespace_name , round((nvl(total.total_space,0)
    
14. / 1000000), 0), round(((nvl(total.total_space,0) - nvl(free.free
    
15. _space, 0)) / (total.total_space) * 100), 2) from (select tables
    
16. pace_name, sum(bytes) total_space from dba_data_files group by t
    
17. ablespace_name) total,  (select tablespace_name, sum(bytes) free

复制代码

log buffer space => Log Buffer:      1,024K  适当增大log buffer  1M太小了 ，至少3M


实例参数方面：

_enable_list_io      

ODM Data

1. 1. Customer wants to set _enable_list_io = true and would like to know that if
   
2. he enables this parameter, he will not cause corruption or performance
   
3. degradation within his environment since oracle has to go back and look for
   
4. the i/o responses.
   
5. 
   
6. Just to clarify what he will be changing:
   
7. 
   
8. disk_asynch_io will be set to true
   
9. _enable_list_io will be set to true.
   
10. 
    
11. ...thus causing oracle to do kaio calls, which should fail and ultimately be
    
12. handled by lwp calls of aioread aiowrite -- the enablie_list_io should allow
    
13. solaris 8 to asynchronously handle these i/o requests for oracle using the
    
14. aio_suspend mechanism.
    
15. 
    
16. The customer has traced the performance issue to the commonly known tcp/ip
    
17. stack issues within Solaris 8 and the increased cpu requirements. However,
    
18. they are unable to make their host larger.  To help tune the load against the
    
19. tcp/ip stack they are looking to turn disk_Asynch_io on and to enable the use
    
20. of the listio features within Sun.  This requires setting the hidden parameter
    
21. of _enable_list_io to true. They are not using Veritas quick i/o, as they have
    
22. purposely moved off of it.
    
23. 
    
24. This bug is really an informative question as there is very little to no
    
25. information regarding the _enable_list_io parameter other than the fact that
    
26. it enables list I/O and that there were a couple of bugs logged against this
    
27. parameter when set true in 8i.
    
28. 
    
29. 
    
30. 4. Customer has migrated from a SAN environment to NAS using NFS (storage is
    
31. now a NetApp Fas960) and now performance is a lot worse.
    
32. 
    
33. 5. Performance is now very slow.
    
34. 
    
35. DIAGNOSTIC ANALYSIS:
    
36. --------------------
    
37. questions
    
38. 
    
39. 
    
40. WORKAROUND:
    
41. -----------
    
42. Keep _enable_list_io set to its default value of false.

复制代码

设置该参数_enable_list_io       是出于何种目的？



dbwr_io_slaves 16

disk_asynch_io FALSE

filesystemio_options          directIO

是何种操作系统 + 文件系统？　不支持异步IO 需要去用slaves IO ?


log_buffer                    1048576  log buffer 过小 上面提过了


时间紧蹙 ， 可能不够全面  希望大家补充

vicry21

SQL ordered by Gets for DB: ARCSIGHT  Instance: arcsight  Snaps: 5 -6
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------
      6,811,814        2,440        2,791.7   20.6   108.97   1716.59 2946511209
INSERT INTO arc_event (event_id, name, message, event_type, star
t_time, end_time, manager_receipt_time, trans_protocol, bytes_in
, bytes_out, cat_descriptor_id, model_confidence, severity, rele
vance, asset_criticality, priority, agt_severity, agt_receipt_ti
me, dvc_event_category, dvc_severity, dvc_action, dvc_receipt_ti

      2,350,550          739        3,180.7    7.1    37.38    586.75  634135794
INSERT INTO arc_event (event_id, name, message, event_type, star
t_time, end_time, manager_receipt_time, trans_protocol, bytes_in
, bytes_out, cat_descriptor_id, model_confidence, severity, rele
vance, asset_criticality, priority, agt_severity, agt_receipt_ti
me, dvc_event_category, dvc_severity, dvc_action, dvc_receipt_ti

Elapsd time远远大于CPU time,可见这个sql大部分时间出于等待.可否通过hash value将这两个sql贴出来。

不了峰

多少cpu的主机???

1.每次的逻辑有  2062 ..有点大 对OLTP系统

2.可能把这个解决了 INSERT INTO arc_event  就好了

3. 可以commit过于频繁提交

4. io很差

不了峰

有一个疑问在Top 5 Timed Events 没有出现cpu time这一个指标的值
也不出现在下面的event中

这难道也是证明 io是这个系统的主要问题？