Chinaunix首页 | 论坛 | 博客

沧海拾贝brjl.blog.chinaunix.net

首页　| 　博文目录　| 　关于我

brjl

  • 博客访问： 3672524
  • 博文数量： 715
  • 博客积分： 1860
  • 博客等级： 上尉
  • 技术积分： 7745
  • 用 户 组： 普通用户
  • 注册时间： 2008-04-07 08:51
个人简介

偶尔有空上来看看

文章分类

全部博文（715）

文章存档

2023年（75）

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）

我的朋友
最近访客
推荐博文
相关博文
【转】ashtop 方便看当前活动会话

分类： 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.

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

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



使用方法：
将上述脚本保存为 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
阅读(1677) | 评论(0) | 转发(0) |
0

上一篇：安装ahf后的后遗症

下一篇：rm -fr /oradata 不要慌

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6