Search BC Sites

Free Oracle Tips   HTML Text

Free Oracle App Server Tips   HTML Text

Privacy Policy

Redneck

Dress Code

Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

Service Statistics

The next section of the AWRRPTreport is also new in 10g.  It reports on the various services and shows their activity levels. The following listing is an example from the report:

Service Statistics  DB/Inst: SSD/ssd2  Snaps: 3-4
-> ordered by DB Time 

                                            Physical    Logical
Service Name     DB Time (s)   DB CPU (s)      Reads      Reads
---------------- ----------- ------------ ---------- ----------
ssd                    591.9        203.5      5,468  6,041,482
SYS$USERS                0.1          0.1          0         36
SYS$BACKGROUND           0.0          0.0         52     96,198
ssdXDB                   0.0          0.0          0          0
          ----------------------------------------------------- 

Service Wait Class Stats  DB/Inst: SSD/ssd2  Snaps: 3-4
-> Wait Class info for services in the Service Statistics section.
-> Total Waits and Time Waited displayed for the following wait
   classes:  User I/O, Concurrency, Administrative, Network
-> Time Waited (Wt Time) in centisecond (100th of a second) 

Service Name
----------------------------------------------------------------
 User I/O  User I/O  Concurcy Concurcy     Admin   Admin   Network Network
Total Wts   Wt Time Total Wts  Wt Time Total Wts Wt Time Total Wts Wt Time
--------- --------- --------- -------- --------- ------- --------- -------
ssd
     7806       242      1411     4114         0       0     31702    2628
SYS$USERS
        0         0         0        0         0       0        72       0
SYS$BACKGROUND
    49003         1       147       16         0       0         1       0
          -------------------------------------------------------------

This report shows exactly which services spent the most time waiting, and in general, what they waited for. Some times are reported in units of centiseconds while others are in seconds. In this report, the SSD service dominates the time utilized.  This was the service used to run the test cases. The high percentage of reads that are logical reads indicate a nearly fully cached system.

For more details and scripts, see my new book " Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot.