OracleSQL tuning
Full SQL text for a SQL_ID
The complete statement, not the first 1,000 characters. Reads the cursor cache; an optional AWR lookup for aged-out statements is included, commented out because it needs the Diagnostics Pack.
Not yet verified. How scripts are tested
1CLEAR COLUMNS2SET LINESIZE 200 PAGESIZE 0 TRIMOUT ON TAB OFF VERIFY OFF LONG 1000000 LONGCHUNKSIZE 10000003 4SELECT sql_fulltext5FROM v$sql6WHERE sql_id = '&&sql_id'7 AND ROWNUM = 1;8 9-- If nothing came back and you have the Diagnostics Pack,10-- remove the -- from the next three lines:11-- SELECT sql_text12-- FROM dba_hist_sqltext13-- WHERE sql_id = '&&sql_id';14 15UNDEFINE sql_id16SET PAGESIZE 100Save it as ora-sql-text.sql and run it with SQL> @ora-sql-text.
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…
- 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…