12C : SQL MANAGEMENT BASE STORES PLAN ROWS TOO

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

Implementation :

11g Database

– Create table hr.emp which is a copy of hr.employees with primary key constraint and hence index on employee_id

SQL>set line 500
    set pagesize 80

    drop table hr.emp purge;
    create table hr.emp as 
           select * from      hr.employees;
    alter table hr.emp 
    add constraint emp_pk primary key (employee_id);

 – Issue a select statement which accesses hr.emp using index

SQL>select employee_id, last_name
from hr.emp
where employee_id = 101;

– check that the statement is executed using the index

SQL>select * from table (dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
SQL_ID  2t38scsy1hct2, child number 0
-------------------------------------
select employee_id, last_name from hr.emp where employee_id = 101

Plan hash value: 4024650034

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|         |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    27 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |     1 |       |     0   (0)|         |
--------------------------------------------------------------------------------------

– Load the execution plan into SMB

SQL> DECLARE
l_plans_loaded  PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id=>'2t38scsy1hct2');
END;
/

– Check that the plan has been loaded into SMB

SQL> col SQL_HANDLE for a25
col plan_name for a30
select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
from dba_sql_plan_baselines
WHERE sql_text like '%select employee_id, last_name%';

SQL_HANDLE                PLAN_NAME                      ENA ACC FIX
------------------------- ------------------------------ --- --- ---
SYS_SQL_90c5335bf23b296d  SQL_PLAN_91j9mbgt3qabd447b331a YES YES NO

– verify that the plan stored in SMB is same as that was used when the statement was executed

SQL> set line 999
set pagesize 999

select *
from table(dbms_xplan.display_sql_plan_baseline
(plan_name=>'SQL_PLAN_91j9mbgt3qabd447b331a'));

SQL handle: SYS_SQL_90c5335bf23b296d
SQL text: select employee_id, last_name from hr.emp where employee_id = 101
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_91j9mbgt3qabd447b331a         Plan id: 1148924698
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 4024650034

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    27 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    27 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

– Drop the primary key constraint and hence the index which was used during execution

SQL>alter table hr.emp drop primary key;

-- verify that the plan  that was used when the statement was executed cannot be reproduced as the index has been dropped

SQL>select *
from  table(dbms_xplan.display_sql_plan_baseline
(plan_name=>'SQL_PLAN_91j9mbgt3qabd447b331a'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_90c5335bf23b296d
SQL text: select employee_id, last_name from hr.emp where employee_id = 101
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_91j9mbgt3qabd447b331a         Plan id: 1148924698
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |   108 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     4 |   108 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

——————————————

12c Database

– Create table hr.emp which is a copy of hr.employees with primary key constraint and hence index on employee_id

SQL>set line 500
    set pagesize 80

    drop table hr.emp purge;
    create table hr.emp as 
           select * from      hr.employees;
    alter table hr.emp 
    add constraint emp_pk primary key (employee_id);

 – Issue a select statement which accesses hr.emp using index

SQL>select employee_id, last_name
from hr.emp
where employee_id = 101;

– check that the statement is executed using the index

SQL>select * from table (dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
SQL_ID  2t38scsy1hct2, child number 0
-------------------------------------
select employee_id, last_name from hr.emp where employee_id = 101

Plan hash value: 4024650034

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    12 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |     1 |       |     0   (0)|          |
--------------------------------------------------------------------------------------

– Load the execution plan into SMB

SQL> DECLARE
l_plans_loaded  PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id=>'2t38scsy1hct2');
END;
/

– Check that the plan has been loaded into SMB

SQL>col SQL_HANDLE for a25
col plan_name for a30
 select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
 from dba_sql_plan_baselines
 WHERE sql_text like '%select employee_id, last_name%';

SQL_HANDLE                PLAN_NAME                      ENA ACC FIX
 ------------------------- ------------------------------ --- --- ---
 SQL_90c5335bf23b296d      SQL_PLAN_91j9mbgt3qabd447b331a YES YES NO

– verify that the plan stored in SMB is same as that was used when the statement was executed

SQL> set line 999
set pagesize 999

select *
from table(dbms_xplan.display_sql_plan_baseline
(plan_name=>'SQL_PLAN_91j9mbgt3qabd447b331a'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------
SQL handle: SQL_90c5335bf23b296d
SQL text: select employee_id, last_name from hr.emp where employee_id = 101
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_91j9mbgt3qabd447b331a         Plan id: 1148924698
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 4024650034

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    12 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |     1 |       |     0   (0)|          |
--------------------------------------------------------------------------------------

– Drop the primary key constraint and hence the index which was used during execution

SQL>alter table hr.emp drop primary key;

-- verify that the plan  that was used when the statement was executed has been  reproduced even thoughthe index has been dropped as the plan rows are stored in SMB

SQL>select *
from table(dbms_xplan.display_sql_plan_baseline
(plan_name=>'SQL_PLAN_91j9mbgt3qabd447b331a'));

PLAN_TABLE_OUTPUT
----------------------------------
--------------
SQL handle: SQL_90c5335bf23b296d
SQL text: select employee_id, last_name from hr.emp where employee_id = 101
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_91j9mbgt3qabd447b331a         Plan id: 1148924698
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 4024650034

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    12 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |     1 |       |     0   (0)|          |
--------------------------------------------------------------------------------------

I hope this post was useful.

Your comments and suggestions are always welcome!

—————————————————————-

Related Links:

Home

Database Index
Oracle 12c Index
Parameters Deprecated  in 12c
Undocumented Parameters in 12c

———————————————————————————–

Your comments and suggestions are welcome!