RE: ** keep/pin objects in sga
- From: "Polarski, Bernard" <Bernard.Polarski@xxxxxxxxxxxxxx>
- To: <Laimutis.Nedzinskas@xxxxxxxxxxxxx>,<oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 2 Nov 2006 17:56:10 +0100
I have been lately playing heavily with swingbench to load database. I could
generate without to 1500 session busy with transaction using MTS on my small
linux box.
Tuning and againg tuning and finally I ended with PGA and SGA aggretate target
as only parameters. removed even DB_CACHE_SIZE. let oracle do its internall
cooking. However I have tested this only with A 1,5G SGA. I heard that when you
go in the very big size SGA Oracle is not so good but can't confirm. I do not
have a test box with 100g ram.
B. Polarksi
_____
From: Laimutis Nedzinskas [mailto:Laimutis.Nedzinskas@xxxxxxxxxxxxx]
Sent: Thursday, 02 November, 2006 5:00 PM
To: ajoshi9777@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: ** keep/pin objects in sga
Pinning individual sql is a pain. A fuzzy science. IMHO, unless it is really
needed for some well identified sql then you are better off letting Oracle to
manage it. You can greatly help Oracle accomplish this by buying more RAM for
the shared pool. 1G shared pool is not uncommon.
I can explain my point:
A) Caching. if sql is really often used then Oracle *must* keep it in the
pool(cache) because it is hot(warm)
If it is not then Oracle should be able to push out this sql to make space.
B) Fragmentation. Unfortunetely (or fortunetely) Oracle has no concept of
compiled(stored) SQL (like DB2 has for example) This means that you can pin SQL
after DB startup and/or pool flush only after it arrives into the pool. The SQL
you want to pin may arrive N-hours after database is started. So what? The pool
gets fragmented long before. I think that by pinning this SQL you fragment the
pool even more because from my understanding Oracle is not able to
relocated(well, push out of pool) this SQL any more, is it? Hope I am wrong.
Conclusion: given todays coding culture I do not think much can be done except
of allocating large enough pool. This reduces some pool problems and does not
introduce to many others.
_____
From: A Joshi [mailto:ajoshi977@xxxxxxxxx]
Sent: 2. nóvember 2006 15:45
To: Laimutis Nedzinskas; oracle-l@xxxxxxxxxxxxx
Subject: RE: ** keep/pin objects in sga
Thanks Laimutis and Mark Bobak, jame tong, Eagle fan, Tanel Poder, Beranrd
Polarski.
I looked at everything. I think cursor_space_for_time will not suit our env
going by what Tanel said. About sessions_cached_cursors according to metalink
note 208857.1 sql I used to see the recommendation : it suggests the current
value of 300 is fully used and could be candidate for increase. However, from
below document http://www.miracleas.dk/tools/Miracle_2_cursor.pdf
by bjorn ensig of miracle it says : 'There is some overhead in CPU, effectively
making this parameter useless for values higher than 50-100.
So am trying to decide which way to go : high or low. I think if it could help
with memory so should I reduce it.
I think apart from that I am trying to pin objects and pinning
package,package body,function,procedure, trigger,type, sequence
I think individual sql can also be pinned but I do not know how easy and
beneficial that is. Is there anything else I can pin?
I am planning to set
_kghdsidx_count to 1 explicitly. I assume there is no issue with that. It is
having 2 subpool now. I do not know in what way 2 subpool helps.
Thanks for all the help.
Laimutis Nedzinskas <Laimutis.Nedzinskas@xxxxxxxxxxxxx> wrote:
Take a look at this too:
http://download-uk.oracle.com/oowsf2005/003wp.pdf
It is really one of the best explanations I´ve ever seen. At least it
sounds close to what Oracle programmers really did.
_____
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of jame tong
Sent: 24. október 2006 07:43
To: ORACLE-L
Subject: Re: ** keep/pin objects in sga
have a read at this document .
http://www.miracleas.dk/tools/Miracle_2_cursor.pdf
Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer
<http://www.landsbanki.is/disclaimer>
_____
Cheap Talk? Check out Yahoo! Messenger's low PC-to-Phone call rates.
<http://us.rd.yahoo.com/mail_us/taglines/postman8/*http://us.rd.yahoo.com/evt=39663/*http://voice.yahoo.com>
Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer <http://www.landsbanki.is/disclaimer>
Other related posts:
