RE: 10g RAC using raw devices - curiousity question
- From: <Christopher.Taylor2@xxxxxxxxxxxx>
- To: <usn@xxxxxxxxx>
- Date: Mon, 24 Sep 2012 14:48:54 -0500
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_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
Chris
-----Original Message-----
From: Martin Klier [mailto:usn@xxxxxxxxx]
Sent: Monday, September 24, 2012 2:21 PM
To: Taylor Christopher - Nashville
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: 10g RAC using raw devices - curiousity question
Hi Christopher,
anything that improves storage IO power will help, basically. Stuff like
aligning disks etc.
In fact, you are using ASM, true? There are several sources on the web for ASM
tuning, I think you will have more success on this layer.
Christopher.Taylor2@xxxxxxxxxxxx schrieb:
> We have some dss type reports that run on this 10g RAC database using ASM and
> raw devices and I have created a schema logon trigger to set some session
> parameters for the report login.
> I'm curious if there are specific Oracle parameters (hidden or not) that I
> should explore/test/research related to raw device I/O and how to improve it?
>
> Some things bouncing around in my head (in no particular order) are
> buffered/unbuffered IOs, sorting multiblock IOs etc.
>
> I'm not looking for silver bullets and have put a lot of time in on improving
> the query runtimes at the database level (the queries are housed inside a
> Business Objects report on the business objects server and so far I haven't
> been able to get them moved into the database layer).
>
> So if you guys know of any particular IO type parameters that will improve
> raw device IO performance, I'd be interested in hearing them.
>
> Regards,
>
> Chris
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Usn's IT Blog for Linux, Oracle, Asterisk http://www.usn-it.de
--
//www.freelists.org/webpage/oracle-l
Other related posts:
