We are an ETL shop and make heavy use of external tables. Typically these tables are queried to populated staging tables. I am surprised at the time it takes to for queries to return data from the external tables.
Typically there is around a 15 second delay before any result is returned. This is true even in the cases when the data file contains no data and when the data file does not exist. The delay doesn't seem related to the number of rows in the file.
I am logging into the database server itself, on which the external table data files are located.
Is this expected behaviour?
File system operations (ls, vim) at least on smaller files happen with no delay.
All files on local disk.
Oracle 12.1.
Oracle Linux Server release 6.6
I would recommend reviewing or looking into release Oracle 12.2 notes. There was a Patch for both the Big Data Appliance Firmware (22911748) for Exadata and a fix made in 12.2.
It addresses a view that is specific to the access to external tables. It's possible that you are impacted by this view. The view name is LOADER_DIR_OBJS, which is used to query the directory that external tables point to.
Our customers are running into very similar issues, and Oracle recommended installing the 12.2 release which contains the patch.
So, we are currently testing the 12.2 release. Anytime an external table is read, it has to have access to the LOADER_DIR_OBJS system view. Typically, the poor performance comes from this view, which accesses the SYS.OBJ$ and SYS.X$DIR system object because query plan is not optimal. Some people have found work arounds. (See Oracle Workaround Document ID 2034938.1 to see if it applies to you).