Oracle Scratchpad

January 13, 2014


Filed under: Infrastructure,Oracle — Jonathan Lewis @ 10:09 am GMT Jan 13,2014

I see Tanel has just started a short series of articles on where the X$ data come from so, for those with time on their hands (?anyone?) here’s a little script I wrote a few years ago when I wanted to check which v$ objects corresponded to the same internal structure in the SGA: (as v$session and v$session_wait used to)

It starts with a function that has to be created in the SYS schema – so no doing this on production systems. The function is a pipelined function so that we can treat its output like a table, which means I need to create an object type and a table type before creating the function.  In the function I select each x$ name from the list of x$ names (x$kqfta) and for each table I construct a dynamic SQL statement selecting the first row from the table.

Over the versions of Oracle various bugs have resulted in deadlocks and crashes when selecting rows from particular X$ objects, so I’ve included a short list of exceptions that has grown gradually over time.  The code has some calls to dbms_output(), which you can choose to hide by setting serveroutput off.  Depending on your system you may see a very large number of x$ structures with no data in them.

rem     Script:         x_link.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Sept 2003
rem     Purpose:        Help to find which X$ tables overlap

create type jpl_row as
	object(x_address raw(8), x_name varchar2(30), x_indx number);

create type jpl_table as
	table of jpl_row;

