Interpreting Statspack part-IV

Instance Activity Statistics:

Following are some important statistics:

Consistent gets: Number of blocks accessed from buffer cache for queries.

Db block gets: Blocks accessed from buffer cache for DML.

Physical reads: Data blocks read from the disk from DML and select statements.

Note: Buffer Hit Ratio= (consistent gets + db block gets) – physical reads / logical reads

Dirty buffers inspected: If this value is high then it is indicated that the DBWR is not being able to keep up with the load. You could be benefited by increasing the number of DBWR processes or by faster disks or spreading IO across disks.

Enqueue Time outs: It reflects times when lock was requested and was not available. Investigate locking if this statistics is greater than zero.

Free buffers inspected: The buffers that were skipped because they were dirty, pinned or busy. Large number could indicate need to increase buffer_cache_size.

Sorts Disk: This shows the times when the sorting was done in the temporary segment. Increase pga_aggregate_target or sort_area_size depending on your relevant parameters.

Table fetch continued row: This indicates that a chained row was fetched. Chained rows can be very detrimental to database performance.

Table scans (Long/short tables): Scan on long tables is not encouraged while it is acceptable with short tables. These tables are with NO CACHE clause.

Tablespace IO Stats: -

The above indicates the I/O break down per tablespace and data files.If one particular data file is getting high IO consider stripping it with RAID. Avoid RAID 5.

Enjoy:-)