RE: 10g RAC using raw devices - curiousity question
- From: <Christopher.Taylor2@xxxxxxxxxxxx>
- To: <usn@xxxxxxxxx>
- Date: Mon, 24 Sep 2012 15:02:59 -0500
(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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
