Tracking Oracle table/index statistics changes made by users

[#1]

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

[stefan_koehler]

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

[JimSpath]

Gordon:

  Jürgen Kirschner says:

This is most likely because of bug #14255600 (which I logged).
 
 The first fix we got accidentally recollected statistics during index rebuild (even when stats were locked).
 
 SAP parameter note contained '14255600:ON'  then when we detected the problem  '14255600:OFF' and now again '14255600:ON'.
 
 Depending on the SBP which the customer is using, he [may] need to set "_fix_control: '14255600:ON' accordingly.

Jim