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:
Database Index
Database 12c Index
Parameters Deprecated in 12c
Undocumented Parameters in 12c
———————————————————————————–