经验首页 前端设计 程序设计 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 10g中gv$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 10g中gv$active_session_history，没有time_model、px_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 10g中gv$active_session_history 没有time_model、px_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

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

Oracle热门文章

  • 【Oracle学习笔记】Oralce随机数与随机取样
  • java.sql.SQLSyntaxErrorException: ORA-01722: 无效数字
  • .NET多种数据库大数据批量插入、更新（支持SqlServer、MySql、PgSql和Oracle）
  • Navicat Premium 连接oracle ORA-01017：用户名/口令无效；登陆被拒绝
  • oracle - for in loop 循环更新
  • oracle json 解析函数
  • expdp导出时报错ora-16000
  • WARNING OGG-01519
  • ORA-12514 TNS 监听程序当前无法识别连接描述符中请求服务
  • oracle数据库与其他数据库区别

    • Oracle推荐文章

  • 在RockyLinux 9.3环境中采用RPM模式部署Oracle 19C
  • 你真会判断DataGuard的延迟吗？
  • Oracle rac环境的数据库导入操作记录
  • 使用Oracle进行数据库备份与还原
  • 从零写一个兼容MySQL/Oracle的Proxy中件间（一）《初识Oracle的通信协议》
  • 一文详解TDSQL PG版Oracle兼容性实践
  • 达梦与Oracle逻辑备份与还原性能对比
  • [20211213]提示precompute_subquery.txt
  • ORA-01536: space quota exceeded for tablespace案例
  • ORACLE如何查看当前账号的相关信息总结

    • 本站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号