Creating SQL Plan Baselines with SQL Plan Management

Posted: May 28, 2014 in Uncategorized
Tags: ,

“If you can hint it, you can baseline it” Thomas Kyte, RMOUG Training Days 2014

Referenced the following blog entry: <https://blogs.oracle.com/optimizer/entry/how_does_sql_plan_management&gt;

by Maria Colgan on Jan 09,2013

1) Execute your sql statement to see the cost-based plan.

SQL> set feedback off
SQL> set pagesize 100
SQL> spool jmbquery.sql
SQL> set linesize 100
select a...
from ...;
select * from table(dbms_xplan.display_cursor());
-------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID 187x8ssmc1999, child number 0
-- $Source:
-- $Date: 2011/03/14 $
selectme: pv-1-0-1-0 $
from (_b.count as…
Plan hash value: 2812978407
| Id | Operation                   | Name                         | Rows | Bytes |TempSpc| Cost (%CPU)| Time     |   TQ |IN-
OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                               |       |       |       |   346K(100)|         |       |
   |           |
|   1 | VIEW                       |                              |   67 | 1273 |       |   346K (10)| 01:20:45 |       |
   |           |\
|   2 |   SORT UNIQUE               |                               |   67 |   543 |   210M|   346K(100)| 01:20:45 |       |
   |           |

2) Create a SQL baseline for this SQL statement. We can do this by capturing the plan directly from the cursor cache into a SQL plan baseline, using the DBMS_SPM package.

But first we need the SQL_ID for our statement, which we can get from V$SQL.

select sql_id, plan_hash_value, sql_text
from v$sql
where sql_id = '0krnbaq14rsqj';

— Load the plan from the cursor cache into a SQL plan baseline

variable cnt number;
execute :cnt :=dbms_spm.load_plans_from_cursor_cache(sql_id=>'0krnbaq14rsqj');
anonymous block completed
select :cnt from dual;
       :cnt
-----------
         1
select sql_handle,sql_text,parsing_schema_name,plan_name,enabled,accepted
from dba_sql_plan_baselines;
SQL_HANDLE                 SQL_TEXT   PARSING_SCHEMA PLAN_NAME                                   ENA ACC
--------------------------------------------------------------------------------------
SQL_464017c87b04c0b7 SELECT ... orcl                  SQL_PLAN_4ch0rt1xh9h5r1ba50f44 YES YES
SQL_464017c87b04c0b7 SELECT ... orcl                  SQL_PLAN_4ch0rt1xh9h5r1ba50f44 YES YES

3) If we re-execute the query the SQL plan baseline is now being used even if we change the query by inserting a blank line or add white space.

SQL> set feedback off
SQL> set pagesize 100
SQL> spool jmbquery.sql
SQL> set linesize 100
select   a...
from     ...;
select sql_handle,sql_text,parsing_schema_name,plan_name,enabled,accepted
from dba_sql_plan_baselines;
SQL_HANDLE                 SQL_TEXT   PARSING_SCHEMA PLAN_NAME                                   ENA ACC
--------------------------------------------------------------------------------------
SQL_464017c87b04c0b7 SELECT ... orcl                  SQL_PLAN_4ch0rt1xh9h5r1ba50f44 YES YES
SQL_464017c87b04c0b7 SELECT ... orcl                  SQL_PLAN_4ch0rt1xh9h5r1ba50f44 YES YES
select sql_id, plan_hash_value, sql_text
from v$sql
where sql_id = '0krnbaq14rsqj';
SQL_ID       PLAN_HASH_VALUE
------------- ---------------
0krnbaq14rsqj     2812978407
select * from table(dbms_xplan.display_cursor());
-------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID 187x8ssmc1999, child number 0
-- $Source:
-- $Date: 2011/03/14 $
selectme: pv-1-0-1-0 $
from (_b.count as…
Plan hash value: 281297840  | Id | Operation                   | Name                         | Rows | Bytes |TempSpc| Cost (%CPU)| Time     |   TQ |IN-
OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                               |       |       |       |   346K(100)|         |       |
   |           |
|   1 | VIEW                       |                              |   67 | 1273 |       |   346K (10)| 01:20:45 |       |
   |           |
|   2 |   SORT UNIQUE               |                               |   67 |   543 |   210M|   346K(100)| 01:20:45 |       |
   |           |
Note
------
   -SQL plan baseline SQL_PLAN_4ch0rt1xh9h5r1ba50f44 used for this statement

From these two queries and the explain plan we can see the same plan name and the same plan hash value.

What if we connect as another user, create an identical copy of the tables in said schema but it is missing an index. Should it use the same baseline? It will not use the baseline or even the same plan.

As Maria mentions in her blog article (referenced above), “when the statement is issued in the (other schema) Oracle determined the best cost-based plan for the SQL statements… Before executing this plan we checked to see if the SQL statement matched an existing SQL plan baseline using its SQL signature…

The existing plan required this missing index which doesn’t exist in the new schema. So the query will have to use the cost-based plan that the Optimizer came up with at parse. Querying the SQL plan baseline will show both plans, adding the new plan in the new schema but it is not accepted.

If the new plan is accepted using DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE procedure, we will see it used by the identical SQL statement in the new schema. See the last image in Maria’s post…

https://blogs.oracle.com/optimizer/entry/how_does_sql_plan_management

If we were to run the SQL statement again in the new schema, we will see the same SQL plan baseline being used for an identical SQL statement issued from two different schemas!

Viel Gluck!!

Jason

 

 

 

Advertisements

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