SQL management base (SMB) en 12c

The SQL management base (SMB) resides in the SYSAUX tablespace and stores SQL plan baselines, statement logs, plan histories, and SQL profiles.

In Oracle Database 11g, when DBMS_XPLAN.DISPLAY_PLAN_BASELINE is invoked to view a plan in SMB, the  execution plan is displayed by compiling the statement with the baseline, mainly using outlines. The plan rows are not stored in the SMB and hence the actual execution plan may not be reproduced.
 In Oracle Database12c, when a new plan is added to the plan history of a SQL statement, plan rows are also stored in the SMB . when DBMS_XPLAN.DISPLAY_PLAN_BASELINE is invoked to view a plan in SMB, the plan rows are displayed from the SMB. This facilitates easier diagnosability when plan cannot be reproduced.
Let’s demonstrate :

Overview:

11g Database

- Create table hr.emp which is a copy of hr.employees with primary key constraint and hence index on employee_id
- Issue a select statement which accesses hr.emp and load the execution plan into SMB
- verify that the plan stored in SMB is same as that was used when the statement was executed (Index access)
- Drop the primary key constraint and hence the index which was used during execution
- verify that the plan  that was used when the statement was executed cannot be reproduced as the index has been dropped

12c Database

- Create table hr.emp which is a copy of hr.employees with primary key constraint and hence index on employee_id
- Issue a select statement which accesses hr.emp and load the execution plan into SMB
- verify that the plan stored in SMB is same as that was used when the statement was executed (Index access)
- Drop the primary key constraint and hence the index which was used during execution
- verify that the plan  that was used when the statement was executed (index access) has been  reproduced even though the index has been dropped as the plan rows are stored in SMB

Leer el artículo completo aqui en el blog de Anju Garg