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" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED] 09/30/2003 07:49 AM
|
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 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: [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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Orr, Steve
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).
