Oracle internals, debugging and undocumented features
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
First option is to define content type.Self explained.
SQL> oradebug direct_access set content_type = ‘text/plain’
Enable writing to trace file.Query output written to trace.
SQL> oradebug direct_access enable trace
Disable writing to trace file:
SQL> oradebug direct_access disable trace
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
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.
ORA-01013: user requested cancel of current operation
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
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
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