Hello gurus,
I have a problem with bind variables in 19c.
SQL> set feedback on sql_id
SQL> VARIABLE DPT_ID01 NUMBER;
SQL> VARIABLE DPT_ID02 NUMBER;
SQL> EXEC :DPT_ID01 := 50;
SQL> EXEC :DPT_ID02 := 80;
SQL> select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = :DPT_ID01 OR D.DEPARTMENT_ID = :DPT_ID02) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME;
DEPARTMENT_NAME DEPARTMENT_ID FIRST_NAME LAST_NAME EMPLOYEE_ID
------------------------------ ------------- -------------------- ------------------------- -----------
Sales 80 Alberto Errazuriz 147
…
Shipping 50 Winston Taylor 180
79 rows selected.
Values of bind variables in execution plan are wrong : 80 and NULL instead of 50 and 80.
SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('43gugwnrgt1mf', NULL, FORMAT => 'TYPICAL +PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
SQL_ID 43gugwnrgt1mf, child number 0
-------------------------------------
select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME,
E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID =
D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = :DPT_ID01 OR D.DEPARTMENT_ID =
:DPT_ID02) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME
Plan hash value: 2480766633
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT ORDER BY | | 17 | 646 | 4 (25)| 00:00:01 |
| 2 | NESTED LOOPS | | 17 | 646 | 3 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 17 | 646 | 3 (0)| 00:00:01 |
| 4 | INLIST ITERATOR | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 1 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 3 | | 0 (0)| |
| 8 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 17 | 374 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :DPT_ID01 (NUMBER): 80
2 - :DPT_ID02 (NUMBER): (null)
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(("D"."DEPARTMENT_ID"=:DPT_ID01 OR "D"."DEPARTMENT_ID"=:DPT_ID02))
7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter(("E"."DEPARTMENT_ID"=:DPT_ID01 OR "E"."DEPARTMENT_ID"=:DPT_ID02))
Note
-----
- this is an adaptive plan
40 rows selected.In V$SQL_BIND_CAPTURE, values are wrong.
SQL> select NAME, VALUE_STRING from V$SQL_BIND_CAPTURE where SQL_ID = '43gugwnrgt1mf';
NAME VALUE_STRING
--------------------------------
:DPT_ID01 80
:DPT_ID02 NULL
2 rows selected.
A solution is to execute the SELECT like a 11.2 database.
SQL> select /*+ optimizer_features_enable('11.2.0.1') */ D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = :DPT_ID01 OR D.DEPARTMENT_ID = :DPT_ID02) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME;
DEPARTMENT_NAME DEPARTMENT_ID FIRST_NAME LAST_NAME EMPLOYEE_ID
------------------------------ ------------- -------------------- ------------------------- -----------
Sales 80 Alberto Errazuriz 147
...
Shipping 50 Winston Taylor 180
79 rows selected.
SQL_ID: 29rn000mnkzgw
SQL> select NAME, VALUE_STRING from V$SQL_BIND_CAPTURE where SQL_ID = '29rn000mnkzgw';
NAME VALUE_STRING
---------------------------------
:DPT_ID01 50
:DPT_ID02 80
2 rows selected.
SQL_ID: fcjhgqc1pf2dt
SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('29rn000mnkzgw', NULL, FORMAT => 'TYPICAL +PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 29rn000mnkzgw, child number 0
-------------------------------------
select /*+ optimizer_features_enable('11.2.0.1') */ D.DEPARTMENT_NAME,
E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from
employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND
(D.DEPARTMENT_ID = :DPT_ID01 OR D.DEPARTMENT_ID = :DPT_ID02) order by
D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME
Plan hash value: 2480766633
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT ORDER BY | | 14 | 532 | 4 (25)| 00:00:01 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 14 | 532 | 3 (0)| 00:00:01 |
| 4 | INLIST ITERATOR | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 2 | 32 | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | DEPT_ID_PK | 2 | | 1 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 7 | | 0 (0)| |
| 8 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 7 | 154 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :DPT_ID01 (NUMBER): 50
2 - :DPT_ID02 (NUMBER): 80
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(("D"."DEPARTMENT_ID"=:DPT_ID01 OR "D"."DEPARTMENT_ID"=:DPT_ID02))
7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter(("E"."DEPARTMENT_ID"=:DPT_ID01 OR "E"."DEPARTMENT_ID"=:DPT_ID02))
37 rows selected.
Is this a bug?
Thank you for your help.
Best regards,
David
This is expected behaviour; v$sql_bind_capture only stores samples, not every bind value used.
From the docs:
Bind values are captured when SQL statements are executed. To limit the overhead, binds are captured at most every 15 minutes for a given cursor. https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/V-SQL_BIND_CAPTURE.html#GUID-D353F4BE-5943-4F5B-A99B-BC9505E9579C The PEEKED_BINDS option for DBMS_xplan shows the bind values the optimizer used to generate the plan. It only does this on the initial parse (when you first run the query) or the optimizer decides to reparse the statement (e.g. because adaptive cursor sharing kicked in, stats were gathered recently, ...)
So presumably the first query had been executed recently with the values you see (80 and NULL).
The reason the "solution" works is because this changes the text of the SQL statement. This forces a reparse. ANY change to the statement will do this (e.g. changing the case).
For example:
col name format a10
col value_string format a10
set feedback on sql_id
set serveroutput off
var empid number;
exec :empid := 100;
select first_name, last_name
from hr.employees
where employee_id = :empid;
FIRST_NAME LAST_NAME
-------------------- -------------------------
Steven King
1 row selected.
SQL_ID: 5xcha6c6gzxd8
select *
from dbms_xplan.display_cursor( format => 'TYPICAL +PEEKED_BINDS' );
SQL_ID 5xcha6c6gzxd8, child number 0
-------------------------------------
select first_name, last_name from hr.employees where employee_id =
:empid
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 19 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| |
---------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :EMPID (NUMBER): 100
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=:EMPID)
select name, value_string
from v$sql_bind_capture
where sql_id = '5xcha6c6gzxd8';
NAME VALUE_STRI
---------- ----------
:EMPID 100
exec :empid := 999;
select first_name, last_name
from hr.employees
where employee_id = :empid;
0 rows selected.
select *
from dbms_xplan.display_cursor( format => 'TYPICAL +PEEKED_BINDS' );
SQL_ID 5xcha6c6gzxd8, child number 0
-------------------------------------
select first_name, last_name from hr.employees where employee_id =
:empid
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 19 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| |
---------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :EMPID (NUMBER): 100
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=:EMPID)
select name, value_string
from v$sql_bind_capture
where sql_id = '5xcha6c6gzxd8';
NAME VALUE_STRI
---------- ----------
:EMPID 100
SELECT FIRST_NAME, LAST_NAME
FROM HR.EMPLOYEES
WHERE EMPLOYEE_ID = :EMPID;
select *
from dbms_xplan.display_cursor( format => 'TYPICAL +PEEKED_BINDS' );
SQL_ID btrjnuspwkyza, child number 0
-------------------------------------
SELECT FIRST_NAME, LAST_NAME FROM HR.EMPLOYEES WHERE EMPLOYEE_ID =
:EMPID
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 19 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| |
---------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :EMPID (NUMBER): 999
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=:EMPID) Notice that the SQL id changes => new statement => new parse => new bind values are catured.