RE: x$ constructs and memory

Description of the X$ does reside in the dictionary cache, but those tables are entry points into the code. So, besides their description, they don't consume memory, i.e. their blocks aren't cached.

On Tue, 2003-09-30 at 15:29, Jared.Still_at_radisys.com wrote:
> I don't generally get too involved in the x$ stuff, just because it
> normally helps me very little in my DBA work.
>
> Nonetheless, I have been following this one somewhat, and if my
> understanding is correct, x$ tables are not actually responsible
> for consuming memory, they are merely a mechanism for displaying
> various structures internal to the kernel, many of which happen to
> be transient.
>
> Jared
>
>
>
>
>
> "Orr, Steve"
> <sorr_at_rightnow.com>
> Sent by:
> ml-errors_at_fatcity.com
>
> 09/30/2003 07:49 AM
> Please respond to
> ORACLE-L
>
> To:
> Multiple recipients of
> list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> cc:
> Subject:
> RE: x$ constructs and
> memory
>
>
> Hi Steve and welcome back,
>
> Thanks for that detailed answer BUT... A practical question from the
> original post remains: "What happens when these x$constructs begin to
> consume large amounts of memory?" From your explanation I'm assuming
> that, beyond monitoring the SGA and PGA, memory consumption of
> individual X$ in-memory data structures is generally not something we
> need to "worry" about. How can we determine how much memory they
> actually consume? Are there any related tunable parameters of which we
> should be aware?
>
> Thanks,
> Steve Orr
>
>
>
> -----Original Message-----
> Sent: Monday, September 29, 2003 5:25 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi Daniel and list,
>
> There are two types of X$ row sources. "X$ tables" export in-memory
> data
> structures that are inherently tabular, and "X$ interfaces" that call
> functions to return data is non-tabular, or not memory resident.
>
> For example, the array of structs in the SGA representing processes is
> exported as the "X$ table" X$KSUPR. Not all of the struct members are
> exported as columns, but all of the rows are exported. There is a
> freelist, implemented as a header that points to the first free slot
> in
> the array, and a member of each struct to point to the next free slot.
> The 'process allocation' latch protects this freelist.
>
> The most obvious example of an "X$ interface" to return non-tabular
> data
> is X$KSMSP, which returns one row for each chunk of memory in the
> shared
> pool. (There are similar X$ interfaces for other memory heaps). As you
> may know, heaps are implemented as a heap descriptor and linked list
> of
> extents, and within each extent there is a linked list of chunks. So
> what is done is that when the X$ interface is queried these linked
> lists
> are navigated (under the protection of the relevant latch if
> necessary)
> an a array is built in the CGA (part of the PGA) from which rows are
> then returned by the row source.
>
> An example of an "X$ interface" that returns data that is not memory
> resident is X$KCCLE, which returns one row for each log file member
> entry in the controlfile. In fact, all the X$KCC* interfaces read data
> directly from the controlfile. Similarly, the X$KTFB* interfaces
> return
> LMT extent information - from the bitmap blocks (for free extents) and
> from the segment header and extent map blocks (for used extents).
>
> Some "X$ tables" have become "X$ interfaces" in recent versions, for
> example X$KTCXB and X$KSQRS. These correspond to the transactions and
> enqueue resources arrays respectively. The reason is that they are no
> longer fixed arrays. Instead they are "segmented arrays" that can be
> dynamically extended by adding discontiguous chunks of shared pool
> memory to the array. The freelists and latching for these arrays in
> unchanged however. All you will notice is that the ADDR column of the
> X$
> output now returns addresses which map into your PGA rather than the
> SGA. In fact, that is in general a good way to work out whether you
> are
> looking at an X$ table or an X$ interface.
>
> @ Regards,
> @ Steve Adams
> @ http://www.ixora.com.au/ - For DBAs
> @ http://www.christianity.net.au/ - For all
>
> -----Original Message-----
> Daniel Fink
> Sent: Tuesday, 30 September 2003 1:10 AM
> To: Multiple recipients of list ORACLE-L
>
>
> 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
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Steve Adams
> INET: steve.adams_at_ixora.com.au
>
> 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).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Orr, Steve
> INET: sorr_at_rightnow.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).
>
>

Note:
This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mladen_at_wangtrading.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).