pando,
Don't know about connection pooling - will ask. What would be a better alternative?

How can I tell how many "sessions" or users passed thru the system in the 10 mins of the snap shot when in MTS mode?



roadwarriorDBA,

I *think* the 'log sync wait' can be a red herring - if you follow this logic...

I have 91 transactions per second (from the statspack report) - and in the 10 minutes I waited 225,427 cs.

91 commits per second * 60 seconds * 10 minutes = 54600 commits in 10 minutes.

225,427 cs waited / 54,600 = 4.13 cs per commit. Each commit waits, on average 4 hundreths of a second.

I waited 56,757 times - each time 0.04 of a second. Sounds not too bad like that?

Or tell me I'm wrong!

From your stats, you can see that your log sync waits are caused by slow log file parallel writes.


I've basically got the following options (if redo speed is at fault)

1/ I have 6 JBOD drives on the system - I can create 3 pairs of redo drives - each "pair" would be hold one redo set. After a log switch, LGWR would move to the next drive and archiver would use the previous drive. No contention there. But is it fast enough?

2/ I stripe 3 drives and mirror them. Will this be faster that dedicated drives?

3/ I reclaim one of the R5 arrays as redo. (not nice)

4/ I spend many $$$ on solid state REDO drives. BUT NOT before I PROVE redo speed is at the root cause.


BTW - Our ARCH space is a 9 x 72GB RAID5 fibre channel array. We will soon be using a remote STANDBY database - so archives will be shipped over the WAN too.



One way to test this would be to do a statspack report while the system is running a CTAS with a snap before and after. I'll do this later today when no-one is looking


Thanks for your feedback so far guy's (and gal's) It's giving me plenty of idea's.