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!!!!!

Slow Mview Refresh in Oracle 12.2

Recently i got a complain that MView refresh are getting slower. When traced got to know that refresh is slow due to internal queries taking full scans on the tables. All these internal queries are related to new feature of Oracle 12.2 which is related to materialized view usage tracking. Feature is good but i see lot of slowness from the application perspective.

Just brief about feature: Starting 12.2 Oracle Database collects and stores statistics about materialized view refresh operations. These statistics are accessible using data dictionary views.

Statistics for both current and historical materialized view refresh operations are stored in the database. Historical materialized view refresh statistics enable you to understand and analyze materialized view refresh performance over time in your database. Refresh statistics can be collected at varying levels of granularity.

Back to the problem in trace I saw Oracle executed DML’s and selects on below tables and were performing full scan. See the execution plan below

mvref$_stats;

mvref$_run_stats;

mvref$_change_stats;

mvref$_stmt_stats;

Now consider with each refresh lot of entries are inserted/updated/deleted in above tables; so consider if you have millions of entries then such statements with huge of executions can prove to be a disaster in terms of performance. Strange is that there are no indexes on the table at least for the 

deletes.:(

DELETE FROM SYS.MVREF$_CHANGE_STATS WHERE REFRESH_ID = :B2 AND MV_OBJ#

= :B1

Plan hash value: 2107128738

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

| Id  | Operation          | Name                | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | DELETE STATEMENT   |                     |       |       |     2 (100)|          |

|   1 |  DELETE            | MVREF$_CHANGE_STATS |       |       |            |          |

|*  2 |   TABLE ACCESS FULL| MVREF$_CHANGE_STATS |     1 |    26 |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - filter(("REFRESH_ID"=:B2 AND "MV_OBJ#"=:B1))

SQL_ID  9g4dd2hsrpbph, child number 0

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

DELETE FROM SYS.MVREF$_RUN_STATS WHERE REFRESH_ID = :B1

Plan hash value: 2011437079

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

| Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | DELETE STATEMENT   |                  |       |       |     2 (100)|          |

|   1 |  DELETE            | MVREF$_RUN_STATS |       |       |            |          |

|*  2 |   TABLE ACCESS FULL| MVREF$_RUN_STATS |     1 |    13 |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - filter("REFRESH_ID"=:B1)

There is a collection level parameter which specifies the collection level for materialized view refresh statistics. The values that can be set for the COLLECTION_LEVEL parameter are:

NONE: No statistics are collected for materialized view refresh operations.

TYPICAL : Only basic refresh statistics are collected for materialized view refresh operations. This is the default setting.

ADVANCED : Detailed statistics, including the parameters used in the refresh operation and the SQL statements that are run, are collected for materialized view refresh operations.

In my case i asked to turn of the feature.

In general also if we see lot of MVIEWS related activities it is better to turn of this feature as running such deletes will make application slow.

So to avoid it better to turn off the collection/tracking level. By default collection level is typical. So as to stop completely we need to execute below statement to turn it off. 

exec dbms_mview_stats.set_system_default('COLLECTION_LEVEL', 'NONE');

select * from DBA_MVREF_STATS_SYS_DEFAULTS;

PARAMETER_NAME   VALUE

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

COLLECTION_LEVEL NONE

RETENTION_PERIOD 365

There are lot of stuff related to MViews usage tracking so please explore and use it wisely.

Enjoy Learning!!!