Link to home
Start Free TrialLog in
Avatar of dbadm
dbadmFlag for Italy

asked on

SQL Plan Baseline is not used

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;
/

Open in new window



For example if I run

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

Open in new window


--------------------------------------------------------------------------------
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

Open in new window

   
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
ASKER CERTIFIED SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial