[20211223]tpt ash ash_index_helperx指令碼.txt
[20211223]tpt ash ash_index_helperx指令碼.txt
--//tpt scripts包的ash目錄下有兩個指令碼ash_index_helperx.sql，ash_index_helper.sql指令碼用來定位表來提供索引建立幫助。
--//不過我下載執行包如下錯誤。
xxxx> @ash/ash_index_helper % PPPPPP_HHH.EMR_WJZ &day
-- Santa's Little (Index) Helper BETA v0.5 - by Tanel Poder ( )
AND sq.sql_plan_hash_value(+) = plan.plan_hash_value
*
ERROR at line 88:
ORA-01417: a table may be outer joined to at most one other table
--//我僅僅修改刪除外連線的加號+在118行處。修改如下：
--//ash_index_helperx指令碼修來類似。
-- Copyright 2018 Tanel Poder. All rights reserved. More info at
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
SET LINESIZE 999 PAGESIZE 5000 TRIMOUT ON TRIMSPOOL ON
COL accessed_table HEAD Accessed_Table FOR a20
COL aindex_operation HEAD Plan_Operation FOR a54
COL aindex_predicates HEAD PREDICATES FOR a60 truncate
COL obj_alias_qbc_name FOR a40
COL options FOR a30
COL AAS FOR 9999.9
COL cpu_pct HEAD CPU FOR A5
COL wait_pct HEAD WAIT FOR A5
COL ela_sec_exec HEAD "ELA_SEC/EXEC" FOR 9999990.999
COL aindex_plan_hash_value HEAD PLAN_HASH_VALUE PRINT
COL aindex_sql_id HEAD SQL_ID NOPRINT
COL aindex_sql_child HEAD "CHILD" PRINT
COL aindex_sample_time HEAD SAMPLE_HOUR
COL projection FOR A520
COL pct_child HEAD "Activity %" FOR A8
COL pct_child_vis HEAD "Visual" FOR A12
COL aindex_id HEAD "ID" FOR 9999
COL aindex_parent_id HEAD "PID" FOR 9999
PROMPT
PROMPT -- Santa's Little (Index) Helper BETA v0.5 - by Tanel Poder ( )
WITH
tab AS (SELECT /*+ NO_MERGE */ owner, table_name, num_rows
FROM dba_tables
WHERE UPPER(table_name) LIKE
UPPER(CASE
WHEN INSTR('&2','.') > 0 THEN
SUBSTR('&2',INSTR('&2','.')+1)
ELSE
'&2'
END
) ESCAPE '\'
AND owner LIKE
CASE WHEN INSTR('&2','.') > 0 THEN
UPPER(SUBSTR('&2',1,INSTR('&2','.')-1))
ELSE
user
END ESCAPE '\'
),
ind AS (SELECT /*+ NO_MERGE */ owner, index_name, table_owner, table_name
FROM dba_indexes
WHERE (table_owner, table_name) IN (SELECT owner, table_name FROM tab)),
sample_times AS (
select * from dual
),
sq AS (
SELECT
count(*) samples
, ash.sql_id
, ash.sql_child_number
, ash.sql_plan_hash_value
, NVL(ash.sql_plan_line_id,1) sql_plan_line_id -- this is because simple "planless" operations like single-row insert
, ash.sql_plan_operation
, ash.sql_plan_options
, ash.session_state
, ash.wait_class
, ash.event
FROM
v$active_session_history ash
WHERE
1=1
AND ash.sql_plan_operation IN ('TABLE ACCESS', 'INDEX')
AND ash.sql_id LIKE '&1'
AND ash.sample_time BETWEEN &3 AND &4
GROUP BY
ash.sql_id
, ash.sql_child_number
, ash.sql_plan_hash_value
, NVL(ash.sql_plan_line_id,1)
, ash.sql_plan_operation
, ash.sql_plan_options
, ash.session_state
, ash.wait_class
, ash.event
),
ash_and_plan AS (
SELECT
plan.sql_id
, plan.child_number
, plan.plan_hash_value
, sq.samples seconds
, LPAD(TO_CHAR(ROUND(RATIO_TO_REPORT(sq.samples) OVER (PARTITION BY sq.sql_id, sq.sql_plan_hash_value) * 100, 1), 999.9)||' %',8) pct_child
, '|'||RPAD( NVL( LPAD('#', ROUND(RATIO_TO_REPORT(sq.samples) OVER (PARTITION BY sq.sql_id, sq.sql_plan_hash_value) * 10), '#'), ' '), 10,' ')||'|' pct_child_vis
--, LPAD(plan.id,4)||CASE WHEN parent_id IS NULL THEN ' ' ELSE ' <- ' END||LPAD(plan.parent_id,4) aindex_plan_id
, plan.id aindex_id
, plan.parent_id aindex_parent_id
, plan.operation ||' '|| plan.options || NVL2(plan.object_name, ' ['||plan.object_owner||'.'||plan.object_name ||']', null) aindex_operation
, plan.object_owner
, plan.object_name
, plan.object_type
, plan.cardinality
, stat.executions
, stat.elapsed_time
, sq.session_state
, sq.wait_class
, sq.event
, plan.object_alias || CASE WHEN plan.qblock_name IS NOT NULL THEN ' ['|| plan.qblock_name || ']' END obj_alias_qbc_name
, CASE WHEN plan.access_predicates IS NOT NULL THEN '[A:] '|| SUBSTR(plan.access_predicates,1,1994) END || CASE WHEN plan.filter_predicates IS NOT NULL THEN ' [F:] ' || SUBSTR(plan.filter_predicates,1,1994) END aindex_predicates
-- , plan.projection
FROM
v$sql_plan plan
, v$sqlstats_plan_hash stat
, sq
WHERE
1=1
--AND sq.sql_id(+) = plan.sql_id
--AND sq.sql_child_number(+) = plan.child_number
--AND sq.sql_plan_line_id(+) = plan.id
--AND sq.sql_plan_hash_value(+) = plan.plan_hash_value
AND sq.sql_id = plan.sql_id
AND sq.sql_child_number = plan.child_number
AND sq.sql_plan_line_id = plan.id
AND sq.sql_plan_hash_value = plan.plan_hash_value
--
AND sq.sql_id(+) = stat.sql_id
AND sq.sql_plan_hash_value(+) = stat.plan_hash_value
--AND sq.sql_plan_hash_value = stat.plan_hash_value
AND stat.sql_id = plan.sql_id
AND stat.plan_hash_value = plan.plan_hash_value
--
AND plan.sql_id LIKE '&1'
----
)
SELECT * FROM (
SELECT
SUM(ap.seconds) seconds
, ROUND(SUM(ap.seconds) / ((CAST(&4 AS DATE) - CAST(&3 AS DATE)) * 86400), 1) AAS
, LPAD(TO_CHAR(ROUND(SUM(CASE WHEN ap.session_state = 'ON CPU' THEN ap.seconds ELSE 0 END) / SUM(ap.seconds) * 100))||'%',4) cpu_pct
, LPAD(TO_CHAR(ROUND(SUM(CASE WHEN ap.session_state = 'WAITING' THEN ap.seconds ELSE 0 END) / SUM(ap.seconds) * 100))||'%',4) wait_pct
--, SUM(CASE WHEN ap.wait_class = 'User I/O' THEN ap.seconds ELSE 0 END) iowait_sec
--, t.owner||'.'||table_name accessed_table
, table_name accessed_table
, ap.aindex_operation
, ap.cardinality plan_card
, t.num_rows table_rows
, ap.cardinality / NULLIF(t.num_rows,0) * 100 filter_pct
, ap.executions sql_execs
, ROUND(ap.elapsed_time / NULLIF(ap.executions,0) / 1000000,3) ela_sec_exec
, ap.aindex_predicates
-- , COUNT(DISTINCT ap.sql_id) dist_sqlids
-- , COUNT(DISTINCT ap.plan_hash_value) dist_plans
, MIN(ap.sql_id) sql_id
-- , MAX(ap.sql_id)
-- , ap.projection
FROM
ash_and_plan ap
, (SELECT tab.*, 'TABLE' object_type, tab.owner object_owner, tab.table_name object_name FROM tab
UNION ALL
SELECT tab.*, 'INDEX', ind.owner object_owner, ind.index_name object_name
FROM tab, ind
WHERE tab.owner = ind.table_owner AND tab.table_name = ind.table_name
) t
WHERE
ap.object_owner = t.object_owner AND ap.object_name = t.object_name AND SUBSTR(ap.object_type,1,5) = t.object_type
AND ap.seconds > 0
GROUP BY
t.owner
, t.table_name
, ap.aindex_operation
, t.num_rows
, ap.cardinality
, ap.executions
, ap.elapsed_time
, ap.aindex_predicates
-- , ap.projection
ORDER BY
seconds DESC
)
WHERE rownum <= 50
/
xxxx> @ash/ash_index_helper % PPPPPP_HHH.EMR_WJZ &day
-- Santa's Little (Index) Helper BETA v0.5 - by Tanel Poder ( )
SECONDS AAS CPU WAIT Accessed_Table Plan_Operation PLAN_CARD TABLE_ROWS FILTER_PCT SQL_EXECS ELA_SEC/EXEC PREDICATES SQL_ID
---------- ------- ----- ----- -------------------- ------------------------------------------------------ ---------- ---------- ---------- ---------- ------------ ------------------------------------------------------------ -------------
607 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 355866 0.023 [A:] ((NVL("QRBZ_YS",0)=:SYS_B_03 OR NVL("QRBZ_YS",0) IS NUL 8f7f72arar3py
421 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 247498 0.023 [A:] ((NVL("QRBZ_YS",0)=:SYS_B_03 OR NVL("QRBZ_YS",0) IS NUL djhrk78afjdyy
332 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 188324 0.022 [A:] ((NVL("QRBZ_YS",0)=:SYS_B_03 OR NVL("QRBZ_YS",0) IS NUL 6nzva10qqnrwa
292 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 127460 0.022 [A:] ((NVL("QRBZ_YS",0)=:SYS_B_03 OR NVL("QRBZ_YS",0) IS NUL arp9g8xcbgszg
103 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 74952 0.024 [A:] ("T1"."CHECK_DATE">SYSDATE@!-:SYS_B_3 AND (NVL("QRBZ_HS 0hdxt5nbannwn
94 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 55866 0.022 [A:] ((NVL("QRBZ_YS",0)=:SYS_B_03 OR NVL("QRBZ_YS",0) IS NUL a298xspjq5xh1
56 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 29661 0.022 [A:] ((NVL("QRBZ_YS",0)=:SYS_B_03 OR NVL("QRBZ_YS",0) IS NUL fc932tp1afh85
36 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 18420 0.022 [A:] ((NVL("QRBZ_YS",0)=:SYS_B_03 OR NVL("QRBZ_YS",0) IS NUL d6nq8ahwvqcwc
20 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 15152 0.022 [A:] (NVL("QRBZ_HS",0)=:SYS_B_02 AND "T1"."CHECK_DATE">=TO_D 6v1u0yhqkx90z
13 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 6699 0.023 [A:] ((NVL("QRBZ_HS",0)=:SYS_B_02 OR NVL("QRBZ_HS",0) IS NUL gy3ca7rznha52
12 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 6033 0.023 [A:] ((NVL("QRBZ_HS",0)=:SYS_B_02 OR NVL("QRBZ_HS",0) IS NUL fd4wuqrbnk728
10 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 6007 0.023 [A:] (NVL("QRBZ_HS",0)=:SYS_B_02 AND "T1"."CHECK_DATE">=TO_D 9fayz6g6tsuw5
10 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 5027 0.022 [A:] (NVL("QRBZ_HS",0)=:SYS_B_02 AND "T1"."CHECK_DATE">=TO_D 1n2bd7d11d8kz
10 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 6554 0.022 [A:] (NVL("QRBZ_HS",0)=:SYS_B_02 AND "T1"."CHECK_DATE">=TO_D amb27snvunp46
9 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 3615 0.022 [A:] (NVL("QRBZ_HS",0)=:SYS_B_02 AND "T1"."CHECK_DATE">=TO_D au7jzujzkkk3d
8 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 3586 0.023 [A:] ((NVL("QRBZ_HS",0)=:SYS_B_02 OR NVL("QRBZ_HS",0) IS NUL d16zspm00rzm1
7 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 2258 0.023 [A:] (NVL("QRBZ_HS",0)=:SYS_B_02 AND "T1"."CHECK_DATE">=TO_D 18pfsvgzxv5yx
7 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 1769 0.024 [A:] ((NVL("QRBZ_HS",0)=:SYS_B_02 OR NVL("QRBZ_HS",0) IS NUL 4y1aa8bz8wz3u
6 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 901 0.022 [A:] ((NVL("QRBZ_YS",0)=:SYS_B_03 OR NVL("QRBZ_YS",0) IS NUL a7kvhyr0mzbn5
6 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 1918 0.022 [A:] (NVL("QRBZ_HS",0)=:SYS_B_2 AND "T1"."CHECK_DATE">=TO_DA cyxvj52k6tdk7
6 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 29240 0.285 [A:] ("PATIENT_ID"=:AS_MZHM AND "A"."REQUEST_DATE">SYSDATE@! 85czs0cvnscvj
5 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 6017 0.022 [A:] (NVL("QRBZ_HS",0)=:SYS_B_02 AND "T1"."CHECK_DATE">=TO_D g0yhbm687478z
4 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 2532 0.022 [A:] (NVL("QRBZ_HS",0)=:SYS_B_02 AND "T1"."CHECK_DATE">=TO_D 1ffpyd02n05z2
4 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 1676 0.022 [A:] (NVL("QRBZ_HS",0)=:SYS_B_02 AND "T1"."CHECK_DATE">=TO_D gz7vvu41y2czv
4 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 1079 0.022 [A:] (NVL("QRBZ_HS",0)=:SYS_B_02 AND "T1"."CHECK_DATE">=TO_D 19h143pgc65tg
4 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 1874 0.022 [A:] (NVL("QRBZ_HS",0)=:SYS_B_02 AND "T1"."CHECK_DATE">=TO_D bqsv58nrr2r9x
3 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 3002 0.022 [A:] (NVL("QRBZ_HS",0)=:SYS_B_2 AND "T1"."CHECK_DATE">=TO_DA 4kbhs1jm2gfsu
2 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 355868 0.023 [A:] ((NVL("QRBZ_YS",0)=:SYS_B_03 OR NVL("QRBZ_YS",0) IS NUL 8f7f72arar3py
2 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 326 0.024 [A:] ((NVL("QRBZ_HS",0)=:SYS_B_02 OR NVL("QRBZ_HS",0) IS NUL 8c55ht7hxg7dp
2 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 1252 0.023 [A:] ((NVL("QRBZ_HS",0)=:SYS_B_02 OR NVL("QRBZ_HS",0) IS NUL fyg4hnfnm1qu3
1 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 108 0.024 [A:] ((NVL("QRBZ_YS",0)=:SYS_B_003 OR NVL("QRBZ_YS",0) IS NU 8bbb73xj6y9sq
1 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 2816 0.022 [A:] ((NVL("QRBZ_YS",0)=:SYS_B_03 OR NVL("QRBZ_YS",0) IS NUL 6jwpkxnrym6bb
1 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 466 0.022 [A:] ((NVL("QRBZ_YS",0)=:SYS_B_03 OR NVL("QRBZ_YS",0) IS NUL d9m4sgr1q902m
1 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 201 0.024 [A:] ((NVL("QRBZ_HS",0)=:SYS_B_02 OR NVL("QRBZ_HS",0) IS NUL faty2cmrp2cs3
1 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 503 0.024 [A:] ((NVL("QRBZ_HS",0)=:SYS_B_02 OR NVL("QRBZ_HS",0) IS NUL drgjh3z2rm3cp
1 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 608 0.023 [A:] ((NVL("QRBZ_HS",0)=:SYS_B_02 OR NVL("QRBZ_HS",0) IS NUL 3v8bkaasw596z
1 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 809 0.264 [A:] ("PATIENT_ID"=:AS_MZHM AND "A"."REQUEST_DATE">SYSDATE@! awww95g9gqa2z
1 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 439 0.024 [A:] ((NVL("QRBZ_HS",0)=:SYS_B_02 OR NVL("QRBZ_HS",0) IS NUL 75sgcvxc5hd17
1 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 815 0.024 [A:] ((NVL("QRBZ_YS",0)=:SYS_B_003 OR NVL("QRBZ_YS",0) IS NU 1yxuj3nd676mu
1 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 247 0.024 [A:] ((NVL("QRBZ_YS",0)=:SYS_B_003 OR NVL("QRBZ_YS",0) IS NU 5658a3vymgmsn
1 .0 100% 0% EMR_WJZ TABLE ACCESS STORAGE FULL [PPPPPP_HHH.EMR_WJZ] 1 39467 .002533762 213 0.024 [A:] ((NVL("QRBZ_YS",0)=:SYS_B_003 OR NVL("QRBZ_YS",0) IS NU ac0vbgfa7tjjv
41 rows selected.
--//PLAN_CARD=1,說明許多情況下建立索引有很好的執行效率，把涉及這些表的sql語句看一下索引就很好了。
--//實際上簡單一點也可以執行：
xxxx> @ ashtop username,sql_id "sql_id in (select sql_id from v$sqlarea where lower(sql_text) like '%emr_wjz%')" trunc(sysdate-1) sysdate
Total Distinct Distinct
Seconds AAS %This USERNAME SQL_ID FIRST_SEEN LAST_SEEN Execs Seen Tstamps
--------- ------- ------- -------------------- ------------- ------------------- ------------------- ---------- --------
398 .0 81% | PPPPPP_HHH 85czs0cvnscvj 2021-12-22 15:47:16 2021-12-23 09:17:47 398 396
63 .0 13% | PPPPPP_HHH awww95g9gqa2z 2021-12-22 15:47:20 2021-12-23 07:39:41 63 63
15 .0 3% | SYS ct9njugaqhj94 2021-12-23 08:58:59 2021-12-23 09:04:42 2 15
8 .0 2% | SYS f5vp0m2fxgt0x 2021-12-23 09:01:58 2021-12-23 09:10:58 2 8
5 .0 1% | SYS 37tgn0ys2rv11 2021-12-23 09:10:07 2021-12-23 09:10:11 2 5
4 .0 1% | SYS 0sk4hxy7h69xf 2021-12-23 09:01:38 2021-12-23 09:01:52 2 4
1 .0 0% | PPPPPP_HHH 97jbadru6td72 2021-12-22 19:45:35 2021-12-22 19:45:35 1 1
7 rows selected.
--//缺點是僅僅查詢包含該字串的語句。你可以發現幾條SYS使用者執行的語句，實際上是ash_index_helper執行的。
xxxx> @ dashtop username,sql_id "sql_id in (select sql_id from v$sqlarea where lower(sql_text) like '%emr_wjz%')" trunc(sysdate-1) sysdate
Total
Seconds AAS %This USERNAME SQL_ID FIRST_SEEN LAST_SEEN
--------- ------- ------- -------------------- ------------- ------------------- -------------------
630 .0 93% PPPPPP_HHH 85czs0cvnscvj 2021-12-22 01:18:09 2021-12-23 08:19:38
50 .0 7% PPPPPP_HHH awww95g9gqa2z 2021-12-22 10:32:06 2021-12-23 05:46:04
--//我以前也改寫ashtop支援查詢特定表的SQL語句,連結blog.itpub.net/267265/viewspace-2749595/
xxxx> @ashtt sql_id 1=1 &day PPPPPP_HHH EMR_WJZ
Total
Seconds AAS %This SQL_ID FIRST_SEEN LAST_SEEN
--------- ------- ------- ------------- ------------------- -------------------
689 .0 23% | 8f7f72arar3py 2021-12-22 15:38:25 2021-12-23 09:10:27
491 .0 17% | djhrk78afjdyy 2021-12-22 15:38:06 2021-12-23 09:10:33
395 .0 13% | 85czs0cvnscvj 2021-12-22 15:39:25 2021-12-23 09:08:01
381 .0 13% | 6nzva10qqnrwa 2021-12-22 15:38:47 2021-12-23 09:08:47
348 .0 12% | arp9g8xcbgszg 2021-12-22 15:39:44 2021-12-23 09:10:33
167 .0 6% | 0hdxt5nbannwn 2021-12-22 15:39:31 2021-12-23 08:59:28
111 .0 4% | a298xspjq5xh1 2021-12-22 15:40:23 2021-12-23 09:05:54
63 .0 2% | awww95g9gqa2z 2021-12-22 15:47:20 2021-12-23 07:39:41
62 .0 2% | fc932tp1afh85 2021-12-22 15:52:35 2021-12-23 09:10:47
43 .0 1% | d6nq8ahwvqcwc 2021-12-22 15:38:07 2021-12-23 09:03:25
25 .0 1% | 6v1u0yhqkx90z 2021-12-22 15:43:53 2021-12-23 09:01:58
15 .0 1% | au7jzujzkkk3d 2021-12-22 15:38:05 2021-12-23 08:57:56
15 .0 1% | gy3ca7rznha52 2021-12-22 15:50:50 2021-12-23 08:57:27
14 .0 0% | fd4wuqrbnk728 2021-12-22 16:15:17 2021-12-23 09:04:19
12 .0 0% | 1n2bd7d11d8kz 2021-12-22 16:10:56 2021-12-23 08:58:47
12 .0 0% | 9fayz6g6tsuw5 2021-12-22 16:31:53 2021-12-23 09:00:24
10 .0 0% | amb27snvunp46 2021-12-22 15:44:48 2021-12-23 08:30:44
8 .0 0% | 18pfsvgzxv5yx 2021-12-22 16:10:59 2021-12-23 02:01:52
8 .0 0% | d16zspm00rzm1 2021-12-22 16:09:53 2021-12-23 08:42:38
7 .0 0% | 4y1aa8bz8wz3u 2021-12-22 15:56:24 2021-12-23 08:51:26
7 .0 0% | bqsv58nrr2r9x 2021-12-22 18:50:59 2021-12-23 08:20:28
6 .0 0% | a7kvhyr0mzbn5 2021-12-22 16:03:04 2021-12-22 21:06:02
6 .0 0% | cyxvj52k6tdk7 2021-12-22 16:39:23 2021-12-23 03:27:44
6 .0 0% | g0yhbm687478z 2021-12-22 18:17:38 2021-12-23 03:52:03
5 .0 0% | 19h143pgc65tg 2021-12-22 15:40:32 2021-12-23 08:53:51
5 .0 0% | 1ffpyd02n05z2 2021-12-22 15:41:29 2021-12-23 00:09:56
5 .0 0% | gz7vvu41y2czv 2021-12-22 16:50:17 2021-12-23 08:52:12
4 .0 0% | 4kbhs1jm2gfsu 2021-12-22 16:37:16 2021-12-22 21:41:31
2 .0 0% | 1yxuj3nd676mu 2021-12-22 15:52:35 2021-12-22 17:32:35
2 .0 0% | 3v8bkaasw596z 2021-12-22 16:28:56 2021-12-23 08:22:08
30 rows selected.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2848987/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20220422]完善tpt ash ash_index_helperx指令碼2.txtIndex指令碼
- [20220129]完善tpt ash ash_index_helperx指令碼.txtIndex指令碼
- [20220510]完善tpt expandz.sql指令碼.txtSQL指令碼
- [20211130]完善tpt t.sql指令碼.txtSQL指令碼
- [20240313]使用tpt ashtop.sql指令碼的困惑.txtSQL指令碼
- [20211129]完善tpt tablist.sql指令碼.txtSQL指令碼
- [20211129]完善tpt killi.sql指令碼.txtSQL指令碼
- [20220323]完善tpt get_trace.sql指令碼.txtSQL指令碼
- [20220217]完善tpt gts.sql指令碼.txtSQL指令碼
- [20220823]完善tpt的ashtop.sql指令碼.txtSQL指令碼
- [20231025]完善tpt的trans.sql指令碼.txtSQL指令碼
- [20211126]完善tpt pr.sql指令碼.txtSQL指令碼
- [20230302]建立完善tpt o2.sql指令碼.txtSQL指令碼
- 自動生成ASH報告指令碼指令碼
- 通過shell定製ash指令碼指令碼
- 透過shell定製ash指令碼指令碼
- [20220111]完善tpt ashash_index_helper指令碼.txtIndex指令碼
- [20230510]測試使用tpt ddl指令碼是否產生日誌.txt指令碼
- [20220317]補充完善TPT 顯示欄位列的指令碼.txt指令碼
- [20220311]完善ash_wait_chains指令碼.txtAI指令碼
- [20231101]使用tpt seg2.sql指令碼問題.txtSQL指令碼
- [20221126]tpt pr.sql指令碼執行問題.txtSQL指令碼
- [20220519]完善tpt dash_wait_chains2.sql指令碼.txtAISQL指令碼
- [20211111]補充完善ash_wait_chains指令碼.txtAI指令碼
- [20170628]完善ooerr指令碼.txt指令碼
- outline操作指令碼_老版本.txt指令碼
- [20210506]完善tix指令碼.txt指令碼
- oracle ASHOracle
- [20190510]快速建立執行指令碼.txt指令碼
- [20190416]exclusive latch測試指令碼.txt指令碼
- [20210407]完善ti.sql指令碼.txtSQL指令碼
- [20210428]改進pr.sql指令碼.txtSQL指令碼
- [20210623]完善清除aud指令碼.txt指令碼
- [20170515]檢查資料庫scn指令碼.txt資料庫指令碼
- [20160501]檢視包引數指令碼.txt指令碼
- [20201202]完善sosi指令碼.txt指令碼
- [20171219]指令碼執行的安全性.txt指令碼
- oracle awr ashOracle