troubleshooting Question

SQL Plan Baseline is not used

Avatar of dbadm
dbadmFlag for Italy asked on
DatabasesOracle Database* Database Performance
1 Comment1 Solution20 ViewsLast Modified:
Hi,
My Oracle version is: Oracle Database 18c EE on Oracle Linux Server 7.9

I have seen various case that SQL Plan Baseline has not been used by optimizer, seems optimizer just used to ignore existing SPB and switched to cost-based decision in order to produce an execution plan for a SQL statement. SQL Plan Baseline was created for the SQL several months ago and it was used by optimizer as expected, but recently optimizer has started to ignore it

Below steps that I used to create SQL Plan Baseline:
exec DBMS_SQLTUNE.CREATE_SQLSET('sqlset_33x51afpuq0mx');


declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(10504, 10505,'sql_id='||CHR(39)||'33x51afpuq0mx'||CHR(39)||' and plan_hash_value=3999990270 and instance_number=1',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('sqlset_33x51afpuq0mx', baseline_ref_cursor);
end;


declare
my_integer pls_integer;
begin
my_integer := dbms_spm.load_plans_from_sqlset(sqlset_name => 'sqlset_33x51afpuq0mx',
                                              sqlset_owner => 'SYS',
                                              fixed => 'YES',
                                              enabled => 'YES');
                                              DBMS_OUTPUT.PUT_line(my_integer);
end;
/


For example if I run

select * from table(dbms_xplan.display_sql_plan_baseline('SQL_9dfa1d331ab96779'))

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_9vyhx6cdbktvhad382142         Plan id: 2906136899
Enabled: YES     Fixed: NO      Accepted: NO      Origin: EVOLVE-LOAD-FROM-AWR
Plan rows: From dictionary
--------------------------------------------------------------------------------


Plan name: SQL_PLAN_9vyhx6cdbktvhb0c0e3ba         Plan id: 2965431229
Enabled: YES     Fixed: NO      Accepted: NO      Origin: EVOLVE-LOAD-FROM-AWR
Plan rows: From dictionary
--------------------------------------------------------------------------------


Note
-----
   - statistics feedback used for this statement
 
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_9vyhx6cdbktvhe14e12fe         Plan id: 3999990270
Enabled: YES     Fixed: YES     Accepted: YES     Origin: MANUAL-LOAD-FROM-STS
Plan rows: From dictionary
--------------------------------------------------------------------------------


--------------------------------------------------------------------------------
Plan name: SQL_PLAN_9vyhx6cdbktvhf5b96f1f         Plan id: 4122570529
Enabled: YES     Fixed: NO      Accepted: NO      Origin: EVOLVE-LOAD-FROM-AWR
Plan rows: From dictionary
--------------------------------------------------------------------------------




--------------------------------------------------------------------------------
Plan name: SQL_PLAN_9vyhx6cdbktvh02b9f50d         Plan id: 45741329
Enabled: YES     Fixed: NO      Accepted: NO      Origin: EVOLVE-LOAD-FROM-AWR
Plan rows: From dictionary
--------------------------------------------------------------------------------


Note
-----
   - statistics feedback used for this statement
 
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_9vyhx6cdbktvh21375c10         Plan id: 557276179
Enabled: YES     Fixed: NO      Accepted: NO      Origin: EVOLVE-LOAD-FROM-AWR
Plan rows: From dictionary
--------------------------------------------------------------------------------


Note
-----
   - statistics feedback used for this statement
   - Failed to use SQL plan baseline for this statement
 
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_9vyhx6cdbktvh7222ae09         Plan id: 1914875409
Enabled: YES     Fixed: NO      Accepted: NO      Origin: EVOLVE-LOAD-FROM-AWR
Plan rows: From dictionary
--------------------------------------------------------------------------------


Note
-----
   - Failed to use SQL plan baseline for this statement
   
From DBA_HIST_SQLSTAT I can see that now for each SNAP_ID there are 3 PLAN_HASH_VALUE (2965431229, 45741329, 3999990270).

Have you any idea Why SQL Plan Baseline is not used? It Should only use 3999990270 PLAN_HASH_VALUE.

Thanks
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 1 Comment.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros