SQL Plan Baselines

SQL Plan Baselines

SQL Plan Baselines are a new feature in Oracle Database relaease 11g that helps to prevent repeatedly used SQL statements  to generate different execution plans

DBA_SQL_PLAN_BASELINES view tell about what are base lines stores on oracle database 

select sql_handle, sql_text, plan_name, origin, enabled, accepted, fixed, autopurge from dba_sql_plan_baselines;

FOR AUTOMATIC CAPTURE OF SQL BASE LINES, we can enable following parameter on database

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE;

when we set this parameter to True, automatic plan capture is enabled on database. SPM repository is populated for every repeated sql on database. To identify repeated sql’s on database, the optimizer will log the repeated sql into the statement log after first time sql statement is compiled. If the same sql is fired again on database, the optimizer identify the sql from statement log and uses the execution plan history of sql statement and reproduce execution plan by using SQL text, outline, bind variables and compilation environment. The current cost of the sql statement is added to SQL BASE LINE and marked as accepted  

If the statistics on the table are changed, again same sql is fired on database, A new baselines is loaded into SPM and this plan marked for verification. 

SQL IS ISSUED

           |

GENERATE EXECUTION PLAN 

           |                         YES                                                       YES 

IS THIS SQL TRACKED? —————> DOES SQL PLAN BASELINE EXIST ————> EXECUTE PLAN

           | if No                                                            |  No

Add entry in sql Log                             CREATE  SQL BASE LINE 

           |                                                                    |

 execute this plan                                  Execution this Plan 

Starting from 11g, baselines are manually loaded for statements directly from the cursor cache into the SQL Management Base. Loading plans directly from the cursor cache can be extremely useful if application SQL has been tuned by hand using hints. Since it is unlikely the application SQL can be changed to include the hint, by capturing the tuned execution plan as a SQL plan baseline you can ensure that the application SQL will use that plan in the future.

we need to identify the sql’s to load plans for statements directly from the cursor cache into the SQL Management Base

when a sql is fired on the database, sql is loaded into the shared sql are in SGA, cursor is opened same time. find out the SQL_ID for the sql statement from view V$SQL; using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE we will load sql base lines into SQL management base . 

Then find the SQL_ID for the statement in the V$SQL view. 

SQL> SELECT sql_id, sql_fulltext FROM V$SQL WHERE sql_text LIKE '%SELECT prod_name, SUM(%';

SQL_ID               SQL_FULLTEXT

 -------------        ——————————————————— 

74hnd835n81yv  select SQL_ID, SQL_FULLTEXT from v$SQL chj6q8z7ykbyy SELECT PROD_NAME,SUM(AMOUNT_SOLD)

SQL> variable cnt number; 

SQL> EXECUTE :cnt :=DBMS_SPM.LOAD_PLAN_FROM_CURSOR_CACHE( sql_id=>'chj6q8z7ykbyy');

we can enable and disable the plans loaded in SQL Management Base using DBMS_SPM.ALTER_SQL_PLAN_BASELINE 

In-order to disable the plan, we need to get SQL_HANDLE & PLAN_NAME from dba_sql_plan_baselines; 

SQL> variable cnt number;

 SQL> exec :cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(

 SQL_HANDLE => 'SYS_SQL_bf5c9b08f72bde3e’, 

 PLAN_NAME => 'SQL_PLAN_byr4v13vkrrjy42949306’,

 ATTRIBUTE_NAME => 'enabled’,

 ATTRIBUTE_VALUE => 'NO'); 

SQL> SELECT sql_handle, sql_text, plan_name, enabled FROM dba_sql_plan_baselines; 

To check the detailed execution plan for any SQL plan baseline you can use the procedure DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE.

select * from table (dbms_plan.display_sql_plan_baseline(

sql_handle =>'SYS_SQL_bf5c9b08f72bde3e’,

plan_name =>'SQL_PLAN_byr4v13vkrrjy42949306’,

format => ‘basic’));

The SQL management base (SMB) is a part of the data dictionary that resides in the SYSAUX tablespace. It stores statement logs, plan histories, SQL plan baselines, and SQL profiles. To allow weekly purging of unused plans and logs, the SMB uses automatic space management.

what is the percentage of space allocated in sysaux tablespace for SQL BASELINES OR SQL profiles and how long they are stored can be know known from "DBA_SQL_MANAGEMENT_CONFIG" view on database;

columns parameter_name

columns parameter_value 

we can configure plan_retention_weeks & space_budget_percent  to manage SQL plan base lines or profiles in sysaux tablespace 

1) Disk Space Usage:-

Disk space used by the SMB is regularly checked against a limit based on the size of the SYSAUX tablespace. By default, the limit for the SMB is no more than 10% of the size of SYSAUX. The allowable range for this limit is between 1% and 50%.

A weekly background process measures the total space occupied by the SMB. When the defined limit is exceeded, the process writes a warning to the alert log. The database generates alerts weekly until one of the following conditions is met:

A weekly background process measures the total space occupied by the SMB. When the defined limit is exceeded, the process writes a warning to the alert log. The database generates alerts weekly until one of the following conditions is met:

• The SMB space limit is increased
• The size of the SYSAUX tablespace is increased
• The disk space used by the SMB is decreased by purging SQL management objects (SQL plan baselines or SQL profiles)
• To change the percentage limit, use the CONFIGURE procedure of the DBMS_SPM package. The following example changes the space limit to 30%:
• SQL> DBMS_SPM.CONFIGURE(‘space_budget_percent’,30);

2) Purge Policy :-

A weekly scheduled purging task manages the disk space used by SQL plan management. The task runs as an automated task in the maintenance window.

The database purges plans not used for more than 53 weeks, as identified by the LAST_EXECUTED timestamp stored in the SMB for that plan. The 53-week period ensures plan information is available during any yearly SQL processing. The unused plan retention period can range between 5 and 523 weeks (a little more than 10 years).

To configure the retention period, use the CONFIGURE procedure of the DBMS_SPM PL/SQL package. The following example changes the retention period to 105 weeks:
BEGIN 
DBMS_SPM.CONFIGURE( ‘plan_retention_weeks’,105); 
END; 
/