​[20231117]完善ashtt.sql脚本.txt

[20231117]完善ashtt.sql脚本.txt --//正好工作需要使用它,我以前以tpt ashtop.sql脚本来修改的,好像原作者做了许多改动,在他的基础上重新改写ashtt.sql. --//加入提示注解: --     @ashttz  <grouping_cols> <filters> <fromtime> <totime> <owner> <table_name> $ cat ashttz.sql -- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions. -------------------------------------------------------------------------------- -- -- File name:   ashtop.sql v1.2 -- Purpose:     Display top ASH time (count of ASH samples) grouped by your --              specified dimensions --               -- Author:      Tanel Poder -- Copyright:   (c) http://blog.tanelpoder.com --               -- Usage:        --     @ashtop <grouping_cols> <filters> <fromtime> <totime> --     @ashttz <grouping_cols> <filters> <fromtime> <totime> <owner> <table_name> -- -- Example: --     @ashtop username,sql_id session_type='FOREGROUND' sysdate-1/24 sysdate -- -- Other: --     This script uses only the in-memory GV$ACTIVE_SESSION_HISTORY, use --     @dashtop.sql for accessiong the DBA_HIST_ACTIVE_SESS_HISTORY archive --               -------------------------------------------------------------------------------- COL "%This" FOR A7 --COL p1     FOR 99999999999999 --COL p2     FOR 99999999999999 --COL p3     FOR 99999999999999 COL p1text              FOR A30 word_wrap COL p2text              FOR A30 word_wrap COL p3text              FOR A30 word_wrap COL p1hex               FOR A17 COL p2hex               FOR A17 COL p3hex               FOR A17 COL dop                 FOR 99 COL AAS                 FOR 9999.9 COL totalseconds        HEAD "Total|Seconds" FOR 99999999 COL dist_sqlexec_seen   HEAD "Distinct|Execs Seen" FOR 999999 COL dist_timestamps     HEAD "Distinct|Tstamps" FOR 999999 COL event               FOR A42 WORD_WRAP COL event2              FOR A42 WORD_WRAP COL time_model_name     FOR A50 WORD_WRAP COL program2            FOR A40 TRUNCATE COL username            FOR A20 wrap COL obj                 FOR A50 COL objt                FOR A60 COL sql_opname          FOR A20 COL top_level_call_name FOR A30 COL wait_class          FOR A15 with sqla as (SELECT /*+ MATERIALIZE ordered use_hash(d) use_hash(c) */                                        DISTINCT c.kglobt03 sql_id                                   FROM sys.x$kglob o                                       ,sys.x$kgldp d                                       ,sys.x$kglcursor c                                  WHERE     o.inst_id = USERENV ('Instance')                                        AND d.inst_id = USERENV ('Instance')                                        AND c.inst_id = USERENV ('Instance')                                        AND o.kglnaown = upper(nvl('&5',user))                                        AND o.kglnaobj = upper('&6')                                        AND d.kglrfhdl = o.kglhdadr                                        AND c.kglhdadr = d.kglhdadr) SELECT     * FROM (     WITH bclass AS (SELECT /*+ INLINE */ class, ROWNUM r from v$waitstat)     SELECT /*+ LEADING(a) USE_HASH(u) */         COUNT(*)                                                     totalseconds       , ROUND(COUNT(*) / ((CAST(&4 AS DATE) - CAST(&3 AS DATE)) * 86400), 1) AAS       , LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ')||' |' "%This"       , &1       , TO_CHAR(MIN(sample_time), 'YYYY-MM-DD HH24:MI:SS') first_seen       , TO_CHAR(MAX(sample_time), 'YYYY-MM-DD HH24:MI:SS') last_seen --    , MAX(sql_exec_id) - MIN(sql_exec_id)       , COUNT(DISTINCT sql_exec_start||':'||sql_exec_id) dist_sqlexec_seen       , COUNT(DISTINCT sample_time) dist_timestamps     FROM         (SELECT              a.*            ,lower(a.program) program1            ,lower(a.module)  module1            , session_id sid            , session_serial# serial            , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex            , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex            , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex            , TRUNC(px_flags / 2097152) dop            , NVL(a.event, a.session_state)||                 CASE                     WHEN a.event like 'enq%' AND session_state = 'WAITING'                     THEN ' [mode='||BITAND(p1, POWER(2,14)-1)||']'                     WHEN a.event IN (SELECT name FROM v$event_name WHERE parameter3 = 'class#')                     THEN ' ['||CASE WHEN a.p3 <= (SELECT MAX(r) FROM bclass)                                THEN (SELECT class FROM bclass WHERE r = a.p3)                                ELSE (SELECT DECODE(MOD(BITAND(a.p3,TO_NUMBER('FFFF','XXXX')) - 17,2),0,'undo header',1,'undo data', 'error') FROM dual)                                END  ||']'                     ELSE null                 END event2 -- event is NULL in ASH if the session is not waiting (session_state = ON CPU)            , CASE WHEN a.session_type = 'BACKGROUND' OR REGEXP_LIKE(a.program, '.*\([PJ]\d+\)') THEN                 REGEXP_REPLACE(SUBSTR(a.program,INSTR(a.program,'(')), '\d', 'n')              ELSE                 '('||REGEXP_REPLACE(REGEXP_REPLACE(a.program, '(.*)@(.*)(\(.*\))', '\1'), '\d', 'n')||')'              END || ' ' program2            , CASE WHEN BITAND(time_model, POWER(2, 01)) = POWER(2, 01) THEN 'DBTIME '  END            ||CASE WHEN BITAND(time_model, POWER(2, 02)) = POWER(2, 02) THEN 'BACKGROUND '  END            ||CASE WHEN BITAND(time_model, POWER(2, 03)) = POWER(2, 03) THEN 'CONNECTION_MGMT '  END            ||CASE WHEN BITAND(time_model, POWER(2, 04)) = POWER(2, 04) THEN 'PARSE '  END            ||CASE WHEN BITAND(time_model, POWER(2, 05)) = POWER(2, 05) THEN 'FAILED_PARSE '  END            ||CASE WHEN BITAND(time_model, POWER(2, 06)) = POWER(2, 06) THEN 'NOMEM_PARSE '  END            ||CASE WHEN BITAND(time_model, POWER(2, 07)) = POWER(2, 07) THEN 'HARD_PARSE '  END            ||CASE WHEN BITAND(time_model, POWER(2, 08)) = POWER(2, 08) THEN 'NO_SHARERS_PARSE '  END            ||CASE WHEN BITAND(time_model, POWER(2, 09)) = POWER(2, 09) THEN 'BIND_MISMATCH_PARSE '  END            ||CASE WHEN BITAND(time_model, POWER(2, 10)) = POWER(2, 10) THEN 'SQL_EXECUTION '  END            ||CASE WHEN BITAND(time_model, POWER(2, 11)) = POWER(2, 11) THEN 'PLSQL_EXECUTION '  END            ||CASE WHEN BITAND(time_model, POWER(2, 12)) = POWER(2, 12) THEN 'PLSQL_RPC '  END            ||CASE WHEN BITAND(time_model, POWER(2, 13)) = POWER(2, 13) THEN 'PLSQL_COMPILATION '  END            ||CASE WHEN BITAND(time_model, POWER(2, 14)) = POWER(2, 14) THEN 'JAVA_EXECUTION '  END            ||CASE WHEN BITAND(time_model, POWER(2, 15)) = POWER(2, 15) THEN 'BIND '  END            ||CASE WHEN BITAND(time_model, POWER(2, 16)) = POWER(2, 16) THEN 'CURSOR_CLOSE '  END            ||CASE WHEN BITAND(time_model, POWER(2, 17)) = POWER(2, 17) THEN 'SEQUENCE_LOAD '  END            ||CASE WHEN BITAND(time_model, POWER(2, 18)) = POWER(2, 18) THEN 'INMEMORY_QUERY '  END            ||CASE WHEN BITAND(time_model, POWER(2, 19)) = POWER(2, 19) THEN 'INMEMORY_POPULATE '  END            ||CASE WHEN BITAND(time_model, POWER(2, 20)) = POWER(2, 20) THEN 'INMEMORY_PREPOPULATE '  END            ||CASE WHEN BITAND(time_model, POWER(2, 21)) = POWER(2, 21) THEN 'INMEMORY_REPOPULATE '  END            ||CASE WHEN BITAND(time_model, POWER(2, 22)) = POWER(2, 22) THEN 'INMEMORY_TREPOPULATE '  END            ||CASE WHEN BITAND(time_model, POWER(2, 23)) = POWER(2, 23) THEN 'TABLESPACE_ENCRYPTION ' END time_model_name         FROM gv$active_session_history a) a       , dba_users u       , (SELECT              object_id,data_object_id,owner,object_name,subobject_name,object_type            , owner||'.'||object_name obj            , owner||'.'||object_name||' ['||object_type||']' objt          FROM dba_objects) o     WHERE         a.user_id = u.user_id (+)     AND a.current_obj# = o.object_id(+)     AND &2     AND sql_id IN (SELECT sql_id FROM sqla )     AND sample_time BETWEEN &3 AND &4     GROUP BY         &1     ORDER BY         TotalSeconds DESC        , &1 ) WHERE     ROWNUM <= 30 /