/* ------------------------------------------------------------------
   Display the execution plan for the last SQL executed in the session
   Join to data in V$TQSTAT to show distribution of rows between 
   parallel slaves. 
   
   Guy Harrison June 2009 
   ----------------------------------------------------------------*/
set echo off   
set lines 100
set pages 1000
col operation format a40 heading "Step"
col in_out format a6 heading "In-Out"
col last_ms format 99999.99 heading "Actual ms" noprint
col time format 99999 heading "Est|(s)" noprint
col rows_out format a15 heading "Out Rows|min->max"
col rows_in format a15 heading "In Rows|min->max"
col dop format 999 heading "Dop"
   
WITH tqstat AS (
    SELECT dfo_number,tq_id,':TQ' || dfo_number || LPAD(tq_id, 4, '0') object_name,
           SUM(producer_rows) sum_producer,  
           CASE WHEN SUM(producer)>1 THEN        
                  TO_CHAR(MIN(producer_rows),'99999EEEE') ||' '   
                   || TO_CHAR(MAX(producer_rows),'99999EEEE') 
                WHEN SUM(ranger)>0 THEN 
                  TO_CHAR(MIN(other_rows),'99999EEEE') ||' '   
                   || TO_CHAR(MAX(other_rows),'99999EEEE') 
           END   AS rows_out,
           TO_CHAR(MIN(consumer_rows),'99999EEEE') ||' '   
               || TO_CHAR(MAX(consumer_rows),'99999EEEE') 
                AS rows_in, 
           NVL(GREATEST(NVL(SUM(producer),1),NVL(SUM(consumer),1),1),1) AS dop ,
           SUM(ranger),SUM(producer)  , SUM(other_rows)             
    FROM (SELECT dfo_number, tq_id,
                 CASE WHEN SUBSTR(server_type, 1, 6) IN ('Produc')                     
                      THEN num_rows  END producer_rows,
                 CASE WHEN SUBSTR(server_type, 1, 8) = 'Consumer' 
                      THEN num_rows   END consumer_rows,
                 CASE WHEN SUBSTR(server_type, 1, 8) 
                           NOT IN ('Consumer', 'Producer') 
                      THEN num_rows  END other_rows,
                 CASE WHEN SUBSTR(server_type, 1, 6) IN ('Produc') 
                           AND SUBSTR(process,1,2) <> 'QC'
                      THEN 1 ELSE 0 END AS producer,
                 CASE
                     WHEN SUBSTR(server_type, 1, 8) = 'Consumer' 
                     THEN 1 ELSE 0 END AS consumer,
                 CASE WHEN SUBSTR(server_type,1,6) = 'Ranger'
                     THEN 1  ELSE 0 END AS ranger
          FROM v$pq_tqstat t) t
    GROUP BY dfo_number, tq_id
 ),
    plan AS
        (
    SELECT id, parent_id,
           operation || ' ' || options || ' '    || object_name
               AS operation,
           object_name,
           DECODE(other_tag,
                  'PARALLEL_TO_PARALLEL', 'P->P',
                  'PARALLEL_TO_SERIAL',   'P->S',
                  'PARALLEL_COMBINED_WITH_PARENT', 'PCWP',
                  'PARALLEL_COMBINED_WITH_CHILD',  'PCWC',
                  'PARALLEL_FROM_SERIAL',   'S->P',
                  other_tag)
               AS in_out,
               time
    FROM (SELECT /*+ materialize */
                p.*
          FROM v$sql_plan p
          JOIN v$session s
            ON (s.prev_sql_id = p.sql_id
           AND s.prev_child_number = p.child_number)
          WHERE audsid = USERENV('sessionid')
                AND program NOT LIKE '%(P0%')
            )
SELECT SUBSTR(RPAD(' ', LEVEL) || p.operation,1,50) operation,
       p.in_out, t.rows_out,
       t.rows_in, t.dop 
  FROM plan p LEFT OUTER JOIN tqstat t
 USING (object_name)
CONNECT BY PRIOR id = parent_id
  START WITH id = 0;




