Interpreting Statspack part-III

Top 5 Events:

♀Wait Events  DB/Inst: TSM/tsm  Snaps: 1-3

-> s - second, cs - centisecond,  ms - millisecond, us - microsecond

-> %Timeouts:  value of 0 indicates value was < .5%.  Value of null is truly 0

-> Only events with Total Wait Time (s) >= .001 are shown

-> ordered by Total Wait Time desc, Waits desc (idle events last)

Here in the above figure the statistics are ordered by the waits in second. Hence it can be deduced that the parameter timed_statistics=true in the database where this report was run.

However in a system that demands tuning you will see that the waits will be much higher and see figures for waits and wait times in hundreds of thousands as mentioned above.

Below is listing of the wait events.

  ♀Wait Event Histogram  DB/Inst: TSM/tsm Snaps: 1-3

Details: -

Db File scattered read: This indicated full table scans. Consider proper indexing.

Db file sequential read: This indicates index reads. Look for inefficient SQL to fix the issue.

Buffer busy wait: This indicates that buffer is used in an exclusive manner. This could be due to multiple reasons but you can start by increasing freelists and then the db_cache_size.

Latch Free: Contention of low lying queuing mechanisms.

Enqueue: Enqueue is a lock that protects a shared resource.

Logfile switch: This wait is due to checkpoints not being completed. Increase the log file size and run your index tablespace in NOLOGGING mode provided you have a script to recreate them. Add database writers if DBWR is the issue.

Log buffer space: The LGWR is not coping up with the load. Increase log buffer size, increase log file size and see if the issue is not because your log files are on the same device as other busy tablespace.

Log File sync: Put log files on a faster disk.

Top SQL: -

Look for SQL which have high buffer gets and high Physical reads. Tune the SQL with high buffer gets first so that space can be freed in the cache to minimize physical reads. You will see SQL ordered by a)buffer gets, b)physical reads, c)executions and d)parse calls and in the mentioned order.

Tuning trouble queries on non tuned system can give almost unbelievable performance gains.

Enjoy:-)