ASH报告解读

  • 不一样的天空w

    2017-01-12 10:21:55
  • Oracle
  • 转载
首先是收集，想必大家都知道 嘿嘿

下面是选取采样时间，这个第一次做的朋友可能都有些晕啦。

再往下是生成的awr

分TOP EVENT 和TOP SQL

Top Events

Back to Top


Top User Events

Event Event Class % Activity Avg Active Sessions
CPU + Wait for CPU CPU 69.83 2.75
cursor: pin S wait on X Concurrency 22.50 0.89
db file sequential read User I/O 3.36 0.13

Back to Top Events 
Back to Top

这一部分是用户会话的等待事件的信息。列出了采样时段内数据库发生的显著用户等待！


Top Background Events

No data exists for this section of the report.

Back to Top Events 
Back to Top


Top Event P1/P2/P3 Values

Event % Event P1 Value, P2 Value, P3 Value % Activity Parameter 1 Parameter 2 Parameter 3
cursor: pin S wait on X 22.50 "2407790554","7443178323968","21474939807" 0.03 idn value where|sleeps
db file sequential read 3.36 "24","3361539","1" 0.06 file# block# blocks

Back to Top Events 
Back to Top

这部分信息显示了在抽样会话活动中占很高百分比的等待事件的参数值它通过总的等待时间(%Event)百分比进行排序后被显示.对于每一个等待事件p1,p2,p3的值与等待事件参数parameter


Load Profile

Back to Top


Top Service/Module

Service Module % Activity Action % Action
SYS$USERS JDBC Thin Client 96.39 UNNAMED 96.39
db10g JDBC Thin Client 1.50 UNNAMED 1.50
SYS$BACKGROUND UNNAMED 1.16 UNNAMED 1.16

Back to Load Profile 
Back to Top

这部分信息显示了在抽样会话活动中占很高百分比的服务和模块信息


Top Client IDs

No data exists for this section of the report.

Back to Load Profile 
Back to Top

这部分信息显示了在抽样会话活动中占很高百分比的客户端的id信息它是数据库会话中应用程序的特定标识符

Top SQL Command Types

  • 'Distinct SQLIDs' is the count of the distinct number of SQLIDs with the given SQL Command Type found over all the ASH samples in the analysis period
SQL Command Type Distinct SQLIDs % Activity Avg Active Sessions
SELECT 721 92.18 3.63
INSERT 26 3.13 0.12
DELETE 4 1.02 0.04

Back to Load Profile 
Back to Top

这部分信息显示了在抽样会话活动中占很高百分比的sql命令类型比如select或update


Top SQL

Back to Top


Top SQL Statements

SQL ID Planhash % Activity Event % Event SQL Text
d0bntffqjt8wn   32.29 CPU + Wait for CPU 18.32 select * from (select "GAMS_AS...
    32.29 cursor: pin S wait on X 13.97 select * from (select "GAMS_AS...
  3360248884 10.84 CPU + Wait for CPU 5.42 select * from (select "GAMS_AS...
      cursor: pin S wait on X 5.42
dky5jwk7s7xyu 3882758678 9.03 CPU + Wait for CPU 5.93 select count(1) "C" from (sele...
      cursor: pin S wait on X 3.10
fnw8mawmhqnqd 675345696 6.83 CPU + Wait for CPU 6.83 select "pi"."WORKCATEGORY" "WO...
4crmacgh98zur 4277285853 2.03 CPU + Wait for CPU 2.03 select '????' as name, (sjzcam...

Back to Top SQL 
Back to Top

这部分安装sql的活动频度列出了前6位的sql语句。空值说明没有采样到数据。

Top SQL using literals

Plan Hash % Activity # of Sampled SQL Versions Example SQL 1 Example SQL TEXT 1 Example SQL 2 Example SQL TEXT 2
4047759096 1.21 43 004m842u7jag2 select * from ( select rownum ... gsp8kxf900anp select * from ( select rownum ...

Back to Top SQL 
Back to Top

这部分列出了一些未绑定变量的sql。


Complete List of SQL Text




SQL Id SQL Text
004m842u7jag2 select * from ( select rownum rn, page2.* from (select * from T_GLBdgCtrl t where ( ( ACCOUNT = 1863 ) ) and ( paytype=1076 and exists (select 1 from t_planmain a, t_planaccredi b where a.printid = b.billcode AND a.toctrlid = ctrlid AND a.wfstatus = 96 ) ) and ( ( (EXISTS(Select 1 from (Select * from T_PUBAGENCY where (STATUS = 2 and ((TYPE = 1036069 or TYPE = 1036085 or TYPE = 1036070 or T YPE = 90001883 )))) where t.BDGAGENCY=ITEMID and CODE Like '042203%'))) and curbal > 0 ) and 1 = 1 ) page2 where rownum<= 30) where rn > 0
4crmacgh98zur select '????' as name, (sjzcamt/apamt) * 100 as value from v_indi_expenditurcity
d0bntffqjt8wn
dky5jwk7s7xyu
fnw8mawmhqnqd select "pi"."WORKCATEGORY" "WORKCATEGORY", count(1) "workCount" from "PARTICIPANT" "pi" where "pi"."ACTION"=0 and "pi"."WORKCATEGORY" is not null and ("pi"."USERID"=:1 or "pi"."COMMISSION"=:2) group by "pi"."WORKCATEGORY"
gsp8kxf900anp select * from ( select rownum rn, page2.* from (select * from T_GLBdgCtrl t where ( ( ACCOUNT = 1863 ) ) and ( paytype=1076 and exists (select 1 from t_planmain a, t_planaccredi b where a.printid = b.billcode AND a.toctrlid = ctrlid AND a.wfstatus = 96 ) ) and ( ( (EXISTS(Select 1 from (Select * from T_PUBAGENCY where (STATUS = 2 and ((TYPE = 1036069 or TYPE = 1036085 or TYPE = 1036070 or TYPE = 90001883 )))) where t.BDGAGENCY=ITEMID and CODE Like '135204%'))) and curbal > 0 ) and 1 = 1 ) page2 where rownum<= 30) where rn > 0

这部分是列出了所有的sql语句。

Top PL/SQL Procedures

No data exists for this section of the report.


Back to Top



Top Sessions

Back to Top


Top Sessions

  • '# Samples Active' shows the number of ASH samples in which the session was found waiting for that particular event. The percentage shown in this column is calculated with respect to wall clock time and not total database activity.
  • 'XIDs' shows the number of distinct transaction IDs sampled in ASH when the session was waiting for that particular event
  • For sessions running Parallel Queries, this section will NOT aggregate the PQ slave activity into the session issuing the PQ. Refer to the 'Top Sessions running PQs' section for such statistics.
Sid, Serial# % Activity Event % Event User Program # Samples Active XIDs
1724, 12 25.37 CPU + Wait for CPU 23.82 BJ_ZCGL JDBC Thin Client 844/900 [ 94%] 0
    cursor: pin S wait on X 1.55     55/900 [ 6%] 0
2057,21653 19.56 cursor: pin S wait on X 19.39 BJ_ZCGL JDBC Thin Client 687/900 [ 76%] 0
1709,51321 5.50 CPU + Wait for CPU 5.00 BJ_ZCGL JDBC Thin Client 177/900 [ 20%] 1
2023, 11 2.77 CPU + Wait for CPU 2.17 IFMIS_2015 JDBC Thin Client 77/900 [ 9%] 5
1712,53483 2.60 cursor: pin S wait on X 1.55 BJ_ZCGL JDBC Thin Client 55/900 [ 6%] 0


Back to Top

这部分列出了活动最频繁的会话信息。这一部分为我们提供了非常有用的信息，那就是哪些进程活动频繁。我们通过这一部分获取到某个活动频繁的会话信息，可以通过使用ashrpti.sql来生成针对这个会话的ASH性能报告，或者可以使用sql_trace对这个会话进程跟踪。这样我们就能了解到当前这个会话正在做什么。


Top Blocking Sessions

No data exists for this section of the report.


Back to Top

这一部分列举了被阻塞的会话信息。


Top Sessions running PQs

No data exists for this section of the report.

这部分信息显示了哪些在抽样会话活动中占很高百分比的正处于等待的并行查询


Back to Top


这部分列出了活动频繁的前几位并行执行的会话信息。

Top Objects/Files/Latches

Back to Top



Top DB Objects

No data exists for this section of the report.


Back to Top


Top DB Files

  • With respect to Cluster and User I/O events only.
File ID % Activity Event % Event File Name Tablespace
15 1.50 db file sequential read 0.87 +DKGRP2/bj_zcgl01 BJ_ZCGL
12 1.07 db file sequential read 0.82 +DKGRP1/bj_zcgl BJ_ZCGL


Back to Top

这部分信息显示了在抽样会话活动中占访问量很高百分比的数据库文件


Top Latches

No data exists for this section of the report.

这部分信息显示了在抽样会话活动中占很高百分比的闩锁信息
闩锁是一种简单低级别串行化机制用来保护sga中的共享数据结构.比如闩锁保护当前访问数据库和缓冲区缓存中数据块结构的用户列表.当维护或查找这些结构时服务器或后台进程请求持有闩锁的时间是非常短暂的.闩锁的实现依赖于操作系统特别是一个进程等待获取一个闩锁多长时间.


Back to Top


这部分列出latch竞争。

Activity Over Time

  • Analysis period is divided into smaller time slots
  • Top 3 events are reported in each of those slots
  • 'Slot Count' shows the number of ASH samples in that slot
  • 'Event Count' shows the number of ASH samples waiting for that event in that slot
  • '% Event' is 'Event Count' over all ASH samples in the analysis period
Slot Time (Duration) Slot Count Event Event Count % Event
10:05:57 (3 secs) 16 CPU + Wait for CPU 8 0.23
    cursor: pin S wait on X 8 0.23
10:06:00 (2.0 min) 447 CPU + Wait for CPU 326 9.20
    cursor: pin S wait on X 102 2.88
    db file sequential read 9 0.25
10:08:00 (2.0 min) 418 CPU + Wait for CPU 354 9.99
    cursor: pin S wait on X 32 0.90
    db file sequential read 20 0.56
10:10:00 (2.0 min) 512 CPU + Wait for CPU 333 9.40
    cursor: pin S wait on X 119 3.36
    db file sequential read 40 1.13
10:12:00 (2.0 min) 486 CPU + Wait for CPU 317 8.95
    cursor: pin S wait on X 119 3.36
    db file parallel read 15 0.42
10:14:00 (2.0 min) 468 CPU + Wait for CPU 304 8.58
    cursor: pin S wait on X 120 3.39
    db file sequential read 15 0.42
10:16:00 (2.0 min) 432 CPU + Wait for CPU 293 8.27
    cursor: pin S wait on X 120 3.39
    db file sequential read 9 0.25
10:18:00 (2.0 min) 495 CPU + Wait for CPU 359 10.13
    cursor: pin S wait on X 120 3.39
    db file sequential read 9 0.25
10:20:00 (57 secs) 269 CPU + Wait for CPU 205 5.79
    cursor: pin S wait on X 57 1.61
    db file sequential read 3 0.08

这部分列举了各种等待的细粒度显示。
这一部分是ash报告信息最丰富的一部分.这部分信息对于长时间周期的ash报告来说因为在分析期间它提供了关于活动和工作负载概要深层次的详细信息.activity over time会被分成10个时段.每个时段的大小基于分析所持续的时间.第一个和最后一个时段是奇怪.所有内部时段是相等的大小它们可以相互比较.例如,如果分析时间持续10分钟那么所有的时段将会是每个一分钟.然后如果分析时间持续9分30秒,那么外部的时段可能是每个15秒内部的时段可能每个1分钟
特定时段中每个时段包含的信息如下:

列                               描述
slot time(持续时间)      时段的持续时间
solt count                  在时段中抽样会话的数量
event                          在时段中顶级的三个等待事件
event count                ash抽样等待的等待事件的数量
%event                       ash抽样等待的等待事件在整个分析期间所占的百分比


当比较内部时段时,通过识别异常的event count和slot count列执行一个倾斜分析.event count列的异常指示了在抽样会话中等待的等待事件数量增加了.slot count列的异常指示活动的会自豪感增加了,因为ash数据只从活动会话中进持抽样说明数据库的

Back to Top


End of Report

参考：http://blog.itpub.net/26015009/viewspace-776642/

请使用浏览器的分享功能分享到微信等