Plan taking long time +recently wrong stats gathered (Pin an execute plan in memory)

When a SQL query’s execution plan changes for the worse, pin the old execution plan to memory.


Problem
The top session in the database is running a query against table TABLE1.  It is eating 100% of the CPU and it is processing only 5 records per second when before it processed at least 90 records per second.
The top session in OEM is sid = 123
This query runs every week without issue, we need to see if the execution plan has changed recently:

1. Look up the session in v$session and grab the sql_id

select sql_id
from v$session
where sid = 123;

2. Look up the query in v$sql to get more info

select sql_text, plan_hash_value
from v$sql
where sql_id = ‘2absdksfkd3249’;

It returns the query text and 2228017235 as the plan hash value


3. Find all the hash values for the query
select plan_hash_value
from v$sql_plan
where sql_id = ‘2absdksfkd3249’;

This query actually shows two hash values for the  query id
2228016959
and
2228017235


So the query’s execution plan has changed recently.  This may have happened due to a change in statatistic for the table.  See if the statatistics for the table have changed recently:

4. Check the statistics to see when they were last gathered

select LAST_ANALYZED from dba_tables where table_name = ‘TABLE1’;


This shows that the table was last analyzed on Saturday and since then the query has been running badly.  This is because the Gather Stats job ran this weekend and changed the execute plan for the query.


Solution :
======

It turns out that since the Gather Statistics job ran this past weekend and changed the execution plan for the query.  The previous execution plan worked much better.  The solution in this case is to pin the old execution plan so that the query will use it instead of the new one.  Whenever new statatistics are gathered and a new execution plan is suggested, it will be ignored and the pinned one will be used instead.

This will pin the hash value of old plan to the query id:

DECLARE
 i NATURAL;
BEGIN
  i := dbms_spm.load_plans_from_cursor_cache(‘5xqp2k44fafy9’, 2228016959);
END;
/

Verified the base line creation using the following sql statement…
 
SELECT * FROM dba_sql_plan_baselines;