OracleSQL tuning
Execution plan from the cursor cache
Prompts 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 TYPICAL to ALLSTATS LAST.
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_CURSOR('&sql_id', NULL, 'TYPICAL'));6 7SET PAGESIZE 100Save it as ora-plan-cursor.sql and run it with SQL> @ora-plan-cursor.
More Oracle scripts: SQL tuning
- 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…
- 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…