Skip to Main Content
  • Questions

  • View for finding Bind variable values

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Paras.

Asked: July 06, 2017 - 8:47 am UTC

Last updated: March 15, 2021 - 5:03 am UTC

Version: 11.2.0.4

Viewed 50K+ times! This question is

You Asked

Hi,

We are using the 11.2.0.4 database enterprise edition , and due to the application performance need we had to set CURSOR_SHARING=FORCE, as application was not able to use bind variables.

When i try to generate the PLAN for sql_id using OR when try to get the SQL text the values listed between "Select...from" get replaced by the system generated bind variables as expected behavior. the bind values replaced in "where" clause can be foun by querying view V$SQL_BIND_CAPTURE/DBA_HIST_SQLBIND, but this view doesn't show the bind values which were in between "SELECT...FROM".

The question i have is is there any view or WAY where i can find those bind values which are replaced between "Select....from" in sql statement/


For Example:-

select
cola,
colb,
to_char(colc, :"SYS_B_00"),
(cold / :"SYS_B_01") ,
(cole) / :"SYS_B_02",
decode(colf, :"SYS _B_03", :"SYS_B_04", :"SYS_B_05", :"SYS_B_06")
from
tablea
where
cola != :"SYS_B_08"
and colb = :"SYS_B_09"
and colc between trunc(sysdate - :"SYS_B_10") and sysdate
order by
ttd.transactiondate desc;

Now, when i query view GV$SQL_BIND_CAPTURE i don't see values for bind from 1 to 7, how can i find those?

select INST_ID,SQL_ID,POSITION,NAME,VALUE_STRING from GV$SQL_BIND_CAPTURE where sql_id='g0fdjxptp64bh' and inst_id=1 order by POSITION

INST_ID SQL_ID POSITION NAME VALUE_STRING
---------- ------------- ---------- ------------------------------ ------------
1 g0fdjxptp64bh 1 :SYS_B_00 --> NO VALUE
1 g0fdjxptp64bh 2 :SYS_B_01
1 g0fdjxptp64bh 3 :SYS_B_02
1 g0fdjxptp64bh 4 :SYS_B_03
1 g0fdjxptp64bh 5 :SYS_B_04
1 g0fdjxptp64bh 6 :SYS_B_05
1 g0fdjxptp64bh 7 :SYS_B_06
1 g0fdjxptp64bh 8 :SYS_B_07 9999999999
1 g0fdjxptp64bh 9 :SYS_B_08 Y
1 g0fdjxptp64bh 10 :SYS_B_09 XXX
1 g0fdjxptp64bh 11 :SYS_B_10 10


Thanks.

and Connor said...

Just an aside, dbms_xplan lets you look at binds as well

select * from table (dbms_xplan.display_cursor('[mysqlid]',[child], format => 'TYPICAL +PEEKED_BINDS'));

but in any event, V$SQL_BIND_CAPTURE is not a "complete" way of monitoring binds - it's too expensive for us to populate that for every execution, so its a sampled data set.

If you are licensed for sql monitoring, you can use V$SQL_MONITOR to get the binds, but similarly, this will be for queries that are being monitored (aka, long running queries only by default).

If you really want to get *every* bind, then you pretty much have to resort to low level tracing via DBMS_MONITOR with 'binds' set to true.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, July 07, 2017 - 3:00 am UTC


Bind Variables

Amin Adatia, March 10, 2021 - 12:38 pm UTC

I have the same SQL_ID but the Bind variables I pass to the procedure are different. I run the procedure 4 times with different values. GV_$SQL_BIND_CAPTURE shows the same value no matter which job I check running the same procedure
Connor McDonald
March 11, 2021 - 2:56 am UTC

As the answer says

V$SQL_BIND_CAPTURE is not a "complete" way of monitoring binds - it's too expensive for us to populate that for every execution, so its a sampled data set.

If you want to grab every execution and its binds, then a trace is probably your best bet

