Oracle Statistics - Best Practice?

[richard_howard]

We run stats with brconnect weekly:

brconnect -u / -c -f stats -t all

I'm trying to understand how some of our stats are old or stale.  Where's my gap?  We are running Oracle 11g and have Table Monitoring set on every table.  My user_tab_modifications is tracking changes in just over 3,000 tables.  I believe that when those entries surpass 50% changed, then they will be flagged for the above brconnect to update their stats.  Correct?

Plus, we have our DBSTATC entries.  A lot of those entries were last analyzed some 10 years ago.  Does the above brconnect consider DBSTATC at all?  Or do we need to regularly run the following, as well?

brconnect -u / -c -f stats -t dbstatc_tab

I've got tables that are flagged as stale, so something doesn't seem to be quite right in our best practice.

SQL> select count(*) from dba_tab_statistics
  2  where owner = 'SAPR3' and stale_stats = 'YES';

  COUNT(*)
----------
      1681

I realize that stats last analyzed some ten years ago does not necessarily mean they are no longer good but I am curious if the weekly stats collection we are doing is sufficient.  Any best practices for me to consider?  Is there some kind of onetime scan I should do to check the health of all stats?

[stefan_koehler · Accepted solution]

Hi Richard,

> We are running Oracle 11g and have Table Monitoring set on every table.

Table monitoring attribute is not necessary anymore or better said it is deprecated due to the fact that these metrics are controlled by STATISTICS_LEVEL nowadays. Table monitoring attribute is valid for Oracle versions lower than 10g.

> I believe that when those entries surpass 50% changed, then they will be flagged for the above brconnect to update their stats.  Correct?

Correct, if BR*Tools parameter stats_change_threshold is set to its default. Brconnect reads the modifications (number of inserts, deletes and updates) from DBA_TAB_MODIFICATIONS and compares the sum of these changes to the total number of rows. It gathers statistics, if the amount of changes is larger than stats_change_threshold.

> Does the above brconnect consider DBSTATC at all?

Yes, it does.

> I've got tables that are flagged as stale, so something doesn't seem to be quite right in our best practice.

The column STALE_STATS in view DBA_TAB_STATISTICS is calculated differently. This flag is used by the Oracle standard DBMS_STATS implementation which is not considered by SAP - for more details check the Oracle documentation "13.3.1.5 Determining Stale Statistics".

The GATHER_DATABASE_STATS or GATHER_SCHEMA_STATS procedures gather new statistics for tables with stale statistics when the OPTIONS parameter is set to GATHER STALE or GATHER AUTO. If a monitored table has been modified more than 10%, then these statistics are considered stale and gathered again.

STALE_PERCENT - Determines the percentage of rows in a table that have to change before the statistics on that table are deemed stale and should be regathered. The valid domain for stale_percent is non-negative numbers.The default value is 10%. Note that if you set stale_percent to zero the AUTO STATS gathering job will gather statistics for this table every time a row in the table is modified.

SAP has its own automatism (like described with brconnect and stats_change_threshold) to identify stale statistics and how to collect statistics (percentage, histograms, etc.) and does not use / rely on the corresponding Oracle default mechanism.

> Any best practices for me to consider?  Is there some kind of onetime scan I should do to check the health of all stats?

No performance issue? No additional and unnecessary load on the system (e.g. dynamic sampling)? No brconnect runtime issue? Then you don't need to think about the brconnect implementation or special settings. Sometimes you need to tweak it (e.g. histograms, sample sizes, etc.), but then you have some specific issue that needs to be solved.

Regards

Stefan

[richard_howard · Accepted solution]

Thanks Stefan.  That's exactly the information I needed.  And good to know the 11g change to STATISTICS_LEVEL.

We had some performance issues last Saturday that resolved on Sunday when stats were collected on some really large tables, like EDIDC.  I was just double-checking that we didn't have any gaps and now I think we are good.

[richard_howard]

I've answered one of my own questions by reviewing the Internal Rules for Update Statistics.  I am correctly gathering statistics as recommended.  BRCONNECT first checks DBSTATC then checks DBA_TAB_MODIFICATIONS (Oracle Table Monitoring).  So there should not be a need to run with -t dbstatc_tab parameter.

That leaves me with the core issue.  If my brconnect is doing it all, then why would I have 1,681 tables that are flagged as Stale?

[former_member188883]

Hi Richard,

Please refer SAP help on using brconnect to update stats.

http://help.sap.com/saphelp_nwpi71/helpdata/en/47/049e01e9e467f9e10000000a1553f7/content.htm

Hope this helps.

Regards,

Deepak Kori