I’ll show you today how to fix a Plan of a given query via the “SQL Plan Baseline”. In this example, I’v created the SQL plan to be fixed for a simple select query:
select /*+ sys_id=895624f3db7a72c0251af9971d961955 */
* from TABLE_EX where sys_id='895624f3db7a72c0251af9971d961955';
Firstly, lets talk a litle bit about “SQL Plan Baselines” !!
Description of SQL Plan Baselines
A SQL Plan baseline is a Oracle feature introduced with Oracle DB 11g. It influences the query optimizer and forces the optimizer to create specific execution plans for a given SQL statement. It is applied to a specific SQL statement without the need to modify the SQL statement itself (adding hint for exemple).
So we can start now 🙂
1. Load SQL_ID and MACHING_SIGNATURE for our query
SQL> select sql_id, to_char(exact_matching_signature), to_char(force_matching_signature) from v$sql where sql_text like '%sys_id=895624f3db7a72c0251af9971d961955%'; SQL_ID EXACT_MATCHING_SIGNATURE ------------- -------------------------------- 84a06djy7m1mw 3279624363112724218
Now, lets check the execution Plan used by default for this query:
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('84a06djy7m1mw'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID 84a06djy7m1mw, child number 0
-------------------------------------
select /*+ sys_id=895624f3db7a72c0251af9971d961955 */ * from ci_server
where sys_id='895624f3db7a72c0251af9971d961955'
Plan hash value: 4128487297
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 465 (100)| |
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| CI_SERVER | 1 | 333 | 465 (1)| 00:00:06 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SYS_ID"='895624f3db7a72c0251af9971d961955')
19 rows selected.
=> As you can see, Oracle does a FULL Scan on the table to find the desired value as no index exists on our table.
Here we will create an index on SYS_ID column for the table TABLE_EX, to have an other execution Plan for our query.
SQL> create index ci_server_sys_id on ci_server(sys_id); Index created.
I will play again the same select query and will display the new execution Plan our index creation :
SQL> SELECT /*+ sys_id=895624f3db7a72c0251af9971d961955 */
* from TABLE_EX where sys_id='895624f3db7a72c0251af9971d961955';
...
...
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('84a06djy7m1mw'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
SQL_ID 84a06djy7m1mw, child number 0
-------------------------------------
select /*+ sys_id=895624f3db7a72c0251af9971d961955 */ * from ci_server
where sys_id='895624f3db7a72c0251af9971d961955'
Plan hash value: 3760629078
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| CI_SERVER | 1 | 333 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | CI_SERVER_SYS_ID | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SYS_ID"='895624f3db7a72c0251af9971d961955')
20 rows selected.
=> Oracle starts to use the index created on the SYS_ID column to improve its search through the Plan hash value: 3760629078.
2. Create Plan Baseline for Plan Hash Value 3760629078 for SQL_ID 84a06djy7m1mw
SQL> var v_num number; exec :v_num:=dbms_spm.load_plans_from_cursor_cache (sql_id => '84a06djy7m1mw',plan_hash_value => 3760629078 );
The plan has been added, enabled and accepted but not Fixed, to the SQL Plan Baseline:
SQL> select sql_handle,
plan_name,
enabled,
accepted,
fixed
from dba_sql_plan_baselines where signature=3279624363112724218;
SQL_HANDLE PLAN_NAME ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SQL_2d8390fb680cf6fa SQL_PLAN_2v0whzdn0txru735bf051 YES YES NO
2. Fix new Plan SQL_PLAN_2v0whzdn0txru735bf051
SQL> declare myplan pls_integer; begin myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle =>'SQL_2d8390fb680cf6fa', plan_name=>'SQL_PLAN_2v0whzdn0txru735bf051', attribute_name=>'FIXED', attribute_value=>'YES'); end; / PL/SQL procedure successfully completed.
So if we check, our Plan Baseline has been Fixed to the SQL Plan Baseline (FIX = YES):
SQL> select sql_handle,
plan_name,
enabled,
accepted,
fixed
from dba_sql_plan_baselines where signature=3279624363112724218;
SQL_HANDLE PLAN_NAME ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SQL_2d8390fb680cf6fa SQL_PLAN_2v0whzdn0txru735bf051 YES YES YES
With this configuration, The optimizer will always chose this Plan Baseline for all execution of our “select” query.
I hope that was clear for you, and now you are able to improve your query execution by fixing the good SQL Plan Baseline.
Founder of RakDBA.com
Expert database administrator on Oracle & SQL Server databases with 10 years experience. Certified OCA for Oracle and MSCP for SQL SERVER 2012 administration.