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
/