RE: 10g RAC using raw devices - curiousity question
Date: Tue, 25 Sep 2012 11:47:35 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E88515BD7C4D_at_NADCWPMSGCMS10.hca.corpad.net>
Niall,
Thanks for the input - it is always welcome. That is *exactly* what I *did* do :)
I even created a SCHEMA level login trigger as opposed to a SYSTEM level login trigger - see how thoughtful I am? ;)
So, I take it you would at least investigate confidently the same approach I am taking then?
I know I snipped off all the previous replies so I understand that it got lost/overlooked.
Chris
From: Niall Litchfield [mailto:niall.litchfield_at_gmail.com]
Sent: Tuesday, September 25, 2012 11:44 AM
To: Taylor Christopher - Nashville
Cc: usn_at_usn-it.de; ORACLE-L; Dave.Herring_at_acxiom.com
Subject: RE: 10g RAC using raw devices - curiousity question
I think that if auto pga isn't working properly for a (subset) if sessions then I'd implement manual PGA for those sessions 'alter session... ' in a trigger before I started looking at instance wide modifications via hidden parameters. You may of course already have done this. On Sep 25, 2012 5:23 PM, <Christopher.Taylor2_at_parallon.net<mailto:Christopher.Taylor2_at_parallon.net>> wrote: 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<mailto:Dave.Herring_at_acxiom.com>]
Sent: Tuesday, September 25, 2012 11:11 AM
To: Taylor Christopher - Nashville; usn_at_usn-it.de<mailto:usn_at_usn-it.de>
Cc: oracle-l_at_freelists.org<mailto: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<mailto:dave.herring_at_acxiom.com>
TEL 630.944.4762<tel:630.944.4762>
MBL 630.430.5988<tel:630.430.5988>
1501 Opus Pl, Downers Grove, IL 60515, USA WWW.ACXIOM.COM<http://WWW.ACXIOM.COM>
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Sep 25 2012 - 11:47:35 CDT