Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: x$ constructs and memory

RE: x$ constructs and memory

From: Stephane Faroult <>
Date: Mon, 29 Sep 2003 07:59:45 -0800
Message-ID: <>

>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
>Where in memory (sga or other) do the x$ constructs
>Some of them are 'populated' by reading file-based
>(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


  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 ...  


Stephane Faroult

Please see the official ORACLE-L FAQ:
Author: Stephane Faroult

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (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).
Received on Mon Sep 29 2003 - 10:59:45 CDT

Original text of this message