SQL Plan Baselines in 11g are the new Stored Outlines – and one of the nicest features of SQL Plan 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):
rem rem Script: fake_sql_baseline.sql rem Author: Jonathan Lewis rem Dated: Oct 2010 rem set serveroutput off create table t1 as with generator as ( select --+ materialize rownum id from dual connect by rownum <= 1e4 -- > comment to avoid wordpress format issue ) select rownum id, mod(rownum,1000) n1000, lpad(rownum,10,'0') small_vc, rpad('x',100) padding from generator v1, generator v2 where rownum <= 1e4 -- > comment to avoid wordpress format issue ; 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 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 Plan 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); 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. --
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 GMT Jan 12,2011 |
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)
Comment by Coskan Gundogar — January 12, 2011 @ 11:35 pm GMT Jan 12,2011 |
I’m using this method, too.
Comment by Pavol Babel — January 13, 2011 @ 1:51 am GMT Jan 13,2011 |
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 GMT Jan 13,2011 |
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 GMT Jan 13,2011 |
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 GMT Jan 14,2011 |
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.
regards,
Marcin
Comment by Marcin Przepiorowski — January 14, 2011 @ 6:55 pm GMT Jan 14,2011 |
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 GMT Jan 16,2011 |
Hi Jonathan,
Thanks for the wonderful blog on the SQL Baselines.
Thanks
Aswath Rao
Comment by Aswath Rao — January 19, 2011 @ 4:14 pm GMT Jan 19,2011 |
[…] 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 GMT Feb 16,2011 |
[…] 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 GMT Feb 14,2012 |
[…] 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 |
[…] Johathan Lewis https://jonathanlewis.wordpress.com/2011/01/12/fake-baselines/ […]
Pingback by One simple example about Oracle SPM | leborchuk — August 19, 2012 @ 9:52 am BST Aug 19,2012 |
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 |
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 |
It’s documented:
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
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 |
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 |
[…] 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 |
[…] 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 |
Jonathan – thank you very much for the insight into baselines … However I had a query regarding Baselines … What about a query that runs for both a very small amount of data and a very large amount of data.If the baseline is set for large amount of data then when we run for a small amount of data it will take minutes instead of seconds. The other way around (set the baseline for small and then run large) could make the large query go from hours to days. Is this possible and if it is so , is there anyway that this can be avoided ?
Comment by Azhar Syed — July 21, 2014 @ 5:53 pm BST Jul 21,2014 |
Azhar,
It doesn’t matter what mechanisms and techniques Oracle Corp. introduces to solve problems, there’s always something a little more complex waiting to be solved.
Oracle introduced Adaptive Cursor Sharing to address the problem of the same text needing different plans because bound values (typically) meant that the “same” query would need to use different plans to return different volumes of data: even a simple query like: “select * from t1 where fk_col = :b1;” could need two different plans. Adaptive cursor sharing associates different cursors (with different plans) with different ranges of bind variables – but initially this association was in-memory only; I haven’t checked yet whether Oracle has combined ACS with Baselines in some way to persist the link between bind variable values and execution plans, but that’s what the problem probably needs unless you make your front-end code aware of the issue. (There’s an old “philosphy” post of mine relating to this problem)
Comment by Jonathan Lewis — August 4, 2014 @ 1:15 am BST Aug 4,2014 |
Hi Jonathan… i tried out ur above said example 11.2.0.3 EE and it was picking the index range scan not the full the scan after implementing the dbms_spnm proc. Any specific parameter needs to be changed ?? !!
Comment by Anand Kumar S — August 1, 2014 @ 10:14 am BST Aug 1,2014 |
Anand,
Apart from the possibility of a small typing error somewhere in your test the only thing I can think of at present is that you’ve got optimizer_use_sql_plan_baselines set to some value other than true.
Comment by Jonathan Lewis — August 5, 2014 @ 12:36 am BST Aug 5,2014 |
I have successfully used this technique a few times and I’m very appreciative that you laid it out here.
Is there an approach that lets me accomplish the same trick, but using AWR as a source? That is, the plan is not in the cursor cache, but it is in AWR, so I can use an approach like:
AWR -> DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY -> DBMS_SQLTUNE.LOAD_SQLSET-> SQL Tuning Set -> DBMS_SPM.LOAD_PLANS_FROM_SQLSET -> Baselines
I am thinking I can stuff the result of DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY into a table, then open a modified cursor on that to feed my altered SQL to DBMS_SQLTUNE. But is there a simpler way?
Comment by Mike Tefft — September 9, 2014 @ 9:23 pm BST Sep 9,2014 |
[…] 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 […]
Pingback by Copy SPM between sqls. | Oracle Tuning — August 10, 2015 @ 1:27 pm BST Aug 10,2015 |
Hi Jonathan,
Thanks for the excellent post as allways. I have a question that: Can we make oracle generate two or more plans -without giving hints. Here in this post you gave a hint (FULL) and but can we see oracle using two or more plans for the same sql without using hints? I posted similar question here:
http://www.orafaq.com/forum/t/199337/
This is because with our jdbc app, I keep seeing several different plans for same sql id , but when I try to generate that (without hint) I get nowhere.
Thanks,
OrauserN
Comment by OrauserN — November 28, 2015 @ 6:44 pm GMT Nov 28,2015 |
OrauserN,
It’s not difficult if you make use of bind variables, histograms, and intermittent cursor invalidation or flushing.
Will comment further when I have a keyboard available.
Comment by Jonathan Lewis — November 30, 2015 @ 9:16 am GMT Nov 30,2015 |
[…] Jonathan Lewis – Manually creating “Fake Baselines“ […]
Pingback by SQL Plan Baselines | Links to Various sources — January 28, 2016 @ 5:57 pm GMT Jan 28,2016 |
[…] as a short-term workaround. It is much more reliable to force the entire plan using e.g. the “fake baselines” technique. 2) But even after we lock down the entire plan, that’s not quite all, because we still need […]
Pingback by Query tuning by waste minimization: a real-life example – Oracle Diagnostician — June 22, 2016 @ 10:58 am BST Jun 22,2016 |
[…] Jonathan Lewis – Manually creating “Fake Baselines“ […]
Pingback by Oracle SQL Plan Stability – LEARNING DBA — February 10, 2020 @ 4:45 pm GMT Feb 10,2020 |
[…] years ago (2011) I wrote a note describing how you could attach the Outline Information from one query to the SQL_ID of another […]
Pingback by Fake Baselines – 2 | Oracle Scratchpad — February 17, 2021 @ 1:39 pm GMT Feb 17,2021 |
[…] the SQL Plan 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 – 2 | Oracle Scratchpad — March 3, 2021 @ 9:41 pm GMT Mar 3,2021 |