OracleSQL tuning
Every historical plan for a SQL_ID
Prints all plans AWR has captured for the statement, so you can compare a good plan with a bad one side by side.
Not yet verified. How scripts are tested
1CLEAR COLUMNS2SET LINESIZE 200 PAGESIZE 0 TRIMOUT ON TAB OFF VERIFY OFF LONG 1000003 4SELECT *5FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));6 7SET PAGESIZE 100Save it as ora-plan-awr.sql and run it with SQL> @ora-plan-awr.
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.
- 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…