Oracle Database Discussions

Hi All,
I am working with a bank.
Our PROD database gets hang everytime and we have restart the database everytime whenever it gets hung.

Whenever we run EOD, It takes 18 hrs to complete.

Database version: 9.2.0.4

I have pasted statspack report below.

Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 105 07-Jun-11 14:19:01 268 16.7
End Snap: 106 07-Jun-11 14:22:56 290 17.5
Elapsed: 3.92 (mins)

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 3,072M Std Block Size: 8K
Shared Pool Size: 400M Log Buffer: 5,120K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 113,840.43 11,452.27
Logical reads: 13,517.63 1,359.86
Block changes: 734.60 73.90
Physical reads: 4,553.60 458.09
Physical writes: 22.31 2.24
User calls: 137.71 13.85
Parses: 176.87 17.79
Hard parses: 5.42 0.54
Sorts: 33.91 3.41
Logons: 1.93 0.19
Executes: 395.18 39.75
Transactions: 9.94

% Blocks changed per Read: 5.43 Recursive Call %: 89.13
Rollback per transaction %: 72.82 Rows per Sort: 19.67

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.50 Redo NoWait %: 100.00
Buffer Hit %: 66.31 In-memory Sort %: 100.00
Library Hit %: 98.48 Soft Parse %: 96.94
Execute to Parse %: 55.24 Latch Hit %: 99.03
Parse CPU to Parse Elapsd %: 82.12 % Non-Parse CPU: 95.73

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 94.76 95.87
% SQL with executions>1: 49.23 50.37
% Memory for SQL w/exec>1: 55.86 57.57

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file scattered read 105,673 610 37.94
db file sequential read 64,554 604 37.55
SQL*Net message from dblink 822 115 7.15
CPU time 114 7.09
buffer busy waits 15,554 78 4.83
-------------------------------------------------------------
Wait Events for DB: FCRLIVE Instance: fcrlive Snaps: 105 -106
-> 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 scattered read 105,673 0 610 6 45.2
db file sequential read 64,554 0 604 9 27.6
SQL*Net message from dblink 822 0 115 140 0.4
buffer busy waits 15,554 0 78 5 6.7
db file parallel write 310 0 44 143 0.1
log file sync 1,070 3 39 37 0.5
log file parallel write 1,052 1,052 2 2 0.5
latch free 728 567 1 1 0.3
control file parallel write 76 0 1 8 0.0
direct path read (lob) 14 0 0 25 0.0
SQL*Net more data to client 12,222 0 0 0 5.2
db file parallel read 1 0 0 8 0.0
SQL*Net break/reset to clien 44 0 0 0 0.0
control file sequential read 112 0 0 0 0.0
SQL*Net message to dblink 822 0 0 0 0.4
SQL*Net more data from dblin 71 0 0 0 0.0
undo segment extension 36 35 0 0 0.0
SQL*Net message from client 25,539 0 54,417 2131 10.9
wakeup time manager 8 8 240 30000 0.0
SQL*Net more data from clien 1,602 0 0 0 0.7
SQL*Net message to client 25,569 0 0 0 10.9
-------------------------------------------------------------
Background Wait Events for DB: FCRLIVE Instance: fcrlive Snaps: 105 -106
-> ordered by wait time desc, waits desc (idle events last)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file parallel write 310 0 44 143 0.1
log file parallel write 1,048 1,048 2 2 0.4
control file parallel write 75 0 1 8 0.0
db file sequential read 38 0 0 2 0.0
latch free 3 0 0 1 0.0
control file sequential read 52 0 0 0 0.0
rdbms ipc reply 3 0 0 0 0.0
db file scattered read 1 0 0 0 0.0
buffer busy waits 1 0 0 0 0.0
rdbms ipc message 5,131 2,539 1,856 362 2.2
smon timer 2 1 463 ###### 0.0
pmon timer 86 86 226 2623 0.0
-------------------------------------------------------------

Please help me on this.

Thanks and Regards,
Jatin