This post discusses a new Oracle Database 23ai SQL Plan Management feature which has also been back ported to Oracle 19c – included in Oracle 19.22.
A new DBMS_SPM function has been added called ADD_VERIFIED_SQL_PLAN_BASELINE.
Prior to Oracle 23ai SQL Plan Management involved a number of steps.
We had to first load plans which involved methods like :
- DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE
- DBMS_SPM.LOAD_PLANS_FROM_AWR
- DBMS_SPM.LOAD_PLANS_FROM_SQLSET
Once the plans were loaded, we had to then execute DBMS_SPM.CREATE_EVOLVE_TASK and DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE.
The process involved examining all the SQL tuning sets, including automatic SQL tuning sets, AWR, and cursor cache to find alternative plans for that particular SQL statement.
Then test each of the plans to find the most optimal one.
Then create a SQL plan baseline with the best plan.
During the next execution, the database will use the best plan.
A new DBMS_SPM function ADD_VERIFIED_SQL_PLAN_BASELINE will verify alternative plans using SPM evolve’s test execution.
If a better plan is found, it will be enforced using a SQL plan baseline – all in one step now in 23ai.
Let us have a look at an example.
Create the test table TEST_TAB.
SQL> create table test_tab as select * from dba_objects;
Table created.
SQL> commit;
Commit complete.
Execute the SQL statement which accesses the TEST_TAB table .
SQL> select /* TEST_23AI_SPM */ owner, object_name from test_tab where object_type='TABLE' and owner='SCOTT';
OWNER OBJECT_NAME
------------------------------ ----------------------------------------
SCOTT MYOBJECTS_NEW
SCOTT MYOBJECTS_NEW_2
SCOTT MYOBJECTS_BLOCKCHAIN
SCOTT MYOBJECTS_NEW
SCOTT MYOBJECTS_NEW_2
...
...
...
The EXPLAIN PLAN shows that the optimizer is using a FULL TABLE SCAN operation.
SQL> explain plan for
select /* TEST_23AI_SPM */ owner, object_name from test_tab where object_type='TABLE' and owner='SCOTT'; 2
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 3962208483
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 69 | 3726 | 437 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_TAB | 69 | 3726 | 437 (1)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - filter("OBJECT_TYPE"='TABLE' AND "OWNER"='SCOTT')
13 rows selected.
We now create an index on the TEST_TAB table.
SQL> create index test_tab_ind on test_tab (object_type);
Index created.
SQL> alter system flush shared_pool;
System altered.
SQL> select /* TEST_23AI_SPM */ owner, object_name from test_tab where object_type='TABLE' and owner='SCOTT';
OWNER OBJECT_NAME
------------------------------ ----------------------------------------
SCOTT TEST_TAB
SCOTT MYOBJECTS_BLOCKCHAIN
SCOTT MYOBJECTS_NEW
SCOTT MYOBJECTS_NEW_2
SCOTT MYOBJECTS_NEW
SCOTT MYOBJECTS_NEW_2
SCOTT MYOBJECTS_BLOCKCHAIN
...
...
...
Query DBA_SQL_PLAN_BASELINES and find that at the moment no SQL Baselines exist for this SQL statement.
SQL> SELECT sql_handle, plan_name, enabled, accepted
FROM dba_sql_plan_baselines
WHERE sql_text like 'select /* TEST_23AI_SPM */ owner, object_name%';
no rows selected
Execute DBMS_SPM.ADD_VERIFIED_SQL_PLAN_BASELINE – pass the SQL ID of the statement as an input.
SQL> set long 100000
set tab off
set linesize 200
column report format a150
var report clob
exec :report := dbms_spm.add_verified_sql_plan_baseline('62ykxtvt09ahu');
select :report report from dual;
PL/SQL procedure successfully completed.
The SQL has now been loaded from the Cursor Cache and a SQL Plan Baseline has been created.
SQL>
REPORT
-----------------------------------------------------------------------------
SQL Plan Baselines verified for SQL ID: 62ykxtvt09ahu
-----------------------------------------------------------------------------
Plan Hash Value Plan Name Reproduced Accepted Source
--------------- ------------------------------ ---------- -------- --------------
2332153113 SQL_PLAN_9pd130pzbjzmua39854f0 YES YES CURSOR CACHE
-----------------------------------------------------------------------------------------
SQL Handle : SQL_9ab423057eb8fe7a
REPORT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Text : select /* TEST_23AI_SPM */ owner, object_name from test_tab where object_type='TABLE' and owner='SCOTT'
-----------------------------------------------------------------------------------------
Execute the EXPLAIN PLAN for the same SQL statement and we can see now that the Optimizer is using a new plan which involves an INDEX RANGE SCAN operation and we can verify that the SQL Plan Baseline listed above is being used.
SQL> explain plan for
select /* TEST_23AI_SPM */ owner, object_name from test_tab where object_type='TABLE' and owner='SCOTT'; 2
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2332153113
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 69 | 3726 | 339 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_TAB | 69 | 3726 | 339 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_TAB_IND | 6303 | | 20 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
1 - filter("OWNER"='SCOTT')
2 - access("OBJECT_TYPE"='TABLE')
Note
-----
- SQL plan baseline "SQL_PLAN_9pd130pzbjzmua39854f0" used for this statement
19 rows selected.
