APPLIES TO:

Oracle Database – Enterprise Edition – Version 11.2.0.3 and later
Information in this document applies to any platform.

SYMPTOMS

AWR shows highest CPU usage and logical read by following recursive sql from sqlobj$auxdata:
SELECT obj_type, plan_id, description, creator, origin, created, last_modified FROM sqlobj$auxdata WHERE signature = :1 AND category = :2

OPTIMIZER_USE_SQL_PLAN_BASELINES and OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES are both set to TRUE
SPM is occupying most of the SYSAUX table-space:
Occupant Name S chema Name Space Usage
| ——————– ——————– —————-
| SQL_MANAGEMENT_BASE SYS 213,779.4 MB
| SM/AWR SYS 12,460.4 MB
| SM/OTHER SYS 659.5 M

CAUSE

Enabling automatic initial plan capture by setting the initialization parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to true (the default is false), the database automatically creates a SQL plan baseline for any repeatable SQL statement executed on the database.

If a SQL plan baseline does not exist, then the optimizer creates a plan history and SQL plan baseline for the statement, marking the initial plan for the statement as accepted and adding it to the SQL plan baseline. If a SQL plan baseline exists, then the optimizer behavior depends on the cost-based plan derived at parse time.

If this plan does not match a plan in the SQL plan baseline, then the optimizer marks the new plan as unaccepted and adds it to the SQL plan baseline.
If this plan does match a plan in the SQL plan baseline, then nothing is added to the SQL plan baseline.

Capturing SQL plan baselines indefinitely means that the plan for every repeatable SQL statement must be stored in the data dictionary. Over the course of a year or more, a large database application can generate hundreds of thousands or even millions of repeatable SQL statements.

SOLUTION

1. Delete unwanted SPM baselines using following note:

Reducing the Space Usage of the SQL Management Base in the SYSAUX Tablespace

2. If delete of baselines is slow, gather optimizer statistics on table SQLOBJ$AUXDATA:

exec DBMS_STATS.GATHER_TABLE_STATS (‘SYS’, ‘SQLOBJ$AUXDATA’);

Source: ORACLE