To Pin the Plan Hash Value for a query using SQL Plan Baseline

 A common experience faced by many developers and DBAs is when a query is performing normally and giving the output within a reasonable time, and suddenly one fine day the same query starts taking a very long time to execute. The most probable reasons for this is due to abnormal change in the data growth of the table and the statistics gathered on the table are not the latest, or there has been some oracle patch or upgrade due to which the query plan has changed and the new plan is not as efficient as the old one. 

If we know that the data for the table is having a fixed pattern, and the current plan is giving the results in good time, we can ensure that oracle will always use that particular plan by pinning the plan hash value for the query.

Below is an example which shows a query for which the plan has changed and we want the query to execute with the original plan.

We create the cal_cntr table.

CREATE TABLE cal_cntr
(
  id NUMBER, 
  cal_date DATE, 
  name VARCHAR2(10)
);

We now pump data into this table for 5 years at an average volume of 1000 records per day, giving a total record count of 3.65 lakh records.

declare
v_count  number;
begin
v_count:=0;
for i in 1..1830 loop
   for j in 1..1000 loop
   v_count:= v_count+1;
   insert into cal_cntr values (v_count, sysdate-i, 'system');
   end loop;
  end loop;
  commit;
end;
/

We now create an index on the cal_date column and gather statistics on the table.

create index cal_cntr_i on cal_cntr(cal_date);

For gathering statistics we run the below sql statement.

exec dbms_stats.gather_table_stats(ownname=>'HR', tabname=>'cal_cntr', cascade=>true, estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1');

The estimate_percent=>null indicates we gather the statistics for all the rows and is equilavent to setting it to estimate_percent=>100. 

For more information on the parameter method_opt you can refer to the link Understand Method Opt Param for stats in oracle. 

 We now select data for 1 year from this table and take the explain plan for the query.

select * from cal_cntr where cal_date > sysdate - 365;

EXPLAIN PLAN FOR
select * from cal_cntr where cal_date > sysdate - 365;

select * from table(dbms_xplan.display);

Output

Plan hash value: 991404550
 
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |   364K|  7113K|  1843   (5)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| CAL_CNTR |   364K|  7113K|  1843   (5)| 00:00:01 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("CAL_DATE">SYSDATE@!-365)

From the plan we see that though there is an index on the cal_date column, the optimizer still prefers to do a FTS as the selectivity of the query is about 20% of all the data in the table. 

We will now insert data for another 5 years in this table and check the plan before and after we gather the statistics.

declare
v_count  number;
begin
v_count:=1830000;
for i in 1..1830 loop
   for j in 1..1000 loop
   v_count:= v_count+1;
   insert into cal_cntr values (v_count, sysdate-1830-i, 'system');
   end loop;
  end loop;
  commit;
end;
/

The plan for the query is as below

Plan hash value: 991404550
 
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |   364K|  7112K|  3048   (3)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| CAL_CNTR |   364K|  7112K|  3048   (3)| 00:00:01 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("CAL_DATE">SYSDATE@!-365)

Thus we see that though the data volume has doubled, the plan still shows that the optimizer is doing a FTS. We now gather statistics and check the plan again.

exec dbms_stats.gather_table_stats(ownname=>'HR', tabname=>'cal_cntr', cascade=>true, estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1');

select * from cal_cntr where cal_date > sysdate - 365;

EXPLAIN PLAN FOR
select * from cal_cntr where cal_date > sysdate - 365;

select * from table(dbms_xplan.display);

Plan hash value: 762351933
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |   364K|  7466K|  3149   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CAL_CNTR   |   364K|  7466K|  3149   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | CAL_CNTR_I |   364K|       |  1483   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CAL_DATE">SYSDATE@!-365)

The new plan after gathering the statistics shows that the index is getting used as data selectivity has now become 10% of the overall data volume. From the plan it is visible that the Cost and Bytes is lesser for a FTS. So if I know that the data volume and growth in this table is going to be in this range and 90% of my queries are on a 1 year range of data I can pin the FTS plan for this query to make sure the query always makes use of the FTS plan. 

 We first find the sql_id for the query as follows

SELECT SQL_TEXT, SQL_ID FROM V$SQL where SQL_TEXT LIKE '%select * from cal_cntr where cal_date%'
AND UPPER(SQL_TEXT) NOT LIKE '%EXPLAIN PLAN%' AND UPPER(SQL_TEXT) NOT LIKE '%SELECT * FROM V$SQL%';

SQL_TEXT	SQL_ID
select * from cal_cntr where cal_date > sysdate - 365	4v8tdnppgjzmt

We query the DBA_SQL_PLAN_BASELINES table to see if a plan for this query is baseline. 

As SYS user on XEPDB1 schema give GRANT on this table to SYSTEM user in XEPDB1.

C:\Users\winni>sqlplus sys@xepdb1 as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Thu Jul 3 11:45:47 2025
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> grant select on DBA_SQL_PLAN_BASELINES TO SYSTEM;

Grant succeeded.  

As a DBA user of SYSTEM user we select from this table for this query

SELECT * FROM sys.DBA_SQL_PLAN_BASELINES where SQL_TEXT LIKE '%select * from cal_cntr where cal_date%';
no rows selected

As seen, no plan for this query is loaded.

We now load the plan for this sql_id as follows

DECLARE
cnt number;
BEGIN
cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'4v8tdnppgjzmt');   --index plan
dbms_output.put_line('cnt ' || cnt);
END;

Now if we query the DBA_SQL_PLAN_BASELINES table we get the following output.

SELECT SQL_HANDLE,SQL_TEXT, PLAN_NAME  FROM sys.DBA_SQL_PLAN_BASELINES where SQL_TEXT LIKE '%select * from cal_cntr where cal_date%';  

SQL_HANDLE	SQL_TEXT	PLAN_NAME
SQL_937d5a47027e4114	select * from cal_cntr where cal_date > sysdate - 365	SQL_PLAN_96zau8w17wh8nb8af81ce

For this sql_handle, we attach the plan hash value of the plan having full table scan as follows. 

The FTS plan has a plan has value of 991404550 (good plan). 

The index range scan has a plan hash value of 762351933 (not so good plan). 

We thus attach the plan that has value of the FTS plan i.e. 991404550 to the SQL_937d5a47027e4114 SQL_handle and sql_id 4v8tdnppgjzmt.

DECLARE
cnt number;
BEGIN
cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '4v8tdnppgjzmt',plan_hash_value => 991404550,sql_handle => 'SQL_937d5a47027e4114');
dbms_output.put_line('cnt ' || cnt);
END;  

We can verify this from DBA_SQL_PLAN_BASELINES

  
SELECT *  FROM sys.DBA_SQL_PLAN_BASELINES where SQL_TEXT LIKE '%select * from cal_cntr where cal_date%';  

SIGNATURE	SQL_HANDLE	SQL_TEXT	PLAN_NAME	CREATOR	ORIGIN	PARSING_SCHEMA_NAME	DESCRIPTION	VERSION	CREATED	LAST_MODIFIED	LAST_EXECUTED	LAST_VERIFIED	ENABLED	ACCEPTED	FIXED	REPRODUCED	AUTOPURGE	ADAPTIVE	OPTIMIZER_COST	MODULE	ACTION	EXECUTIONS	ELAPSED_TIME	CPU_TIME	BUFFER_GETS	DISK_READS	DIRECT_WRITES	ROWS_PROCESSED	FETCHES	END_OF_FETCH_COUNT
10627749956695245076	SQL_937d5a47027e4114	select * from cal_cntr where cal_date > sysdate - 365	SQL_PLAN_96zau8w17wh8nb8af81ce	SYSTEM	MANUAL-LOAD-FROM-CURSOR-CACHE	HR		21.0.0.0.0	03-JUL-25 12.08.52.159000000 PM	03-JUL-25 12.08.52.000000000 PM			YES	YES	NO	YES	YES	NO	1843	SQL Developer		1	467228	181593	13725	0	0	364000	7281	1

Now if we run the query and take the plan for it, it will show that the query is using the FTS plan.

EXPLAIN PLAN FOR
select * from cal_cntr where cal_date > sysdate - 365;

select * from table(dbms_xplan.display);

Plan output

Plan hash value: 991404550
 
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |   361K|  7423K|  3701   (5)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| CAL_CNTR |   361K|  7423K|  3701   (5)| 00:00:01 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("CAL_DATE">SYSDATE@!-365)
 
Note
-----
   - SQL plan baseline "SQL_PLAN_96zau8w17wh8nb8af81ce" used for this statement  

In this output,  the predicate information shows that the query is using the plan which has been baselined with the baseline plan name "SQL_PLAN_96zau8w17wh8nb8af81ce"

To disable a plan from the baseline, we can execute the following script

DECLARE
cnt number;
BEGIN
cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(sql_handle=>'SQL_937d5a47027e4114',attribute_name=>'enabled',attribute_value=>'NO');
dbms_output.put_line('cnt ' || cnt);
END;

SELECT SQL_HANDLE,SQL_TEXT, PLAN_NAME, ENABLED  FROM sys.DBA_SQL_PLAN_BASELINES where SQL_TEXT LIKE '%select * from cal_cntr where cal_date%';

SQL_HANDLE	SQL_TEXT	PLAN_NAME	ENABLED
SQL_937d5a47027e4114	select * from cal_cntr where cal_date > sysdate - 365	SQL_PLAN_96zau8w17wh8nb8af81ce	NO

Now after gathering the statistics we see that the plan from the DBA_SQL_PLAN_BASELINES is no longer being used and the query is doing an index range scan.

Plan hash value: 762351933
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |   360K|  7383K|  3114   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CAL_CNTR   |   360K|  7383K|  3114   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | CAL_CNTR_I |   360K|       |  1465   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CAL_DATE">SYSDATE@!-365)

References

How Does An Execution Plan Suddenly Change When The Statistics (And Everything Else) Remains The Same ? (In Limbo)

Oracle Change Execution Plan for SQL

God's Word for the day

Free citizens will serve a wise servant, 

 and an intelligent person will not complain.

Sirach 10:25