Oracle Scratchpad

January 13, 2014


Filed under: Infrastructure,Oracle — Jonathan Lewis @ 10:09 am BST 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 a row from particular X$ objects, so there 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$ 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 rownum = 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.
As far as Tanel’s series is concerned a key detail here is the way in which you can break the range 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 in user space..


  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 BST 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 BST 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 BST 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 BST Jan 13,2014 | 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 )

Google photo

You are commenting using your Google 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.

Powered by