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 [EMAIL PROTECTED] 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" <[EMAIL PROTECTED]> > Sent: Wednesday, January 21, 2004 7:44 PM > > >> Replies in line... >> >> - Kirti >> >> --- [EMAIL PROTECTED] 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
