How to Collect Statistics

May 4, 2011

In a previous article I shared a couple of the slides that I put together for an Oracle Database presentation for a specific ERP group – a presentation that as designed was far too long to fit into the permitted time slot.  I thought that in today’s blog article I would share a couple of “light-weight” slides from the presentation on the topic of statistics collection.

The notes that go along with the above slide are as follows:

Starting with Oracle Database 10.1 the query optimizer uses system statistics by default.  These statistics tell the optimizer characteristics about the server’s performance.  If you do not collect system statistics, Oracle will automatically use standardized system statistics, which are called NOWORKLOAD statistics.  When your server is under a typical to heavy load, you should gather the system statistics, which may be accomplished using a SQL*Plus command similar to the command at the top of this slide – this command will collect the statistics for a 60 minute time period and then set the statistic values. You can check the current system statistics using the SQL statement at the left.  If you see statistics like those at the right, that means your database is still using NOWORKLOAD statistics.

There is a bug in Oracle Database 11.2.0.1 and 11.2.0.2 related to the calculation of the SREADTIM – the average single-block read time expressed in milliseconds, and the MREADTIM – the average multi-block read time expressed in milliseconds.

—

The notes that go along with the above slide are as follows:

This slide shows an example of the statistics collection bug in Oracle Database 11.2.0.1 and Oracle Database 11.2.0.2.  Notice the large values for SREADTIM and MREADTIM.  These values are typically in the range of 4 to 12 milliseconds, and would normally appear as one or two digit values.  Watch out for cases where the SREADTIM value is greater than the MREADTIM value – that is almost certainly an error in the statistics collecting process and could be due to SAN read-ahead caching.

The calculated value of 78 for the MBRC, which is the average number of blocks read from disk during multi-block reads, might be a little high.  A high value for this parameter could cause the optimizer to use full table scans a little too frequently, and could lead to excessive CPU consumption problems – an example of this problem appears when Visual no longer uses the index on the PART_ID column when querying the INVENTORY_TRANS table when the PART_ID column is specified in the WHERE clause of a SQL statement.

—

The notes that go along with the above slide are as follows:

If you find that the System statistics were collected incorrectly, or if you need to set the system statistics of a test server to match the collected statistics of the production server, you can manually set the statistic values as shown at the top of this slide.

—

The notes that go along with the above slide are as follows:

It is important to make certain that the fixed object statistics are collected.  If the statistics are not collected, there is a strong chance that some queries of Oracle Database views will be slow, or even result in the crashing of the user’s session – see the link at the bottom of this slide for an example that shows what might happen if these statistics are not collected.  You can verify that the statistics were collected by executing the SQL statement at the left – if the SQL statement returns no rows, then the statistics have not been collected.

—

The notes that go along with the above slide are as follows:

Once you have collected the fixed object statistics, the query at the left will return output like you see on the right side of this slide.

—

The notes that go along with the above slide are as follows:

You should collect statistics weekly for the objects in the SYSADM schema (where the ERP data is located), typically when the database experiences little activity.  You can collect those statistics using the SQL*Plus command shown at the top of this slide.  Starting with Oracle Database 10.1 it is also a good idea to collect statistics for the objects in the SYS schema, and that can be done using the second command. 

Starting with Oracle Database 10.1 a stale statistics collecting process collects missing and out of date statistics for objects, typically starting around 10PM.  Even though the statistics collection is now partially automated in recent Oracle Database releases, it is still a good practice to collect statistics weekly.  You can verify that statistics were recently collected using the SQL statements at the bottom of this slide.

—

The notes that go along with the above slide are as follows:

I have been mentioning this warning for the last couple of years in the ERP mailing list – do not use the Update Database Statistics menu item in Visual to collect statistics.  Even in Visual 7.0, this menu command issues ANALYZE commands to collect the statistics, rather than the DBMS_STATS package which became the correct approach when Oracle 8i was introduced a decade ago.  The ANALYZE command does not collect all of the statistics needed by the Oracle query optimizer, and collects some statistics that potentially cause problems for the query optimizer.  You can execute the SQL statement shown on this slide to see if any tables in the SYSADM schema contain remnants from ANALYZE that need to be fixed.  If the query returns any rows, execute the SQL statements that are returned.

—–

Any comments about the above?  How would you do things differently?