Wednesday, August 24, 2011

Interpreting Statspack part-V

Additionally Memory Statistics:

The above provides details of the buffer cache hit ratio. It provides the details of different pools created.It also shows statistics related to PGA and enqueue.

Latch Activity: -

Latch Activity DB/Inst: TSM/tsm  Snaps: 1-3
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
  willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0

Latches are locks on memory which are obtained and released very quickly. There are two kinds of latches. Willing to wait latches and not willing to wait latch. _SPIN_COUNT parameter comes into play for willing to wait latches. Any modification to the same should be done after careful monitoring since spinning is CPU intensive process.
The not willing to wait latches will populate the Pct No Wait Miss column.If this value is greater than .99 investigations is necessary.

Dictionary and Library Cache Statistics: -

Proactively monitor your Dictionary stats.

SGA Memory Statistics:

This section lists the output of V$SGA and a listing of memory changes during the snapshot interval.

It also lists SGA breakdown and Difference in the memory allocation during the snap interval.

List of Non Default Initialization Parameters:

This is the last section of the Statspack report. These are the non default initialization parameter configured in the database. It also show if the parameter was changed during the snap interval.

This is all over now about 'Interpreting statspack series'.Hope this helps.:-)