Oracle internals, debugging and undocumented features

Accessing Fixed Tables using Direct Access

Few years ago I wrote article about direct memory access.


Data are retrieved  directly from SGA.Main advantage is speed .

The Oradebug utility has command called direct_accesswhich as names suggest access fixed tables directly.

Help command will list all options:

SQL> oradebug help direct_access
DIRECT_ACCESS  <set/enable/disable command | select query> Fixed table access
SQL> oradebug setmypid
Statement processed.

First option is to  define content type.Self explained.

SQL> oradebug direct_access set content_type = ‘text/plain’
Statement processed.
Enable writing to trace file.Query output written to trace.

SQL> oradebug direct_access enable trace
Statement processed.
Disable writing to trace file:

SQL> oradebug direct_access disable trace
Statement processed.
Disable display on screen.Otherwise you will see zillions rows flying on screen.Good for tables with few rows.

SQL> oradebug direct_access disable reply
Statement processed.
and most important option:
oradebug direct_access select * <fixed_table>

Here is simple query to list fixed tables

select kqftanam from x$kqfta

I did speed test using  favorite X$KSMMEM  with few  million rows

oradebug direct_access select * X$KSMMEM

12:11:13 SQL> @dm

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
Statement processed.
Statement processed.
ORA-01013: user requested cancel of current operation

12:11:35 SQL>

so I let it run for about 22 seconds.

Trace file has exact time:

*** 2009-07-17 12:11:15.743

Oradebug command ‘tracefile_name’ console output:
Result of fixed table query: select * from X$KSMMEM

and I canceled query at 12:11:35  so 20 seconds total time
And from the end of trace file
ADDR    = 604446E8
INDX    = 559325
KSMMMVAL        = 80AA1D

559326 rows selected
So if approx 28K rows per second.Realy fast.. Like TimesTen.

just regular sql query takes about 30 seconds for only 55 k rows.Like 15 times slower..

select * from X$KSMMEM  takes ten times more time to run..
SQL> select * from X$KSMMEM where rownum < 55930
Elapsed: 00:00:30.86

The oradebug direct_access has some limitations:
Direct access cannot be used to access every fixed table .

ORA-15653: Fixed table “X$KQFTA” is not supported by DIRECT_ACCESS.

Direct access cannot be used with preliminary connection:

-sh-3.1$ sqlplus -prelim / as sysdba

ORA-15655: Fixed table “X$KSUSE” cannot be accessed safely in prelim connection.

Only  simple queries can be used:
Enter value for table_name: X$XSSINFO where rownum < 10
ORA-00933: SQL command not properly ended


7 responses to “Accessing Fixed Tables using Direct Access

  1. Marcin Przepiorowski July 22, 2009 at 8:38 pm


    Very nice, very nice
    Now Direct Access to SGA can be platform independent.
    Is this new feature of 10g or 11g ?

    Some times ago I have used example from http://oraperf.sourceforge.net/ to develop my own version of that tool with SQL text for HP-UX and Linux. After that I have to rewrite everything to make it working on Windows – now it can be one tool.

    Marcin Przepiorowski

  2. oraclue July 23, 2009 at 5:56 pm

    Hi Martin,

    This option is 11g feature.


  3. Marcin Przepiorowski July 27, 2009 at 8:45 am

    Hi Miladin,

    I have tested it on my VM machine, but each time I have used a direct access feature a CPU usage was very high. Ex. using your query with x$ksmmem it was about 50 %, for x$ksuse with 170 session it was about 10 to 12 % of CPU.

    I’m thinking that this is related to poor writing access to trace files because C program can save this same size of data using less then 5 % of CPU for x$ksuse.

    Did you have similar CPU usage ? or maybe it is related to VM


  4. Pingback: Blogroll Report 17/07/2009 – 24/07/2009 « Coskan’s Approach to Oracle

  5. oraclue July 29, 2009 at 6:24 pm

    Hi Marcin,

    I just tested it on my server and I can see increased CPU usage and I am not using VMware..


  6. Pingback: Variabili in SQL*Plus « Oracle and other

  7. Alexey Nikulin June 3, 2011 at 1:54 am

    Hi, Miladin.
    Direct_access works fine in prelim connection for example with x$ksdhng_chains table.
    So i think it would be more correct to say “Direct access cannot be used with preliminary connection with _all_ x$ tables”

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: