[20220111]完善tpt ashash_index_helper指令碼.txt
[20220111]完善tpt ashash_index_helper指令碼.txt
--//定位索引問題很好,但是有時候開發執行不需要最佳化,主要原因可能他們的帶入引數使用非繫結變數,有時候出現隱式轉換,
--//又時候僅僅執行此事很少,這類語句要排除在最佳化範圍之外.修改ashash_index_helper指令碼,加入一個查詢欄位lower(module).
$ cat ash_index_helper.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.
SET LINESIZE 999 PAGESIZE 5000 TRIMOUT ON TRIMSPOOL ON
COL accessed_table HEAD Accessed_Table FOR a20
COL aindex_operation HEAD Plan_Operation FOR a60
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
col module1 format a20
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
, lower(ash.module) module1
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
, lower(ash.module)
),
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
, sq.module1
, 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
, ap.module1
-- , 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.module1
-- , ap.projection
ORDER BY
seconds DESC
)
WHERE rownum <= 50
/
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2852027/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20220510]完善tpt expandz.sql指令碼.txtSQL指令碼
- [20211130]完善tpt t.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指令碼
- [20220422]完善tpt ash ash_index_helperx指令碼2.txtIndex指令碼
- [20220317]補充完善TPT 顯示欄位列的指令碼.txt指令碼
- [20220129]完善tpt ash ash_index_helperx指令碼.txtIndex指令碼
- [20220519]完善tpt dash_wait_chains2.sql指令碼.txtAISQL指令碼
- [20170628]完善ooerr指令碼.txt指令碼
- [20210506]完善tix指令碼.txt指令碼
- [20240313]使用tpt ashtop.sql指令碼的困惑.txtSQL指令碼
- [20210407]完善ti.sql指令碼.txtSQL指令碼
- [20210623]完善清除aud指令碼.txt指令碼
- [20211223]tpt ash ash_index_helperx指令碼.txtIndex指令碼
- [20201202]完善sosi指令碼.txt指令碼
- [20211230]完善sql_id指令碼.txtSQL指令碼
- [20221010]完善descz.sql指令碼.txtSQL指令碼
- [20221101]完善descz.sql指令碼.txtSQL指令碼
- [20221101]完善gts.sql指令碼.txtSQL指令碼
- [20211122]完善descx.sql指令碼.txtSQL指令碼
- [20230414]完善seg2.sql指令碼.txtSQL指令碼
- [20231117]完善ashtt.sql指令碼.txtSQL指令碼
- [20230203]完善awr.sql指令碼.txtSQL指令碼
- [20230510]測試使用tpt ddl指令碼是否產生日誌.txt指令碼
- [20231101]使用tpt seg2.sql指令碼問題.txtSQL指令碼
- [20221126]tpt pr.sql指令碼執行問題.txtSQL指令碼
- [20221012]完善spsw.sql指令碼.txtSQL指令碼
- [20221208]完善bind_cap.sql指令碼.txtSQL指令碼
- [20211202]完善d_buffer.sql指令碼.txtSQL指令碼
- [20220309]完善shp4.sql指令碼.txtSQL指令碼
- [20230210]建立完善swcnm.sql指令碼.txtSQL指令碼
- [20230123]完善curheapz.sql指令碼.txtSQL指令碼