Oracle Scratchpad

January 12, 2011

Fake Baselines

Filed under: Execution plans,Troubleshooting — Jonathan Lewis @ 6:33 pm BST Jan 12,2011

SQL Baslines in 11g are the new Stored Outlines – and one of the nicest features of SQL Baselines is that you are allowed to fake them; or rather, it’s legal to generate an execution plan for one query and transfer its execution plan to another query using the packaged procedure dbms_spm.load_plans_from_cursor_cache(). This posting is a demonstration of the technique.

We start with a sample data set and a query that is going to do “the wrong thing”. As usual I’ve got a locally managed tablespace with 8KB blocks and 1MB uniform extents, freelist management, and I’m running with CPU Costing disabled (and running 11.1.0.6 in this case):


set serveroutput off

create table t1
as
with generator as (
	select	--+ materialize
		rownum id 
	from dual 
	connect by 
		rownum <= 10000
)
select
	rownum			id,
	mod(rownum,1000)	n1000,
	lpad(rownum,10,'0')	small_vc,
	rpad('x',100)		padding
from
	generator	v1,
	generator	v2
where
	rownum <= 10000
;

create index t1_n1 on t1(n1000);

begin
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T1',
		method_opt 	 => 'for all columns size 1'
	);
end;
/
select
	/*+ target_query */
	id
from
	t1
where
	n1000 = 500
;

The final query is the one that I want to fake a baseline for. With my current set up it does an index range scan to pick up 10 rows, but I’m going to make it do a tablescan instead. I’m going to need to pull the exact text of the query from memory in a moment, so I’ll find its sql_id and child_number by searching for the “pseudo-hint” that I’ve included in the text, and I’ll report the execution plan to show that I’ve picked up the right thing (I’m assuming that there’s only one piece of SQL that’s going to include the text “target_query”, of course):

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
from
	v$sql
where
	sql_text like '%target_query%'
and	sql_text not like '%v$sql%'
and	rownum = 1
;

select * from table(dbms_xplan.display_cursor('&m_sql_id_1',&m_child_number_1));


SQL_ID  306m98cpu9yz7, child number 0
-------------------------------------
select  /*+ target_query */  id from  t1 where  n1000 = 500

Plan hash value: 1420382924

---------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |       |       |    11 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |    10 |    80 |    11 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |    10 |       |     1 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("N1000"=500)

The next step is to create a new query (and in a production system that might simply mean running a heavily hinted version of the target query) that uses the execution plan I want to see; and I’ll use the same search technique to find it and report its plan:

select
	/*+ full(t1) alternate_query */
	id
from
	t1
where
	n1000 = 500
;

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
from
	v$sql
where
	sql_text like '%alternate_query%'
and	sql_text not like '%v$sql%'
and	rownum = 1
;

select * from table(dbms_xplan.display_cursor('&m_sql_id_2',&m_child_number_2));

SQL_ID  bvpb73bb6c6uy, child number 0
-------------------------------------
select  /*+ full(t1) alternate_query */  id from  t1 where  n1000 = 500

Plan hash value: 3617692013

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    28 |
|*  1 |  TABLE ACCESS FULL| T1   |    10 |    80 |    28 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N1000"=500)

After been running and reporting these queries and their plans, I’ve captured the SQL_Id, child_number, and plan_hash_value for each query; and this is more than enough information to make it possible to create an SQL Baseline for one query using the execution plan for the other query.

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;
/

I used the SQL_ID and child_number from the first query to get the full SQL text of the query into an in-memory CLOB, and then use the SQL_id and plan_hash_value from the second query to associate the second plan with the first query – storing the result as a SQL Baseline that is enabled and ready for use.

You’ll have to take my word that I haven’t faked the following text, but this is what I get when I re-run the original query (flushing the shared pool first to make sure that I don’t accidentally end up picking up the original child cursor):

alter system flush shared_pool;

select
	/*+ target_query */
	id
from
	t1
where
	n1000 = 500
;

select * from table(dbms_xplan.display_cursor('&m_sql_id_1',null));

SQL_ID  306m98cpu9yz7, child number 1
-------------------------------------
select  /*+ target_query */  id from  t1 where  n1000 = 500

Plan hash value: 3617692013

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    28 |
|*  1 |  TABLE ACCESS FULL| T1   |    10 |    80 |    28 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N1000"=500)

Note
-----
   - cpu costing is off (consider enabling it)
   - SQL plan baseline SYS_SQL_PLAN_3c0ea7f3dbd90e8e used for this statement

