RE: 10g RAC using raw devices - curiousity question
- From: Herring Dave - dherri <Dave.Herring@xxxxxxxxxx>
- To: "Christopher.Taylor2@xxxxxxxxxxxx" <Christopher.Taylor2@xxxxxxxxxxxx>, "usn@xxxxxxxxx" <usn@xxxxxxxxx>
- Date: Tue, 25 Sep 2012 16:10:53 +0000
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@xxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Christopher.Taylor2@xxxxxxxxxxxx
Sent: Tuesday, September 25, 2012 10:20 AM
To: usn@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx]
Sent: Tuesday, September 25, 2012 1:29 AM
To: Taylor Christopher - Nashville
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxx 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@xxxxxxxxx]
> Sent: Monday, September 24, 2012 5:07 PM
> To: Taylor Christopher - Nashville
> Cc: oracle-l@xxxxxxxxxxxxx
> 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@xxxxxxxxxxxx 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
--
//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.
****************************************************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts: