RE: 10g RAC using raw devices - curiousity question

From: <Christopher.Taylor2_at_parallon.net>
Date: Mon, 24 Sep 2012 14:48:54 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E88515BD7155_at_NADCWPMSGCMS10.hca.corpad.net>

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_at_usn-it.de] Sent: Monday, September 24, 2012 2:21 PM To: Taylor Christopher - Nashville
Cc: oracle-l_at_freelists.org
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_at_parallon.net 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
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
Usn's IT Blog for Linux, Oracle, Asterisk http://www.usn-it.de

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

Received on Mon Sep 24 2012 - 14:48:54 CDT