V$SQL_BIND_CAPTURE Timestamp Datatype Binds are Not Captured

Being an Oracle performance expert you always be dealing with lot of queries. While doing SQL tuning of a query, it is quite common that we ask application team what binds are being used or else we capture binds from dictionary views.

But with timestamp as an bind data type most of us might have faced this issue where we see Oracle doesn’t capture TIMESTAMP data type binds variable but shows null; this is basically a bug Bug 6156624 which is supposed to get fix in 11.2 and later version.

The "bug" is that binds are not converted into a string for the value_string column.

Say for e.g. :d and :e is timestamp bind and below you can see the values are not captured with VALUE_STRING.

a99r5tq1dccym

--------------------------------------------------------

SELECT *

  FROM (SELECT   yt, xt

            FROM tab_defn

           WHERE (sy_name IN (:a, :,b :c))

             AND (effective_start_date < :d)

             AND (effective_end_date > :e)

        ORDER BY :SYS_B_0 ASC)

 WHERE ROWNUM <= :SYS_B_1

SELECT b.NAME,  b.datatype_string, b.value_string

  FROM v$sql_bind_capture b

WHERE sql_id = 'a99r5tq1dccym'

NAME                           DATATYPE_STRING                                      VALUE_STRING

------------------------------ -------------------------------------------  -------------------------------

:a                                    VARCHAR2(128)                                         X_9_XXXX

:b                                    VARCHAR2(128)                                         PP_P9_11_30

:c                                    VARCHAR2(128)                                         YYX_P9_000_31

:d                                    TIMESTAMP

:e                                    TIMESTAMP

:SYS_B_0                               NUMBER                                                1:

SYS_B_1                               NUMBER                                                30000

Now how to use it from Oracle perspective: ANYDATA.accesstimestamp (value_anydata) is the column which fetches such data. Please observe it.

/* Formatted on 2019/07/15 18:02 (Formatter Plus v4.8.8) */

SELECT b.NAME,  b.datatype_string, b.value_string,

       ANYDATA.accesstimestamp (value_anydata)

  FROM v$sql_bind_capture b

WHERE sql_id = 'a99r5tq1dccym';

NAME                           DATATYPE_STRING VALUE_STRING                   ANYDATA.ACCESSTIMESTAMP(VALUE_ANYDATA)

------------------------------ --------------- ------------------------------ ------------------------------------------

:a                 VARCHAR2(128)                 X_9_XXXX

:b                 VARCHAR2(128)                 PP_P9_11_30

:c                 VARCHAR2(128)                 YYX_P9_000_31

:d                 TIMESTAMP                                                           12-JUL-19 04.33.33.000000000 PM

:e                 TIMESTAMP                                                           12-JUL-19 04.33.33.000000000 PM

:SYS_B_0           NUMBER                       1

:SYS_B_1           NUMBER                       30000

So see above the timestamp got captured.

Enjoy Learning!!!!!