Re: How to trace what is happening inside the stored procedure

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: Mon, 7 Feb 2005 02:51:26 -0000
Message-ID: <060701c50cbf$eb0705c0$0301a8c0@porgand>

Hi,

> Are you saying that application_info data is duplicated all over the
> place?
> Last time I looked, the V$* views are based on the X$* views, which are
> direct "windows" to shared memory. I'd say that application_info
> data will be in one place only, and visible in various V$* views.
> Any savings are quite frankly moot.

Can you prove it?

Yep, X$ tables still are direct views to memory, both to shared and private memory, or pieces of kernel code which then read (or even write to) memory, to controlfiles, datafile headers or do other stuff ;)

But Tim's comment was very appropriate - module and action info are stored with each separate child cursor during first parse time and that info there remains independent of any session level module or action changes. Here's how I concluded it:

>From v$fixed_view_definition you can see that MODULE column in v$session is
gotten from X$KSUSE.KSUSEAPP and module column in V$SQL is gotten from V$KGLCURSOR.KGLOBTS0 (which is a derived table actually getting info from X$KGLOB as seen from following queries):

SQL> select kqfdtequ from x$kqfdt where kqfdtnam = 'X$KGLCURSOR';

KQFDTEQU

X$KGLOB <--- this means that x$kglcursor actually uses the same memory structure as x$kglob (but can shows different elements/columns of it)

The next query shows us the physical memory offsets X$ table columns representing elements in fixed arrays:

SQL> select t.kqftanam, c.kqfconam, c.kqfcotab, c.kqfcotyp, c.kqfcosiz, c.kqfcolof
  2 from x$kqfta t, x$kqfco c
  3 where t.indx = c.kqfcotab
  4 and c.kqfconam in ('KSUSEAPP', 'KGLOBTS0');

KQFTANAM KQFCONAM KQFCOTAB KQFCOTYP KQFCOSIZ KQFCOLOF ---------- ---------- ---------- ---------- ---------- ----------

X$KSUSE    KSUSEAPP           23          3         48       3740
X$KGLOB    KGLOBTS0          352          3         64       1300

KQFCOTYP = 3 shows that these columns are raw string type, not a pointer to anywhere, thus they are stored within given memory structures (kglob for library cache objects), not somewhere else. Just to check:

SQL> select addr, kglobts0 from x$kglob where kglobts0 is not null and rownum < 5;

ADDR KGLOBTS0

-------- --------------------------------------------------------
05CFB2A8 sqlplus.exe
0566B90C sqlplus.exe

05CFB2A8 sqlplus.exe
0566B90C sqlplus.exe

Also, as you see from following queries, the session and library cache elements are allocated from totally different memory regions, so there is no sharing of common information (derived tables can reference to data structures in shared manner, but only to different columns within the same data structure (as I understand))..

SQL> select min(addr), max(addr) from x$ksuse;

MIN(ADDR MAX(ADDR
-------- --------
6A77A9B0 6A7FA690

SQL> select min(addr), max(addr) from x$kglob;

MIN(ADDR MAX(ADDR
-------- --------
05660AAC 05CFB2A8 This is actually perfectly reasonable, because if there was a common memory location for MODULE and ACTION for both sessions and library cache, then what should happen to module and action values in cached cursors when session sets a new module/action and parses a new query? The old module/action values must not change, thus have to be stored independently in different locations, for each child cursor separately.

Now this overhead is much more important than the session array effect I thought of at first :)

Tanel.

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Feb 06 2005 - 21:54:07 CST