Purging SQL Plan Management SPACE BUDGET PERCET TABLESPACE SYSAUX

CONFIGURACION RECOMENDADA

select parameter_name, parameter_value from dba_sql_management_config;
 
PARAMETER_NAME                 PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT                        30
PLAN_RETENTION_WEEKS                       105

Disk Space Usage

Disk space used by the SQL management base 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 the SYSAUX tablespace. The allowable range for this limit is between 1% and 50%. A weekly background process measures the total space occupied by the SMB, and when the defined limit is exceeded, the process will generate a warning that is written to the alert log. The alerts are generated weekly until either the SMB space limit is increased, the size of the SYSAUXtablespace is increased, or 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:

By default, the limit for the SMB is no more than 10% 

BEGIN
  DBMS_SPM.CONFIGURE(
    'space_budget_percent',30);
END;
/

Purging 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. Any plan that has not been used for more than 53 weeks are purged, as identified by the LAST_EXECUTED timestamp stored in the SMB for that plan. The 53-week time frame ensures plan information will be available during any yearly SQL processing activity. The unused plan retention period can range between 5 weeks 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:
1-2 AÑOS 
BEGIN
  DBMS_SPM.CONFIGURE(
    'plan_retention_weeks',105);
END;
/