OCM Tips and Tricks – Gather statistics on a specific table without invalidating cursors

Gather statistics on a specific table without invalidating cursors

Reference:
Oracle® Database PL/SQL Packages and Types Reference 11g Release 2 -> 140 DBMS_STATS
Rolling Cursor Invalidations with DBMS_STATS.AUTO_INVALIDATE (Doc ID 557661.1)

When gathering statistics using DBMS_STATS package, NO_INVALIDATE parameter can be used to not invalidate the cursors in the cache automatically. This parameter accepts 3 values: 1) TRUE doesn’t invalidate dependent cursors 2) FALSE invalidate dependent cursors 3) AUTO_INVALIDATE which is the default value that lets Oracle decide when to invalidate the cursors. To get the default value of NO_VALIDATE parameter, use GET_PREFS function as follows:

SQL> SELECT DBMS_STATS.GET_PREFS( ‘NO_INVALIDATE’ ) FROM dual;
DBMS_STATS.GET_PREFS(‘NO_INVALIDATE’)
————————————————————
DBMS_STATS.AUTO_INVALIDATE
SQL>

In the following example NO_INVALIDATE with FALSE option will be used to show how cursors are invalidated in the cache. Let’s flush the shared pool, create a new table and gather the statistics:

SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.

SQL> CREATE TABLE tbl_invalidation_test AS SELECT * FROM dba_objects;
Table created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(NULL,’tbl_invalidation_test’);
PL/SQL procedure successfully completed.

Now query the table twice and check V$SQL to see count of executions, status of the cursor and count of invalidations:

SQL> SELECT COUNT(1) FROM tbl_invalidation_test;
COUNT(1)
———-
71972

SQL> /
COUNT(1)
———-
71972

SQL> SELECT child_number,
parse_calls,
executions,
object_status,
invalidations
FROM v$sql
WHERE sql_text = ‘SELECT COUNT(1) FROM tbl_invalidation_test’;

CHILD_NUMBER PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME INVALIDATIONS
———— ———– ———- ——————- ————-
0            2           2          2016-07-19/19:09:06 0

Now gather the statistics of the table with NO_INVALIDATE=>FALSE which will invalidate the cached cursors, query the table and check V$SQL view again:
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(
NULL,’TBL_INVALIDATION_TEST’,
NO_INVALIDATE => FALSE);
PL/SQL procedure successfully completed.

SQL> SELECT COUNT(1) FROM tbl_invalidation_test;
COUNT(1)
———-
71972

SQL> SELECT child_number, parse_calls, executions, object_status, invalidations
FROM v$sql WHERE sql_id = ‘002swby2jr0qz’;

CHILD_NUMBER PARSE_CALLS EXECUTIONS OBJECT_STATUS INVALIDATIONS
———— ———– ———- ——————- ————-
0            2           2          INVALID_UNAUTH      1

As it is seen from the output, the status of the cursor is INVALID_UNAUTH and the count of invalidations is 1.
To change the default value of NO_VALIDATE parameter, use SET_PREFS procedure as follows:

SQL> EXEC DBMS_STATS.SET_PARAM(‘NO_INVALIDATE’,’FALSE’);
PL/SQL procedure successfully completed.

SQL> SELECT DBMS_STATS.GET_PREFS( ‘NO_INVALIDATE’ ) FROM dual;
DBMS_STATS.GET_PREFS(‘NO_INVALIDATE’)
——–
FALSE
SQL>

Practice:
Perform the following practice in 15 min.

► Flush the shared pool, create a new table based on DBA_OBJECTS and gather table statistics. Query the table twice and get count of executions and invalidations from V$SQL view. Then gather a statistics again and invalidate the cached cursors and query V$SQL view again.

If you want to get more information on this topic, get my book from the following link:
https://www.amazon.com/Oracle-Certified-Master-Exam-Guide/dp/1536800791/ 

Leave a reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>