VIEW: SYS.DBA_SQLTUNE_RATIONALE_PLAN

Source

View Text - Preformatted

SELECT task_id, exec_name as execution_name,
         rtn_id AS rationale_id, object_id,
         operation_id,
         (case when plan_attr < power(2, 16) then
           decode(plan_attr,
                0, 'Original',
                1, 'Original with adjusted cost',
                2, 'Using SQL profile',
                3, 'Using new indices',
                7, 'Using parallel execution')
                when plan_attr > 3*power(2, 16) and
                     plan_attr < 4*power(2, 16) then
                     'Plan from workload repository'
                when plan_attr > 4*power(2, 16) and
                     plan_attr < 5*power(2, 16) then
                     'Plan from cursor cache'
                when plan_attr > 5*power(2, 16) and
                     plan_attr < 6*power(2, 16) then
                     'Plan from SQL tuning set'
                when plan_attr > 6*power(2, 16) then
                     'Plan from SQL performance analyzer' end)
                AS plan_attribute
  FROM WRI$_adv_sqlt_rtn_plan

View Text - HTML Formatted

SELECT TASK_ID
, EXEC_NAME AS EXECUTION_NAME
, RTN_ID AS RATIONALE_ID
, OBJECT_ID
, OPERATION_ID
, (CASE WHEN PLAN_ATTR < POWER(2
, 16) THEN DECODE(PLAN_ATTR
, 0
, 'ORIGINAL'
, 1
, 'ORIGINAL WITH ADJUSTED COST'
, 2
, 'USING SQL PROFILE'
, 3
, 'USING NEW INDICES'
, 7
, 'USING PARALLEL EXECUTION') WHEN PLAN_ATTR > 3*POWER(2
, 16) AND PLAN_ATTR < 4*POWER(2
, 16) THEN 'PLAN
FROM WORKLOAD REPOSITORY' WHEN PLAN_ATTR > 4*POWER(2
, 16) AND PLAN_ATTR < 5*POWER(2
, 16) THEN 'PLAN
FROM CURSOR CACHE' WHEN PLAN_ATTR > 5*POWER(2
, 16) AND PLAN_ATTR < 6*POWER(2
, 16) THEN 'PLAN
FROM SQL TUNING SET' WHEN PLAN_ATTR > 6*POWER(2
, 16) THEN 'PLAN
FROM SQL PERFORMANCE ANALYZER' END) AS PLAN_ATTRIBUTE
FROM WRI$_ADV_SQLT_RTN_PLAN