OracleSQL tuning
Purge one statement from the shared pool
Forces a single SQL_ID to hard parse again, usually to pick up new statistics or drop a bad plan, without flushing the whole shared pool.
Not yet verified. How scripts are tested
1SET VERIFY OFF2COLUMN purge_arg NEW_VALUE purge_arg3 4SELECT address || ',' || hash_value AS purge_arg5FROM v$sqlarea6WHERE sql_id = '&sql_id';7 8EXEC DBMS_SHARED_POOL.PURGE('&purge_arg', 'C');Save it as ora-purge-cursor.sql and run it with SQL> @ora-purge-cursor.
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…