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 it’s 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.
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 as /* 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); begin for c1 in (select kqftanam, indx from x$kqfta) loop begin 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)); exception when no_data_found then dbms_output.put_line('Warning: No data-' || ' ' || c1.kqftanam || ' ' || c1.indx); null; when no_table then dbms_output.put_line('Warning: No table-' || ' ' || c1.kqftanam || ' ' || c1.indx); null; when logminer_problem then dbms_output.put_line('Warning: logminer issue' || ' ' || c1.kqftanam || ' ' || c1.indx); null; when logminer_problem2 then dbms_output.put_line('Warning: logminer issue' || ' ' || c1.kqftanam || ' ' || c1.indx); null; when dg_broker_problem then dbms_output.put_line('Warning: DataGuard issue' || ' ' || c1.kqftanam || ' ' || c1.indx); null; when mem_advisor_problem then dbms_output.put_line('Warning: Memory Advisor' || ' ' || c1.kqftanam || ' ' || c1.indx); null; when deadlock then dbms_output.put_line('Warning: deadlock' || ' ' || c1.kqftanam || ' ' || c1.indx); null; when others then dbms_output.put_line('Warning: other' || ' ' || c1.kqftanam || ' ' || c1.indx); raise; end; end loop; return; end; / show errors
Once the function has been created it’s a simple matter to call it, ordering the output by (for example) x_addr.
select * from table(x_first) 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 188.8.131.52 – 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 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..