“external table write” wait events… but I am only running a query?

I was helping a customer debug some external table load problems.  They are developing some code to do massive inserts via external tables.  As the code was being tested, we saw a fair number of tests that were doing simple queries of an external table.  I expected to see “external table read” wait events, but was surprised when we saw more “external table write” wait events than reads.

I thought this was due to writes to the “log” file and possible “bad” file, but I had to be sure.  I searched the docs but could not find reference to this wait event.  I specifically was seeing the following:

WAIT #139931007587096: nam='external table write' ela= 7 filectx=139931005791096 file#=13 size=41 obj#=-1 tim=1398264093597968
WAIT #139931007587096: nam='external table write' ela= 3 filectx=139931005791096 file#=13 size=89 obj#=-1 tim=1398264093597987

I searched on how to debug the filectx and file# but still couldn’t find anything.  So, I resorted to my good old friend “strace” from the Linux side of the house.  By running “strace” on the oracle shadow process, I was able to find indeed that these write events were to going to the LOG file for the external table.

mylap:EXTW glennf$ egrep 'write\(13' strace-truss-trace.txt
 write(13, "\n\n LOG file opened at 04/23/14 0"..., 41) = 41
 write(13, "KUP-05004: Warning: Intra sour"..., 100) = 100
 write(13, "Field Definitions for table ET_T"..., 36) = 36
 write(13, " Record format DELIMITED, delim"..., 43) = 43
 write(13, " Data in file has same endianne"..., 51) = 51
 write(13, " Rows with all null fields are "..., 41) = 41
 write(13, "\n", 1) = 1
 write(13, " Fields in Data Source: \n", 26) = 26
 write(13, "\n", 1) = 1
 write(13, " ID "..., 47) = 47
 write(13, " Terminated by \"7C\"\n", 25) = 25
 write(13, " Trim whitespace same as SQ"..., 41) = 41
 write(13, " TDATE "..., 46) = 46
  ....
  ....

Each time you open an external table, the time is logged as well the table definition.  We have some very wide tables so there was actually more data logged than queried.  With the proper amount of data now in the dat files, we are indeed seeing more “external table read” requests as expected.   Regardless, this was a fun exercise.

So, the moral of the story… Sometimes you have turn over a few rocks and drill down a bit to find the pot of gold.

 

Advertisements

0 Responses to ““external table write” wait events… but I am only running a query?”



  1. Leave a Comment

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





%d bloggers like this: