RE: ** keep/pin objects in sga
- From: "Laimutis Nedzinskas" <Laimutis.Nedzinskas@xxxxxxxxxxxxx>
- To: <ajoshi9777@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 3 Nov 2006 09:42:06 -0000
You mean keep, recycle, etc?
One thing I stumbled recently made a sense:
we have a requirement for one query to be fast, quite a real time - timeout is
set at 1,5 sec. This means that disk reads are highly unwanted (given the
potential number of blocks the query can touch.) But this query is not
executing frequently enough to stay in the cache.
Which makes underlying indexes and tables good candidates for a keep pool.
I guess there are cases for recycle pool too.
Actually, it is best to look into Oracle apps. I mean if Oracle did some real
uncommercial feature then it ment that Oracle really needed it for some real
live problem.
________________________________
From: A Joshi [mailto:ajoshi977@xxxxxxxxx]
Sent: 3. nóvember 2006 03:01
To: ajoshi9777@xxxxxxxxx; Laimutis Nedzinskas; oracle-l@xxxxxxxxxxxxx
Subject: RE: ** keep/pin objects in sga
Can some one also tell on what basis it is decided to have more than one
subpool. I cannot find the basis. There are databases with bigger shared pool
but less subpool.
One difference is that db_cache_size is set for some and for others it is
db_block_buffers. Thanks for the help.
A Joshi <ajoshi977@xxxxxxxxx> wrote:
Laim,
I was thinking of pining sql on startup too. First I would have to
get it into pool and I agree it is pain. About increasing shared pool that is
not a problem but I am trying to avoid a too large a shared pool. it is even
mentioned in the article you suggested :
http://download-uk.oracle.com/oowsf2005/003wp.pdf
now tell me frankly did you read it :) :)
OK you say a bigger pool does not introduce too many problems so maybe
you are saying there is limited downside to it. Thanks :) :)
Laimutis Nedzinskas <Laimutis.Nedzinskas@xxxxxxxxxxxxx> wrote:
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>
________________________________
Access over 1 million songs - Yahoo! Music Unlimited Try it today.
<http://pa.yahoo.com/*http://us.rd.yahoo.com/evt=36035/*http://music.yahoo.com/unlimited/>
________________________________
Get your email and see which of your friends are online - Right on the new
Yahoo.com <http://us.rd.yahoo.com/evt=42973/*http://www.yahoo.com/preview>
Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer
Other related posts:
