Posted by 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.
Learning is not a spectator sport 
Hi,
Probably DBMS_SQLTUNE.IMPORT_SQL_PROFILE (http://kerryosborne.oracle-guy.com/2010/07/sqlt-coe_xfr_sql_profilesql/) can provide the same result without need to cut off Stored Outline.