Slow external table access

We had an interesting issue on 12.1.0.1, where users were reporting very slow performance on queries to external tables. When I tried to replicate the problem, everything seemed just fine, so I initially reported back the familiar "Well, it works on my PC" 🙂 [Just kidding]

Anyway, connecting by proxy to one of their accounts, did reveal the error, which suggested something to do with privileges.  A sql trace revealed that the performance was due to a query which appears to get the list of directories and their privileges:

SELECT NAME, PATH, READ, WRITE, EXECUTE FROM SYS.LOADER_DIR_OBJS

the definition of which was:

create or replace force view sys.loader_dir_objs
(name, path, read, write, execute)
bequeath definer
as
select o.name
      ,d.os_path
      ,'TRUE'
      ,'TRUE'
      ,'TRUE'
from   sys.obj$ o
      ,sys.x$dir d
where  o.obj# = d.obj#
and    (o.owner# = uid
or      exists
          (select null
           from   v$enabledprivs
           where  priv_number in (-177
                                 ,-178)))
union all
select 
       o.name
      ,d.os_path
      ,decode(sum(decode(oa.privilege#, 17, 1, 0)), 0, 'FALSE', 'TRUE')
      ,decode(sum(decode(oa.privilege#, 18, 1, 0)), 0, 'FALSE', 'TRUE')
      ,decode(sum(decode(oa.privilege#, 12, 1, 0)), 0, 'FALSE', 'TRUE')
from   sys.obj$ o
      ,sys.x$dir d
      ,sys.objauth$ oa
where  o.obj# = d.obj#
and    oa.obj# = o.obj#
and    oa.privilege# in (12
                        ,17
                        ,18)
and    oa.grantee# in (select kzsrorol
                       from   x$kzsro)
and    not (o.owner# = uid
or          exists
              (select null
               from   v$enabledprivs
               where  priv_number in (-177
                                     ,-178)))
group by o.name
        ,d.os_path;

Re-gathering dictionary and fixed object stats yielded no benefit, so I tinkered with the view definition to come up with a faster version, which was this:

create or replace force view sys.loader_dir_objs
(name, path, read, write, execute)
bequeath definer
as
select o.name
      ,d.os_path
      ,'TRUE'
      ,'TRUE'
      ,'TRUE'
from   sys.obj$ o
      ,sys.x$dir d
where  o.obj# = d.obj#
and    (o.owner# = uid
or      exists
          (select null
           from   v$enabledprivs
           where  priv_number in (-177
                                 ,-178)))
union all
select /*+ leading(d o oa) */
       o.name
      ,d.os_path
      ,decode(sum(decode(oa.privilege#, 17, 1, 0)), 0, 'FALSE', 'TRUE')
      ,decode(sum(decode(oa.privilege#, 18, 1, 0)), 0, 'FALSE', 'TRUE')
      ,decode(sum(decode(oa.privilege#, 12, 1, 0)), 0, 'FALSE', 'TRUE')
from   sys.obj$ o
      ,sys.x$dir d
      ,sys.objauth$ oa
where  o.obj# = d.obj#
and    oa.obj# = o.obj#
and    oa.privilege# in (12
                        ,17
                        ,18)
and    oa.grantee# in (select kzsrorol
                       from   x$kzsro)
and    not (o.owner# = uid
or          exists
              (select null
               from   v$enabledprivs
               where  priv_number in (-177
                                     ,-178)))
group by o.name
        ,d.os_path;

[Note: If you’re having the same issue, before you race out as use the same hints, then be aware that the hints above work for us because the number of directories in our database is very small.  If you’ve got lots of directories defined, this might not be the best approach for you]

The challenge now was getting this performance benefit without actually hacking the dictionary view (which means calls to MOS..and well…that’s no way to be starting the new year :-))

So initially, I used the standard technique of applying a baseline, but encountered some dramas (more on that in a later post).  Then, following a suggestion from an OakTable colleague (http://www.oaktable.net/users/lothar) I ventured down the path of "sql patch":

I put my ‘corrected’ view in place, issued the problem query, and got the full plan using the DBMS_XPLAN with the +OUTLINE parameter.  I put the original version of the view back in place, and then attempted to patch in the altered plan as thus:

declare
  l_sql  clob;
  l_hint clob :=
'[the full text of the plan outline]';
begin
  --
  -- '23ka1fq59wg0b' is the sqlid for my problem query.  
  --
  select sql_text into l_sql from v$sql where sql_id = '23ka1fq59wg0b';

  sys.dbms_sqldiag_internal.i_create_patch(
    sql_text=>l_sql,
    hint_text=>l_hint,
    name=>'patch_LOADER_DIR_OBJS');
end;

This failed with a PL/SQL error, because the hint text for sql patch is limited to 500 characters.  So then it was a case of stripping out hints that were "redundant" [Note: This is generally a bad idea, because there’s a strong argument to be made that no hint is redundant].  But once within 500 chars, the patch was applied, and external table performance is now fine for all users.

One thought on “Slow external table access

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