Pin old execution plan to memory

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 30 records per second.
The top session in OEM is sid = 209

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 = 209;

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

select sql_text, plan_hash_value 
from v$sql

where sql_id = ‘5xqp2k44fafy9’;

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 = ‘5xqp2k44fafy9’;
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.

SELECT * FROM DBA_SCHEDULER_JOBS;
——————————————————————–
SYS.BSLN_MAINTAIN_STATS_JOB
This is the Oracle defined automatic moving window baseline statistics computation job. This is the job that gathers the statistics the former “Gather Stats”  job.

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;

References

Explanation by Richard Foote

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s