This post shows how to use SQL plan baselines to fix execution plan of a query. This can be used as a replacement for stored outlines.
For this demonstration we create 2 tables each with a row and collect statistics on them.
create table tab1 (a number, b varchar2(10)); insert into tab1 values(10, 'AA'); create index tab1_i1 on tab1(a); create table tab2 (a number, c varchar2(10)); create index tab2_i1 on tab2(a); insert into tab2 values(10, 'BBB'); exec dbms_stats.gather_table_stats (ownname=>'MURTY', tabname=>'TAB1', estimate_percent=>10, METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO', cascade=>true); exec dbms_stats.gather_table_stats (ownname=>'MURTY', tabname=>'TAB2', estimate_percent=>10, METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO', cascade=>true);
Here is the query for which we will change execution plan using SQL plan baselines.
select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a;
The execution plan shows nested loop join
SQL> explain plan for select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3684952675
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 13 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 13 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TAB1 | 1 | 6 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | TAB2_I1 | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| TAB2 | 1 | 7 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("TAB1"."A"="TAB2"."A")
17 rows selected.
If the parameter optimizer_capture_sql_plan_baselines is set to true, after multiple executions of the query, it will create a baseline automatically.
SQL> explain plan for select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 3684952675
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 13 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 13 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TAB1 | 1 | 6 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | TAB2_I1 | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| TAB2 | 1 | 7 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("TAB1"."A"="TAB2"."A")
Note
-----
- SQL plan baseline "SQL_PLAN_2ppbwajdruq05fc48919a" used for this statement
21 rows selected.
If the parameter optimizer_capture_sql_plan_baselines is set to false, we can create a SQL plan baseline manually using SQL_ID of statement.
SQL> select sql_id, sql_text from v$sql where sql_text like 'select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a';
SQL_ID
-------------
SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
bqn8dqudd4ajf
select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a
set serveroutput on
declare
i pls_integer;
BEGIN
i := dbms_spm.load_plans_from_cursor_cache(sql_id => 'bqn8dqudd4ajf');
dbms_output.put_line('Plans Loaded: ' || i);
END;
/
Plans Loaded: 1
PL/SQL procedure successfully completed.
Details of SQL Plan baselines can be viewed from dictionary view dba_sql_plan_baselines
SQL> select SQL_HANDLE, SQL_TEXT, PLAN_NAME from dba_sql_plan_baselines where PLAN_NAME='SQL_PLAN_2ppbwajdruq05fc48919a'; SQL_HANDLE SQL_TEXT PLAN_NAME ------------------------------ -------------------------------------------------------------------------------- ------------------------------ SQL_2ad57c545b7d5805 select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a SQL_PLAN_2ppbwajdruq05fc48919a
And more details can be get like below using sql_handle
SQL> select * from table(dbms_xplan.display_sql_plan_baseline( sql_handle=>'SQL_2ad57c545b7d5805', format=>'basic')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- SQL handle: SQL_2ad57c545b7d5805 SQL text: select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SQL_PLAN_2ppbwajdruq05fc48919a Plan id: 4232614298 Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE -------------------------------------------------------------------------------- Plan hash value: 3684952675 ------------------------------------------------ | Id | Operation | Name | ------------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | NESTED LOOPS | | | 2 | NESTED LOOPS | | | 3 | TABLE ACCESS FULL | TAB1 | | 4 | INDEX RANGE SCAN | TAB2_I1 | | 5 | TABLE ACCESS BY INDEX ROWID| TAB2 | ------------------------------------------------ 23 rows selected.
Now we will try to force sort merge join for the same SQL using hint USE_MERGE
SQL> explain plan for select /*+ USE_MERGE(tab1 tab2) */ tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1351227119
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 1 | 13 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TAB1 | 1 | 6 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | TAB1_I1 | 1 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 1 | 7 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | TAB2 | 1 | 7 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("TAB1"."A"="TAB2"."A")
filter("TAB1"."A"="TAB2"."A")
18 rows selected.
Execute once to get details in v$sql_plan, and get SQL_ID
SQL> select /*+ USE_MERGE(tab1 tab2) */ tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a;
A B C
---------- ---------- ----------
10 AA BBB
SQL> select distinct SQL_ID from v$sql_plan where PLAN_HASH_VALUE=1351227119;
SQL_ID
-------------
fqjpqc2t0byr0
Now we will add the plan that uses sort merge join to SQL plan baseline. So, without using any hint, the execution of query uses sort merge join. Here we are using sql_handle of baseline created for original query, but sql_id and plan_hash_value of hinted query.
set serveroutput on
declare
i pls_integer;
BEGIN
i := dbms_spm.load_plans_from_cursor_cache(sql_id => 'fqjpqc2t0byr0', plan_hash_value => 1351227119, sql_handle => 'SQL_2ad57c545b7d5805');
dbms_output.put_line('Plans Loaded: ' || i);
END;
/
Plans Loaded: 1
PL/SQL procedure successfully completed.
Baseline of original query now shows 2 plans.
SQL> select * from table(dbms_xplan.display_sql_plan_baseline( sql_handle=>'SQL_2ad57c545b7d5805', format=>'basic')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------- SQL handle: SQL_2ad57c545b7d5805 SQL text: select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SQL_PLAN_2ppbwajdruq05e3fe5496 Plan id: 3825095830 Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD -------------------------------------------------------------------------------- Plan hash value: 1351227119 ------------------------------------------------ | Id | Operation | Name | ------------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | MERGE JOIN | | | 2 | TABLE ACCESS BY INDEX ROWID| TAB1 | | 3 | INDEX FULL SCAN | TAB1_I1 | | 4 | SORT JOIN | | | 5 | TABLE ACCESS FULL | TAB2 | ------------------------------------------------ -------------------------------------------------------------------------------- Plan name: SQL_PLAN_2ppbwajdruq05fc48919a Plan id: 4232614298 Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE -------------------------------------------------------------------------------- Plan hash value: 3684952675 ------------------------------------------------ | Id | Operation | Name | ------------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | NESTED LOOPS | | | 2 | NESTED LOOPS | | | 3 | TABLE ACCESS FULL | TAB1 | | 4 | INDEX RANGE SCAN | TAB2_I1 | | 5 | TABLE ACCESS BY INDEX ROWID| TAB2 | ------------------------------------------------ 41 rows selected.
But still we see nested loop join plan only for the query.
SQL> explain plan for select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3684952675
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 13 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 13 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TAB1 | 1 | 6 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | TAB2_I1 | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| TAB2 | 1 | 7 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("TAB1"."A"="TAB2"."A")
Note
-----
- SQL plan baseline "SQL_PLAN_2ppbwajdruq05fc48919a" used for this statement
21 rows selected.
We will make sort-merge join plan as fixed, so it always uses that fixed execution plan.
declare
i pls_integer;
begin
i := dbms_spm.alter_sql_plan_baseline(sql_handle=>'SQL_2ad57c545b7d5805', plan_name=>'SQL_PLAN_2ppbwajdruq05e3fe5496', attribute_name=>'FIXED', attribute_value=>'YES');
dbms_output.put_line('Plans altered: ' || i);
end;
/
Plans altered: 1
PL/SQL procedure successfully completed.
Details of baseline now show that sort-merge plan as fixed.
SQL> select * from table(dbms_xplan.display_sql_plan_baseline( sql_handle=>'SQL_2ad57c545b7d5805', format=>'basic')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- SQL handle: SQL_2ad57c545b7d5805 SQL text: select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SQL_PLAN_2ppbwajdruq05e3fe5496 Plan id: 3825095830 Enabled: YES Fixed: YES Accepted: YES Origin: MANUAL-LOAD -------------------------------------------------------------------------------- Plan hash value: 1351227119 ------------------------------------------------ | Id | Operation | Name | ------------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | MERGE JOIN | | | 2 | TABLE ACCESS BY INDEX ROWID| TAB1 | | 3 | INDEX FULL SCAN | TAB1_I1 | | 4 | SORT JOIN | | | 5 | TABLE ACCESS FULL | TAB2 | ------------------------------------------------ -------------------------------------------------------------------------------- Plan name: SQL_PLAN_2ppbwajdruq05fc48919a Plan id: 4232614298 Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE -------------------------------------------------------------------------------- Plan hash value: 3684952675 ------------------------------------------------ | Id | Operation | Name | ------------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | NESTED LOOPS | | | 2 | NESTED LOOPS | | | 3 | TABLE ACCESS FULL | TAB1 | | 4 | INDEX RANGE SCAN | TAB2_I1 | | 5 | TABLE ACCESS BY INDEX ROWID| TAB2 | ------------------------------------------------ 41 rows selected.
So, we can get execution plan with sort-merge without any hints.
SQL> explain plan for select tab1.a, tab1.b, tab2.c from tab1, tab2 where tab1.a=tab2.a;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------
Plan hash value: 1351227119
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 1 | 13 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TAB1 | 1 | 6 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | TAB1_I1 | 1 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 1 | 7 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | TAB2 | 1 | 7 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("TAB1"."A"="TAB2"."A")
filter("TAB1"."A"="TAB2"."A")
Note
-----
- SQL plan baseline "SQL_PLAN_2ppbwajdruq05e3fe5496" used for this statement
22 rows selected.
If we do not like to use plans from SQL Plan baseline, we can set optimizer_use_sql_plan_baselines parameter as false. Also, if we want to drop baseline, we can do it like below.
declare
i pls_integer;
begin
i := dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_2ad57c545b7d5805',plan_name=>NULL);
dbms_output.put_line('Plans removed: ' || i);
end;
/
Plans removed: 2
PL/SQL procedure successfully completed.
