经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置:技术经验 » 数据库/运维 » Oracle » 查看文章
ashtop学习分享
来源:cnblogs  作者:潇湘隐者  时间:2021/1/4 9:36:18  对本文有异议

ashtop.sql这个脚本是Tanel Poder写的一个脚本,用于定位有性能问题的SQL, 此脚本在GitHub上地址为https://github.com/tanelpoder/tpt-oracle/blob/master/ash/ashtop.sql。目前的最新版本为ashtop.sql v1.2

 

  1. -- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
  1. -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
  1.  
  1. --------------------------------------------------------------------------------
  1. --
  1. -- File name:   ashtop.sql v1.2
  1. -- Purpose:     Display top ASH time (count of ASH samples) grouped by your
  1. --              specified dimensions
  1. --             
  1. -- Author:      Tanel Poder
  1. -- Copyright:   (c) http://blog.tanelpoder.com
  1. --             
  1. -- Usage:      
  1. --     @ashtop <grouping_cols> <filters> <fromtime> <totime>
  1. --
  1. -- Example:
  1. --     @ashtop username,sql_id session_type='FOREGROUND' sysdate-1/24 sysdate
  1. --
  1. -- Other:
  1. --     This script uses only the in-memory GV$ACTIVE_SESSION_HISTORY, use
  1. --     @dashtop.sql for accessiong the DBA_HIST_ACTIVE_SESS_HISTORY archive
  1. --  
  1. --------------------------------Add by Kerry------------------------------------
  1. -- 这个脚本只适用于Oracle 11g以及以上的版本,Oracle 10ggv$active_session_history
  1. -- 没有time_model字段。     
  1. --------------------------------------------------------------------------------
  1. COL "%This" FOR A7
  1. --COL p1     FOR 99999999999999
  1. --COL p2     FOR 99999999999999
  1. --COL p3     FOR 99999999999999
  1. COL p1text              FOR A30 word_wrap
  1. COL p2text              FOR A30 word_wrap
  1. COL p3text              FOR A30 word_wrap
  1. COL p1hex               FOR A17
  1. COL p2hex               FOR A17
  1. COL p3hex               FOR A17
  1. COL dop                 FOR 99
  1. COL AAS                 FOR 9999.9
  1. COL totalseconds HEAD "Total|Seconds" FOR 99999999
  1. COL dist_sqlexec_seen HEAD "Distinct|Execs Seen" FOR 999999
  1. COL event               FOR A42 WORD_WRAP
  1. COL event2              FOR A42 WORD_WRAP
  1. COL time_model_name     FOR A50 WORD_WRAP
  1. COL program2            FOR A40 TRUNCATE
  1. COL username            FOR A20 wrap
  1. COL obj                 FOR A30
  1. COL objt                FOR A50
  1. COL sql_opname          FOR A20
  1. COL top_level_call_name FOR A30
  1. COL wait_class          FOR A15
  1.  
  1. SELECT
  1.     *
  1. FROM (
  1.     WITH bclass AS (SELECT /*+ INLINE */ class, ROWNUM r from v$waitstat)
  1.     SELECT /*+ LEADING(a) USE_HASH(u) */
  1.         COUNT(*)                                                     totalseconds
  1.       , ROUND(COUNT(*) / ((CAST(&4 AS DATE) - CAST(&3 AS DATE)) * 86400), 1) AAS
  1.       , LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ')||' |' "%This"
  1.       , &1
  1.       , TO_CHAR(MIN(sample_time), 'YYYY-MM-DD HH24:MI:SS') first_seen
  1.       , TO_CHAR(MAX(sample_time), 'YYYY-MM-DD HH24:MI:SS') last_seen
  1. --    , MAX(sql_exec_id) - MIN(sql_exec_id)
  1.       , COUNT(DISTINCT sql_exec_start||':'||sql_exec_id) dist_sqlexec_seen
  1.     FROM
  1.         (SELECT
  1.              a.*
  1.            , session_id sid
  1.            , session_serial# serial
  1.            , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex
  1.            , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex
  1.            , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex
  1.            , TRUNC(px_flags / 2097152) dop
  1.            , NVL(a.event, a.session_state)||
  1.                 CASE 
  1.                     WHEN a.event like 'enq%' AND session_state = 'WAITING'
  1.                     THEN ' [mode='||BITAND(p1, POWER(2,14)-1)||']'
  1.                     WHEN a.event IN (SELECT name FROM v$event_name WHERE parameter3 = 'class#')
  1.                     THEN ' ['||CASE WHEN a.p3 <= (SELECT MAX(r) FROM bclass)
  1.                                THEN (SELECT class FROM bclass WHERE r = a.p3)
  1.                                ELSE (SELECT DECODE(MOD(BITAND(a.p3,TO_NUMBER('FFFF','XXXX')) - 17,2),0,'undo header',1,'undo data', 'error') FROM dual)
  1.                                END  ||']' 
  1.                     ELSE null 
  1.                 END event2 -- event is NULL in ASH if the session is not waiting (session_state = ON CPU)
  1.            , CASE WHEN a.session_type = 'BACKGROUND' OR REGEXP_LIKE(a.program, '.*\([PJ]\d+\)') THEN
  1.                 REGEXP_REPLACE(SUBSTR(a.program,INSTR(a.program,'(')), '\d', 'n')
  1.              ELSE
  1.                 '('||REGEXP_REPLACE(REGEXP_REPLACE(a.program, '(.*)@(.*)(\(.*\))', '\1'), '\d', 'n')||')'
  1.              END || ' ' program2
  1.            , CASE WHEN BITAND(time_model, POWER(2, 01)) = POWER(2, 01) THEN 'DBTIME '  END
  1.            ||CASE WHEN BITAND(time_model, POWER(2, 02)) = POWER(2, 02) THEN 'BACKGROUND '  END
  1.            ||CASE WHEN BITAND(time_model, POWER(2, 03)) = POWER(2, 03) THEN 'CONNECTION_MGMT '  END
  1.            ||CASE WHEN BITAND(time_model, POWER(2, 04)) = POWER(2, 04) THEN 'PARSE '  END
  1.            ||CASE WHEN BITAND(time_model, POWER(2, 05)) = POWER(2, 05) THEN 'FAILED_PARSE '  END
  1.            ||CASE WHEN BITAND(time_model, POWER(2, 06)) = POWER(2, 06) THEN 'NOMEM_PARSE '  END
  1.            ||CASE WHEN BITAND(time_model, POWER(2, 07)) = POWER(2, 07) THEN 'HARD_PARSE '  END
  1.            ||CASE WHEN BITAND(time_model, POWER(2, 08)) = POWER(2, 08) THEN 'NO_SHARERS_PARSE '  END
  1.            ||CASE WHEN BITAND(time_model, POWER(2, 09)) = POWER(2, 09) THEN 'BIND_MISMATCH_PARSE '  END
  1.            ||CASE WHEN BITAND(time_model, POWER(2, 10)) = POWER(2, 10) THEN 'SQL_EXECUTION '  END
  1.            ||CASE WHEN BITAND(time_model, POWER(2, 11)) = POWER(2, 11) THEN 'PLSQL_EXECUTION '  END
  1.            ||CASE WHEN BITAND(time_model, POWER(2, 12)) = POWER(2, 12) THEN 'PLSQL_RPC '  END
  1.            ||CASE WHEN BITAND(time_model, POWER(2, 13)) = POWER(2, 13) THEN 'PLSQL_COMPILATION '  END
  1.            ||CASE WHEN BITAND(time_model, POWER(2, 14)) = POWER(2, 14) THEN 'JAVA_EXECUTION '  END
  1.            ||CASE WHEN BITAND(time_model, POWER(2, 15)) = POWER(2, 15) THEN 'BIND '  END
  1.            ||CASE WHEN BITAND(time_model, POWER(2, 16)) = POWER(2, 16) THEN 'CURSOR_CLOSE '  END
  1.            ||CASE WHEN BITAND(time_model, POWER(2, 17)) = POWER(2, 17) THEN 'SEQUENCE_LOAD '  END
  1.            ||CASE WHEN BITAND(time_model, POWER(2, 18)) = POWER(2, 18) THEN 'INMEMORY_QUERY '  END
  1.            ||CASE WHEN BITAND(time_model, POWER(2, 19)) = POWER(2, 19) THEN 'INMEMORY_POPULATE '  END
  1.            ||CASE WHEN BITAND(time_model, POWER(2, 20)) = POWER(2, 20) THEN 'INMEMORY_PREPOPULATE '  END
  1.            ||CASE WHEN BITAND(time_model, POWER(2, 21)) = POWER(2, 21) THEN 'INMEMORY_REPOPULATE '  END
  1.            ||CASE WHEN BITAND(time_model, POWER(2, 22)) = POWER(2, 22) THEN 'INMEMORY_TREPOPULATE '  END
  1.            ||CASE WHEN BITAND(time_model, POWER(2, 23)) = POWER(2, 23) THEN 'TABLESPACE_ENCRYPTION ' END time_model_name
  1.         FROM gv$active_session_history a) a
  1.       , dba_users u
  1.       , (SELECT
  1.              object_id,data_object_id,owner,object_name,subobject_name,object_type
  1.            , owner||'.'||object_name obj
  1.            , owner||'.'||object_name||' ['||object_type||']' objt
  1.          FROM dba_objects) o
  1.     WHERE
  1.         a.user_id = u.user_id (+)
  1.     AND a.current_obj# = o.object_id(+)
  1.     AND &2
  1.     AND sample_time BETWEEN &3 AND &4
  1.     GROUP BY
  1.         &1
  1.     ORDER BY
  1.         TotalSeconds DESC
  1.        , &1
  1. )
  1. WHERE
  1.     ROWNUM <= 15
  1. /

