VIEW: SYS.DBA_SQL_PLAN_BASELINES

Source

View Text - Preformatted

SELECT /*+ dynamic_sampling(3) */
    so.signature,
    st.sql_handle,
    st.sql_text,
    so.name,
    ad.creator,
    DECODE(ad.origin, 1, 'MANUAL-LOAD',
                      2, 'AUTO-CAPTURE',
                      3, 'MANUAL-SQLTUNE',
                      4, 'AUTO-SQLTUNE',
                      5, 'STORED-OUTLINE',
                      6, 'EVOLVE-ADVISOR',
                         'UNKNOWN'),
    ad.parsing_schema_name,
    ad.description,
    ad.version,
    ad.created,
    ad.last_modified,
    so.last_executed,
    ad.last_verified,
    DECODE(BITAND(so.flags, 1),   0, 'NO', 'YES'),
    DECODE(BITAND(so.flags, 2),   0, 'NO', 'YES'),
    DECODE(BITAND(so.flags, 4),   0, 'NO', 'YES'),
    DECODE(BITAND(so.flags, 64),  0, 'YES', 'NO'),
    DECODE(BITAND(so.flags, 8),   0, 'NO', 'YES'),
    DECODE(BITAND(so.flags, 256), 0, 'NO', 'YES'),
    ad.optimizer_cost,
    substrb(ad.module,1,(select ksumodlen from x$modact_length)) module,
    substrb(ad.action,1,(select ksuactlen from x$modact_length)) action,
    ad.executions,
    ad.elapsed_time,
    ad.cpu_time,
    ad.buffer_gets,
    ad.disk_reads,
    ad.direct_writes,
    ad.rows_processed,
    ad.fetches,
    ad.end_of_fetch_count
FROM
    sqlobj$        so,
    sqlobj$auxdata ad,
    sql$text       st
WHERE
    so.signature = st.signature AND
    ad.signature = st.signature AND
    so.signature = ad.signature AND
    so.category = ad.category AND
    so.plan_id = ad.plan_id AND
    so.obj_type = 2 AND
    ad.obj_type = 2

View Text - HTML Formatted

SELECT /*+ DYNAMIC_SAMPLING(3) */ SO.SIGNATURE
, ST.SQL_HANDLE
, ST.SQL_TEXT
, SO.NAME
, AD.CREATOR
, DECODE(AD.ORIGIN
, 1
, 'MANUAL-LOAD'
, 2
, 'AUTO-CAPTURE'
, 3
, 'MANUAL-SQLTUNE'
, 4
, 'AUTO-SQLTUNE'
, 5
, 'STORED-OUTLINE'
, 6
, 'EVOLVE-ADVISOR'
, 'UNKNOWN')
, AD.PARSING_SCHEMA_NAME
, AD.DESCRIPTION
, AD.VERSION
, AD.CREATED
, AD.LAST_MODIFIED
, SO.LAST_EXECUTED
, AD.LAST_VERIFIED
, DECODE(BITAND(SO.FLAGS
, 1)
, 0
, 'NO'
, 'YES')
, DECODE(BITAND(SO.FLAGS
, 2)
, 0
, 'NO'
, 'YES')
, DECODE(BITAND(SO.FLAGS
, 4)
, 0
, 'NO'
, 'YES')
, DECODE(BITAND(SO.FLAGS
, 64)
, 0
, 'YES'
, 'NO')
, DECODE(BITAND(SO.FLAGS
, 8)
, 0
, 'NO'
, 'YES')
, DECODE(BITAND(SO.FLAGS
, 256)
, 0
, 'NO'
, 'YES')
, AD.OPTIMIZER_COST
, SUBSTRB(AD.MODULE
, 1
, (SELECT KSUMODLEN
FROM X$MODACT_LENGTH)) MODULE
, SUBSTRB(AD.ACTION
, 1
, (SELECT KSUACTLEN
FROM X$MODACT_LENGTH)) ACTION
, AD.EXECUTIONS
, AD.ELAPSED_TIME
, AD.CPU_TIME
, AD.BUFFER_GETS
, AD.DISK_READS
, AD.DIRECT_WRITES
, AD.ROWS_PROCESSED
, AD.FETCHES
, AD.END_OF_FETCH_COUNT FROM SQLOBJ$ SO
, SQLOBJ$AUXDATA AD
, SQL$TEXT ST WHERE SO.SIGNATURE = ST.SIGNATURE AND AD.SIGNATURE = ST.SIGNATURE AND SO.SIGNATURE = AD.SIGNATURE AND SO.CATEGORY = AD.CATEGORY AND SO.PLAN_ID = AD.PLAN_ID AND SO.OBJ_TYPE = 2 AND AD.OBJ_TYPE = 2