OracleSQL tuning

Plan history for a SQL_ID from AWR

Each AWR snapshot where the statement ran, with its plan hash value and average time. Shows exactly when a plan flipped and what it cost.

Diagnostics PackPrompts for input

Not yet verified. How scripts are tested

ora-plan-history.sql

1CLEAR COLUMNS2SET LINESIZE 140 PAGESIZE 200 TRIMOUT ON TAB OFF VERIFY OFF3COLUMN snap_start      HEADING "SNAPSHOT"     FORMAT A164COLUMN inst            FORMAT 9995COLUMN plan_hash_value HEADING "PLAN HASH"    FORMAT 99999999996COLUMN execs           FORMAT 999,999,9907COLUMN avg_ms          HEADING "AVG MS"       FORMAT 999,999,990.98COLUMN gets_per_exec   HEADING "GETS/EXEC"    FORMAT 999,999,999,9909 10SELECT TO_CHAR(sn.begin_interval_time, 'YYYY-MM-DD HH24:MI') AS snap_start,11       st.instance_number AS inst,12       st.plan_hash_value,13       st.executions_delta AS execs,14       ROUND(st.elapsed_time_delta / NULLIF(st.executions_delta, 0) / 1e3, 1) AS avg_ms,15       ROUND(st.buffer_gets_delta / NULLIF(st.executions_delta, 0)) AS gets_per_exec16FROM dba_hist_sqlstat st17JOIN dba_hist_snapshot sn18  ON sn.snap_id = st.snap_id19 AND sn.dbid = st.dbid20 AND sn.instance_number = st.instance_number21WHERE st.sql_id = '&sql_id'22  AND st.executions_delta > 023ORDER BY sn.begin_interval_time;

Save it as ora-plan-history.sql and run it with SQL> @ora-plan-history.

Open in denrepo

More Oracle scripts: SQL tuning

Execution plan from the cursor cachePrompts for a SQL_ID and prints every cached child plan. For actual row counts, run the statement with the GATHER_PLAN_STATISTICS hint and change…
Full SQL text for a SQL_IDThe complete statement, not the first 1,000 characters. Reads the cursor cache; an optional AWR lookup for aged-out statements is included, commented…
SQL running with more than one planStatements in the cursor cache with several plan hash values, showing the best and worst average time. A wide gap is the classic sign of plan…
Every historical plan for a SQL_IDPrints all plans AWR has captured for the statement, so you can compare a good plan with a bad one side by side.
Literal SQL that should use bind variablesGroups statements that are identical apart from literals. Hundreds of copies of the same shape means hard parsing and shared pool churn; show the…
SQL plan baselines and SQL profilesWhat's pinning plans in this database. Baselines come with Enterprise Edition; SQL profiles are created by the SQL Tuning Advisor, which needs the…