| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: ** keep/pin objects in sga
Laim,
Iam talking about subpool meaning : shared pool is divided into subpools.
For table, index I think it will be cache. like alter table 'abc' cache. or cache with optimizer hint. I do not know the exact way to cache an index.
Sometimes ORA-4031 leads to database not being accessible and needs a bounce. Is there someway this can be predicted so that preventive action can be taken. Bounce gets counted as downtime and avoidable. And what can be the preventive action : Since it is fragmentation then will creating a big stored procedure help in some way. Meaning it will need a big chunk and can replace fragmented shared pool parts. Only problem is it will get replaced only after all parts that came before it are replaced and database needs to survive till then. Or maybe this can be done once in a while. Am I thinking on right lines or it is totally crazy (crazier things have been done) simplistic and could cause some other major problems. Thanks
Laimutis Nedzinskas <Laimutis.Nedzinskas_at_landsbanki.is> wrote:
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.
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_at_yahoo.com> 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_at_landsbanki.is> 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:
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.
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_at_landsbanki.is> 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.
have a read at this document .
http://www.miracleas.dk/tools/Miracle_2_cursor.pdf
Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Nov 05 2006 - 12:15:39 CST
 |
 |