It’s so much easier than the fiddling one had to do for stored outlines which was quite easy in 8i, but got a little nasty in 9i because of the extra (undocumented) details that looked as if they might have been necessary when the third table appeared in the outln schema. However, in 10g, the dbms_outln package was enhanced to allow you to create outlines from the library cache – see this note from Randolf Geist for more details – but remember that stored outlines will be deprecated in Oracle 12.

Footnote: the dbms_spm package is created by script $ORACLE_HOME/rdbms/admin/dbmsspm.sql, and there is a comment near the top saying:


--           Grant the execute privilege on SPM package to public.           --
--           But ADMINISTER SQL MANAGEMENT OBJECT privilege is checked       --
--           before a user is allowed to execute.                            --

19 Comments »

  1. Jonathan,

    this is what I was spoking about in your recent SQL PLAN Baselines post. It now legal to set execution plan using SPM without any touch to application code and that’s remarkable. This is the peace of code which I’m missing in 10g and 9i so much!

    Randolf’s note is a good one, but I have also bad experiences with dbms_outline.create_online, too. It is very simple to create stored outlines from library cache, but optimizer does not use them in many situations without any apparent reason.

    You are also able to import OUTLINE into SQL PROFILE, but it is quite nasty technique and completely unsupported. And you can face problems, too, just like Kerry showed. As you personally wrote Kerry, SQL PROFILES were not designed to freeze execution plans. And the very last note, SQL PROFILES ARE NOT FREE OF CHARGE! Actually, it’s pretty expensive feature!

    Comment by Pavol Babel — January 12, 2011 @ 11:02 pm BST Jan 12,2011 | Reply

  2. Jonathan,

    Isn’t this method easier

    1- create disabled plan baseline for non-hinted version like you did
    2- create plan baseline for the hinted version associated with the plan_handle of non-hinted one with the sql below (enabled by default non-fixed by default)

    var res number 
    exec :res := dbms_spm.load_plans_from_cursor_cache( - 
            sql_id => '&hinted_SQL_ID', - 
            plan_hash_value => &hinted_plan_hash_value, - 
            sql_handle => '&sql_handle_for_original');
    

    Comment by Coskan Gundogar — January 12, 2011 @ 11:35 pm BST Jan 12,2011 | Reply

    • I’m using this method, too.

      Comment by Pavol Babel — January 13, 2011 @ 1:51 am BST Jan 13,2011 | Reply

    • Coskan,

      I don’t think there’s much to choose between the methods, really, although the working environment might make one a little easier to do than the other. If I had to describe your method in full the number of steps is about the same as the thing I’ve demonstrated:

        a) Find SQL ID etc. for unhinted statement
        b) Create baseline for unhinted statement
        c) Find handle for baseline – is this returned in any version of the call to create it ?
        d) Prepare hinted statement with fixed plan
        e) Find SQL_ID etc. for hinted statement
        f) Hack baseline into place
        g) Delete unwanted baseline – might not bother to do this, is there any argument for or against ?

      If you’re using OEM/Grid Control, of course, the task of finding almost all of the details you want are just a couple of clicks away – so your approach would only need a couple of copy and paste steps to be complete.

      Comment by Jonathan Lewis — January 13, 2011 @ 7:59 am BST Jan 13,2011 | Reply

  3. What is the real difference between sql profile import technique in 10g and official method in 11g? It’s seems they use the same approach.
    Is there any problems with sql profile import in 10g (when we import hint set from v$sql_plan)? I don’t think that profile import is “nasty”. It works fine as a rule…

    Comment by Ilya Deev — January 13, 2011 @ 11:37 am BST Jan 13,2011 | Reply

    • Ilya,

      I might not be answering the question you had in mind, but importing the hint set for a plan as a PROFILE is an operation that creates profile content of a type that would not (at present) be generated by Oracle (and it requires you to pay a licence fee); importing the hint set as a BASELINE creates baseline content that is exactly the type of content that Oracle would generate anyway – plus it’s using the call in exactly the way defined in the documentation, without trying to manipulate the input, and it doesn’t (appear to) require payment of a licence fee.

      Comment by Jonathan Lewis — January 14, 2011 @ 8:40 am BST Jan 14,2011 | Reply

  4. Hi Jonathan,

    There is one thing I would like to point as well. If unhinted statement is using a bind variables, new hinted statement has to use it as well.

    I had a lot of problems with that issue, as Oracle allow me to assign a plan with literals to bind variable query but never use it – CBO trace claim that it was unable to reproduce that new plan with a bind variables query.

    The solution was simple but required some more work – a new hinted query has to have bind variables and it is enough if you just open a cursor with it – we don’t need to fetch rows.

    declare
      stm varchar2(4000);
      a1 varchar2(128) := '999999';
      TYPE CurTyp  IS REF CURSOR;
      tmpcursor    CurTyp;
    
    begin
      stm:='select /*HINT */ * from t1 where id = :1';
      open tmpcursor for stm using a1;
    end;
    /
    

    regards,
    Marcin

    Comment by Marcin Przepiorowski — January 14, 2011 @ 6:55 pm BST Jan 14,2011 | Reply

    • Marcin,

      Thanks for that – it’s a useful point to highlight.

      Particularly relevant, probably, when trying to fake things while cursor_sharing is set to force or similar – which can, of course, also cause confusion with “explain plan” and stored outlines.

      Comment by Jonathan Lewis — January 16, 2011 @ 5:43 pm BST Jan 16,2011 | Reply

  5. Hi Jonathan,

    Thanks for the wonderful blog on the SQL Baselines.

    Thanks
    Aswath Rao

    Comment by Aswath Rao — January 19, 2011 @ 4:14 pm BST Jan 19,2011 | Reply

  6. [...] issued by Oracle to validate a constraint will be parallel? If you want 100% guarantee, you can use baselines. I’ve tested this approach and it worked as expected. Here is a run-time plan which used a [...]

    Pingback by Enabling constraint in parallel « Timur Akhmadeev's blog — February 16, 2011 @ 7:05 pm BST Feb 16,2011 | Reply

  7. [...] the view and attach it to the version of the query with the inline view but without the hint. (See Fake Baselines for one possible method of doing this capture.) Share this:TwitterLike this:LikeBe the first to [...]

    Pingback by Subquery Factoring « Oracle Scratchpad — February 14, 2012 @ 6:01 pm BST Feb 14,2012 | Reply

  8. [...] look at the option for creating an SQL Plan Baseline for a modified version of the query, and then attaching that plan to this query. Let’s work on that idea in steps – first we’ll just try aggregating the object [...]

    Pingback by PK Problem « Oracle Scratchpad — July 12, 2012 @ 4:19 pm BST Jul 12,2012 | Reply

  9. Hi Jonathan

    do you know how to diagnose why DBMS_SPM doesn’t load plans from cache? Both plans are definitely in the cursor cache, but for some reason DBMS_SPM returns zero as a result. I tried both doing it myself with your approach and with coe_load_sql_baseline.sql script from SQLTXPLAIN. This is RAC 11.2.0.3.2 on Linux, I’m using same instance for sure; and problem SQL runs in the scheduler job. Thanks for any pointers.

    Comment by Timur Akhmadeev — August 24, 2012 @ 9:54 am BST Aug 24,2012 | Reply

    • I’ve found how to enable tracing for SPM: SQL PLAN MANAGEMENT TRACING ID 789520.1 Looks like something is not OK with the type of this statement. I’ll follow up on this if I find something interesting.

      Comment by Timur Akhmadeev — August 24, 2012 @ 10:03 am BST Aug 24,2012 | Reply

      • It’s documented:

        Plan stability is not supported for multitable insert statements.

        And the reason for such restriction comes from the fact such statement misses OUTLINE part in the cursor, and SPM ignores it and writes following to the trace

        load cc: plan outline extracted
        load cc: plan has empty outline, skipping it
        load cc: plans total=0plans loaded=0
        

        SQL Patch is also not an option for the same reason.

        Comment by Timur Akhmadeev — August 24, 2012 @ 12:06 pm BST Aug 24,2012 | Reply

    • Timur,

      Thanks for the question – and the comprehensive follow-up.
      The tracing information is particularly useful.

      Comment by Jonathan Lewis — August 29, 2012 @ 8:56 am BST Aug 29,2012 | Reply

  10. […] there’s always the option for bypassing the problem by fixing a suitable execution plan with an SQL Baseline, of course. Alternatively, if you can think of the right hint you could create an “SQL […]

    Pingback by Dynamic Sampling – 2 | Oracle Scratchpad — May 23, 2013 @ 12:47 pm BST May 23,2013 | Reply

  11. […] the SQL Baseline from your hinted code to the SQL text from the original. (See – for example: https://jonathanlewis.wordpress.com/2011/01/12/fake-baselines/ […]

    Pingback by How to hint | Oracle Scratchpad — May 28, 2013 @ 5:26 pm BST May 28,2013 | Reply


RSS feed for comments on this post. TrackBack URI

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

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,507 other followers