From Oramosswiki
rem
rem Script: get_query_progress.sql
rem Author: Jeff Moss
rem Dated: November 2005
rem Purpose: Gets the execution plan for a query and shows the work areas
rem and longops associated with it...in order to try and show
rem where a query is at in it's execution.
rem
rem Versions tested
rem 10.1.0.4
rem
rem Notes:
rem The script isn't perfect for example, it doesn't cope well with:
rem 1. Situations where there is no work area for an operation
rem 2. Situations where there are no long ops for an operation
rem 3. There is a bug in 10.1.0.4 where the PARENT_ID columns can
rem be corrupted - Bug ###, SR 4990863.
rem Causes retrieved execution plan to be malformed even when statement runs
rem 4. Doesn't deal with some cases where the statement pointed to by V$SESSION is
rem not the actual statement being run, e.g. when refreshing a Materialized View.
rem
rem BUT, at least it's better than nothing.
UNDEFINE sid
ACCEPT search_sid NUMBER FORMAT 9999999990 PROMPT 'Enter SID: '
COLUMN id HEADING "ID" FORMAT 999
COLUMN operation HEADING "Operation" FORMAT A40 WRAP
COLUMN options HEADING "Options" FORMAT A15 WRAP
COLUMN object_name HEADING "Object|Name" FORMAT A20 WRAP
COLUMN cardinality HEADING "Cardinality" FORMAT 999,999,999,990
COLUMN operation_type HEADING "Operation|Type" FORMAT A15 WRAP
COLUMN active_time HEADING "Active|For(s)" FORMAT 999,990
COLUMN WA_Size HEADING "Workarea|Size (Mb)" FORMAT 999,990
COLUMN sid HEADING "SID" FORMAT 99999
COLUMN time_remaining HEADING "Time|Left" FORMAT A7
COLUMN last_execution HEADING "Last|Execution" FORMAT A10
COLUMN message HEADING "Longop|Msg" FORMAT A20 WRAP
SET VERIFY OFF
SET FEEDBACK OFF
SET TIMING OFF
SET PAGESIZE 10000
SET LINESIZE 200
SET WRAP OFF
clear breaks
clear computes
break ON id ON operation ON options ON object_name ON CARDINALITY ON operation_type ON active_time ON REPORT
COMPUTE SUM OF WA_Size ON REPORT
WITH v_session AS
(
SELECT /*+ NO_MERGE */
sql_child_number
, sql_id
FROM v$session
WHERE sid = &search_sid
)
, v_sql_plan AS (
SELECT sp.child_number
, sp.sql_id
, sp.parent_id
, sp.id
, sp.operation
, sp.object_name
FROM v$sql_plan sp
, v_session s
WHERE sp.sql_id = s.sql_id
AND sp.child_number = s.sql_child_number
ORDER BY sp.id
)
, get_plan_lines AS
(
SELECT /*+ NO_MERGE */
sp_sw_swa.id
, sp_sw_swa.parent_id
, sp_sw_swa.operation
, sp_sw_swa.object_name
, sp_sw_swa.operation_type
, sp_sw_swa.message
, (CASE WHEN sp_sw_swa.active_time > 0 THEN ROUND(sp_sw_swa.active_time / 1000000) ELSE 0 END) active_time
, (CASE WHEN sp_sw_swa.work_area_size > 0 THEN ROUND(sp_sw_swa.work_area_size / (1024*1024) ) ELSE 0 END) WA_Size
, sp_sw_swa.sid sid
, sp_sw_swa.time_remaining
FROM v_session s
, v$sqlarea sq
, (SELECT sp.id
, sp.parent_id
, sp.sql_id
, sp.operation
, sp.object_name
, sw.operation_type
, swa.active_time
, swa.work_area_size
, swa.sid
, sl.time_remaining
, sl.message
FROM v_sql_plan sp
, v$sql_workarea sw
, v$sql_workarea_active swa
, v$session_longops sl
WHERE sp.sql_id = sw.sql_id(+)
AND sp.child_number = sw.child_number(+)
AND sp.id = sw.operation_id(+)
AND sp.sql_id = swa.sql_id(+)
AND sp.id = swa.operation_id(+)
AND swa.sql_id = sl.sql_id(+)
AND swa.sid = sl.sid(+)
AND sl.time_remaining(+) != 0
AND DECODE(swa.operation_type,'HASH-JOIN','HASH'
,'BUFFER','SORT'
,'WINDOW (SORT)','SORT'
,'GROUP BY (SORT)','SORT'
,'LOAD WRITE BUFFERS','SORT'
,'XXXX') = UPPER(SUBSTR(sl.opname(+),1,4))
) sp_sw_swa
WHERE s.sql_id = sq.sql_id(+)
AND s.sql_id = sp_sw_swa.sql_id(+)
)
, get_whole_plan AS
(
SELECT id
, LPAD(' ',1*(level-1))||operation operation
, object_name
, operation_type
, active_time
, NVL(TO_CHAR(time_remaining),'N/A') time_remaining
, sid
, WA_Size
, NVL(message,'No Long Op Running') message
FROM get_plan_lines
START WITH id = 0
CONNECT BY PRIOR id = parent_id
ORDER BY id
)
, add_rn AS
(
SELECT id
, operation
, object_name
, operation_type
, active_time
, time_remaining
, sid
, WA_Size
, message
, ROW_NUMBER() OVER(PARTITION BY id,sid
ORDER BY id) rn
FROM get_whole_plan
)
SELECT *
FROM add_rn
WHERE rn = 1
/
