>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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