create or replace function x_first
return jpl_table pipelined
/*	Created by x_link.sql	*/
--	Pipeline function to examine addresses of data in x$ objects
--	Uses types jpl_row and jpl_table to implement the pipeline
	dg_broker_problem exception;
	pragma EXCEPTION_INIT(dg_broker_problem, -16525);

	logminer_problem exception;
	pragma EXCEPTION_INIT(logminer_problem, -1306);

	logminer_problem2 exception;
	pragma EXCEPTION_INIT(logminer_problem2, -1307);

	mem_advisor_problem exception;
	pragma exception_init(mem_advisor_problem, -1007);

	deadlock exception;
	pragma EXCEPTION_INIT(deadlock, -60);

	no_table exception;
	pragma EXCEPTION_INIT(no_table, -942);

	m_addr		x$kqfta.addr%type;
	m_indx		x$kqfta.indx%type;
	m_table		varchar2(30);

	for c1 in (select kqftanam, indx from x$kqfta) loop
			execute immediate
				' select indx, addr, ''' || c1.kqftanam ||
				''' from ' || c1.kqftanam ||
				' where indx = 1'
				into m_indx, m_addr, m_table;
				dbms_output.put_line(m_addr || ' ' || m_table || ' ' || c1.indx);
				pipe row (jpl_row(m_addr, m_table, c1.indx));
			when no_data_found then
				dbms_output.put_line('Warning: No data-' || ' ' || c1.kqftanam || ' ' || c1.indx);
			when no_table then
				dbms_output.put_line('Warning: No table-' || ' ' || c1.kqftanam || ' ' || c1.indx);
			when logminer_problem then
				dbms_output.put_line('Warning: logminer issue' || ' ' || c1.kqftanam || ' ' || c1.indx);
			when logminer_problem2 then
				dbms_output.put_line('Warning: logminer issue' || ' ' || c1.kqftanam || ' ' || c1.indx);
			when dg_broker_problem then
				dbms_output.put_line('Warning: DataGuard issue' || ' ' || c1.kqftanam || ' ' || c1.indx);
			when mem_advisor_problem then
				dbms_output.put_line('Warning: Memory Advisor' || ' ' || c1.kqftanam || ' ' || c1.indx);
			when deadlock then
				dbms_output.put_line('Warning: deadlock' || ' ' || c1.kqftanam || ' ' || c1.indx);
			when others then
				dbms_output.put_line('Warning: other' || ' ' || c1.kqftanam || ' ' || c1.indx);
	end loop;

show errors

Once the function has been created it’s a simple matter to call it, ordering the output by (for example) x_addr.

order by
	x_address, x_indx

X_ADDRESS        X_NAME                             X_INDX
---------------- ------------------------------ ----------
0000000000000000 X$KSUTM                                41
                 X$KSUSGIF                              42
                 X$KTTVS                               477
                 X$KDXHS                               487
                 X$KZSRO                               528
00000000099EC500 X$KGSKCP                              139
00000000099EC560 X$KGSKPP                              140
00000000099EC5D0 X$KGSKASP                             141
000000000BEE9560 X$KSFDFTYP                            119
000000000C0F5B1C X$KQDPG                               495
000000000C0F7660 X$KQFTVRTTST0                         863

0000000060000000 X$KSMMEM                               71
0000000060001190 X$KSMSP_DSNEW                          82
00000000600077CC X$KGICS                               513
00000000AAE1B588 X$KSQST                                32
00000000AAE35598 X$KSRPCIOS                            118

00007FFB03E92478 X$DBGDIREXT                           820
00007FFB04274F50 X$KSMSP                                75
00007FFB045D4E28 X$ACTIVECKPT                          270
00007FFB093A7B48 X$KXFPSST                             569
00007FFB093A9D48 X$KOCST                               638

473 rows selected.

This was 64-bit Oracle version – and I’ve only recorded data in 473 of the possible 950 x$ structures.

Some of the structures share the same starting address but that’s not the point I want to pursue at present; as far as Tanel’s series is concerned the key detail I want to mention is the way in which you can break the output into 4 chunks:

  • The zeros – x$ which I assume don’t take any memory but simply return the result of a real-time function call.
  • The low range (up to C0F7660) which is a fixed data segment (whose technical name in x86-speak I forget) in the SGA
  • The mid range (60000000 to AAE35598) which is the SGA heap
  • The high range (from 7FFB03E9247 upwards) which is user space (PGA/UGA addressing)

As Tanel’s first post explains, and as you can infer from the sample listing above, when you query x$ksmsp you are querying a structure that has been created on demand in user space.  When you query objects in user space you may see a couple of odd effects, notably: sometimes every row in a structure will have the same address (addr), sometimes the address(es) of rows in a structure will change from time to time (or even every time) you query the structure. So when two structures in userspace seems to share addresses it doesn’t mean that they are overlapping structures as (for example) x$ksuse and x$ksusecst are in the SGA.


  1. Thanks and Tanel giving us new year treat :) Could you please explain the exception block especially logminer issue (twice) and dataguard issue? Although I have absolutely no idea about rest of them except first two.

    Thanks again,

    Comment by swkambli — January 13, 2014 @ 2:09 pm GMT Jan 13,2014 | Reply

    • Swapnil,

      It’s vaguely mentioned in the text: “Over the versions of Oracle, various bugs have resulted in deadlocks and crashes when selecting a row from particular X$ objects” – I couldn’t recall the exact details when I wrote the notes, but each exception in that list has been raised in some version of Oracle, including the most recent. I’ve just checked what 1306 and 1307 are – and they’re totally understandable in the circumstances:

      SQL> execute dbms_output.put_line(sqlerrm(-1306))
      ORA-01306: dbms_logmnr.start_logmnr() must be invoked before selecting from v$logmnr_contents
      SQL> execute dbms_output.put_line(sqlerrm(-1307))
      ORA-01307: no LogMiner session is currently active

      Comment by Jonathan Lewis — January 13, 2014 @ 2:23 pm GMT Jan 13,2014 | Reply

  2. Hi Jonathan,

    thanks a lot for sharing this, it’s very useful (especially the categorization of address ranges!).
    For the (non-zero) addresses below x$ksmmem start, this would be the real “fixed area”, as opposed to the permanent part of the variable area that comprises x$ksuse et al., right? Which would also match the observation that row size in, e.g., X$KSLEPX is constant, whereas x$ksuse, x$ksupr etc. every nth row is padded (because of segmentation, as you wrote on Oracle-L).

    Would you perhaps have any further information on that segmentation? I’ve noticed that n is different for different tables, e.g., in one case it’s every 43rd row that “overly large”, in another it’s every 41st… Still every such big row seems to be of a different size, so at first glance it’s not easy to see the systematics…


    Comment by Sigrid — January 13, 2014 @ 9:30 pm GMT Jan 13,2014 | Reply

    • Sigrid,

      re: “fixed area” I believe that is correct – it’s the part of memory allocation that can be fixed at compile time, as opposed to the parts of the heap (like x$ksuse / v$session) that become fixed relative to the application at startup.

      The segmentation is typically either about a fixed number of rows like 16 (powers of 2 seem to be popular) or convenient memory chunk size (such as 4KB plus a little bit. Sometimes the “overly large” offsets can be deceptive because a couple of adjacent chunks happen to have been allocated in quick succession; sometimes the first few chunks seem to be allocated in a very large (e.g. 8MB chunk).

      Comment by Jonathan Lewis — January 13, 2014 @ 10:10 pm GMT Jan 13,2014 | Reply

  3. […] one last thought – the X$ “tables” fall into 4 different categories and you can identify which category that is by check the address (addr) of the first row of each table (provided the table has some […]

    Pingback by Excavating x$ | Oracle Scratchpad — March 16, 2022 @ 11:13 pm GMT Mar 16,2022 | Reply

  4. […] length for the row and a gap of 212 bytes to explain; and even though there are several x$ tables starting at the same address (x$ksuse, x$ksusio, x$lsusecst, x$ksuru, x$ksusm) on the same internal structure none of them has a […]

    Pingback by X$ row sizes | Oracle Scratchpad — March 21, 2022 @ 11:00 am GMT Mar 21,2022 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: