RE: v$sesstat vs v$mystat. A curiousity

Hi,  

The info you see in those tables does come from shared pool, but is collected into PGA before returning to client. This happens for non-trivial X$ tables which don't just read from an array based on hard-coded offsets but rather have to do some more work for getting the info.  

See how a set of the same addresses is used for returning data:  

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

MIN(ADDR MAX(ADDR
-------- --------

08348BAC 08348BAC   SQL> select min(addr), max(addr) from x$ksusesta;  

MIN(ADDR MAX(ADDR
-------- --------

08349BC4 08349BC4  

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

MIN(ADDR MAX(ADDR
-------- --------

08348BAC 08348BAC   SQL> select min(addr), max(addr) from x$ksumysta;  

MIN(ADDR MAX(ADDR
-------- --------

08349BC4 08349BC4  

SQL> select min(addr), max(addr) from x$kccfn; <-- controlfile info is also read from disk to PGA before presenting it  

MIN(ADDR MAX(ADDR
-------- --------

08348BAC 08348BAC   SQL> select min(addr), max(addr) from x$kccfn;  

MIN(ADDR MAX(ADDR
-------- --------

08349BC4 08349BC4

Tanel.

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Henry Poras
Sent: 16 November 2006 17:37
To: oracle-l_at_freelists.org
Subject: v$sesstat vs v$mystat. A curiousity

Poking around with (g)v$mystat and (g)v$sesstat (looking in v$fixed_view_definition) I noticed that they come from two different, but similar x$ tables.

gv$mystat --> x$ksumysta
gv$sesstat --> x$ksusesta

The table structures are the same, but x$ksumysta just has a single sid (ksusenum) while x$ksusesta has all. Comparing a record using the same sid and statistic# returned different addresses (ADDR) from the two tables.

I would guess that x$ksumysta is built from other underlying structures (x$ksusesta???) otherwise we have redundant memory structures (and anyway, how did it know and apply my ksusenum/sid?).

Nothing really important here. Just wondering if anyone has any ideas what is going on?

Henry

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