LGWR process priorities on the Solaris platform

From: Johnson, George <GJohnson_at_GAM.COM>
Date: Wed, 12 Jul 2006 09:10:38 +0100
Message-ID: <0A0E50CC00E3DB41A1B41739B9BD31C601C0F1C5@MSAS-LDN90P.global.gam.com>

We recently suffered a spate of very high redo wait in our production DB, simply appeared to arrive out of nowhere. We tuned up the online log sizes and moved the logs to their own filesystems and managed to alleviate some of the wait but not much, the performance was getting worse and massive amounts of "log file sync" was observed. We started checking with developers that apps hadn't increased commit rates, but nothing unusual. Finally in desperation we rebooted the server, which hadn't been rebooted in about 6 weeks. When it all came back we observed an obvious massive increase in performance, so we let it settle for 3-4 days to take some observations. So far, touch wood, the heavy redo wait has not returned yet. The DB was shutdown twice during the entire 3 week period of the redo wait, so the DB was not up and open for the entire period, but the redo wait continued before and after each restart, which suggests something in the O/S.

After doing a little reading up I came across Steve Adams' note on process priorities with regards the background processes. Opinion seems to be divided on whether it's wise to place the BG procs into real-time and leave user process in shared? The only concern I have is that profiling would need to be introduced into the DB to try to stop the job/schedule J00x BG processes getting carried away.

I am hoping to gather some opinions from list members on the use of real-time priorities, specifically on the Sparc platform, but any info at all is welcome, as this has been a bit foxed.

The other question I have for Solaris admins on the list, would process priorities be remembered by process signature across process restarts? Ie if you shutdown the DB the LGWR process will get a new process ID on each restart, so would the Solaris O/S remember and keep hampering the LGWR process based on what it remembered about it before?

( We have one of those rather pleasant "post-mortem" meetings coming up, so it would be good to go armed with some useful info!)

	Rough server spec outline:
	DB: 10.2 Ent ( 4Gb assigned mem, 1Gb Shrd P)
	OS: Sol 9
	Svr: SPARC 890 ( 8 cpu, 16Gb)

	Kind regards
	George J



****************************************************************************

This message contains confidential information and is intended only for the individual or entity named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. This message is provided for informational purposes and should not be construed as an invitation or offer to buy or sell any securities or related financial instruments.
GAM operates in many jurisdictions and is regulated or licensed in those jurisdictions as required.

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

Received on Wed Jul 12 2006 - 03:10:38 CDT