RE: 10g RAC using raw devices - curiousity question

From: <Christopher.Taylor2_at_parallon.net>
Date: Wed, 26 Sep 2012 07:08:29 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E88515C32AEB_at_NADCWPMSGCMS10.hca.corpad.net>

The SQL(s) are housed within a Business Objects report object on the business objects server - My first recommendation was to move them to the database in the form of pl/sql packages but that is an audit violation as Business Objects reports can only issue SELECTs. So my next recommendation was to move them into views in the database. So far, no traction on that strategy. SQL Profile is actually slower for the sqls in question - cost is lower but execution time is longer.

Chris

From: pier paolo Bruno [mailto:pbrunoster_at_gmail.com] Sent: Wednesday, September 26, 2012 6:08 AM To: Taylor Christopher - Nashville
Cc: usn_at_usn-it.de; oracle-l_at_freelists.org Subject: Re: 10g RAC using raw devices - curiousity question

can't you try to modify query with hint and manual sqlprofile ?

2012/9/25 <Christopher.Taylor2_at_parallon.net<mailto:Christopher.Taylor2_at_parallon.net>> 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<mailto:usn_at_usn-it.de>] Sent: Tuesday, September 25, 2012 1:29 AM To: Taylor Christopher - Nashville
Cc: oracle-l_at_freelists.org<mailto: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<mailto: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<mailto:usn_at_usn-it.de>]
> Sent: Monday, September 24, 2012 5:07 PM
> To: Taylor Christopher - Nashville
> Cc: oracle-l_at_freelists.org<mailto: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<mailto:Christopher.Taylor2_at_parallon.net> schrieb:
>> alter session set workarea_size_policy=manual; alter session set
>> sort_area_size73741824; alter session set
>> hash_area_size73741824; alter session set
>> "_complex_view_merging"úLSE; --- found on Metalink for a different
>> perf problem & still testing alter session set
>> "_sort_multiblock_read_count"%6; --- set for workarea_size_policy
>> manual alter session set "_hash_multiblock_io_count"%6; --- set for
>> workarea_size_policy manual alter session set
>> "_unnest_subquery"úLSE; --- found on Metalink for a different perf
>> problem & still testing alter session set
>> "_optimizer_use_histograms"úlse; --- Current statistics gathering
>> strategy needs work alter session set
>> "_optimizer_squ_bottomup"úlse;
>> --- Metalink Note:
>> 1118446.1
>> alter session set "_optimizer_sortmerge_join_enabled"úlse;
>> ---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"€000; --- alter session set
>> "_OPTIMIZER_COST_BASED_TRANSFORMATION"=EXHAUSTIVE;
>> ---Metalink Note: 567354.1 -- testing alter session set
>> "_newsort_enabled"úlse; --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



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

Received on Wed Sep 26 2012 - 07:08:29 CDT