Operating System Statistics
The stress on the Oracle server is important, and this section
explores the main external resources including I/O, CPU, memory, and
network usage. This information helps the DBA view performance in a
more complex way, thus providing more details about possible system
bottlenecks.
In order to isolate a performance bottleneck at the database level,
the information from the next section may be used.
Service Statistics
The service statistics section gives information about how
particular services configured in the database are operating. A
sample of the AWR Service Report may look like the following:
Service Statistics DB/Inst: LSQ/lsq
Snaps: 1355-1356
-> ordered by DB Time
-> us - microsecond - 1000000th of a
second
Physical Logical
Service Name DB Time (s) DB CPU
(s) Reads Reads
-------------- ------------
------------ ---------- ----------
SYS$USERS 3,715.8
67.7 191,654 525,164
LSQ 3,572.4
83.1 187,653 544,836
LSQXDB 0.0
0.0 0 0
SYS$BACKGROUND 0.0
0.0 1,927 70,537
----------------------------------------
Service Wait Class Stats DB/Inst: LSQ/lsq
Snaps: 1355-1356
-> 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
--------- --------- -- -- ---------
--------- --------- ---------
SYS$USERS
36370 360380 73 66
0 0 1313 4
LSQ
35514 346409 85 95
0 0 8057 7
SYS$BACKGROUND
1226 29322 4 4
0 0 0 0
-----------------------------------------------
If particular production databases are configured for different
database services, the above report section allows the DBA to
quickly isolate which application places the most overhead on the
system. Furthermore, the report indicates where the application
waits most of the time. The report shows that that applications that
use the SYS$USERS service is spending most of the time performing
I/O operations.
It is necessary to notice that this SYS$USERS service is a default
service for all applications. To take advantage of isolating
applications performance, some DBAs will configure separate services
for each kind of production application.
The DBA is now ready to find which particular SQL statements may
cause that stress.
SEE CODE DEPOT FOR FULL SCRIPTS
