It often happens that changes are implemented in new Oracle releases without most (if not all) DBAs and developers being informed. Among those changes, introduced most probably in version 19.13, is a behavior change in the force matching the signature of a SQL query using a mix of bind variables and literals. Here is a setup that I will run in two different databases 19.3 and 21.3 respectively:
SQL> select banner_full from v$version;
BANNER_FULL
-----------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> var empnum number
SQL> exec :empnum := 7839
SQL> select count(1) from emp where empno = :empnum and deptno=10; ----> deptno =10
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID 9kmf47x7byqq8, child number 0
-------------------------------------
-------------------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 |
-------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPTNO"=10)
3 - access("EMPNO"=:EMPNUM)
SQL> select count(1) from emp where empno = :empnum and deptno=20; ----> deptno =20
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID gz41rf5f6cww1, child number 0
-------------------------------------
-------------------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 |
-------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPTNO"=20)
3 - access("EMPNO"=:EMPNUM)
SQL> select
sql_id
,to_char(force_matching_signature)
from
gv$sql
where
sql_id in ('9kmf47x7byqq8','gz41rf5f6cww1');
SQL_ID TO_CHAR(FORCE_MATCHING_SIGNATURE)
------------- ----------------------------------------
gz41rf5f6cww1 13157356546279728935
9kmf47x7byqq8 12268692852591778641
Notice that I ran the same query twice changing only the value of the department 10 and then 20. As a result, the force-matching signatures of the two SQL_IDs are different. This is quite normal and predictable, isn’t it?
But now look what I get when I re-execute the same setup on a 21.3:
SQL> select banner_full from v$version;
BANNER_FULL
------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> var empnum number
SQL> exec :empnum := 7839
SQL> select count(1) from emp where empno = :empnum and deptno=10; ----> deptno =10
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID 9kmf47x7byqq8, child number 0
-------------------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 |
-------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPTNO"=10)
3 - access("EMPNO"=:EMPNUM)
SQL> select count(1) from emp where empno = :empnum and deptno=20; ----> deptno =20
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID gz41rf5f6cww1, child number 0
-------------------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 |
-------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPTNO"=20)
3 - access("EMPNO"=:EMPNUM)
select
sql_id
,to_char(force_matching_signature)
from
gv$sql
where
sql_id in ('9kmf47x7byqq8','gz41rf5f6cww1');
SQL_ID TO_CHAR(FORCE_MATCHING_SIGNATURE)
------------- ---------------------------------
gz41rf5f6cww1 12531360796234248997
9kmf47x7byqq8 12531360796234248997
The two forces matching signatures of the two SQL_IDs are now identical
What impact can this have?
The first positive collateral side effect, if it can be considered as a positive effect, concerns the use of SQL profiles to fix an execution plan for a query using both bind variables and literals. If you read the Hacking Profiles article by Jonathan Lewis, you will understand that Jonathan has already explained the critical limitations (restrictions) of SQL Profiles when used with queries mixing bind variables and literals. They won’t be used in this case even if you force the SQL profile to use their force_mathing property. Here’s below the demonstration starting with 19.3 and then 21.3 respectively.
SQL> @coe_xfr_sql_profile.sql ----> Carlos Sierra script to fix a SQL profile
Parameter 1:
SQL_ID (required)
Enter value for 1: gz41rf5f6cww1
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
1126804136 ,002
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 1126804136
SQL>select to_char(signature), force_matching from dba_sql_profiles where name like '%gz41rf5f6cww1%';
TO_CHAR(SIGNATURE) FOR
---------------------------------------- ---
13157356546279728935 YES
SQL> select count(1) from emp where empno = :empnum and deptno=20;
SQL_ID gz41rf5f6cww1, child number 0
-------------------------------------
-------------------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 |
-------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPTNO"=20)
3 - access("EMPNO"=:EMPNUM)
Note
-----
- SQL profile coe_gz41rf5f6cww1_1126804136 used for this statement
SQL> select count(1) from emp where empno = :empnum and deptno=10;
SQL_ID 9kmf47x7byqq8, child number 0
-------------------------------------
-------------------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 |
-------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPTNO"=10)
3 - access("EMPNO"=:EMPNUM)
Exactly as demonstrated by Jonathan Lewis, as soon as we change the value of the département(from 20 to 10) the SQL profile stops being used.
As of version 19.13, this is no longer the case. Indeed, in the same situation as above, the SQL Profile is now usable. Here is a demonstration done on 21.3. version:
SQL> select banner_full from v$version;
BANNER_FULL
------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> @coe_xfr_sql_profile.sql
Parameter 1:
SQL_ID (required)
Enter value for 1: gz41rf5f6cww1
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
1126804136 .125
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 1126804136
SQL>select to_char(signature), force_matching from dba_sql_profiles where name like '%gz41rf5f6cww1%';
TO_CHAR(SIGNATURE) FOR
---------------------------------------- ---
12531360796234248997 YES
SQL> select count(1) from emp where empno = :empnum and deptno=20;
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID gz41rf5f6cww1, child number 0
-------------------------------------
-------------------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 |
-------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPTNO"=20)
3 - access("EMPNO"=:EMPNUM)
Note
-----
- SQL profile coe_gz41rf5f6cww1_1126804136 used for this statement
SQL> select count(1) from emp where empno = :empnum and deptno=10;
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID 9kmf47x7byqq8, child number 0
-------------------------------------
-------------------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 |
-------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPTNO"=10)
3 - access("EMPNO"=:EMPNUM)
Note
-----
- SQL profile coe_gz41rf5f6cww1_1126804136 used for this statement
As you can see, unlike version 19.3, in 21.3, the SQL profile has been used for both queries even though I changed the literal department value from 20 to 10
Bottom Line
I don’t really know if this change was worth a blog post but, even if some people will say that you should avoid using SQL Profiles at the detriment of SPM Baseline, it’s always a good thing to see a restriction in the use of SQL profiles removed by Oracle.
Mohamed Houri’s Oracle Notes 