Gather Statistics on a Specific Table Without Invalidating Cursors

1. Documentación -> Oracle Database 11g Release 2 (11.2)
-> Masters Book List -> PL / SQL Packages and Types Reference -> 141 DBMS_STATS
2. This objective is very specific , so have attached documentation page that explains all that
when we collect statistics for a table ( GATHER_TABLE_STATS method DBMS_STATS package).
The GATHER_TABLE_STATS procedimento has a parameter to determine whether or not we want to
override the cursor is in memory ( NO_INVALIDATE ) . When we decided to invalidate a cursor ,
if you re- run, you must obtain a new execution plan may differ from the above depending on
the statistics you just collected .
By default, the parameter is set NO_INVALIDATE of DBMS_STATS.AUTO_INVALIDATE ,
causing Oracle decide whether to invalidate cursors or not. If we want to change
this behavior we have two ways of doing this . The easiest way is to call the procedure
indicating GATHER_TABLE_STATS NO_INVALIDATE = TRUE. The second way is to change the
preferences of the table for the cursor associated with it is not overridden
- Generate a cursor as an example for our tests
- I use a fictitious Hint TEST_CURSOR to locate the cursor easily
SELECT / * TEST_CURSOR * / COUNT ( *) FROM SH.CUSTOMERS ;
- Obtain the status of the cursor
- · We found that the cursor has not been overrides and was run only once
- · The current state of the cursor is VALID
SELECT SQL_ID , Executions , INVALIDATIONS , OBJECT_STATUS
FROM V $ SQL WHERE SQL_TEXT LIKE ' SELECT / * TEST_CURSOR % ' ;
- Obtain table statistics without invalidating the cursor
EXEC DBMS_STATS.GATHER_TABLE_STATS ( ' SH ' , 'CUSTOMERS' , no_invalidate = > TRUE ) ;

- We check that has not invalidated the cursor
SELECT SQL_ID , Executions , INVALIDATIONS , OBJECT_STATUS
FROM V $ SQL WHERE SQL_TEXT LIKE ' SELECT / * TEST_CURSOR % ' ;
We collect statistics again , this time invalidating the mouse cursor
EXEC DBMS_STATS.GATHER_TABLE_STATS  ( ' SH ' , 'CUSTOMERS' , no_invalidate = > FALSE ) ;

- We note that this time the cursor has been invalidated
- · OBJECT_STATUS shows the value of " INVALID_UNAUTH "
EXEC DBMS_STATS.GATHER_TABLE_STATS ( ' SH ' , 'CUSTOMERS' , no_invalidate = > FALSE ) ;
- If the query again launched a new valid cursor is generated
SELECT / * TEST_CURSOR * / COUNT ( *) FROM SH.CUSTOMERS ;

- OBJECT_STATUS re displays the string " VALID "
SELECT SQL_ID , Executions , INVALIDATIONS , OBJECT_STATUS
FROM V $ SQL WHERE SQL_TEXT LIKE ' SELECT / * TEST_CURSOR % ' ;
- If nothing , Oracle invalidates cursors decice whether or not
EXEC DBMS_STATS.GATHER_TABLE_STATS ( ' SH ' , 'CUSTOMERS' ) ;

- In the example we are not doing our cursor is invalidated
SELECT SQL_ID , Executions , INVALIDATIONS , OBJECT_STATUS
FROM V $ SQL WHERE SQL_TEXT LIKE ' SELECT / * TEST_CURSOR % ' ;
3. The second option we have for the cursor in a table, a diagram or even the entire DB is not invalidated ,
it is to set the default attributes of the table , schema, or DB .
- Continuing with the example above, we modify the attributes of table SH.CUSTOMERS
EXEC DBMS_STATS.SET_TABLE_PREFS ( ' SH ' , 'CUSTOMERS' , ' NO_INVALIDATE ', ' TRUE ');

- We can see the default attributes of a table view DBA_TAB_STAT_PREFS
SELECT * FROM DBA_TAB_STAT_PREFS WHERE TABLE_NAME = 'CUSTOMERS ' AND OWNER = ' SH ' ;
- Eliminated attribute settings NO_INVALIDATE
EXEC DBMS_STATS.DELETE_TABLE_PREFS ( ' SH ' , 'CUSTOMERS' , ' NO_INVALIDATE ');

- We can set the attribute for all objects in a schema
- What the procedure is modificiar the attribute table by table NO_INVALIDATE
EXEC DBMS_STATS.SET_SCHEMA_PREFS ( ' SH ', ' NO_INVALIDATE ', ' TRUE ');

- We delete the configuration
EXEC DBMS_STATS.DELETE_SCHEMA_PREFS ( ' SH ', ' NO_INVALIDATE ');
 4. Spend statistics with the DBMS_STATS package is quite simple. We can
    also do with Enterprise Manager although it is not usual.
On the home page of EM ->
-> Click Databases - >
-> Click " OCM " ->
-> Click " Server" ->
-> Click "Manage Optimizer Statistics" ->
-> Click "Gather Optimizer Statistics" ->
-> Select " Tables" ->
-> Selecccionamos "Customize Options" ->
-> Click " Next" ->
-> Click " Add" ->
-> Schema = "HR " ->
-> Object = "employees" ->
-> Click " Search" ->
-> Select " hr.employees " ->
-> Click " OK" ->
-> Click " Next" ->
- > Cursor Invalidation = " None" (this is where we specify that cursors are not invalidate ->
-> Click " Next" ->
-> Click " Next" ->
-> Click " Submit"
Let's see if they have passed the correct statistics.

Click "Object Statistics" ->
-> Schema = "HR " ->
-> Object = "employees" ->
-> Click "Go" ->
-> We check that the current date and time in the recent past we have statistics
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s