SQL Tuning - Expert Oracle RAC Performance Diagnostics and Tuning - page 224

User Process

Plan History

YES NO

Plan History

Execute SQL
Statement

PLAN_BASELINE=TRUE Plan in History Plan History

NO

YES NO

Plan has
baseline

New > Old

New Execution
Plan

DBMS_XPLAN.DISPLAY

Figure 7-4. SPM process flow

The statement log, plan history, and plan baselines are stored in the SQL management base (SMB), which also
contains SQL profiles. The SMB is part of the data dictionary and is stored in the SYSAUX tablespace. By default, the
size allocated for plan management is 10% of the size of the SYSAUX tablespace. This can be changed using the DBMS_
SPM.CONFIGURE procedure and can be set to a maximum space budget of 50%. The default unused plan retention
period is 1 year and 1 week and will be automatically purged if it is not used for more than 1 year. The maximum
retention period cannot be higher can 523 weeks.
The current setting of these parameters could be checked using the following query:

SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE FROM DBA_SQL_MANAGEMENT_CONFIG;
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT 10
PLAN_RETENTION_WEEKS 53

If the default retention period needs to be increased to a higher value, the following procedure should help:

BEGIN DBMS_SPM.CONFIGURE('plan_retention_weeks',200); END;

Workshop—Plan Management
We use the SQL query example used in our previous discussions and discuss the process of SQL plan management.