Tuesday, February 14, 2012

gathering system stats

The workload needs to be very exhaustive to gather the correct set of values - a good mix of sessions some doing multi-block reads and others sequential reads. I tried to capture these stats using three different workloads, and the stats had a stark variation.
Workload1: created two indexes on a fact table.
Workload2: Executed 8 BI queries in parallel.
Workload3: Executed a bi query making it fairly complex by self joining the fact table.

These are the results:

Workload1 Workload2 Workload3
CPUSPEED 948 948 916
CPUSPEEDNW 948 948 948
IOTFRSPEED 91662 91662 91662
MAXTHR 311120896 129651712 360448
3 6
MREADTIM 16.741 2419.531 35318.899
SLAVETHR 5065728 4799488 4799488
547.395 331.686

11.2 has a bug which miscalculates MREAD and SREAD time inflating them by a factor of 1000:
Bug 9842771 - Wrong SREADTIM and MREADTIM statistics in AUX_STATS$ [ID 9842771.8]

best is to set these stats manually:

Both these link are mentioned in Jonathan blog post - http://jonathanlewis.wordpress.com/category/oracle/statistics/system-stats/.

Further to this, as far as I understand the absolute values of these two attributes MREADTIM and SREADTIM is something that is inconsequential. Its the ratio of these two that drives optimizer to choose between Full table scan or Index scans.