OracleSQL tuning

SQL plan baselines and SQL profiles

What's pinning plans in this database. Baselines come with Enterprise Edition; SQL profiles are created by the SQL Tuning Advisor, which needs the Tuning Pack.

Not yet verified. How scripts are tested

ora-baselines.sql

1CLEAR COLUMNS2SET LINESIZE 180 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN sql_handle HEADING "SQL HANDLE" FORMAT A224COLUMN plan_name  HEADING "PLAN NAME"  FORMAT A325COLUMN enabled    HEADING "ENABLED"    FORMAT A76COLUMN accepted   HEADING "ACCEPTED"   FORMAT A87COLUMN fixed      FORMAT A58COLUMN origin     FORMAT A289COLUMN created    FORMAT A1010COLUMN name       HEADING "PROFILE"    FORMAT A3211COLUMN category   FORMAT A1012COLUMN status     FORMAT A813COLUMN sql_text   HEADING "SQL"        FORMAT A60 TRUNCATE14 15SELECT sql_handle, plan_name, enabled, accepted, fixed, origin,16       TO_CHAR(created, 'YYYY-MM-DD') AS created17FROM dba_sql_plan_baselines18ORDER BY created DESC;19 20SELECT name, category, status,21       TO_CHAR(created, 'YYYY-MM-DD') AS created,22       DBMS_LOB.SUBSTR(sql_text, 60, 1) AS sql_text23FROM dba_sql_profiles24ORDER BY created DESC;

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

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…
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…