Re: RAMSAN Experience
Date: Mon, 21 Sep 2009 09:19:06 -0400
Message-ID: <52a152eb0909210619j7ebc689ds667f7239e187d877_at_mail.gmail.com>
We use this in production for 3 years now.
Reproducing is very easy, run a big query with sorting, before and after the change.
- Sort operations between 1 GiB and 3.5 GiB will be significantly faster as there will be no use of temporary space (watch out for swapping)
- Sort operations bigger than 3.5 GiB will be significantly faster, when you change the disk IO sizes. It will be dependent on your hardware delivering however. NOTE that this ONLY applies if you are using direct IO. If you are using buffered IO it wont matter as much. ASM Always uses direct IO.
Greg, Check out my presentation, it has lots of examples.
As I said, I don't know why Oracle is not changing these defaults. If there is more to it, then neither I, or anyone else has any information as I haven't received any feedback. I've actually spoken with developers from Oracle on the automatic pga management team.
These values are far from optimal, they just relax the limits to the maximum possible. Ideally, Oracle's PGA management should enter the 64 bit world, and allow for work areas larger than 4 GiB. I understand you have parallel query for that, but in some cases, it doesn't work as good. Particularly when the ranger doesn't split the data properly and 90% of the data goes to a single parallel server, regardless of parallelism level.
On Fri, Sep 18, 2009 at 7:52 PM, Greg Rahn <greg_at_structureddata.org> wrote:
> Christo-
>
> Do you have some reproducible test cases with performance metrics that
> demonstrate that these hidden parameters do indeed better performance
> and quantify any gains?
>
> I guess it seems very odd to me that if the default values of these
> hidden parameter are suboptimal then why doesn't Oracle just change
> them to be optimal? (I know this seems to be your point, however mine
> is, there must be more to it)
>
> On Wed, Sep 16, 2009 at 11:18 AM, Christo Kutrovsky
> <kutrovsky.oracle_at_gmail.com> wrote:
>> Short story (assuming 64 bit OS and Oracle):
>>
>> Increase limit to 4 GB (8 GB per process). Note, first parameter is in
>> bytes, second in KiB.
>> *._pga_max_size=8000000000
>> *._smm_max_size=5000000
>>
>> Even when it does spill to disk, increase IO sizes so that it's more efficient:
>> *._smm_auto_max_io_size=2048
>> *._smm_auto_min_io_size=256
>>
>> The IO setting can increase a spilled sort/hash join by a factor of
>> 2-3, but is very SAN Cache/Usage specific.
>>
>> It still puzzles me why Oracle has limited global bound to 1GB...
>
> --
> Regards,
> Greg Rahn
> http://structureddata.org
>
-- Christo Kutrovsky Senior DBA The Pythian Group - www.pythian.com I blog at http://www.pythian.com/blogs/ -- http://www.freelists.org/webpage/oracle-lReceived on Mon Sep 21 2009 - 08:19:06 CDT