115 lines
4.2 KiB
SQL
115 lines
4.2 KiB
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: asqlmon.sql (v1.3)
|
|
--
|
|
-- Purpose: Report SQL-monitoring-style drill-down into where in an execution plan the execution time is spent
|
|
--
|
|
-- Author: Tanel Poder
|
|
--
|
|
-- Copyright: (c) http://blog.tanelpoder.com - All rights reserved.
|
|
--
|
|
-- Disclaimer: This script is provided "as is", no warranties nor guarantees are
|
|
-- made. Use at your own risk :)
|
|
--
|
|
-- Usage: @asqlmon <sqlid> <child#> <from_time> <to_time>
|
|
--
|
|
-- Notes: This script runs on Oracle 11g+ and you should have the
|
|
-- Diagnostics pack license for using it as it queries
|
|
-- some separately licensed views.
|
|
--
|
|
------------------------------------------------------------------------------------------------------------------------
|
|
SET LINESIZE 999 PAGESIZE 5000 TRIMOUT ON TRIMSPOOL ON
|
|
|
|
COL asqlmon_operation HEADING Plan_Operation FORMAT A72
|
|
COL asqlmon_predicates HEAD PREDICATES FOR a100 word_wrap
|
|
COL obj_alias_qbc_name FOR a40
|
|
COL options FOR a30
|
|
|
|
COL asqlmon_plan_hash_value HEAD PLAN_HASH_VALUE FOR 99999999999
|
|
COL asqlmon_sql_id HEAD SQL_ID
|
|
COL asqlmon_sql_child HEAD CHILD FOR 999999
|
|
|
|
COL pct_child HEAD "Activity %" FOR A8
|
|
COL pct_child_vis HEAD "Visual" FOR A12
|
|
|
|
COL asqlmon_id HEAD "ID" FOR 9999
|
|
COL asqlmon_parent_id HEAD "PID" FOR 9999
|
|
|
|
COL inst_id HEAD "I#" FOR 99
|
|
|
|
BREAK ON asqlmon_sql_id SKIP 1 ON asqlmon_sql_child SKIP 1 ON asqlmon_plan_hash_value SKIP 1 ON asqlmon_operation
|
|
|
|
PROMPT
|
|
PROMPT -- ASQLMon v1.3 - by Tanel Poder ( https://tanelpoder.com ) - Display SQL execution plan line level activity breakdown from ASH
|
|
|
|
WITH sample_times AS (
|
|
select * from dual
|
|
),
|
|
sq AS (
|
|
SELECT
|
|
count(*) samples
|
|
, ash.inst_id
|
|
, 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.event
|
|
-- , AVG(ash.p3) avg_p3 -- p3 is sometimes useful for listing block counts for IO wait events
|
|
FROM
|
|
gv$active_session_history ash
|
|
WHERE
|
|
1=1
|
|
AND ash.sql_id LIKE '&1'
|
|
AND ash.sql_child_number LIKE '&2'
|
|
AND ash.sample_time BETWEEN &3 AND &4
|
|
GROUP BY
|
|
ash.inst_id
|
|
, 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.event
|
|
)
|
|
SELECT
|
|
sq.inst_id
|
|
, plan.child_number asqlmon_sql_child
|
|
, plan.plan_hash_value asqlmon_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) asqlmon_plan_id
|
|
, plan.id asqlmon_id
|
|
, plan.parent_id asqlmon_parent_id
|
|
, LPAD(' ', depth, ' ') || plan.operation ||' '|| plan.options || NVL2(plan.object_name, ' ['||plan.object_name ||']', null) asqlmon_operation
|
|
, sq.session_state
|
|
, sq.event
|
|
-- , sq.avg_p3
|
|
, 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,1990) END || CASE WHEN plan.filter_predicates IS NOT NULL THEN ' [F:] ' || SUBSTR(plan.filter_predicates,1,1990) END asqlmon_predicates
|
|
-- , plan.projection
|
|
FROM
|
|
gv$sql_plan plan
|
|
, 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 plan.sql_id LIKE '&1'
|
|
AND plan.child_number LIKE '&2'
|
|
ORDER BY
|
|
plan.child_number
|
|
, plan.plan_hash_value
|
|
, plan.id
|
|
/
|