[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
-- 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
/
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2998085/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20170628]完善ooerr指令碼.txt指令碼
- [20210506]完善tix指令碼.txt指令碼
- [20210407]完善ti.sql指令碼.txtSQL指令碼
- [20210623]完善清除aud指令碼.txt指令碼
- [20201202]完善sosi指令碼.txt指令碼
- [20220510]完善tpt expandz.sql指令碼.txtSQL指令碼
- [20211230]完善sql_id指令碼.txtSQL指令碼
- [20221010]完善descz.sql指令碼.txtSQL指令碼
- [20221101]完善descz.sql指令碼.txtSQL指令碼
- [20221101]完善gts.sql指令碼.txtSQL指令碼
- [20211130]完善tpt t.sql指令碼.txtSQL指令碼
- [20211122]完善descx.sql指令碼.txtSQL指令碼
- [20230414]完善seg2.sql指令碼.txtSQL指令碼
- [20230203]完善awr.sql指令碼.txtSQL指令碼
- [20221012]完善spsw.sql指令碼.txtSQL指令碼
- [20221208]完善bind_cap.sql指令碼.txtSQL指令碼
- [20211129]完善tpt tablist.sql指令碼.txtSQL指令碼
- [20211202]完善d_buffer.sql指令碼.txtSQL指令碼
- [20211129]完善tpt killi.sql指令碼.txtSQL指令碼
- [20220323]完善tpt get_trace.sql指令碼.txtSQL指令碼
- [20220309]完善shp4.sql指令碼.txtSQL指令碼
- [20220217]完善tpt gts.sql指令碼.txtSQL指令碼
- [20230210]建立完善swcnm.sql指令碼.txtSQL指令碼
- [20230123]完善curheapz.sql指令碼.txtSQL指令碼
- [20210125]完善hide.sql指令碼.txtIDESQL指令碼
- [20190416]完善shared latch測試指令碼2.txt指令碼
- [20220823]完善tpt的ashtop.sql指令碼.txtSQL指令碼
- [20231025]完善tpt的trans.sql指令碼.txtSQL指令碼
- [20221208]完善bind_cap_awr.sql指令碼.txtSQL指令碼
- [20211126]完善tpt pr.sql指令碼.txtSQL指令碼
- [20191111]完善bind_cap.sql指令碼.txtSQL指令碼
- [20220311]完善ash_wait_chains指令碼.txtAI指令碼
- [20230302]建立完善tpt o2.sql指令碼.txtSQL指令碼
- [20230203]建立完善sp1x.sql指令碼.txtSQL指令碼
- [20220422]完善tpt ash ash_index_helperx指令碼2.txtIndex指令碼
- [20220111]完善tpt ashash_index_helper指令碼.txtIndex指令碼
- [20220317]補充完善TPT 顯示欄位列的指令碼.txt指令碼
- [20220129]完善tpt ash ash_index_helperx指令碼.txtIndex指令碼