rem
rem	Script:		get_sql_dop_analysis.sql
rem	Author:		Jeff Moss
rem	Dated:		August 2006
rem	Purpose:	Determines the Required and Actual DOP by SQL
rem             statement for analysis purposes.
rem
rem	Versions tested 
rem		10.2.0.2
rem
WITH master_history AS
-- NOTE - this code does not currently calculate the Required DOP
--  correctly in all cases since it does not understand the full
--  rules for how to calculate the required DOP. Set and Sort
--  operations make this calculation trickier than normal.
(
-- Get the query co-ordinator (parallel) or master (serial) sessions 
SELECT /*+ NO_MERGE */
       snap_id
,      dbid
,      instance_number
,      sample_id
,      sql_id
,      FIRST_VALUE(sample_id) OVER(PARTITION BY sql_id
                                   ORDER BY sample_time DESC) latest_sample_id
FROM   dba_hist_active_sess_history
WHERE  qc_session_id IS NULL
)
, unique_master_history AS
(
-- Get the information at the latest sample point by SQL_ID 
SELECT /*+ NO_MERGE */
       snap_id
,      dbid
,      instance_number
,      sample_id
,      sql_id
,      latest_sample_id
FROM   master_history
WHERE  sample_id = latest_sample_id
)
, px_history AS
(
-- Get the PX slave sessions
SELECT /*+ NO_MERGE */
       snap_id
,      dbid
,      instance_number
,      sample_id
,      sql_id
,      qc_session_id
FROM   dba_hist_active_sess_history
WHERE  qc_session_id IS NOT NULL
AND    session_id != qc_session_id
)
, sql_id_max_dop AS
(
-- Get the maximum DOP of the the tables involved in all queries
-- ... or in other words find out the maximum DOP it requires.
SELECT /*+ NO_MERGE */
       hsp.sql_id
,      MAX(t.degree) required_dop
FROM   dba_hist_sql_plan hsp
,      dba_tables t
WHERE  hsp.object_owner = t.owner
AND    hsp.object_name = t.table_name
AND    hsp.object_owner IS NOT NULL
AND    t.degree IS NOT NULL
AND    LTRIM(RTRIM(t.degree)) != '1'
GROUP BY hsp.sql_id
)
, count_actual_px AS
(
-- Count up the PX slaves for the statement at each sample point
-- ... or in other words find out how much DOP it used at each time point.
SELECT /*+ NO_MERGE */
       sql_id
,      snap_id
,      dbid
,      instance_number
,      sample_id
,      COUNT(1) actual_dop
FROM   px_history
GROUP BY sql_id
,        snap_id
,        dbid
,        instance_number
,        sample_id
)
, max_actual_px AS
(
-- Get the maximum actual PX by statement
-- ... or in other words find out the maximum DOP it used.
SELECT /*+ NO_MERGE */
       sql_id
,      snap_id
,      dbid
,      instance_number
,      MAX(actual_dop) max_actual_dop
FROM   count_actual_px
GROUP BY sql_id
,        snap_id
,        dbid
,        instance_number
)
-- Now link the Master/QC rows to the count of PX rows and SQLTEXT 
SELECT /*+ ORDERED
           USE_HASH(cap,simd)
        */
       umh.sql_id
,      umh.snap_id
,      umh.dbid
,      umh.instance_number
,      dhn.begin_interval_time
,      dhn.end_interval_time
,      umh.sample_id
,      simd.required_dop
,      NVL(map.max_actual_dop,0) actual_dop
,      CAST (SUBSTR(dhs.sql_text,1,4000) AS VARCHAR2(4000)) sql_text_vc2
FROM   unique_master_history umh
,      max_actual_px map
,      sql_id_max_dop simd
,      dba_hist_sqltext dhs
,      dba_hist_snapshot dhn
WHERE  umh.sql_id = map.sql_id(+)
AND    umh.snap_id = map.snap_id(+)
AND    umh.sql_id = simd.sql_id
AND    umh.sql_id = dhs.sql_id
AND    umh.snap_id = dhn.snap_id
/