Miladin Modrakovic's Blog: Oraclue

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