When SQL suddenly ends up with wrong execution plan and Tuning Advisor fails to help

If you suddenly notice that a SQL runs bad but it did not cause any problem earlier, this could be addressed as follows when Tuning Advisor can't suggest any fix -

1. Look at some statistics to check how bad is the query looking at various values like below -

SELECT sql_text, executions, DECODE(executions,0,0,round(((elapsed_time/1000000)/executions),2)) "Elapsed/Exec", version_count, invalidations, parse_calls
FROM v$sqlarea where sql_id='&sql_id'
AND last_active_time BETWEEN (sysdate - 15/1440) AND sysdate;

2. Look at the current plan if it looks bad/wrong -

SELECT * FROM TABLE(dbms_xplan.display_cursor('&sql_id'));

3. Flush the cursor for the bad plan and let it reload -


SET SERVEROUT ON
DECLARE
stmt varchar2(1000);
BEGIN
FOR dr IN (SELECT sql_text, address||','||hash_value ah from v$sqlarea where sql_id='&sql_id')
LOOP
stmt := 'dbms_shared_pool.purge('|| dr.ah||',''c'')';
dbms_shared_pool.purge(dr.ah,'c');
dbms_output.put_line(stmt);
END LOOP;
END;
/

4. Check the current execution plan -


SELECT * FROM TABLE(dbms_xplan.display_cursor('&sql_id'));


5. If the plan is still same and wrong, look at the old execution plan from AWR snapshots for the query and see what's the difference -


@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

Or,

Look at all the plans for the SQL in AWR snapshots -


SELECT plan_table_output FROM table (dbms_xplan.display_awr('&sql_id'));

6. Collect statistics for all the associated tables for the problem query (if you know statistics is not recent)

7. Flush the cursor for the bad plan and let it reload (step 3 above)

* Note that a permanent solution could be setting up SQL Plan Baselines for the SQL if wrong execution plan was the problem.
* There may be high spikes/lock in shared memory when the plan is being purged - depends on system type and activities.

***