Baselines Part-II
In my previous post, I had described how to implement hints through baselines without changing code. Also, I touched upon two parameters namely
1. Optimizer_capture_sql_plan_baselines 2. Optimizer_use_sql_plan_baseline
As I had written “optimizer_capture_sql_plan_baseline if set to true, let oracle automatically capture sql plan baselines.”
Does it mean it will not capture any plan for any sql if set to false ?
What if we want to capture baselines for only selected queries automatically.
The whole answer lies in values of “ENABLED,ACCEPTED AND FIXED” column if dba_sql_plan_baselines. How!!!!!? Lets find out…
As usual, lets create a table and insert few records
Session1>show parameter capture
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
create table test (name varchar2(10), id number(*));
begin
for i in 1..1000
loop
insert into test values ('SUMIT',i);
end loop;
commit;
end;
/
begin
for i in 1001..10000
loop
insert into test values ('BHATIA',i);
end loop;
commit;
end;
/
begin
for i in 10001..100000
loop
insert into test values ('BASELINE',i);
end loop;
commit;
end;
/
begin
for i in 100001..1000000
loop
insert into test values ('SPM',i);
end loop;
commit;
end;
/
SELECT COUNT(*),name from test group by name order by 1;
COUNT(*) NAME
---------- ----------
1000 SUMIT
9000 BHATIA
90000 BASELINE
900000 SPM
4 rows selected.
Time to query table and load the plan into the baseline.
--Inserting hint to easily identify the query
Select /*+ id=1000 */ * FROM TEST where id=1000
NAME ID
---------- ----------
SUMIT 1000
select sql_id,exact_matching_signature,force_matching_signature from v$sql where sql_text like '%id=1000%';
SQL_ID EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE
------------- -------------------------------- --------------------------------
duk2ypk5fz9g6 7784548270786280511 4428329137525653294
SELECT * FROM TABLE(dbms_xplan.display_cursor('duk2ypk5fz9g6'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
SQL_ID duk2ypk5fz9g6, child number 0
-------------------------------------
select /*+ id=1000 */ * FROM TEST where id=1000
Plan hash value: 1357081020
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| TEST |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1000)
Note
-----
- rule based optimizer used (consider using cbo)
--Creating the baseline for the plan
var v_num number;
exec :v_num:=dbms_spm.load_plans_from_cursor_cache(sql_id => 'duk2ypk5fz9g6',plan_hash_value => 1357081020 );
select sql_handle, plan_name, enabled, accepted,fixed from dba_sql_plan_baselines where signature=7784548270786280511;
SQL_HANDLE PLAN_NAME ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f97bbe3d0 YES YES NO
As we can see, If we load the plan manually, that will plan will be enabled and accepted but not fixed. Its time create an index and run the query again.
create index test_id on test(id);
Index created.
select /*+ id=1000 */ * FROM TEST where id=1000
NAME ID
---------- ----------
SUMIT 1000
Oracle is still using SYS_SQL_PLAN_7118fc3f97bbe3d0 baseline.
However, if we query dba_sql_plan_baselines, we will see that oracle has started capturing baselines for this sql. (plan name SYS_SQL_PLAN_7118fc3f642e4a26)
PLAN_TABLE_OUTPUT
-----------------------------------------------------------
SQL_ID duk2ypk5fz9g6, child number 1
-------------------------------------
select /*+ id=1000 */ * FROM TEST where id=1000
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 554 (100)| |
|* 1 | TABLE ACCESS FULL| TEST | 1643 | 32860 | 554 (2)| 00:00:07 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1000)
Note
-----
- SQL plan baseline SYS_SQL_PLAN_7118fc3f97bbe3d0 used for this statement
select sql_handle, plan_name, enabled, accepted,fixed,signature from dba_sql_plan_baselines where signature=7784548270786280511;
SQL_HANDLE PLAN_NAME ENA ACC FIX SIGNATURE
------------------------------ ------------------------------ --- --- --- ---------------------
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f642e4a26 YES NO NO 7784548270786280511
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f97bbe3d0 YES YES NO 7784548270786280511
So even if capture baseline is set to false, oracle will still capture the new plans and baselines for that particular sql onwards. We can evolve the new plan and can check if index is getting used or not.
/*Evolving new plan */
SET SERVEROUTPUT ON
SET LONG 10000
set lines 175
DECLARE
report clob;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => '&sql_handle', time_limit => &time);
DBMS_OUTPUT.PUT_LINE(report);
END;
/
Enter value for sql_handle:SYS_SQL_6c0845687118fc3f
Enter value for time:60
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_6c0845687118fc3f
PLAN_NAME =
TIME_LIMIT = 60
VERIFY = YES
COMMIT = YES
Plan: SYS_SQL_PLAN_7118fc3f642e4a26
-----------------------------------
Plan was verified: Time used .05 seconds.
Passed
performance criterion: Compound improvement ratio >= 504.88
Plan was changed to an accepted plan.
Baseline Plan Test Plan Improv. Ratio
------------- --------- -------------
Execution Status: COMPLETE COMPLETE
Rows Processed: 1 1
Elapsed Time(ms): 26 0
CPU Time(ms): 25 0
Buffer Gets: 2017 4 504.25
Disk Reads: 0 0
Direct Writes: 0 0
Fetches: 0 0
Executions: 1 1
-------------------------------------------------------------------------------
Report
Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 1.
As we can read in the report, Oracle has marked the new plan as accepted. Oracle keeps a tab if the plan was loaded manually or captured automatically. This can be verified from origin column of dba_sql_plan_baselines.
select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN
------------------------------ ------------------------------ --- --- --- --------------
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f642e4a26 YES YES NO AUTO-CAPTURE
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f97bbe3d0 YES YES NO MANUAL-LOAD
So far, the story was pretty much straight. Here’s a quick recap what we have done
1. Created Table and Inserted records
2. Query the table and baseline the query
3. Created Index and verified new plan is loaded but not accepted.
4. Evolve the new plan and accepted=yes.
5. New plan(Index Range Scan) started getting used.
Its time to discuss the usage of enabled,accepted and fixed parameters.
TEST1: ENABLED=NO.
For every sql_id, oracle checks if the baselines exists or not. If yes, then it will filter all the enabled plans. The plans for which enabled=NO will not be considered even if accepted and fixed are set to yes.
Other than this, if any plan is set enabled=yes for a query,oracle will start capturing new plans also.
Therefore, enabled is the first level. If enabled is set to NO,oracle will mark not to use and evolve that plan.
Lets have a test case. I will mark enabled =no for the plan that was using index and check which plan is used.
select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN
------------------------------ ------------------------------ --- --- --- --------------
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f642e4a26 YES YES NO AUTO-CAPTURE
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f97bbe3d0 YES YES NO MANUAL-LOAD
/* Setting Enabled=NO for SYS_SQL_PLAN_7118fc3f642e4a26 */
declare
myplan pls_integer;
begin
myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => '&sql_handle',plan_name => '&plan_name',attribute_name => 'ENABLED', attribute_value => 'NO');
end;
/
Enter value for sql_handle: SYS_SQL_6c0845687118fc3f
Enter value for plan_name: SYS_SQL_PLAN_7118fc3f642e4a26
select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN
------------------------------ ------------------------------ --- --- --- --------------
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f642e4a26 NO YES NO AUTO-CAPTURE
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f97bbe3d0 YES YES NO MANUAL-LOAD
2 rows selected.
So, plan SYS_SQL_PLAN_7118fc3f642e4a26 is accepted but not enabled. Lets execute the query again and see which plan is getting used.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
SQL_ID duk2ypk5fz9g6, child number 1
-------------------------------------
select /*+ id=1000 */ * FROM TEST where id=1000
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 554 (100)| |
|* 1 | TABLE ACCESS FULL| TEST | 1643 | 32860 | 554 (2)| 00:00:07 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1000)
Note
-----
- SQL plan baseline SYS_SQL_PLAN_7118fc3f97bbe3d0 used for this statement
Oracle choses SYS_SQL_PLAN_7118fc3f97bbe3, since that plan is still enabled and accepted.
Therefore, minimum requirement for oracle to use a plan as baseline is, it should be marked both as ENABLED and ACCEPTED.
The next level is Accepted. In this level oracle will pick all the plans that are accepted to the users and discard all “ACCEPTED=NO” plans. As we had already seen,there can be more than one plan for accepted=yes. All these plans are already evolved plans and are candidate for being the final execution plan.
If no plan is marked “FIXED=YES”, then oracle will pick any of these plans based on the costing.
All those plans which are “ENABLED=YES” and “ACCEPTED=NO”, are candidates for evolution. We can also however marked then accepted=yes manually.
Next and the last level is Fixed. If any plan is fixed oracle will use that plan only. If more than one plan is fixed, oracle will use costing as criteria to select the plan among those.
TEST2: Setting any one plan as FIXED=YES
Lets mark index plan as accepted again and FTS plan as fixed.
select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN
------------------------------ ------------------------------ --- --- --- --------------
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f642e4a26 NO YES NO AUTO-CAPTURE
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f97bbe3d0 YES YES NO MANUAL-LOAD
/*Resetting SYS_SQL_PLAN_7118fc3f642e4a26 to Enabled */
declare
myplan pls_integer;
begin
myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle =>'&sql_handle',plan_name=>'&plan_name',attribute_name =>'ENABLED',attribute_value =>'YES');
end;
/
Enter value for sql_handle: SYS_SQL_6c0845687118fc3f
Enter value for plan_name: SYS_SQL_PLAN_7118fc3f642e4a26
/*Setting SYS_SQL_PLAN_7118fc3f97bbe3d0 to FIXED */
declare
myplan pls_integer;
begin
myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle =>'&sql_handle',plan_name=>'&plan_name',attribute_name=>'FIXED',attribute_value=>'YES');
end;
/
Enter value for sql_handle: SYS_SQL_6c0845687118fc3f
Enter value for plan_name: SYS_SQL_PLAN_7118fc3f97bbe3d0
select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN
------------------------------ ------------------------------ --- --- --- --------------
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f642e4a26 YES YES NO AUTO-CAPTURE
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f97bbe3d0 YES YES YES MANUAL-LOAD
Executing the query and checking the plan again
SELECT * FROM TABLE(dbms_xplan.display_cursor('duk2ypk5fz9g6'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID duk2ypk5fz9g6, child number 1
-------------------------------------
select /*+ id=1000 */ * FROM TEST where id=1000
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 554 (100)| |
|* 1 | TABLE ACCESS FULL| TEST | 1643 | 32860 | 554 (2)| 00:00:07 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1000)
Note
-----
- SQL plan baseline SYS_SQL_PLAN_7118fc3f97bbe3d0 used for this statement
As we can confirm oracle start using FTS as that plan is marked fixed.
TEST3: Setting FIXED=YES for more than one plan
Lets mark second plan “FIXED=YES” and re-execute the query
/*Setting SYS_SQL_PLAN_7118fc3f642e4a26 to FIXED */
declare
myplan pls_integer;
begin
myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle=>'&sql_handle',plan_name=>'&plan_name',attribute_name=>'FIXED',attribute_value=>'YES');
end;
/
Enter value for sql_handle: SYS_SQL_6c0845687118fc3f
Enter value for plan_name: SYS_SQL_PLAN_7118fc3f642e4a26
select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN
------------------------------ ------------------------------ --- --- --- --------------
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f642e4a26 YES YES YES AUTO-CAPTURE
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f97bbe3d0 YES YES YES MANUAL-LOAD
select /*+ id=1000 */ * FROM TEST where id=1000
NAME ID
---------- ----------
SUMIT 1000
1 row selected.
SELECT * FROM TABLE(dbms_xplan.display_cursor('duk2ypk5fz9g6'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------
SQL_ID duk2ypk5fz9g6, child number 0
-------------------------------------
select /*+ id=1000 */ * FROM TEST where id=1000
Plan hash value: 1699862855
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_ID | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1000)
Note
-----
- dynamic sampling used for this statement
- SQL plan baseline SYS_SQL_PLAN_7118fc3f642e4a26 used for this statement
Test 4:
Also, if any of the plan is marked as fixed, oracle will stop capturing and evolving new plans.
To simulate the test, lets again mark the plan(using index) as fixed=no and accepted=no.
select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN
------------------------------ ------------------------------ --- --- --- --------------
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f642e4a26 YES YES YES AUTO-CAPTURE
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f97bbe3d0 YES YES YES MANUAL-LOAD
/* Resetting SYS_SQL_PLAN_7118fc3f642e4a26 to FIXED=NO */
declare
myplan pls_integer;
begin
myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => '&sql_handle',plan_name => '&plan_name',attribute_name => 'FIXED', attribute_value => 'NO');
end;
/
Enter value for sql_handle: SYS_SQL_6c0845687118fc3f
Enter value for plan_name: SYS_SQL_PLAN_7118fc3f642e4a26
/* Resetting SYS_SQL_PLAN_7118fc3f642e4a26 to ACCEPTED=NO */
declare
myplan pls_integer;
begin
myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => '&sql_handle',plan_name => '&plan_name',attribute_name => 'ACCEPTED', attribute_value => 'NO');
end;
/
Enter value for sql_handle: SYS_SQL_6c0845687118fc3f
Enter value for plan_name: SYS_SQL_PLAN_7118fc3f642e4a26
select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN
------------------------------ ------------------------------ --- --- --- --------------
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f642e4a26 YES NO NO AUTO-CAPTURE
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f97bbe3d0 YES YES YES MANUAL-LOAD
Lets try to evolve the index plan (SYS_SQL_PLAN_7118fc3f642e4a26) again, like we did previously. The only difference is fixed=yes this time for plan using FTS
/* TRYING TO EVOLVE SYS_SQL_PLAN_7118fc3f642e4a26 */
SET SERVEROUTPUT ON
SET LONG 10000
set lines 175
DECLARE
report clob;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => '&sql_handle', time_limit => &time);
DBMS_OUTPUT.PUT_LINE(report);
END;
/
Enter value for sql_handle: SYS_SQL_6c0845687118fc3f
Enter value for time: 60
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_6c0845687118fc3f
PLAN_NAME =
TIME_LIMIT = 60
VERIFY = YES
COMMIT = YES
-------------------------------------------------------------------------------
Report
Summary
-------------------------------------------------------------------------------
There were no SQL plan baselines that required processing.
As we can confirm
TEST5:
Lets go back a step further and drop the baseline as well as index. We will try to simulate a test case to check if oracle captures new plan if any plan is marked as FIXED=YES
SET SERVEROUTPUT ON
DECLARE
l_plans_dropped PLS_INTEGER;
BEGIN
l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (
sql_handle => 'SYS_SQL_6c0845687118fc3f',
plan_name => 'SYS_SQL_PLAN_7118fc3f642e4a26');
DBMS_OUTPUT.put_line(l_plans_dropped);
END;
/
drop index test_id;
Index dropped.
select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN
------------------------------ ------------------------------ --- --- --- --------------
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f97bbe3d0 YES YES YES MANUAL-LOAD
1 row selected.
So, we are back to square one from where we have started. Lets again create the same index and query the table to see if it still capture new plans.
create index test_id on test(id);
Index created.
select /*+ id=1000 */ * FROM TEST where id=1000;
NAME ID
---------- ----------
SUMIT 1000
1 row selected.
select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN
------------------------------ ------------------------------ --- --- --- --------------
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f97bbe3d0 YES YES YES MANUAL-LOAD
1 row selected.
As we can see, oracle has stopped capturing new plans for this sql as SYS_SQL_PLAN_7118fc3f97bbe3d0 is already fixed.
Lets mark fixed=NO again and re-execute the query. Oracle should capturing the plan again for this query.
select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN
------------------------------ ------------------------------ --- --- --- --------------
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f97bbe3d0 YES YES YES MANUAL-LOAD
1 row selected.
declare
myplan pls_integer;
begin
myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => '&sql_handle',plan_name => '&plan_name',attribute_name => 'FIXED', attribute_value => 'NO');
end;
/
Enter value for sql_handle: SYS_SQL_6c0845687118fc3f
Enter value for plan_name: SYS_SQL_PLAN_7118fc3f97bbe3d0
select /*+ id=1000 */ * FROM TEST where id=1000
NAME ID
---------- ----------
SUMIT 1000
1 row selected.
select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN
------------------------------ ------------------------------ --- --- --- --------------
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f642e4a26 YES NO NO AUTO-CAPTURE
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f97bbe3d0 YES YES NO MANUAL-LOAD
2 rows selected.
So, as we can confirm oracle again started capturing new plans we turn FIXED=NO.
Here’s the quick summary,
1. ENABLED=YES (For any of the plan) :- Oracle will start capturing new plans for those queries.
2. Enabled=NO :- Than plan won’t we used.
3. Accepted=Yes (Any one Plan) :- That plan will be used for execution
4. Accepted=YES (For Multiple plans):- Any one plan can be used if fixed=no for all plans.
5. Accepted=No: Plan wont be used.
6. FIXED=YES (for only one plan) Only that plan will be used and oracle will stop capturing/evolving new plans.
7. FIXED=YES (for many plans) Oracle will chose execution plan only from that pool.
SPECIAL CASE :-ENABLED=YES,FIXED=YES BUT ACCEPTED=NO.
What if in our given scenario, we mark SYS_SQL_PLAN_7118fc3f97bbe3d0 as fixed and enabled but not accepted. Will oracle capture the new plan and will it evolve it.
select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN
------------------------------ ------------------------------ --- --- --- --------------
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f97bbe3d0 YES NO YES MANUAL-LOAD
1 row selected.
Lets discuss the above scenario and try to figure out who takes precedence under what condition
select /*+ id=1000 */ * FROM TEST where id=1000;
NAME ID
---------- ----------
SUMIT 1000
1 row selected.
select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN
------------------------------ ------------------------------ --- --- --- --------------
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f642e4a26 YES NO NO AUTO-CAPTURE
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f97bbe3d0 YES NO YES MANUAL-LOAD
2 rows selected.
Oracle has captured new plan. Lets try to evolve that one also.
SET SERVEROUTPUT ON
SET LONG 10000
set lines 175
DECLARE
report clob;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => '&sql_handle', time_limit => &time);
DBMS_OUTPUT.PUT_LINE(report);
END;
/
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_6c0845687118fc3f
PLAN_NAME =
TIME_LIMIT =
60
VERIFY = YES
COMMIT = YES
-------------------------------------------------------------------------------
Report
Summary
-------------------------------------------------------------------------------
There were no SQL plan baselines that required processing.
select sql_handle, plan_name, enabled, accepted,fixed,origin from dba_sql_plan_baselines where signature=7784548270786280511;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN
------------------------------ ------------------------------ --- --- --- --------------
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f642e4a26 YES NO NO AUTO-CAPTURE
SYS_SQL_6c0845687118fc3f SYS_SQL_PLAN_7118fc3f97bbe3d0 YES NO YES MANUAL-LOAD
2 rows selected.
ENABLED takes precedence over FIXED in case of capturing the new plan and vice versa in case of evolving.
I hope above article will help in understanding the role/usage of ENABLE/ACCEPTED/FIXED parameters. Please do write in case you find any discrepancy or want to share some details
Like this:
Like Loading...
