OracleSQL tuning

SQL running with more than one plan

Statements 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 instability.

Not yet verified. How scripts are tested

ora-multi-plan.sql

1CLEAR COLUMNS2SET LINESIZE 140 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN sql_id       FORMAT A134COLUMN plans        FORMAT 9905COLUMN execs        HEADING "EXECS"        FORMAT 999,999,9996COLUMN best_avg_ms  HEADING "BEST AVG MS"  FORMAT 999,999,990.97COLUMN worst_avg_ms HEADING "WORST AVG MS" FORMAT 999,999,990.98 9SELECT * FROM (10  SELECT sql_id,11         COUNT(DISTINCT plan_hash_value) AS plans,12         SUM(executions) AS execs,13         ROUND(MIN(elapsed_time / NULLIF(executions, 0)) / 1e3, 1) AS best_avg_ms,14         ROUND(MAX(elapsed_time / NULLIF(executions, 0)) / 1e3, 1) AS worst_avg_ms15  FROM v$sql16  WHERE executions > 017  GROUP BY sql_id18  HAVING COUNT(DISTINCT plan_hash_value) > 119  ORDER BY MAX(elapsed_time / NULLIF(executions, 0)) DESC20) WHERE ROWNUM <= 25;

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

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…
Plan history for a SQL_ID from AWREach AWR snapshot where the statement ran, with its plan hash value and average time. Shows exactly when a plan flipped and what it cost.
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…