Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Buffer Pool KEEP -- NOT keeping !!
Before you issue the DBMS_SHARED_POOL.KEEP, do you reference the object?
You need to reference the object before you can pin it.
HTH,
Brian
Allen Hayden wrote:
>
> My understanding was that objects with properties
> assigned the Buffer_Pool ='KEEP' would be permanently
> held in that KEEP cache.
>
> I created a KEEP pool of 200M with the goal of having
> this large index permanently cached there. For the
> most part it works great!
>
> BUT there's a little mystery...
>
> I created a procedure that would force a full index
> scan to prime that index into the KEEP cache. After i
> run the priming procedure i check the cache to see how many
> buffers are used by the object using this SQL script
>
> SQL> l
> 1 select obj,count(*)
> 2 from sys.xbh
> 3 where obj =2821
> SQL> /
>
> OBJ COUNT(*)
> --------- ---------
> 2821 14506
>
> The Mystery is that some 10 minutes later if i run the
> same script, its shows a LOWER buffer count. If all buffers
> that are thrown into KEEP cache are supposed to be pinned there
> then why are my pinned buffers for this object less on the
> 2nd query.
>
> FYI: I have only one object in the entire database that is
> assigned to the KEEP cache. I'm running this on an NT box
> with 458M of RAM using v8.0.5.
>
> Any comments would be appreciated.
> Thanks
> Jason
--