Skip to Main Content
  • Questions
  • SELECT with bind variables : wrong execution plan and wrong values in v$sql_bind_capture

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, David.

Asked: June 03, 2022 - 9:40 am UTC

Last updated: June 07, 2022 - 3:11 am UTC

Version: 19.0.0.0.0

Viewed 100+ times

You Asked

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

and Chris said...

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.

Rating

  (1 rating)

Comments

David, June 06, 2022 - 4:18 pm UTC

Perfect response as usual :-)
Thank you very much, it is clearer now.
Connor McDonald
June 07, 2022 - 3:11 am UTC

Glad we could help

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.