RE: 10g RAC using raw devices - curiousity question

From: <Christopher.Taylor2_at_parallon.net>
Date: Mon, 24 Sep 2012 15:02:59 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E88515BD71CC_at_NADCWPMSGCMS10.hca.corpad.net>

(resend with clean formatting)

I guess I was more asking about from within the database when using ASM with RAW devices.

(Maybe ASM doesn't matter really) I guess I'm looking for tidbits about 10g on RAW devices (which in this case are managed by ASM) - any database parameters that would improve lun access that I should investigate?

One thing I'm seeing on a couple of the queries is that they spill over to the temp sorting area instead of in memory sorting and PGA is 5 GB.

I disabled automatic workarea for the SESSION logins via trigger and setup some parameters for these individual sessions and that has improved things greatly.

I was trying to determine if I could squeeze some more throughput out between the database and the IO layer (specifically, how the database is sending/receiving IOs to the storage and which optimizer parameters affect some of that). I've found several that have helped and I guess part of me was wondering what else could be tweaked for these reporting sessions.

Here's what I have tweaked so far (schema level logon trigger):

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

Chris

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

Received on Mon Sep 24 2012 - 15:02:59 CDT