Oracle 19c New Feature High-Frequency Statistics

The automatic optimizer statistics collection job which calls DBMS_STATS package runs in predefined maintenance windows and these maintenance windows are open once a day during which various jobs including the gathering of statistics is performed. For volatile tables statistics can go stale between two consecutive executions of such automatic statistics collection jobs. The presence of […]

Why do my execution plans not change after gathering statistics? – A look at Rolling Cursor Invalidations

In releases prior to Oracle 10g, the gathering of statistics using DBMS_STATS resulted in immediate invalidations of dependent cached cursors. This was true unless NO_INVALIDATE parameter of the DBMS_STATS command was set to TRUE. It was felt that gathering of statistics could actually have a negative impact on performance because of the fact that invalidation […]

Using TUNE_MVIEW and EXPLAIN_REWRITE to enable us to tune MV Fast Refreshes and Query Rewrites

We can use the DBMS_MVIEW.EXPLAIN_REWRITE package (which was first introduced in Oracle 9i if memory serves me right) to diagnose why Query redirection to a materialized view is not happening. We can redirect the output to a table called REWRITE_TABLE which is created via the utlxrw.sql script located under $ORACLE_HOME/rdbms/admin. Let us take a simple […]

Solving a 10g Bind Variable Peeking problem by granting and revoking privileges

The Cost Based optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. Based on this, the optimizer determines the selectivity of the WHERE clause condition as if literals have been used instead of bind variables. On subsequent invocations of the cursor, no bind peeking takes place, and the […]

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?