RE: 10g RAC using raw devices - curiousity question
Date: Tue, 25 Sep 2012 16:10:53 +0000
Message-ID: <BD475CE0B3EE894DA0CAB36CE2F7DEB401043DD342_at_LITIGMBCRP02.Corp.Acxiom.net>
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 -----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Christopher.Taylor2_at_parallon.net Sent: Tuesday, September 25, 2012 10:20 AM To: usn_at_usn-it.de
Cc: oracle-l_at_freelists.org
Subject: RE: 10g RAC using raw devices - curiousity question
Fair enough. What is the risk potential and how much is it growing? Also how are these specific parameters affecting the risk potential?
Would you agree that research and testing these changes and applying them ONLY for a specific logon session/username limits the risk to the rest of the system?
Would you agree that "some" risk is acceptable to decrease run times when the queries are not modifiable directly and the business requires the processes to run within a specific window of time?
Chris
-----Original Message-----
From: Martin Klier [mailto:usn_at_usn-it.de]
Sent: Tuesday, September 25, 2012 1:29 AM
To: Taylor Christopher - Nashville
Cc: oracle-l_at_freelists.org
Subject: Re: 10g RAC using raw devices - curiousity question
Trouble is, that most of those notes and underscore-parameter-manifested events are made for special purposes, to work around bugs and specific issues. You are exposed to special and not regression-tested source code. With every parameter you are growing the rist exponential...
Do as Oracle says: Use them only under supervision of Oracle Support. You might have issues on the other end of the product without knowing why and where.
And in general, IMHO narrowing down the freedom of the CBO ist in 99% of the cases a problem-maker, not a problem-solver.
Regards
Martin
Christopher.Taylor2_at_parallon.net schrieb:
> What is the concern, and what about it concerns you if I were to move it to production?
>
> Chris
>
> -----Original Message-----
> From: Martin Klier [mailto:usn_at_usn-it.de]
> Sent: Monday, September 24, 2012 5:07 PM
> To: Taylor Christopher - Nashville
> Cc: oracle-l_at_freelists.org
> Subject: Re: 10g RAC using raw devices - curiousity question
>
> Uahhh
> =:-O
>
> For a sandbox, it's a brave approach. Hope this isn't production.
>
> Christopher.Taylor2_at_parallon.net schrieb:
>> alter session set workarea_size_policy=manual; alter session set >> sort_area_size=1073741824; alter session set >> hash_area_size=1073741824; alter session set >> "_complex_view_merging"=FALSE; --- found on Metalink for a different >> perf problem & still testing alter session set >> "_sort_multiblock_read_count"=256; --- set for workarea_size_policy >> manual alter session set "_hash_multiblock_io_count"=256; --- set for >> workarea_size_policy manual alter session set >> "_unnest_subquery"=FALSE; --- found on Metalink for a different perf >> problem & still testing alter session set >> "_optimizer_use_histograms"=false; --- Current statistics gathering >> strategy needs work alter session set >> "_optimizer_squ_bottomup"=false; >> --- Metalink Note: >> 1118446.1 >> alter session set "_optimizer_sortmerge_join_enabled"=false; >> ---Metalink >> Note: 444609.1 >> alter session set "_optimizer_join_sel_sanity_check" = true; --- >> alter session set "_always_semi_join" = off; alter session set >> "_optimizer_max_permutations"=80000; --- alter session set >> "_OPTIMIZER_COST_BASED_TRANSFORMATION"=EXHAUSTIVE; >> ---Metalink Note: 567354.1 -- testing alter session set >> "_newsort_enabled"=false; --Metalink Bug 6817844.8 >> -- testing, zero effect observed
>
> --
> Usn's IT Blog for Linux, Oracle, Asterisk http://www.usn-it.de
>
>
-- Usn's IT Blog for Linux, Oracle, Asterisk http://www.usn-it.de -- http://www.freelists.org/webpage/oracle-l *************************************************************************** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. **************************************************************************** -- http://www.freelists.org/webpage/oracle-lReceived on Tue Sep 25 2012 - 11:10:53 CDT