7 Plan History For Particular SQL Id

7_plan_history_for_particular_sql_id.sql

Disclaimer: This script is provided for educational and learning purposes. Review it carefully, test in a non-production environment, confirm privileges, and validate impact before using it in production.

SET PAGESIZE 200
SET LINESIZE 320
SET LONG 100000
SET LONGCHUNKSIZE 100000
SET TRIMSPOOL ON
SET TAB OFF
SET VERIFY OFF
SET FEEDBACK ON

COLUMN sql_id FORMAT A13 HEADING 'SQL_ID'
COLUMN operation FORMAT A30 HEADING 'OPERATION'
COLUMN options FORMAT A25 HEADING 'OPTIONS'
COLUMN object_owner FORMAT A20 HEADING 'OBJECT_OWNER'
COLUMN object_name FORMAT A35 HEADING 'OBJECT_NAME'
COLUMN object_type FORMAT A15 HEADING 'OBJECT_TYPE'
COLUMN cost FORMAT 999,999,999 HEADING 'COST'
COLUMN cardinality FORMAT 999,999,999,999 HEADING 'CARDINALITY'
COLUMN bytes FORMAT 999,999,999,999 HEADING 'BYTES'
COLUMN access_predicates FORMAT A80 HEADING 'ACCESS_PREDICATES'
COLUMN filter_predicates FORMAT A80 HEADING 'FILTER_PREDICATES'

ACCEPT sql_id CHAR PROMPT 'Enter SQL_ID: '

SELECT
    sql_id,
    id,
    parent_id,
    operation,
    options,
    object_owner,
    object_name,
    object_type,
    cost,
    cardinality,
    bytes,
    access_predicates,
    filter_predicates
FROM
    dba_hist_sql_plan
WHERE
    sql_id = '&sql_id'
ORDER BY
    plan_hash_value,
    id;