xDollar

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 (gradually growing) list of exceptions .  The code has some calls to dbms_output(), which you can choose to hide by setting serveroutput off.  Depending on what your system is and how it’s configured you may see a very large number of x$ structures with no data in them.

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

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 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));
                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 11.2.0.4 – and I’ve found data in only 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 highlight is the way 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 in my output) 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, in particular

• 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 user space seems to share addresses it doesn’t mean that they are overlapping structures, unlike (for example) x$ksuse and x$ksusecst which are overlapping subsets of the same structure in the SGA.