RE: x$ constructs and memory

>I was sitting on a mountain here in Colorado,
>pondering Oracle
>optimization and an interesting scenario crossed my
>feeble mind.
>As I began to ponder this (I asked the resident
>marmot, but he
>must be a SQL*Server expert...), I came up with
>several
>questions.
>
>Where in memory (sga or other) do the x$ constructs
>reside?
>Some of them are 'populated' by reading file-based
>structures
>(control file, datafile headers, undo segments).
>Does this
>information reside in memory or is it loaded each
>time the x$
>construct is accessed?
>What happens when these x$constructs begin to
>consume large
>amounts of memory? Is there an upper bound?
>
>Daniel Fink

Dan,

  Concerning question 1, I think that most X$ information resides in the SGA, however some X$ tables obviously map the PGA (those on which GV$SQL_BIND_DATA is based are an obvious example - you can only see what refers to your own session, for what I have seen; other examples with cursor-related fixed views).   I don't believe that any of them is ever reloaded - they contain data which is really useful to Oracle.   For the last point, if you run a count(*) on them you will notice a stunning resemblance to some of your init.ora parameters ... It's less obvious with the V$ views because the V$ views chop any null (not in the SQL acceptance) row off, but the X$ are unrepentant memory arrays, with 0s or similar at unused positions.    What I have not checked so far is how an ALTER SYSTEM increasing a parameter affects the SGA. In practice it's a realloc() (functionally speaking). It would seem reasonable to me to have a shared memory segment to hold all parameters which can by dynamically changed. I wouldn't touch it if parameters are decreased, but I would have to realloc it in case of a massive increase. Hmm, I guess that I would allow some spare memory initially, performance penalty would otherwise be severe. Which all makes the 10g dynamic rearrangement quite sensible ...  

Regards,

Stephane Faroult
Oriole

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).