SQL Plan Management:- How many days the sql plan baselines or management data can be retained?

If you want to understand more about SQL Plan Management, read this post before reading further,

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

Debate- SPA vs. SPM vs. ACS, SPM vs. sql_profiles etc

As you see in the figure, Each and every sql baselines has multiple plans associated with it and stored in sql plan management base. Some of the statements plan has verified and not verified and some are repeatable. Means, used plans, unused plans etc resides in sql plan management base.

Source: Optimizer blog.

So now back to the post, the question here is,

1) Where does they store?

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.

2) How many days this plan baselines are stored or retained?

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:

• 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;
/

3) Are they part of AWR collection & awr retention?

No they are not part of AWR collection and not follows AWR retention.

4) Views

SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE FROM DBA_SQL_MANAGEMENT_CONFIG;

PARAMETER_NAME                 PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT                        30
PLAN_RETENTION_WEEKS                       105

5) What are the tables that belongs to sql plan management baselines.

According to my understanding, The DBA_SQL_PLAN_BASELINES view is based on SQLOBJ$, SQLOBJ$DATA, and SQLOBJ$AUXDATA, so the data for sql profiles and baselines will be stored in this tables.

Source:-

http://docs.oracle.com/cd/E11882_01/server.112/e10821/optplanmgmt.htm

-Thanks

Geek DBA