This is not an instance tuning issue. You have only waited 38 seconds for the latch free waits. I suggest that you investigate the CPU usage and I/O. Maybe the delete does repeated full table scans? Maybe there is a freelist management problem? I suggest the you run the process again with sql_trace enabled and use tkprof to look for the problem SQL.
We are in the process of upgrading an 815 database to 816 (AIX 4.3) and I came across this problem during the test phase on the new server. We have a process which inserts about 30-40,000 rows (100m) of data into a table and we see to it that a commit is done after insert of every 10,000 rows (as you must have guessed we do use a cursor). If the process fails midway and we restart it again the process deletes all rows which have been inserted incompletely for the day and re-inserts. We find that when this is done the delete and insert process hangs for a few hours. The output given below was taken a about an hour after the process was restarted (the total process used to take only about 30 mins earlier).
From the output given below (I started the database afresh, pinned all packages and started of just this process and of course these results were taken after the rerun) I can see contention for the shared pool (I never thought this would be a problem in 816), problems possibly because my logfiles are not big enough (I have increased them to about 200m in size but still they switch about 3 -4 times during peak activity). Apart from this I also notice very high contention for the library cache load lock and for the cache buffer chain latch. Interestingly I have increased the SGA size by more than 2 times for the new database (it was a measly 250m for a 60G database earlier and now is about 700m) and the logfile size has been increased by about 5 times. I'd like to note that I have set the log bugger size to 160k and that the sync cost ratio(LGWR_STATS) crosses once this process has been executed, the average log write size goes down to about 1/4th the background size. The new machine has 4 cpus as opposed to 2 on the old one.
SQL> @resource_waits.sql
EVENT TIME_WAITED AVERAGE_WAIT
---------------------------------------- ----------- ------------
latch free 3864 47.7037037
buffer busy waits 0 0
log buffer space 355 1.3099631
log file switch completion 117 19.5
library cache load lock 2 .666666667
SQL> @latch_sleeps.sql
NAME IMPACT
SLEEP_RATE HOLDING LEVEL#
---------------------------------------------------------------- ---------- ----
------ ---------- ----------
shared pool 540.8
0.05% 12 7
library cache 20.25
0.00% 28 5
cache buffers chains 19
0.00% 88 1
Checkpoint queue latch 1
0.00% 46 7
SQL> @shared_pool_free_lists.sql
BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZE BIGGEST
---------- ---------- ----------- ------------ ----------
1 2260 113 20 20
2 2112 88 24 24
3 588 21 28 28
4 192 6 32 32
5 3636 101 36 36
6 2800 70 40 40
7 1496 34 44 44
8 1008 21 48 48
9 520 10 52 52
10 2296 41 56 56
11 1380 23 60 60
12 1024 16 64 64
13 2176 32 68 68
14 2448 34 72 72
15 684 9 76 76
16 400 5 80 80
17 2184 26 84 84
18 1232 14 88 88
19 2576 28 92 92
199 872 1 872 872
248 3956 1 3956 3956
35447708 5850 6059 9976092
22 rows selected.
SQL> @lgwr_stats
Average Log Background
Write Size Write Theshold
-------------- --------------
33280 54784
Sync Cost Ratio
---------------
2.10