External table’s performance for a row and undocumented Oracle functions

I wanted to share two OTN forum threads I found interesting this weekend. First one is this ;

http://forums.oracle.com/forums/thread.jspa?messageID=2313216

Here Daniel Morgan advises a function called WM_CONCAT for the opponent’s need. There are several interesting undocumented Oracle functions which you may test carefully and decide to use or not to, and here is some of them listed at psoug;

http://www.psoug.org/reference/undocumented.html

The second thread is about using an external table for retrieving a row several times. External tables are great tools when you need to access a huge flat file on the operating system. You can process and with functions at select side also transform this bulk data into a custom Oracle table with create table as select which can be done nologging and parallel.

But as mentioned in this thread for an external table selecting a row’s cost is 48 consistent gets where as for a permanent table it is 3 consistent gets;

SQL> set autotrace traceonly statistics
SQL> SELECT enckey FROM ext_ops_sec;

Statistics
----------------------------------------------------------
         17  recursive calls
          0  db block gets
         48  consistent gets
          0  physical reads
          0  redo size
        417  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> SELECT enckey FROM tab_ops_sec;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        417  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

This cost’s details are listed in the thread with event 10046 level 8 trace’s tkprof report sys=yes and waits=yes options enabled. So if you are not supposed to use an external table for a row you may test and choose one of the alternatives mentioned in the thread;

http://forums.oracle.com/forums/thread.jspa?threadID=610678

1 Comment