Oracle Database Discussions

I am working on assisting with a system slowness issue for a 9.2.0.5 instance running on an older Sun server with Unix SunOS 5.9. This system supports an OLTP web based application and as a newer DBA I am having a difficult time deciphering the statspack reports I have generated. System slowness is a regular issue here and there doesn't seem to be much of a pattern aside from slowness occurring all day every day. I am hoping someone can point me in the right direction in terms of where to start by putting some excerpts of the statspack report here. I know this isn't everything but hopefully some of these sections can provide some useful insight. Any help is much appreciated. (I apologize in advance for the formatting from the report, I see it isn't easy to read).

STATSPACK report for

DB Name DB Id Instance Inst Num Release Cluster Host

PRD 16349253558 PRD 9.2.0.5.0 NO eng-test38

Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 24194 07-Apr-11 14:32:47 553 68.2
End Snap: 24195 07-Apr-11 14:45:14 534 72.0
Elapsed: 12.45 (mins)

Cache Sizes (end)

Buffer Cache: 2,512M Std Block Size: 8K
Shared Pool Size: 432M Log Buffer: 1,024K

Load Profile
Per Second Per Transaction
--------------- ---------------
Redo size: 17,500.56 4,550.27
Logical reads: 81,395.06 21,163.28
Block changes: 108.83 28.30
Physical reads: 803.74 208.98
Physical writes: 540.84 140.62
User calls: 540.71 140.59
Parses: 556.50 144.69
Hard parses: 17.98 4.67
Sorts: 252.74 65.71
Logons: 0.39 0.10
Executes: 1,107.77 288.03
Transactions: 3.85

% Blocks changed per Read: 0.13 Recursive Call %: 75.14
Rollback per transaction %: 38.60 Rows per Sort: 159.66

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.98 Redo NoWait %: 100.00
Buffer Hit %: 99.27 In-memory Sort %: 99.99
Library Hit %: 99.14 Soft Parse %: 96.77
Execute to Parse %: 49.76 Latch Hit %: 99.87
Parse CPU to Parse Elapsd %:96.99 % Non-Parse CPU: 92.76

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 87.19 95.96
% SQL with executions>1: 31.72 38.13
% Memory for SQL w/exec>1: 30.93 39.29

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 1,377 34.61
db file sequential read 117,391 1,137 28.58
wait for unread message on broadcast channel 745 726 18.25
db file scattered read 55,899 276 6.93
direct path write 8,191 237 5.96
-------------------------------------------------------------
Wait Events for DB: PRD1 Instance: PRD1 Snaps: 24194 -24195
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file sequential read 117,391 0 1,137 10 40.9
wait for unread message on b 745 709 726 975 0.3
db file scattered read 55,899 0 276 5 19.5
direct path write 8,191 0 237 29 2.9
buffer busy waits 9,964 0 81 8 3.5
PL/SQL lock timer 77 76 76 991 0.0
db file parallel write 270 0 24 88 0.1
log file sync 1,473 1 16 11 0.5
log file parallel write 2,337 1,786 15 6 0.8
control file parallel write 240 0 7 29 0.1
latch free 4,192 35 2 1 1.5
direct path read 5,442 0 2 0 1.9
db file parallel read 10 0 1 94 0.0
direct path read (lob) 54 0 1 16 0.0
SQL*Net more data to client 4,933 0 0 0 1.7
direct path write (lob) 21 0 0 9 0.0
SQL*Net break/reset to clien 364 0 0 0 0.1
process startup 3 0 0 28 0.0
control file sequential read 218 0 0 0 0.1
enqueue 1 0 0 7 0.0
library cache pin 2 0 0 2 0.0
LGWR wait for redo copy 10 0 0 0 0.0
SQL*Net message from client 384,581 0 207,385 539 133.9
wakeup time manager 150 150 2,805 18703 0.1
jobq slave wait 727 658 2,105 2895 0.3
pipe get 404 190 726 1798 0.1
queue messages 107 61 715 6679 0.0
SQL*Net more data from clien 384 0 1 2 0.1
SQL*Net message to client 384,560 0 0 0 133.9
-------------------------------------------------------------
Background Wait Events for DB: PRD1 Instance: PRD1 Snaps: 24194 -24195
-> ordered by wait time desc, waits desc (idle events last)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
wait for unread message on b 744 708 725 975 0.3
db file parallel write 270 0 24 88 0.1
log file parallel write 2,341 1,789 15 6 0.8
control file parallel write 240 0 7 29 0.1
db file sequential read 82 0 1 7 0.0
db file scattered read 17 0 0 26 0.0
control file sequential read 132 0 0 0 0.0
latch free 5 0 0 0 0.0
rdbms ipc reply 1 0 0 0 0.0
LGWR wait for redo copy 10 0 0 0 0.0
rdbms ipc message 5,308 2,521 3,451 650 1.8
pmon timer 250 250 725 2900 0.1
smon timer 2 2 600 ###### 0.0

Many thanks,
TJ