Home > Internals > Accessing Fixed Tables using Direct Access

Accessing Fixed Tables using Direct Access

Few years ago I wrote article about direct memory access.

www.petefinnigan.com/Storing_Data_Directly_From_Oracle_SGA.pdf

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-main/app/oracle/admin/dwdev11g/diag/rdbms/dwdev11g/dwdev11g/trace/dwdev11g_ora_21535.trc
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:
/ora-main/app/oracle/admin/dwdev11g/diag/rdbms/dwdev11g/dwdev11g/trace/dwdev11g_ora_21535.trc
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
INST_ID = 1
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

About these ads
Categories: Internals
  1. July 22, 2009 at 8:38 pm | #1

    Hi,

    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.

    Thanks.
    Marcin Przepiorowski

  2. July 23, 2009 at 5:56 pm | #2

    Hi Martin,

    This option is 11g feature.

    Thanks,
    Miladin

  3. July 27, 2009 at 8:45 am | #3

    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

    regards,
    Marcin

  4. July 29, 2009 at 6:24 pm | #4

    Hi Marcin,

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

    Miladin

  5. Alexey Nikulin
    June 3, 2011 at 1:54 am | #5

    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”

  1. July 27, 2009 at 2:29 pm | #1
  2. August 14, 2009 at 1:38 pm | #2

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

Follow

Get every new post delivered to your Inbox.

Join 41 other followers

%d bloggers like this: