//
you're reading...
Oracle Database Performance Tuning, Oracle Internal

Oracle SQL Plan Baseline

Oracle Database SQL Plan Baseline (11g)

First, the basic concept

Oracle 11g began to provide a new method of fixed operating plan that is SQL plan baseline; it can be summarized in the following two:

  • Provide stable operation plan to the SQL Statement. Prevent the operating environment or object statistics and other factors change have an impact on operational plans of SQL statements.
  • Reducing the probability of degradation of the performance of SQL statements in the database. Theoretically you do not agree with a statement to switch to a new operating plan that is much slower and has been run.

note:

  • From a development perspective view of Oracle, Oracle is expected to develop in such way and the direction of improvement. Outline has now been abandoned, sql profile is also difficult to improve. Therefore, for Oracle 11g, be sure have to understand the sql baseline, because this is the future mainstream.
  • SQL plan baseline is stored in the data dictionary. The query optimizer will use their own initiative to infer them.

Second, the working mechanism

From Oracle 11g, because of the presence of the baseline, the process to parse a sql statement as follow

  • SQL statement is hard parsed. CBO(Optimizer) will generate multiple execute plans. CBO choose the lowest cost plan to run the SQL statement.
  • Forming a hash value (signature) base on the text-based SQL statement. Through the hash value to check whether the same baseline exist in the data dictionary.
  • If the Baseline exist, the optimizer will compare with the sql plan just generated with the sql plan stored in SQL plan baseline.
  • If the sql execution plan in baseline match the CBO generated sql execution plan. And it is marked as acceptable ( ‘accepted’). Then the CBO generated sql execution plan is enabled.
  • If the sql execution plan in baseline doesn’t match the CBO generated sql execution plan, CBO baseline assessment is marked as ‘accepted’with multiple SQL execution plans. And select among the lowest cost plan. (Note that a statement can have multiple baseline execution plan, it is the major different compare with SQL profile and Outlines)
  • Suppose plan just run hard parsing CBO selected is lower than in the baseline saved run program COST, this new generation running program is marked as ‘not-accepted’ and stored in the baseline.

The plan was to run until the evolution will be considered and verified use, that is marked as accepted (evolution and validated. Oracle can be simply interpreted as confirmation of the operational program can bring better performance).

Oracle is such a way to ensure top performance of SQL statements will not degenerate, referred to as “running a conservative plan selection strategy.”

Third, some of the features of the baseline

  • By OPTIMIZER_USE_SQL_PLAN_BASELINE to control whether Oracle will use the baseline(TRUE/FALSE), the default value is TRUE. That will take the initiative to use their own baseline.
  • 11g default will not create a baseline on their own initiative.
  • OUTLINE and SQL Profile are different, the concept of classification does not exist in the baseline.
  • OUTLINE and SQL Profile are different. Each SQL statement can have multiple baseline. Oracle based rulemaking to infer whether the details of which baseline.
  • For RAC instance, the baseline will take effect in all instances.
  • There are two baseline expressed: sql_handle and sql_plan_name. sql_handle Can be understood to mean that uniquely identifies the text of the statement. sql_plan_name can be understood as a unique identification of the sql plan.

  • sql profile can apply the different literal SQL statements to a baseline through force_matching property

Several ways to create a baseline

  • On their own initiative to capture baseline. By optimizer_cature_sql_plan_baselines set to true. Optimizer will generate and save baseline for the SQL statements has been run for more than twice (able to change the system or session level).
  • Loading from SQL tuning set. To load from baseline SQL tuning set by using the package dbms_spm.load_plans_from_sqlset.
  • 	DECLARE
    		l_plans_loaded  PLS_INTEGER;
    	BEGIN
    		l_plans_loaded := DBMS_SPM.load_plans_from_sqlset(
    		sqlset_name => 'my_sqlset');
    	END;
    	/
    	
  • Loading from the library cache, it has been in the cursor cache statements create a baseline package dbms_spm.load_plans_from_cursor_cache by a function
  • 	DECLARE
    		l_plans_loaded  PLS_INTEGER;
    	BEGIN
    		l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => '1fkh93md0802n',plan_hash_value=>null);
    	END;
    	/
    	

Several state of the base baseline

A SQL statement corresponding baseline, I will summarize them as three states

  • accepted(acceptable), only to have such a state baseline, the optimizer will consider the baseline in the operating plan
  • no-accepted(unacceptable). Baseline this state, the optimizer will not be considered during SQL statement parsing. Such baseline condition must be verified through evolution and, later accepted into the state. Will be considered by the optimizer use
  • fixed to yes(fixed), baseline intrinsic highest priority such a state! This state has the highest priority compare with other two.

Basice views for baseline

  • Basic View: dba_sql_plan_baselines and dba_sql_management_config
  • Bottom View: sqlobj$data and sqlobj$. (Maintain detailed hint), for example, view the following baseline saved run program statement:
  • 	select
    		extractvalue(value(d), '/hint') as outline_hints
    	from
    		xmltable('/outline_data/hint'
    		passing (
    		select
    		xmltype(comp_data) as xmlval
    	from
    		sqlobj$data sod, sqlobj$ so
    		where so.signature = sod.signature
    		and so.plan_id = sod.plan_id
    		and comp_data is not null
    		and name like '&baseline_plan_name'
    		)
    	) d;
    	
  • Using function to see details of the baseline:
  • 	select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SYS_SQL_11bcd50cd51504e9',plan_name=>'SQL_PLAN_13g6p1maja1790cce5f0e'));
    	

baseline evolution

If we want to compare a sql plan in the baseline with another sql plan, to check which plan has accpable execution plan and more efficent. To do this, we have to use Envolution to prove. If the performance increased, so the state of the baseline will change from un-acceptable to acceptable.

  • Manually execution
  • 	SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_xxxxxxxxxxxxx') From dual;
    	

Modify the baseline

We can use dbms_spm.alert_sql_plan_baseline to change some properties of the baseline.

  • ENABLED
  • FIXED
  • AUTOPURG
  • plan_name
  • description

Syntax

SET SERVEROUTPUT ON
DECLARE
 v_text PLS_INTEGER;
BEGIN
 v_text := DBMS_SPM.alter_sql_plan_baseline(sql_handle  => 'SYS_SQL_xxxxxx',plan_name => 'SYS_SQL_PLAN_xxxxxxxxx',
      attribute_name  => 'fixed',attribute_value => 'YES');
  DBMS_OUTPUT.put_line('Plans Altered: ' || v_text  );
END;
/

Migrate the baseline

dbms_spm provides several procedures to migrate SQL plan baseline between databases

  • create_stgtab_baseline create a plan to save the baseline table
  • pack_stgtab_baseline copied from the baseline to the first step in the data dictionary table
  • unpack_stgtab_baseline baseline copy from the table to save the data dictionary database migration

Steps such as the following:

  • to create a baseline table stored in the data dictionary contents of the user table
  • exec dbms_spm.create_stgtab_baseline (table_name => 'BASELINE_TEST', table_owner => 'SCOTT', tablespace_name => '');
  • the content of the baseline data dictionary tables into the first step in creating a user table
  • exec: i: = dbms_spm.pack_stgtab_baseline (table_name => 'BASELINE_TEST', table_owner => 'SCOTT');

    Note: The insert can support a variety of ways, such as including the SQL specific character related to baseline. sql_handle to accurately identify a baseline, see detailed documentation

  • through the migration tools to migrate user table. exp/imp or expdp/impdp
  • will migrate over the users table stores baseline data dictionary content into the current library. Enabling migration
  • exec: i: = dbms_spm.unpack_stgtab_baseline (table_name => 'BASELINE_TEST', table_owner => 'SCOTT');

Delete the baseline

  • We can use dbms_SPM.drop_sql_plan_baseline to delete the baseline in data dictionary manually.
  • Because we want to use the baseline; for those baselines with fixed = no, if the baseline has been keep for certain time, it will be deleted automatically. (Can check dba_sql_management_config view)

For manual deleting

SET SERVEROUTPUT ON
DECLARE
  v_text  PLS_INTEGER;
BEGIN
  v_text := DBMS_SPM.drop_sql_plan_baseline(sql_handle => 'SYS_SQL_7b76323ad90440b9',plan_name  => NULL); 
  DBMS_OUTPUT.put_line(v_text);
END;
/

A SQL Statement is fxied we expect operation plan

  • create a baseline for the SQL statement
  • Add hint to the SQL statement to ensure the SQL statement after running the program and we expect the same
  • The execution plan generated in step2, add it to the baseline created in step1.
  • Delete the baseline created in step1 (By this way, we can make sure only the plan we expected is in the baseline.)
  • To verify the whether the approach works as we expected.

Demo, let’s fix one sql plan as we expected

SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

SQL> alter system flush shared_pool;
system changed

SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711;
ID   NAME                       TYPE
---- -------------------------- -------
711  I_STREAMS_PROCESS_PARAMS1  INDEX

SQL> select /* outlinetest3 */ /*+ index(dh_stat) */ * from dh_stat where id=711;
ID   NAME                       TYPE
---- -------------------------- -------
711  I_STREAMS_PROCESS_PARAMS1  INDEX

SQL> select sql_text,sql_id,hash_value,child_number,plan_hash_value,to_char(LAST_ACTIVE_TIME,'hh24:mi:ss') time
  2   from v$sql a where sql_text like '%outlinetest%' and sql_text not like '%v$sql%';
SQL_TEXT                                                SQL_ID        HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE TIME
------------------------------------------------------- ------------- ---------- ------------ --------------- --------
select /* outlinetest2 */ /*+ full(dh_stat) */ * from d 4vaj9fgjysy9c 3823925548            0      1845196118 12:27:31
h_stat where id=711
select /* outlinetest3 */ /*+ index(dh_stat) */ * from  fm35jcmypb3qu 4250242778            0      2780970545 12:27:41
dh_stat where id=711

SQL> select * from table(dbms_xplan.display_cursor('4vaj9fgjysy9c','',''));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4vaj9fgjysy9c, child number 0
-------------------------------------
select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where
id=711
Plan hash value: 1845196118
-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |       |       |   124 (100)|          |
|*  1 |  TABLE ACCESS FULL| DH_STAT |     1 |    38 |   124   (1)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"=711)
already select 19 rows

SQL>  select * from table(dbms_xplan.display_cursor('fm35jcmypb3qu','',''));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fm35jcmypb3qu, child number 0
-------------------------------------
select /* outlinetest3 */ /*+ index(dh_stat) */ * from dh_stat where
id=711
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DH_STAT |     1 |    38 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_1   |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=711)
already select 20 rows

SQL> DECLARE
  2   k1 pls_integer;
  3  begin
  4  k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
  5  sql_id=>'4vaj9fgjysy9c',
  6  plan_hash_value=>1845196118
  7  );
  8  end;
  9  /
  
SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME                      SQL_TEXT                                        ACC
------------------------------ ------------------------------ ------------------------------------------------------- ---
SYS_SQL_11bcd50cd51504e9       SQL_PLAN_13g6p1maja17934f41c8d select /* outlinetest2 */ /*+ full(dh_stat) */ * from d YES
                                                              h_sta

When the sql plan baseline generated. First search, couldn’t find the search plan. Until the second time, we could see it.

SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711;
        ID NAME                           TYPE
---------- ------------------------------ ---------------
       711 I_STREAMS_PROCESS_PARAMS1      INDEX
SQL> select * from table(dbms_xplan.display_cursor('4vaj9fgjysy9c','',''));
PLAN_TABLE_OUTPUT
---------------------------------------------------------
SQL_ID: 4vaj9fgjysy9c cannot be found
SQL> select sql_text,sql_id,hash_value,child_number,plan_hash_value,to_char(LAST_ACTIVE_TIME,'hh24:mi:ss') time
  2   from v$sql a where sql_text like '%outlinetest%' and sql_text not like '%v$sql%';
SQL_TEXT                                                SQL_ID        HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE TIME
------------------------------------------------------- ------------- ---------- ------------ --------------- --------
select /* outlinetest3 */ /*+ index(dh_stat) */ * from  fm35jcmypb3qu 4250242778            0      2780970545 12:27:41
dh_stat where id=711


SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711;
        ID NAME                           TYPE
---------- ------------------------------ ---------------
       711 I_STREAMS_PROCESS_PARAMS1      INDEX
SQL> select sql_text,sql_id,hash_value,child_number,plan_hash_value,to_char(LAST_ACTIVE_TIME,'hh24:mi:ss') time
  2   from v$sql a where sql_text like '%outlinetest%' and sql_text not like '%v$sql%';

