cancel
Showing results for 
Search instead for 
Did you mean: 

Tracking Oracle table/index statistics changes made by users

0 Kudos
185

We've implemented the SAP-delivered Oracle statistics from Note 1020260.  However, when diagnosing a performance issue, we sometimes find that these statistics have been changed, and it turns out that's the cause of the issue.  The tables with the delivered stats have the stats locked, of course, so someone must me deliberately unlocking the stats and changing them.  We want to find who's making unauthorized statistics changes and get it stopped.

The Oracle system tables for tracking statistics changes tell you when changes were made, but not who made them.  My next thought was to check in the Oracle Audit functionality, but the documentation I've found on it is sketchy, not going down to the level of detail I need.  Is there a way to tell Oracle's auditing function to track when table or index statistics are changed, on what objects, and by whom?  If so, where do I go to find such data?

Thanks very much.

                                         Gordon

View Entire Topic
stefan_koehler
Active Contributor
0 Kudos

Hi Gordon,

>  The tables with the delivered stats have the stats locked, of course, so someone must me deliberately unlocking the stats and changing them

Not necessarily. You are able to force statistic collection with PL/SQL package DBMS_STATS even if they are locked.

Documentation: http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_stats.htm#i1036461

force  Gather statistics of table even if it is locked

The PL/SQL calls are controlled by brconnect parameters, which can force such behavior.

Documentation: http://help.sap.com/saphelp_nw04s/helpdata/DE/cb/f1e33a5bd8e934e10000000a114084/content.htm

> Is there a way to tell Oracle's auditing function to track when table or index statistics are changed, on what objects, and by whom?

You can audit PL/SQL calls for example (so you can track the call and the caller).

Documentation: http://docs.oracle.com/cd/E11882_01/network.112/e16543/auditing.htm#DBSEG408

Regards

Stefan