Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

January 28, 2008

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

Filed under: Oracle How To — H.Tonguç Yılmaz @ 5:28 am

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 »

  1. […] Oracle Performance — H.Tonguç Yılmaz @ 5:27 am I have written on external tables before; External table’s performance for a row and undocumented Oracle functions Unload Data with External Tables and Data […]

    Pingback by A little more on External Tables « H.Tonguç YILMAZ Oracle Blog — March 11, 2008 @ 5:28 am | Reply


RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: