ASH信息是我们Troubleshooting一个很重要的信息来源，但是我们也不一定要收集一个ASH报告来分析，
一般从v$active_session_history可以得到想要的信息，如果前面视图里想要的消息已经不存在，那么可以通过
DBA_HIST_ACTIVE_SESS_HISTORY来获取需要的信息。

SELECT * FROM (
SELECT a.module
, a.sql_id
, a.session_state
, count(*)
, lpad(round(ratio_to_report(count(*)) over () * 100)||'%',10,' ') percent
FROM
dba_hist_active_sess_history a
WHERE
a.sample_time BETWEEN to_date('2014-09-15 15:00:00','yyyy-mm-dd hh24:mi:ss')
AND to_date('2014-09-15 17:00:00','yyyy-mm-dd hh24:mi:ss')
GROUP BY
a.module
, a.sql_id
, a.session_state
ORDER BY
percent DESC
)
WHERE ROWNUM <= 30

MODULE SQL_ID SESSION_STATE COUNT(*) PERCENT
1 w3wp.exe gfmyt48vhwcr2 WAITING 1569 6%
2 JDBC Thin Client 9xrv18n3v0gsk ON CPU 1441 5%
3 JDBC Thin Client cxjkmrp2hv4qb WAITING 761 3%
4 PL/SQL Developer 6s0myu37mh62a WAITING 718 3%
5 SUERWSService.exe arcgxfv8qbqd1 WAITING 686 3%
6 JDBC Thin Client 3hj3hd7zxr28s WAITING 434 2%
7 JDBC Thin Client 8k9jvyvr85035 WAITING 483 2%
8 JDBC Thin Client bp6udmt6t19bw WAITING 483 2%
9 JDBC Thin Client cqhd6mjxdb2kj WAITING 575 2%
10 JDBC Thin Client chy2xtw2cx87z WAITING 530 2%
11 JDBC Thin Client a65w4f5n6a0vf ON CPU 475 2%
12 JDBC Thin Client WAITING 408 2%
13 WAITING 545 2%
14 ON CPU 604 2%
15 w3wp.exe 8rhavxqvv3s2n ON CPU 585 2%
16 w3wp.exe 4u8bffjz63qk5 WAITING 520 2%
17 JDBC Thin Client 01wdcnjt7f5vf WAITING 166 1%
18 JDBC Thin Client 0v36w0k8k3vp1 WAITING 334 1%
19 JDBC Thin Client 1p5kgpyy8mpbc WAITING 198 1%
20 JDBC Thin Client 5fzarbzvpsh7y WAITING 342 1%
21 JDBC Thin Client 2bh1qrw5bdx28 WAITING 233 1%
22 JDBC Thin Client 2cq0m14zr77b8 WAITING 346 1%
23 JDBC Thin Client 27ky1txj7x52u WAITING 167 1%
24 JDBC Thin Client 6mybkwsdj1kjq WAITING 288 1%
25 JDBC Thin Client fupuj9zy4pxkd ON CPU 278 1%
26 JDBC Thin Client f6gk47n7up9xr WAITING 193 1%
27 JDBC Thin Client dju15fptcjn84 ON CPU 143 1%
28 JDBC Thin Client db7krjcw011w8 WAITING 168 1%
29 JDBC Thin Client db4xsmcupcm4m ON CPU 187 1%
30 JDBC Thin Client d47vhdx8x65qc WAITING 400 1%

从上面的查询可知：sql_id为gfmyt48vhwcr2的SQL最耗CPU。

SELECT * FROM (
SELECT a.event
, a.sql_id
, count(*)
, lpad(round(ratio_to_report(count(*)) over () * 100)||'%',10,' ') percent
FROM
dba_hist_active_sess_history a
WHERE
a.sample_time BETWEEN to_date('2014-09-15 15:00:00','yyyy-mm-dd hh24:mi:ss')
AND to_date('2014-09-15 17:00:00','yyyy-mm-dd hh24:mi:ss')
GROUP BY
a.event
, a.sql_id
ORDER BY
percent DESC
)
WHERE ROWNUM <= 30

EVENT SQL_ID COUNT(*) PERCENT
1 cursor: pin S wait on X gfmyt48vhwcr2 1441 5%
2 9xrv18n3v0gsk 1441 5%
3 PX Deq Credit: send blkd 6s0myu37mh62a 718 3%
4 742 3%
5 db file sequential read 3hj3hd7zxr28s 413 2%
6 db file sequential read bp6udmt6t19bw 480 2%
7 db file sequential read chy2xtw2cx87z 526 2%
8 db file sequential read 8k9jvyvr85035 475 2%
9 db file sequential read 4u8bffjz63qk5 492 2%
10 db file sequential read cqhd6mjxdb2kj 553 2%
11 log file sync 443 2%
12 a65w4f5n6a0vf 475 2%
13 8rhavxqvv3s2n 585 2%
14 db file sequential read cxjkmrp2hv4qb 451 2%
15 control file sequential read 139 1%
16 db file sequential read 1dpjjrg0dy6yd 217 1%
17 db file sequential read 1p5kgpyy8mpbc 193 1%
18 db file sequential read 0v36w0k8k3vp1 325 1%
19 db file sequential read 01wdcnjt7f5vf 165 1%
20 db file sequential read 27ky1txj7x52u 164 1%
21 db file sequential read 2cq0m14zr77b8 333 1%
22 db file sequential read c5sr3xm426qrg 248 1%
23 db file sequential read c2896zrznwb1w 356 1%
24 db file sequential read arcgxfv8qbqd1 292 1%
25 db file sequential read 96yd761jd1kmm 208 1%
26 db file sequential read 7rmx2d6pyj87d 223 1%
27 db file sequential read 7r497wfbypbw9 317 1%
28 db file sequential read 7hh46gy17z7p6 145 1%
29 db file sequential read 6mybkwsdj1kjq 139 1%
30 db file sequential read 5fzarbzvpsh7y 328 1%

sql_id为gfmyt48vhwcr2的SQL 产生的等待事件是：cursor: pin S wait on X

根据AWR可以该SQL为：
SELECT * FROM e3new.巴枪记录表

select sql_text from v$sql
where sql_id='gfmyt48vhwcr2'

ITPub博客

巧妙使用ASH信息

最新文章