简单使用案例:

 

  1. @ashtop username,sql_id session_type='FOREGROUND' trunc(sysdate)+8/24 sysdate
  1.  
  1. @ashtop username,sql_id session_type='BACKGROUND' trunc(sysdate)+8/24 sysdate
  1.  
  1. @ashtop username,sql_id 1=1 sysdate-30/1440 sysdate

 

注意,这个脚本只适用于Oracle 11g以及以上的版本,Oracle 10ggv$active_session_history,没有time_modelpx_flags等字段。   

 

下面是我简单魔改的适用于Oracle 10g的脚本:ashtop_10g.sql 

  1. -- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
  1. -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
  1.  
  1. --------------------------------------------------------------------------------
  1. --
  1. -- File name:   ashtop.sql v1.2
  1. -- Purpose:     Display top ASH time (count of ASH samples) grouped by your
  1. --              specified dimensions
  1. --             
  1. -- Author:      Tanel Poder
  1. -- Copyright:   (c) http://blog.tanelpoder.com
  1. --             
  1. -- Usage:      
  1. --     @ashtop <grouping_cols> <filters> <fromtime> <totime>
  1. --
  1. -- Example:
  1. --     @ashtop username,sql_id session_type='FOREGROUND' sysdate-1/24 sysdate
  1. --
  1. -- Other:
  1. --     This script uses only the in-memory GV$ACTIVE_SESSION_HISTORY, use
  1. --     @dashtop.sql for accessiong the DBA_HIST_ACTIVE_SESS_HISTORY archive
  1. --  
  1. --------------------------------Add by Kerry------------------------------------
  1. -- Oracle 10ggv$active_session_history 没有time_modelpx_flags等字段。
  1.         
  1. --------------------------------------------------------------------------------
  1. COL "%This" FOR A7
  1. --COL p1     FOR 99999999999999
  1. --COL p2     FOR 99999999999999
  1. --COL p3     FOR 99999999999999
  1. COL p1text              FOR A30 word_wrap
  1. COL p2text              FOR A30 word_wrap
  1. COL p3text              FOR A30 word_wrap
  1. COL p1hex               FOR A17
  1. COL p2hex               FOR A17
  1. COL p3hex               FOR A17
  1. COL dop                 FOR 99
  1. COL AAS                 FOR 9999.9
  1. COL totalseconds HEAD "Total|Seconds" FOR 99999999
  1. COL dist_sqlexec_seen HEAD "Distinct|Execs Seen" FOR 999999
  1. COL event               FOR A42 WORD_WRAP
  1. COL event2              FOR A42 WORD_WRAP
  1. COL time_model_name     FOR A50 WORD_WRAP
  1. COL program2            FOR A40 TRUNCATE
  1. COL username            FOR A20 wrap
  1. COL obj                 FOR A30
  1. COL objt                FOR A50
  1. COL sql_opname          FOR A20
  1. COL top_level_call_name FOR A30
  1. COL wait_class          FOR A15
  1.  
  1. SELECT
  1.     *
  1. FROM (
  1.     WITH bclass AS (SELECT /*+ INLINE */ class, ROWNUM r from v$waitstat)
  1.     SELECT /*+ LEADING(a) USE_HASH(u) */
  1.         COUNT(*)                                                     totalseconds
  1.       , ROUND(COUNT(*) / ((CAST(&4 AS DATE) - CAST(&3 AS DATE)) * 86400), 1) AAS
  1.       , LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ')||' |' "%This"
  1.       , &1
  1.       , TO_CHAR(MIN(sample_time), 'YYYY-MM-DD HH24:MI:SS') first_seen
  1.       , TO_CHAR(MAX(sample_time), 'YYYY-MM-DD HH24:MI:SS') last_seen
  1. --    , MAX(sql_exec_id) - MIN(sql_exec_id)
  1. --    , COUNT(DISTINCT sql_exec_start||':'||sql_exec_id) dist_sqlexec_seen
  1.     FROM
  1.         (SELECT
  1.              a.*
  1.            , session_id sid
  1.            , session_serial# serial
  1.            , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex
  1.            , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex
  1.            , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex
  1.            --, TRUNC(px_flags / 2097152) dop
  1.            , NVL(a.event, a.session_state)||
  1.                 CASE 
  1.                     WHEN a.event like 'enq%' AND session_state = 'WAITING'
  1.                     THEN ' [mode='||BITAND(p1, POWER(2,14)-1)||']'
  1.                     WHEN a.event IN (SELECT name FROM v$event_name WHERE parameter3 = 'class#')
  1.                     THEN ' ['||CASE WHEN a.p3 <= (SELECT MAX(r) FROM bclass)
  1.                                THEN (SELECT class FROM bclass WHERE r = a.p3)
  1.                                ELSE (SELECT DECODE(MOD(BITAND(a.p3,TO_NUMBER('FFFF','XXXX')) - 17,2),0,'undo header',1,'undo data', 'error') FROM dual)
  1.                                END  ||']' 
  1.                     ELSE null 
  1.                 END event2 -- event is NULL in ASH if the session is not waiting (session_state = ON CPU)
  1.            , CASE WHEN a.session_type = 'BACKGROUND' OR REGEXP_LIKE(a.program, '.*\([PJ]\d+\)') THEN
  1.                 REGEXP_REPLACE(SUBSTR(a.program,INSTR(a.program,'(')), '\d', 'n')
  1.              ELSE
  1.                 '('||REGEXP_REPLACE(REGEXP_REPLACE(a.program, '(.*)@(.*)(\(.*\))', '\1'), '\d', 'n')||')'
  1.              END || ' ' program2
  1.         FROM gv$active_session_history a) a
  1.       , dba_users u
  1.       , (SELECT
  1.              object_id,data_object_id,owner,object_name,subobject_name,object_type
  1.            , owner||'.'||object_name obj
  1.            , owner||'.'||object_name||' ['||object_type||']' objt
  1.          FROM dba_objects) o
  1.     WHERE
  1.         a.user_id = u.user_id (+)
  1.     AND a.current_obj# = o.object_id(+)
  1.     AND &2
  1.     AND sample_time BETWEEN &3 AND &4
  1.     GROUP BY
  1.         &1
  1.     ORDER BY
  1.         TotalSeconds DESC
  1.        , &1
  1. )
  1. WHERE
  1.     ROWNUM <= 15
  1. /

原文链接:http://www.cnblogs.com/kerrycode/p/14219416.html

 友情链接:直通硅谷  点职佳  北美留学生论坛

本站QQ群:前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

W3xue 的所有内容仅供测试,对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接(目前9元/月)请联系QQ:27243702 沸活量
皖ICP备17017327号-2 皖公网安备34020702000426号