Chinaunix首页 | 论坛 | 博客
  • 博客访问: 3620522
  • 博文数量: 738
  • 博客积分: 1860
  • 博客等级: 上尉
  • 技术积分: 8006
  • 用 户 组: 普通用户
  • 注册时间: 2008-04-07 08:51
个人简介

偶尔有空上来看看

文章分类

全部博文(738)

文章存档

2024年(24)

2023年(74)

2022年(134)

2021年(238)

2020年(115)

2019年(11)

2018年(9)

2017年(9)

2016年(17)

2015年(7)

2014年(4)

2013年(1)

2012年(11)

2011年(27)

2010年(35)

2009年(11)

2008年(11)

最近访客

分类: Oracle

2021-04-26 14:50:10



  1. -- Copyright 2018 Tanel Poder. All rights reserved. More info at
  2. -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.

  3. --------------------------------------------------------------------------------
  4. --
  5. -- File name: ashtop.sql v1.2
  6. -- Purpose: Display top ASH time (count of ASH samples) grouped by your
  7. -- specified dimensions
  8. --
  9. -- Author: Tanel Poder
  10. -- Copyright: (c) http://blog.tanelpoder.com
  11. --
  12. -- Usage:
  13. -- @ashtop
  14. --
  15. -- Example:
  16. -- @ashtop username,sql_id session_type='FOREGROUND' sysdate-1/24 sysdate
  17. --
  18. -- Other:
  19. -- This script uses only the in-memory GV$ACTIVE_SESSION_HISTORY, use
  20. -- @dashtop.sql for accessiong the DBA_HIST_ACTIVE_SESS_HISTORY archive
  21. --
  22. --------------------------------------------------------------------------------
  23. COL "%This" FOR A7
  24. --COL p1 FOR 99999999999999
  25. --COL p2 FOR 99999999999999
  26. --COL p3 FOR 99999999999999
  27. COL p1text FOR A30 word_wrap
  28. COL p2text FOR A30 word_wrap
  29. COL p3text FOR A30 word_wrap
  30. COL p1hex FOR A17
  31. COL p2hex FOR A17
  32. COL p3hex FOR A17
  33. COL dop FOR 99
  34. COL AAS FOR 9999.9
  35. COL totalseconds HEAD "Total|Seconds" FOR 99999999
  36. COL dist_sqlexec_seen HEAD "Distinct|Execs Seen" FOR 999999
  37. COL event FOR A42 WORD_WRAP
  38. COL event2 FOR A42 WORD_WRAP
  39. COL time_model_name FOR A50 WORD_WRAP
  40. COL program2 FOR A40 TRUNCATE
  41. COL username FOR A20 wrap
  42. COL obj FOR A30
  43. COL objt FOR A50
  44. COL sql_opname FOR A20
  45. COL top_level_call_name FOR A30
  46. COL wait_class FOR A15

  47. SELECT
  48.     *
  49. FROM (
  50.     WITH bclass AS (SELECT /*+ INLINE */ class, ROWNUM r from v$waitstat)
  51.     SELECT /*+ LEADING(a) USE_HASH(u) */
  52.         COUNT(*) totalseconds
  53.       , ROUND(COUNT(*) / ((CAST(&4 AS DATE) - CAST(&3 AS DATE)) * 86400), 1) AAS
  54.       , LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ')||' |' "%This"
  55.       , &1
  56.       , TO_CHAR(MIN(sample_time), 'YYYY-MM-DD HH24:MI:SS') first_seen
  57.       , TO_CHAR(MAX(sample_time), 'YYYY-MM-DD HH24:MI:SS') last_seen
  58. -- , MAX(sql_exec_id) - MIN(sql_exec_id)
  59.       , COUNT(DISTINCT sql_exec_start||':'||sql_exec_id) dist_sqlexec_seen
  60.     FROM
  61.         (SELECT
  62.              a.*
  63.            , session_id sid
  64.            , session_serial# serial
  65.            , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex
  66.            , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex
  67.            , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex
  68.            , TRUNC(px_flags / 2097152) dop
  69.            , NVL(a.event, a.session_state)||
  70.                 CASE
  71.                     WHEN a.event like 'enq%' AND session_state = 'WAITING'
  72.                     THEN ' [mode='||BITAND(p1, POWER(2,14)-1)||']'
  73.                     WHEN a.event IN (SELECT name FROM v$event_name WHERE parameter3 = 'class#')
  74.                     THEN ' ['||CASE WHEN a.p3 <= (SELECT MAX(r) FROM bclass)
  75.                                THEN (SELECT class FROM bclass WHERE r = a.p3)
  76.                                ELSE (SELECT DECODE(MOD(BITAND(a.p3,TO_NUMBER('FFFF','XXXX')) - 17,2),0,'undo header',1,'undo data', 'error') FROM dual)
  77.                                END ||']'
  78.                     ELSE null
  79.                 END event2 -- event is NULL in ASH if the session is not waiting (session_state = ON CPU)
  80.            , CASE WHEN a.session_type = 'BACKGROUND' OR REGEXP_LIKE(a.program, '.*\([PJ]\d+\)') THEN
  81.                 REGEXP_REPLACE(SUBSTR(a.program,INSTR(a.program,'(')), '\d', 'n')
  82.              ELSE
  83.                 '('||REGEXP_REPLACE(REGEXP_REPLACE(a.program, '(.*)@(.*)(\(.*\))', '\1'), '\d', 'n')||')'
  84.              END || ' ' program2
  85.            , CASE WHEN BITAND(time_model, POWER(2, 01)) = POWER(2, 01) THEN 'DBTIME ' END
  86.            ||CASE WHEN BITAND(time_model, POWER(2, 02)) = POWER(2, 02) THEN 'BACKGROUND ' END
  87.            ||CASE WHEN BITAND(time_model, POWER(2, 03)) = POWER(2, 03) THEN 'CONNECTION_MGMT ' END
  88.            ||CASE WHEN BITAND(time_model, POWER(2, 04)) = POWER(2, 04) THEN 'PARSE ' END
  89.            ||CASE WHEN BITAND(time_model, POWER(2, 05)) = POWER(2, 05) THEN 'FAILED_PARSE ' END
  90.            ||CASE WHEN BITAND(time_model, POWER(2, 06)) = POWER(2, 06) THEN 'NOMEM_PARSE ' END
  91.            ||CASE WHEN BITAND(time_model, POWER(2, 07)) = POWER(2, 07) THEN 'HARD_PARSE ' END
  92.            ||CASE WHEN BITAND(time_model, POWER(2, 08)) = POWER(2, 08) THEN 'NO_SHARERS_PARSE ' END
  93.            ||CASE WHEN BITAND(time_model, POWER(2, 09)) = POWER(2, 09) THEN 'BIND_MISMATCH_PARSE ' END
  94.            ||CASE WHEN BITAND(time_model, POWER(2, 10)) = POWER(2, 10) THEN 'SQL_EXECUTION ' END
  95.            ||CASE WHEN BITAND(time_model, POWER(2, 11)) = POWER(2, 11) THEN 'PLSQL_EXECUTION ' END
  96.            ||CASE WHEN BITAND(time_model, POWER(2, 12)) = POWER(2, 12) THEN 'PLSQL_RPC ' END
  97.            ||CASE WHEN BITAND(time_model, POWER(2, 13)) = POWER(2, 13) THEN 'PLSQL_COMPILATION ' END
  98.            ||CASE WHEN BITAND(time_model, POWER(2, 14)) = POWER(2, 14) THEN 'JAVA_EXECUTION ' END
  99.            ||CASE WHEN BITAND(time_model, POWER(2, 15)) = POWER(2, 15) THEN 'BIND ' END
  100.            ||CASE WHEN BITAND(time_model, POWER(2, 16)) = POWER(2, 16) THEN 'CURSOR_CLOSE ' END
  101.            ||CASE WHEN BITAND(time_model, POWER(2, 17)) = POWER(2, 17) THEN 'SEQUENCE_LOAD ' END
  102.            ||CASE WHEN BITAND(time_model, POWER(2, 18)) = POWER(2, 18) THEN 'INMEMORY_QUERY ' END
  103.            ||CASE WHEN BITAND(time_model, POWER(2, 19)) = POWER(2, 19) THEN 'INMEMORY_POPULATE ' END
  104.            ||CASE WHEN BITAND(time_model, POWER(2, 20)) = POWER(2, 20) THEN 'INMEMORY_PREPOPULATE ' END
  105.            ||CASE WHEN BITAND(time_model, POWER(2, 21)) = POWER(2, 21) THEN 'INMEMORY_REPOPULATE ' END
  106.            ||CASE WHEN BITAND(time_model, POWER(2, 22)) = POWER(2, 22) THEN 'INMEMORY_TREPOPULATE ' END
  107.            ||CASE WHEN BITAND(time_model, POWER(2, 23)) = POWER(2, 23) THEN 'TABLESPACE_ENCRYPTION ' END time_model_name
  108.         FROM gv$active_session_history a) a
  109.       , dba_users u
  110.       , (SELECT
  111.              object_id,data_object_id,owner,object_name,subobject_name,object_type
  112.            , owner||'.'||object_name obj
  113.            , owner||'.'||object_name||' ['||object_type||']' objt
  114.          FROM dba_objects) o
  115.     WHERE
  116.         a.user_id = u.user_id (+)
  117.     AND a.current_obj# = o.object_id(+)
  118.     AND &2
  119.     AND sample_time BETWEEN &3 AND &4
  120.     GROUP BY
  121.         &1
  122.     ORDER BY
  123.         TotalSeconds DESC
  124.        , &1
  125. )
  126. WHERE
  127.     ROWNUM <= 15
  128. /



使用方法:
将上述脚本保存为 ashtop.sql

然后执行:
PROMPT What's going on? Showing top timed events of last minute from ASH...
@ashtop session_state,event &filter sysdate-1/24/60 sysdate


PROMPT Showing top SQL and wait classes of last minute from ASH...
@ashtop sql_id,session_state,wait_class &filter sysdate-1/24/60 sysdate
阅读(1502) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~