RE: 10g RAC using raw devices - curiousity question
Date: Tue, 25 Sep 2012 11:20:45 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E88515BD7B8E_at_NADCWPMSGCMS10.hca.corpad.net>
Dave, thanks for your input.
I think this may be drifting into a philosophical discussion on when and where (and to whom) it is acceptable to leverage hidden parameters for a specific session/operation. :)
Oracle has given plenty of guidance on the parameters I've used (well, maybe not all of them as some I was testing with). I provided the Metalink doc #s beside the parameters to illustrate that I've put a lot of research into this and am not adjusting these parameters on a whim.
I tried getting auto PGA to do what I needed it to do, but it was a no go - there are some parameters that can be adjusted at a SYSTEM level for PGA but very few for a SESSION level (caveat 1: 10g, caveat 2: I may have missed something). Some of the PGA tuneables require a database bounce and affect the entire system.
My approach is to limit the tuning to the problem area (keeping in mind I'm handcuffed at the moment by certain business strategies regarding statistics and automatic memory management i.e. red tape).
Chris
-----Original Message-----
From: Herring Dave - dherri [mailto:Dave.Herring_at_acxiom.com]
Sent: Tuesday, September 25, 2012 11:11 AM
To: Taylor Christopher - Nashville; usn_at_usn-it.de
Cc: oracle-l_at_freelists.org
Subject: RE: 10g RAC using raw devices - curiousity question
Chris,
It sounds the real issue is getting Oracle sessions to use as much memory as possible for sorting before dumping to temp, not really I/O performance with ASM. Correct? If that's the case, I wouldn't mess around with hidden params, as Martin previously recommended. There are a variety of approaches, from figuring out how to get automatic PGA management to go past certain thresholds to manual, the latter of which you found to be beneficial. I know oracle-l has had a number of discussions on this in the past (sorry, don't have any links to them at the moment).
BTW, if you want to check I/O with 10g and ASM I suggest you use asmiostat (from MOS) with OSW. That way you get just ASM path names and their related I/O stats for checking whatever you want. I think you have to adjust the OSW scripts to tie in asmiostat so that it behaves like the other stats collected but it's not very hard.
HTH.
DAVID HERRING
DBA
Acxiom Corporation
EML dave.herring_at_acxiom.com TEL 630.944.4762 MBL 630.430.5988
1501 Opus Pl, Downers Grove, IL 60515, USA WWW.ACXIOM.COM
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Sep 25 2012 - 11:20:45 CDT