troubleshooting Question
SQL Plan Baseline is not used
asked on
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:
For example if I run
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
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.
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.