Re: Largest shared pool
- From: "Sultan Syed" <ssyed@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 8 Apr 2004 14:56:37 +0400
Thanks Tanel.
Syed
----- Original Message -----
From: "Tanel Põder" <tanel.poder.003@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, April 07, 2004 9:23 PM
Subject: 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 //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
