The system wait statistics described in the
stats$waitstat table can be useful if you suspect that your database
is undergoing resource bottlenecks.
By looking at the total time, you can often
determine which one of the wait statistics is causing a bottleneck
within your Oracle database. We will return to the use of the wait
statistics table later on in the chapter where we investigate SGA
tuning.
The most common wait event we will be taking a
look at in our chapter on object tuning is waits on the freelists. One
of the best ways to find out if you've got an object that has improper
storage parameter settings is to take a look at freelist waits. If
your freelist waits are very high, there is a good chance you have
tables that have competing INSERT or UPDATE tasks and these tables do
not have enough freelists defined. This is arguably the most important
section in the report because it shows how long Oracle is waiting for
resources.
SQL> desc
STATS$WAITSTAT;
Name Null? Type
----------------------------------------- --------
-------------------
SNAP_ID NOT NULL NUMBER(6)
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
CLASS NOT NULL VARCHAR2(18)
WAIT_COUNT NUMBER
TIME NUMBER
Here is a sample report on the data from
stats$waitstat, showing various classes and the wait counts and times
for each class.
Yr. Mo Dy
Hr CLASS WAIT_COUNT TIME
------------- -------------------- ---------- ------------
2001-09-21 15 data block 3 0
2001-10-02 15 data block 3 0
2001-10-02 15 undo block 8 0
2001-12-11 18 undo header 19 4
