Re: Largest shared pool

Hi!
This memory management of Oracle needs much more research before I can claim 
anything by sure, but there is one small example:

In one session I executed:

SQL> select * from t6;

no rows selected

This parsed the statement and allocated chunks for parent and child cursor.
Then in another session I executed the following, to age out these chunks:

declare
  j number;
  i number;
  str varchar2(100);
begin
  for j in 1..50000 loop
    str:='select count(*) from t where ' || j || ' = ' || j || '';
    execute immediate str;
    execute immediate str;
    execute immediate str;
    execute immediate str;
  end loop;
end;
/

When the loop was running, I scanned the x$kglob view which containts 
information about library cache objects and pointers to their various heaps.

SQL> select KGLNAOBJ, KGLHDADR, KGLOBHD0 from x$kglob where kglnaobj = 'select 
* from t6';

KGLNAOBJ                       KGLHDADR KGLOBHD0
------------------------------ -------- --------
select * from t6               2F959350 2FAC23A8
select * from t6               306D8B20 2FACAA00

Both cursors have a heap 0 allocated above (KGLOBHD0 points to a valid address)
It can't be seen from this listing, but the child cursor here has a valid 
pointer in KGLOBHD6 as well, to the execution plan. The parent cursor has a 
null pointer in KGLOBHD6..

SQL> select KGLNAOBJ, KGLHDADR, KGLOBHD0 from x$kglob where kglnaobj = 'select 
* from t6';

KGLNAOBJ                       KGLHDADR KGLOBHD0
------------------------------ -------- --------
select * from t6               306D8B20 00

After a while, when some chunks in memory were freed (and possibly reused), the 
child cursor is kicked out from memory completely, but the parent cursor still 
remains there, but having no pointer to heap 0 (KGLOBHD0 = 00). So, this shows 
that it is possible to age out a heap 0 of a library cache object without aging 
out the object (handle) itself. 

SQL> select KGLNAOBJ, KGLHDADR, KGLOBHD0 from x$kglob where kglnaobj = 'select 
* from t6';

no rows selected

I waited a bit more and this loop of execute immediate's running in another 
session aged out the handle of parent cursor too..

In this example, when a cursor was cached, its corresponding library cache 
handle had a null mode lock held on it (KGLHDLMD = 1),  and KGHLDFLG had 28th 
bit set (from 0..31), this should mean that when the library cache object is 
locked, it's corresponding heap 0 should be pinned. That way the chunk can't be 
aged out, which my brief tests seemed to show as well.

Note that this is gray area to me as well, so I might very well be wrong here. 
I'm just starting to dig into Oracle memory management, it's quite hard and 
time consuming.. and unfortunately 9i and 10g don't make it any easier with 
their query execution services layer and various data sampling/gathering 
modules...

Tanel.

----- Original Message ----- 
From: "Sultan Syed" <ssyed@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, April 07, 2004 9:10 AM
Subject: Re: Largest shared pool


> Hi Tanel,
> Please clarify me the following
> 
> From your reply
> "For example if Heap 0 for a library cache object has been aged out by KGH,
> the library cache
> object handle has to be updated with relevant information."
> 
> I was in the impression that if heap 0 ,which is freeable chunk,is aged out
> the handle also will be aged out since heap 0 will be containing the handle
> also.
> But your post says that handle will be updated with relevant information.
> Means handle still be there ?
> And at the time of aging out,what will happen to the cursor which is already
> cached
> using session_cached_cursors .
> Could you  put  your points more on this.
> Syed

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: