Re: RAMSAN Experience
- From: Christo Kutrovsky <kutrovsky.oracle@xxxxxxxxx>
- To: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- Date: Mon, 21 Sep 2009 09:19:06 -0400
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@xxxxxxxxxxxxxxxxxx> 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@xxxxxxxxx> 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/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
