FreeLists / oracle-l / RE: ** keep/pin objects in sga

[A Joshi <ajoshi977@xxxxxxxxx>]

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

    
---------------------------------
  Cheap Talk? Check out Yahoo! Messenger's low PC-to-Phone call rates.   
Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer


 
---------------------------------
Access over 1 million songs - Yahoo! Music Unlimited Try it today.