SQL_TEXT                                                SQL_ID        HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE TIME
------------------------------------------------------- ------------- ---------- ------------ --------------- --------
select /* outlinetest2 */ /*+ full(dh_stat) */ * from d 4vaj9fgjysy9c 3823925548            0      1845196118 12:30:54
h_stat where id=711
select /* outlinetest3 */ /*+ index(dh_stat) */ * from  fm35jcmypb3qu 4250242778            0      2780970545 12:27:41
dh_stat where id=711


SQL> select * from table(dbms_xplan.display_cursor('4vaj9fgjysy9c','',''));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4vaj9fgjysy9c, child number 0
-------------------------------------
select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where
id=711

Plan hash value: 1845196118
-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |       |       |   124 (100)|          |
|*  1 |  TABLE ACCESS FULL| DH_STAT |     1 |    38 |   124   (1)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=711)
Note
-----
   - SQL plan baseline SQL_PLAN_13g6p1maja17934f41c8d used for this statement
already select 23 rows

That matchs the sql execution plan we expected, so it has been transferred into the baseline generated in first place.

SQL> DECLARE
  2   k1 pls_integer;
  3  begin
  4  k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
  5  sql_id=>'fm35jcmypb3qu',
  6  plan_hash_value=>2780970545,sql_handle=>'SYS_SQL_11bcd50cd51504e9'
  7  );
  8  end;
  9  /

There are 2 plan_name under SYS_SQL_11bcd50cd51504e9

SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME                      SQL_TEXT                                        ACC
------------------------------ ------------------------------ ------------------------------------------------------- ---
SYS_SQL_11bcd50cd51504e9       SQL_PLAN_13g6p1maja1790cce5f0e select /* outlinetest2 */ /*+ full(dh_stat) */ * from d YES
                                                              h_sta

SYS_SQL_11bcd50cd51504e9       SQL_PLAN_13g6p1maja17934f41c8d select /* outlinetest2 */ /*+ full(dh_stat) */ * from d YES
                                                              h_sta

Delete the first plan_name.

SQL> DECLARE
  2   k1 pls_integer;
  3  begin
  4  k1 := DBMS_SPM.drop_sql_plan_baseline (  sql_handle=>'SYS_SQL_11bcd50cd51504e9',plan_name=>'SQL_PLAN_13g6p1maja17934f41c8d');
  5  end;
  6  /

Following the test above, we can spot the new sql plan baseline has been verifed.

SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711;
        ID NAME                           TYPE
---------- ------------------------------ ---------------
       711 I_STREAMS_PROCESS_PARAMS1      INDEX

SQL> select * from table(dbms_xplan.display_cursor('4vaj9fgjysy9c','',''));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4vaj9fgjysy9c, child number 1
-------------------------------------
select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where
id=711
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DH_STAT |     1 |    38 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_1   |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=711)
Note
-----
   - SQL plan baseline SQL_PLAN_13g6p1maja1790cce5f0e used for this statement
already 24 rows

By using dba_sql_plan_baselines to show usable SQL plan baseline information.

select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SYS_SQL_11bcd50cd51504e9',plan_name=>'SQL_PLAN_13g6p1maja1790cce5f0e'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------
SQL handle: SYS_SQL_11bcd50cd51504e9
SQL text: select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where
          id=711
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_13g6p1maja1790cce5f0e         Plan id: 214851342
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 2780970545

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    38 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DH_STAT |     1 |    38 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_1   |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=711)
already selected 26 rows

To check the hint message sets

SQL> conn /as sysdba

SQL> select
  2  extractvalue(value(d), '/hint') as outline_hints
  3  from
  4  xmltable('/outline_data/hint'
  5  passing (
  6  select
  7  xmltype(comp_data) as xmlval
  8  from
  9  sqlobj$data sod, sqlobj$ so
 10  where so.signature = sod.signature
 11  and so.plan_id = sod.plan_id
 12  and comp_data is not null
 13  and name like '&baseline_plan_name'
 14  )
 15  ) d;
Input: baseline_plan_name ??:  SQL_PLAN_13g6p1maja1790cce5f0e
Original Value: 13: and name like '&baseline_plan_name'
New Value: and name like 'SQL_PLAN_13g6p1maja1790cce5f0e'

OUTLINE_HINTS
-----------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "DH_STAT"@"SEL$1" ("DH_STAT"."ID"))
selected 6 rows
Advertisements

About daviewning

I am an Oracle DBA

Discussion

No comments yet.

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

%d bloggers like this: