| 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: Largest shared pool
Thanks Tanel.
Syed
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;
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.
> 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
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org 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 -----------------------------------------------------------------Received on Thu Apr 08 2004 - 05:53:18 CDT
 |
 |