Copy SPM between sqls.

I came across an interesting blog by Jonathan Lewis, he called it fake baselines. Which basically means that we can take a good plan for a modified sql and transfer it to the original sql. I’ll show you how I used it.

I have a support case with our Siebel installation where we have a search that takes to long to execute. The original plan took around 81 seconds and did 2.3 million buffer gets. I was able to create a much better plan by using a merge hint. We need a workaround for the users while support is helping us analyze the root cause.
This is the sql.

The plan looked like this:

ScreenHunter_62 Aug. 04 15.40

I noticed that the IN subquery wasn’t merged, and according to the documentation an IN subquery is mergable. So I added a merge hint to the subquery and the plan changed. Now the view was merged, but it was still poor.

What I then did was to check which order the tables was joined and I noticed it started
with a table that had no predicates on them. So I thought that was odd, we have the predicate T29.X_PARMA_CODE LIKE :v3 and the bind data is ‘908997%’. I did a test and the predicate was selective, it returned one row.

So my idea is that if we start with table T29, we will have a better plan. And it was greatly improved. So I recollected statistics for all tables involved, since this is a Siebel application we have specific script that collects stats. After that it started with the correct table (T29) as I thought. So lack of accurate statistics was one part of the problem. Since the view still wasn’t merged I had to rewrite the sql to get the better plan. I now used the trick presented by Jonathan Lewis.

column sql_id new_value m_sql_id_1
column plan_hash_value new_value m_plan_hash_value_1
column child_number new_value m_child_number_1
SELECT sql_id,
 plan_hash_value,
 child_number,
 buffer_gets
FROM v$sql
WHERE sql_id = '49mct71gmryy5' AND
 sql_text NOT LIKE '%v$sql%' AND
 ROWNUM = 1;
SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER BUFFER_GETS
------------- --------------- ------------ -----------
49mct71gmryy5       149630575            1     2314661

I collected the information about the original poor plan and put the result into the variables defined in the beginning. Note the buffer gets to compare. I verified that the plan listed was my poor plan.

Then I did the same for my fixed sql, which had the merge hint added.

column sql_id new_value m_sql_id_2
column plan_hash_value new_value m_plan_hash_value_2
column child_number new_value m_child_number_2
SELECT sql_id,
 plan_hash_value,
 child_number,
 buffer_gets
FROM v$sql
WHERE sql_text LIKE '%FIXED1%' AND
 sql_text NOT LIKE '%v$sql%' AND
 ROWNUM = 1;
SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER BUFFER_GETS
------------- --------------- ------------ -----------
953cn9sgphv7c      4133135115            0          96

Since I rewrote my sql to produce a better plan the sql_id has changed, but you see now that I have a different much better plan. (compare buffer gets) The plan looks like this. I confirmed that the plan is my better plan.

ScreenHunter_63 Aug. 04 15.41

The last step is to load the plan from sql_id 953cn9sgphv7c  and create abaseline assigned to sql_id 49mct71gmryy5  and the sql listed text.

DECLARE
 m_clob CLOB;
BEGIN
 SELECT sql_fulltext
 INTO m_clob
 FROM v$sql
 WHERE sql_id = '&m_sql_id_1' AND
 child_number = &m_child_number_1;
dbms_output.put_line(m_clob);
dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(sql_id => '&m_sql_id_2', plan_hash_value => &m_plan_hash_value_2, sql_text => m_clob, fixed => 'NO', enabled => 'YES'));
END;
/
old 7: WHERE sql_id = '&m_sql_id_1' AND
new 7: WHERE sql_id = '49mct71gmryy5' AND
old 8: child_number = &m_child_number_1;
new 8: child_number = 1;
old 12: dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(sql_id => '&m_sql_id_2', plan_hash_value => &m_plan_hash_value_2, sql_text => m_clob, fixed => 'NO', enabled => 'YES'));
new 12: dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(sql_id => '953cn9sgphv7c ', plan_hash_value =>4133135115 , sql_text => m_clob, fixed => 'NO', enabled => 'YES'));
PL/SQL procedure successfully completed.

We are using the package dbms_spm.load_plans_from_cursor_cache to load the plan.

When we are done, and execute the original sql we can see that we have a baseline defined and that is used to force the better plan.

ScreenHunter_64 Aug. 05 14.16

Also my tests in the application confirms the better plan, my search now takes 4-5 seconds instead of ~80 seconds.

Advertisements

One thought on “Copy SPM between sqls.

  1. Pingback: Testing application sql and maintaining the sql_id. | Oracle Tuning

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