Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: pga_aggregate_target and a memory leak

Re: pga_aggregate_target and a memory leak

From: Mladen Gogala <mladen_at_wangtrading.com>
Date: Fri, 23 Jan 2004 06:09:25 -0800
Message-ID: <F001.005DDFA9.20040123060925@fatcity.com>


I read the paper about the adaptive memory and how it gets wasted, but with 10G SGA you can afford to be a bit wasteful. I would set workarea_size_policy to manual and then set sort_area_size to 32M and hash area size to 128M. With the memory sizes you mentioned, there shouldn't be any problems. Anything"adaptive", on the other hand, is an overhead. That overhead is implemented in the oracle server processes (ora_s000...) and any bug has a great potential to waste more then a little CPU. Also, you don't want segment space management to be set on AUTO in your tablespaces because DW type databases are not update intensive and you don't want to be reading any more blocks then necessary because of the free space in the block that is left to accommodate updates that will never come. Also, if you can get your data files on a file system that supports direct I/O, it would be nice. VxFS is the first thing that comes to mind...If you manage to make it happen, set filesystemio_options parameter to setall, so that oracle will use both asynchronous and direct I/O. You should also minimize the number of DML_LOCKS that you wish to allow and consider using table locking ( row_locking=intent ), to shorten the path through the oracle code.

On 01/22/2004 11:24:41 PM, Tim Gorman wrote:
> So, my intention to set P_A_T to 140G on a new datawarehouse is
> ill-advised?
>
> I'm not kidding, by the way. The Sun E15K belonging to the project
> I'm
> currently working on (purportedly) has 160G of RAM. It is still in
> the box,
> so I'm not believing anything until I type "prtconf"...
>
> I wasn't planning to use more than 10G or so for SGA, and that much
> only
> because I can... <wee-hah!>...
>
> Any thoughts?
>
>
>
>
> on 1/21/04 3:14 PM, Jonathan Lewis at jonathan_at_jlcomp.demon.co.uk
> wrote:
>
> >
> > A comment I picked up from Tom Kyte's
> > Masterclass in Copenhagen last week was
> > that there is an effective limit of 1GB to
> > P_A_T - and although a single session is
> > supposed to be allowed 5% of the P_A_T,
> > you could get about 90MB. So there are
> > some funny things going on in that area
> > which still need fixing.
> >
> > It's a bit tough for big systems, as I've
> > found that the optimizer seems to be
> > much smarter about memory user and
> > access paths when P_A_T and W_S_P
> > are set.
> >
> > What's the book about ?
> >
> > Regards
> >
> > Jonathan Lewis
> > http://www.jlcomp.demon.co.uk
> >
> > The educated person is not the person
> > who can answer the questions, but the
> > person who can question the answers -- T. Schick Jr
> >
> >
> > Next public appearance2:
> > March 2004 Hotsos Symposium - Keynote
> > March 2004 Charlotte NC - OUG Tutorial
> > April 2004 Iceland
> >
> >
> > One-day tutorials:
> > http://www.jlcomp.demon.co.uk/tutorial.html
> >
> >
> > Three-day seminar:
> > see http://www.jlcomp.demon.co.uk/seminar.html
> > ____UK___February
> >
> >
> > The Co-operative Oracle Users' FAQ
> > http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> >
> >
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Wednesday, January 21, 2004 7:44 PM
> >
> >
> >> Replies in line...
> >>
> >> - Kirti
> >>
> >> --- Jared.Still_at_radisys.com wrote:
> >>> Kirti, you're back!
> >>
> >> Thanks. Found some slack time from routine DBA work!
> >>
> >>>
> >>> Must have finished the book. :)
> >>
> >> Not yet.. Its tough..
> >>
> >>
> >>
> >>>
> >>> Re the PGA problems, what was the value for 'over allocation
> count' in
> >>> v$pgastat?
> >>
> >> Actually, I never bothered to look at v$pgastat. Should have.. and
> will,
> > when we do some more
> >> testing next week..
> >>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Tim Gorman
> INET: tim_at_sagelogix.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mladen_at_wangtrading.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Fri Jan 23 2004 - 08:09:25 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US