RE: 10G and UFS - long write times

All this flipping of switches in hope that maybe you hit the jackpot - the root cause of problem - is desperate.

It takes a lot of time and effort and it might get you there.  

Finding the root cause with appropriate tools is not desperate.

It takes lot of time and effort and WILL get you there.  

Every step you do, takes you closer to root cause and you learn. Flipping switches does take you to random distances from solution and you don't learn.  

So what you do when response time in one system is slower than in another? You break down the timings to find out WHERE the extra time is spent.

This means, v$session_event, 10046 trace.  

You found most of the extra time is spent on redo logging related issues? Then you find out WHY more time is spent on redo logging:

1. Is the redo size much bigger? - v$sesstat
2. If not, is it written to disk with much larger number of IO requests? - v$session_event for LGWR
3. If not, do those write requests take more time? - v$session_event for LGWR, truss -c -p <lgwr spid>
4. If not, is there a LGWR scheduling issue ( enable timed_os_statistics (microstate accounting on solaris!), check for "Wait-cpu (latency) time" for LGWR)
5. Your problem will likely rank into one of the above categories.

When you've identified the category, you'll take one step deeper again, using appropriate tools. And if you don't know what tools to use, then google or ask around - you'll learn. Next time your troubleshooting artillery is more powerful again. With flipping switches it's not. You will just have even longer list of switches which to flip - which means your desperate troubleshooting will take even more time and is even more frustrating.  

Hopefully it helps J

Tanel.  

Btw, there's a bug in 10g with excessive redo generation from single row processing PL/SQL loops - which didn't exist in 9i - try to turn off in memory undo in your test env - set _in_memory_undo = false , bounce the instance and try again.  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hallas, John, Tech Dev
Sent: 08 June 2006 22:45
To: rshamsud_at_jcpenney.com; ganstadba_at_hotmail.com Cc: oracle-l_at_freelists.org
Subject: RE: 10G and UFS - long write times  

All valid points, however in what is a complicated scenario for us I am trying to keep it as simple as possible

The test scenario I used was developed as a direct response to the realisation that we were having problems wit transaction with many writes.

This was borne out when a Windows PC was 20 times faster than a Sun V440 server.  

I will take the comments on board.  

The latest information we can see is that mounting a UFS disk with the directio and logging option seems to resolve the problem and improve performance OF THAT TEST by a very considerable factor. Previously, logging was the only mount option used (other than defaults), however I am not an Unix SA so I do stand to be corrected on that point.

It does appear that 10G , for whatever reason, seems to be affected much more FOR THIS TEST AND OTHER WRITES than 9i was.  

John  

--
http://www.freelists.org/webpage/oracle-l