dbms_monitor.session_trace_enable(binds=>true)

Another approach apart from Tracing

Rajeshwaran Jeyabal, March 11, 2021 - 4:21 pm UTC

<quote>
....
If you really want to get *every* bind, then you pretty much have to resort to low level tracing via DBMS_MONITOR with 'binds' set to true.
....
</quote>

Just thought of keeping you posted that an another approach available to capture all the bind variables apart from tracing is using FGA.

something like this:

demo@ORA12C> SELECT * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-1980        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-1981       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-1981       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-1981       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-1981       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-1981       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-1981       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-1987       3000                    20
      7839 KING       PRESIDENT            17-NOV-1981       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-1981       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-1987       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-1981        950                    30
      7902 FORD       ANALYST         7566 03-DEC-1981       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-1982       1300                    10

14 rows selected.

demo@ORA12C> begin
  2     dbms_fga.add_policy(
  3             object_schema=>user,
  4             object_name=>'EMP',
  5             policy_name=>'EMP_SAL_ACCESS',
  6             audit_condition=> ' sal > 1500 ',
  7             audit_column=>'SAL' ) ;
  8  end;
  9  /

PL/SQL procedure successfully completed.

demo@ORA12C> @printtbl "select * from dba_fga_audit_trail "

PL/SQL procedure successfully completed.

demo@ORA12C> select empno,ename from emp;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

demo@ORA12C> @printtbl "select * from dba_fga_audit_trail "

PL/SQL procedure successfully completed.

demo@ORA12C>
demo@ORA12C> variable sal number
demo@ORA12C> exec :sal := 1400;

PL/SQL procedure successfully completed.

demo@ORA12C> select empno,ename from emp where sal < :sal;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7521 WARD
      7654 MARTIN
      7876 ADAMS
      7900 JAMES
      7934 MILLER

6 rows selected.

demo@ORA12C> @printtbl "select * from dba_fga_audit_trail "

PL/SQL procedure successfully completed.

demo@ORA12C>
demo@ORA12C>
demo@ORA12C>
demo@ORA12C> exec :sal := 1700;

PL/SQL procedure successfully completed.

demo@ORA12C> select empno,ename from emp where sal < :sal;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7654 MARTIN
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7934 MILLER

8 rows selected.

demo@ORA12C> @printtbl "select * from dba_fga_audit_trail "
SESSION_ID                    : "6090006"
TIMESTAMP                     : "03-may-2017 16:26:42"
DB_USER                       : "DEMO"
OS_USER                       : "local\admin"
USERHOST                      : "localhost"
CLIENT_ID                     : ""
ECONTEXT_ID                   : ""
EXT_NAME                      : "local\admin"
OBJECT_SCHEMA                 : "DEMO"
OBJECT_NAME                   : "EMP"
POLICY_NAME                   : "EMP_SAL_ACCESS"
SCN                           : "4780181"
SQL_TEXT                      : "select empno,ename from emp where sal < :sal"
SQL_BIND                      : " #1(4):1700"  <======== our bind variable values
COMMENT$TEXT                  : ""
STATEMENT_TYPE                : "SELECT"
EXTENDED_TIMESTAMP            : "03-MAY-17 04.26.42.227000 PM +05:30"
PROXY_SESSIONID               : ""
GLOBAL_UID                    : ""
INSTANCE_NUMBER               : "0"
OS_PROCESS                    : "4796:9400"
TRANSACTIONID                 : ""
STATEMENTID                   : "83"
ENTRYID                       : "2"
OBJ_EDITION_NAME              : ""
DBID                          : "316670787"
RLS_INFO                      : ""
CURRENT_USER                  : "DEMO"
-----------------

PL/SQL procedure successfully completed.

demo@ORA12C>

Connor McDonald
March 15, 2021 - 5:03 am UTC

True but thats a non trivial parsing over head you have just throw onto your system
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

DBMS_XPLAN

More on PL/SQL routine DBMS_XPLAN here