SYS.LOADER_DIR_OBJS Query: Privileges and Performance

Oracle Query On External Table and Data Pump hit performance problem when missing "Create/Drop Any Directory" Privileges. The triggered slow query is on SYS.LOADER_DIR_OBJS located in an Oracle internal package.

Oracle MOS has one matching Documentation:
    Slow Query On External Table If User Executing the Select Does Not Have "Create/Drop Any Directory" Privileges (Doc ID 2034938.1)

  Symptoms
    . Query against external tables perform slowly
    . User executing the select does not have "create any directory" or "drop any directory" privileges
    . The slowest query from the 10046 trace is the following recursive SQL:
        SELECT NAME, PATH, READ, WRITE, EXECUTE FROM SYS.LOADER_DIR_OBJS;
      
  Solution
    1. The solution is to gather statistics for the KZSROROL column as follows:
         exec dbms_stats.gather_table_stats(null,'X$KZSRO',method_opt=>'for columns KZSROROL',no_invalidate => false);
    2. Afterwards to make sure that these statistics will not get deleted by future runs of 
       DBMS_STATS.GATHER_FIXED_OBJECTS_STATS command, lock the statistics:
         exec dbms_stats.lock_table_stats('SYS','X$KZSRO');

This performance problem is worth of studying because it is directly related to System Privileges.

We will set up one System privilege and one Object privilege. Toggling both privileges, make 5 different tests:

  Test-1: Object privilege: YES; System privilege: YES
  Test-2: Object privilege: YES; System privilege: NO
  Test-3: Object privilege: NO;  System privilege: YES
  Test-4: Object privilege: NO;  System privilege: NO
  Test-5: Object privilege: NO;  System privilege: NO, and SQL Patch

The first two Tests are querying one external table; the next three are querying SYS.LOADER_DIR_OBJS. Test-5 is same as Test-4, but with SQL Patch applied.

Oracle 12.1 introduced SQL Patch (unofficial, undocumented) to tune packaged SQL Statements. Although it is possible to manipulate xplan with SQL Patch, but it cannot detour Oracle security to influence query result.

There is another related MOS Docu: Diagnosing a High Number of Executions Against Table SYS.DIR$ (Doc ID 2230171.1)

Note: All tests are done in Oracle 12.1.0.2 on Solaris.

1. Test Setup

sqlplus admin/admin123@TESTDB

drop user ksun cascade;

create user ksun identified by test123;

grant select any table, create any table, drop any table, create session, execute any procedure to ksun;

grant create any directory to ksun;
--revoke create any directory from ksun; 

create or replace directory test_dir as '/oradata/app/oracle/admin/testdb/io';

grant read, write on directory test_dir to ksun;
--revoke read, write on directory test_dir from ksun; 

exec dbms_stats.unlock_table_stats('SYS','X$KZSRO');

exec dbms_stats.gather_table_stats('SYS','X$KZSRO',method_opt=>'for columns KZSROROL',no_invalidate => false);

exec dbms_stats.lock_table_stats('SYS','X$KZSRO');

drop table testt1_ext;

create table testt1_ext
  organization external
    (type oracle_datapump 
     default directory test_dir 
     location ('testt1_ext.txt'))
  as select 321 x from dual;

2. Test-1: Object privilege: YES; System privilege: YES

sqlplus admin/admin123@TESTDB

grant read, write on directory test_dir to ksun;
grant create any directory to ksun;

select * from sys.dba_sys_privs where grantee = 'KSUN';

  GRAN PRIVILEGE              ADMIN_OPTION COMMON
  ---- ---------------------- ------------ ------
  KSUN CREATE TABLE           NO           NO
  KSUN EXECUTE ANY PROCEDURE  NO           NO
  KSUN DROP ANY TABLE         NO           NO
  KSUN CREATE ANY TABLE       NO           NO
  KSUN CREATE ANY DIRECTORY   NO           NO
  KSUN SELECT ANY TABLE       NO           NO
  KSUN CREATE SESSION         NO           NO

select grantee, table_name, privilege, type from sys.dba_tab_privs where grantee = 'KSUN';

  GRANTEE TABLE_NAME PRIVILEGE TYPE
  ------- ---------- --------- ---------
  KSUN    TEST_DIR   READ      DIRECTORY
  KSUN    TEST_DIR   WRITE     DIRECTORY

The query takes less than 1 second.

sqlplus ksun/test123@TESTDB

select sql_id, executions, rows_processed, buffer_gets, elapsed_time, cpu_time, last_load_time   --,v.* 
 from v$sql v where sql_id = 'f5ra7dru5fk5n';
 
  SQL_ID        EXECUTIONS ROWS_PROCESSED BUFFER_GETS ELAPSED_TIME   CPU_TIME LAST_LOAD_TIME
  ------------- ---------- -------------- ----------- ------------ ---------- -------------------
  f5ra7dru5fk5n          5           5660       32729       198104     198283 2018-02-19/08:38:40 

select * from testt1_ext;
  
           X
  ----------
         321
  
1 row selected.
Elapsed: 00:00:00.03

select sql_id, executions, rows_processed, buffer_gets, elapsed_time, cpu_time, last_load_time   --,v.* 
 from v$sql v where sql_id = 'f5ra7dru5fk5n';
 
   SQL_ID        EXECUTIONS ROWS_PROCESSED BUFFER_GETS ELAPSED_TIME   CPU_TIME LAST_LOAD_TIME
  ------------- ---------- -------------- ----------- ------------ ---------- -------------------
  f5ra7dru5fk5n          6           6792       37343       217328     217563 2018-02-19/08:38:40

(delta BUFFER_GETS=37343-32729=4,614, ELAPSED_TIME=217328-198104=19,224)

alter session set tracefile_identifier = 'ksun_trc_test1';
exec dbms_monitor.session_trace_enable(waits=>true, binds=>false, plan_stat=>'all_executions');
select * from testt1_ext;
exec dbms_monitor.session_trace_disable;

********************************************************************************

SQL ID: f5ra7dru5fk5n Plan Hash: 1161458834

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

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     1133      0.05       0.05          0       3474          0        1132
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1135      0.05       0.05          0       3474          0        1132

Rows (avg) Row Source Operation
---------- ---------------------------------------------------
      1132 VIEW  LOADER_DIR_OBJS (cr=4614 pr=0 pw=0 time=53950 us cost=1189 size=2381728 card=1132)
      1132  UNION-ALL  (cr=4614 pr=0 pw=0 time=53093 us)
      1132   NESTED LOOPS  (cr=4614 pr=0 pw=0 time=51662 us cost=1131 size=99528 card=1131)
      1132    NESTED LOOPS  (cr=3482 pr=0 pw=0 time=48085 us cost=1131 size=99528 card=1131)
      1132     FIXED TABLE FULL X$DIR (cr=1140 pr=0 pw=0 time=39002 us cost=0 size=65598 card=1131)
      1132     INDEX RANGE SCAN I_OBJ1 (cr=2342 pr=0 pw=0 time=3597 us cost=1 size=0 card=1)(object id 1591123)
      1132    TABLE ACCESS BY INDEX ROWID OBJ$ (cr=1132 pr=0 pw=0 time=2996 us cost=1 size=30 card=1)
         1     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=29 us cost=0 size=7 card=1)
         0   HASH GROUP BY (cr=0 pr=0 pw=0 time=411 us cost=58 size=105 card=1)
         0    FILTER  (cr=0 pr=0 pw=0 time=29 us)
         0     NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=57 size=105 card=1)
         0      NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=57 size=47 card=1)
         0       HASH JOIN RIGHT SEMI (cr=0 pr=0 pw=0 time=0 us cost=56 size=17 card=1)
         0        VIEW  VW_NSO_1 (cr=0 pr=0 pw=0 time=0 us cost=0 size=60 card=15)
         0         FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=60 card=15)
         0        TABLE ACCESS FULL OBJAUTH$ (cr=0 pr=0 pw=0 time=0 us cost=56 size=120471 card=9267)
         0       TABLE ACCESS BY INDEX ROWID BATCHED OBJ$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=30 card=1)
         0        INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 1591123)
         0      FIXED TABLE FIXED INDEX X$DIR (ind:1) (cr=0 pr=0 pw=0 time=0 us cost=0 size=58 card=1)
         1     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=27 us cost=0 size=7 card=1)

********************************************************************************

3. Test-2: Object privilege: YES; System privilege: NO

sqlplus admin/admin123@TESTDB

grant read, write on directory test_dir to ksun;
revoke create any directory from ksun; 

The query takes about 17 seconds because of subquery TABLE FULL X$KZSRO.

sqlplus ksun/test123@TESTDB

select sql_id, executions, rows_processed, buffer_gets, elapsed_time, cpu_time, last_load_time   --,v.* 
 from v$sql v where sql_id = 'f5ra7dru5fk5n';
 
   SQL_ID        EXECUTIONS ROWS_PROCESSED BUFFER_GETS ELAPSED_TIME   CPU_TIME LAST_LOAD_TIME
  ------------- ---------- -------------- ----------- ------------ ---------- -------------------
  f5ra7dru5fk5n          6           6792       37343       217328     217563 2018-02-19/08:38:40

select * from testt1_ext;

           X
  ----------
         321

1 row selected.
Elapsed: 00:00:17.93

select sql_id, executions, rows_processed, buffer_gets, elapsed_time, cpu_time, last_load_time   --,v.* 
 from v$sql v where sql_id = 'f5ra7dru5fk5n';

  SQL_ID        EXECUTIONS ROWS_PROCESSED BUFFER_GETS ELAPSED_TIME   CPU_TIME LAST_LOAD_TIME
  ------------- ---------- -------------- ----------- ------------ ---------- -------------------
  f5ra7dru5fk5n          7           6793      109986     18108955   18104124 2018-02-19/08:38:40

(delta BUFFER_GETS=109986-37343=72,643, ELAPSED_TIME=18108955-217328=17,891,627)

alter session set max_dump_file_size = UNLIMITED;
alter session set tracefile_identifier = 'ksun_trc_test2';
exec dbms_monitor.session_trace_enable(waits=>true, binds=>false, plan_stat=>'all_executions');
select * from testt1_ext;
alter session set session_cached_cursors = 0;
exec dbms_monitor.session_trace_disable;

alter session set session_cached_cursors = 200;

********************************************************************************

SQL ID: f5ra7dru5fk5n Plan Hash: 1161458834

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

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.03       0.03          0        235          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4     38.11      38.10          0      13750          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8     38.15      38.13          0      13985          0           2

Rows (avg) Row Source Operation
---------- ---------------------------------------------------
         1 VIEW  LOADER_DIR_OBJS (cr=77167 pr=0 pw=0 time=19800468 us cost=1189 size=2381728 card=1132)
         1  UNION-ALL  (cr=77167 pr=0 pw=0 time=19800465 us)
         0   NESTED LOOPS  (cr=5776 pr=0 pw=0 time=124426 us cost=1131 size=99528 card=1131)
      1132    NESTED LOOPS  (cr=5747 pr=0 pw=0 time=125176 us cost=1131 size=99528 card=1131)
      1132     FIXED TABLE FULL X$DIR (cr=5664 pr=0 pw=0 time=120445 us cost=0 size=65598 card=1131)
      1132     INDEX RANGE SCAN I_OBJ1 (cr=83 pr=0 pw=0 time=1644 us cost=1 size=0 card=1)(object id 1591123)
         0    TABLE ACCESS BY INDEX ROWID OBJ$ (cr=29 pr=0 pw=0 time=964 us cost=1 size=30 card=1)
         0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=30 us cost=0 size=7 card=1)
         1   HASH GROUP BY (cr=71391 pr=0 pw=0 time=19676026 us cost=58 size=105 card=1)
         2    FILTER  (cr=71391 pr=0 pw=0 time=19675276 us)
         2     NESTED LOOPS  (cr=71391 pr=0 pw=0 time=19675254 us cost=57 size=105 card=1)
     32313      NESTED LOOPS  (cr=6763 pr=0 pw=0 time=363426 us cost=57 size=47 card=1)
     32313       HASH JOIN RIGHT SEMI (cr=196 pr=0 pw=0 time=65194 us cost=56 size=17 card=1)
         2        VIEW  VW_NSO_1 (cr=0 pr=0 pw=0 time=10 us cost=0 size=60 card=15)
         2         FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=6 us cost=0 size=60 card=15)
     43515        TABLE ACCESS FULL OBJAUTH$ (cr=196 pr=0 pw=0 time=45666 us cost=56 size=120471 card=9267)
     32313       TABLE ACCESS BY INDEX ROWID BATCHED OBJ$ (cr=6567 pr=0 pw=0 time=227682 us cost=1 size=30 card=1)
     32313        INDEX RANGE SCAN I_OBJ1 (cr=4851 pr=0 pw=0 time=118484 us cost=1 size=0 card=1)(object id 1591123)
         2      FIXED TABLE FIXED INDEX X$DIR (ind:1) (cr=64628 pr=0 pw=0 time=19318193 us cost=0 size=58 card=1)
         0     FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=16 us cost=0 size=7 card=1)

********************************************************************************

4. Test-3: Object privilege: NO; System privilege: YES

sqlplus admin/admin123@TESTDB

revoke read, write on directory test_dir from ksun; 
grant create any directory to ksun;

Make the test:

sqlplus ksun/test123@TESTDB

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

  NAME       PATH                                READ   WRITE  EXECUTE
  ---------- ----------------------------------- ------ ------ -------
  TEST_DIR   /oradata/app/oracle/admin/testdb/io TRUE   TRUE   TRUE

1 row selected.
Elapsed: 00:00:00.04

The select returns 1 row, and takes less than 1 second.

Here the xplan:

sqlplus admin/admin123@TESTDB

select * from table(dbms_xplan.display_cursor('f5ra7dru5fk5n', null, 'ADVANCED ALLSTATS LAST'));

SQL_ID  f5ra7dru5fk5n, child number 0
-------------------------------------
SELECT NAME, PATH, READ, WRITE, EXECUTE FROM SYS.LOADER_DIR_OBJS
 
Plan hash value: 1161458834
 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name            | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                 |        |       |  1189 (100)|          |
|   1 |  VIEW                                     | LOADER_DIR_OBJS |   1132 |  2325K|  1189   (1)| 00:00:01 |
|   2 |   UNION-ALL                               |                 |        |       |            |          |
|   3 |    NESTED LOOPS                           |                 |   1131 | 99528 |  1131   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                          |                 |   1131 | 99528 |  1131   (0)| 00:00:01 |
|   5 |      FIXED TABLE FULL                     | X$DIR           |   1131 | 65598 |     0   (0)|          |
|*  6 |      INDEX RANGE SCAN                     | I_OBJ1          |      1 |       |     1   (0)| 00:00:01 |
|*  7 |     TABLE ACCESS BY INDEX ROWID           | OBJ$            |      1 |    30 |     1   (0)| 00:00:01 |
|*  8 |      FIXED TABLE FULL                     | X$KZSPR         |      1 |     7 |     0   (0)|          |
|   9 |    HASH GROUP BY                          |                 |      1 |   105 |    58   (2)| 00:00:01 |
|* 10 |     FILTER                                |                 |        |       |            |          |
|  11 |      NESTED LOOPS                         |                 |      1 |   105 |    57   (0)| 00:00:01 |
|  12 |       NESTED LOOPS                        |                 |      1 |    47 |    57   (0)| 00:00:01 |
|* 13 |        HASH JOIN RIGHT SEMI               |                 |      1 |    17 |    56   (0)| 00:00:01 |
|  14 |         VIEW                              | VW_NSO_1        |     15 |    60 |     0   (0)|          |
|  15 |          FIXED TABLE FULL                 | X$KZSRO         |     15 |    60 |     0   (0)|          |
|* 16 |         TABLE ACCESS FULL                 | OBJAUTH$        |   9267 |   117K|    56   (0)| 00:00:01 |
|  17 |        TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$            |      1 |    30 |     1   (0)| 00:00:01 |
|* 18 |         INDEX RANGE SCAN                  | I_OBJ1          |      1 |       |     1   (0)| 00:00:01 |
|* 19 |       FIXED TABLE FIXED INDEX             | X$DIR (ind:1)   |      1 |    58 |     0   (0)|          |
|* 20 |      FIXED TABLE FULL                     | X$KZSPR         |      1 |     7 |     0   (0)|          |
--------------------------------------------------------------------------------------------------------------

5. Test-4: Object privilege: NO; System privilege: NO

Revoke both privileges:

sqlplus admin/admin123@TESTDB

revoke read, write on directory test_dir from ksun; 
revoke create any directory from ksun;

Make the test:

sqlplus ksun/test123@TESTDB

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

no rows selected
Elapsed: 00:00:12.49

no rows selected, and takes about 12 seconds.

Here the xplan (same as Test-3):

sqlplus admin/admin123@TESTDB

select * from table(dbms_xplan.display_cursor('f5ra7dru5fk5n', null, 'ADVANCED ALLSTATS LAST'));

SQL_ID  f5ra7dru5fk5n, child number 0
-------------------------------------
SELECT NAME, PATH, READ, WRITE, EXECUTE FROM SYS.LOADER_DIR_OBJS
 
Plan hash value: 1161458834
 
-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name            | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                 |        |       |  1189 (100)|          |       |       |          |
|   1 |  VIEW                                     | LOADER_DIR_OBJS |   1132 |  2325K|  1189   (1)| 00:00:01 |       |       |          |
|   2 |   UNION-ALL                               |                 |        |       |            |          |       |       |          |
|   3 |    NESTED LOOPS                           |                 |   1131 | 99528 |  1131   (0)| 00:00:01 |       |       |          |
|   4 |     NESTED LOOPS                          |                 |   1131 | 99528 |  1131   (0)| 00:00:01 |       |       |          |
|   5 |      FIXED TABLE FULL                     | X$DIR           |   1131 | 65598 |     0   (0)|          |       |       |          |
|*  6 |      INDEX RANGE SCAN                     | I_OBJ1          |      1 |       |     1   (0)| 00:00:01 |       |       |          |
|*  7 |     TABLE ACCESS BY INDEX ROWID           | OBJ$            |      1 |    30 |     1   (0)| 00:00:01 |       |       |          |
|*  8 |      FIXED TABLE FULL                     | X$KZSPR         |      1 |     7 |     0   (0)|          |       |       |          |
|   9 |    HASH GROUP BY                          |                 |      1 |   105 |    58   (2)| 00:00:01 |   831K|   831K|          |
|* 10 |     FILTER                                |                 |        |       |            |          |       |       |          |
|  11 |      NESTED LOOPS                         |                 |      1 |   105 |    57   (0)| 00:00:01 |       |       |          |
|  12 |       NESTED LOOPS                        |                 |      1 |    47 |    57   (0)| 00:00:01 |       |       |          |
|* 13 |        HASH JOIN RIGHT SEMI               |                 |      1 |    17 |    56   (0)| 00:00:01 |  2293K|  2293K|  709K (0)|
|  14 |         VIEW                              | VW_NSO_1        |     15 |    60 |     0   (0)|          |       |       |          |
|  15 |          FIXED TABLE FULL                 | X$KZSRO         |     15 |    60 |     0   (0)|          |       |       |          |
|* 16 |         TABLE ACCESS FULL                 | OBJAUTH$        |   9267 |   117K|    56   (0)| 00:00:01 |       |       |          |
|  17 |        TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$            |      1 |    30 |     1   (0)| 00:00:01 |       |       |          |
|* 18 |         INDEX RANGE SCAN                  | I_OBJ1          |      1 |       |     1   (0)| 00:00:01 |       |       |          |
|* 19 |       FIXED TABLE FIXED INDEX             | X$DIR (ind:1)   |      1 |    58 |     0   (0)|          |       |       |          |
|* 20 |      FIXED TABLE FULL                     | X$KZSPR         |      1 |     7 |     0   (0)|          |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------

6. Test-5: Object privilege: NO; System privilege: NO, and SQL Patch

Since the recursive query is located in Oracle internal packaged application, SQL Patch is used to add hints.

One similar test was done by Blog: Slow external table access.

Revoke both privileges and create SQL Patch:

sqlplus admin/admin123@TESTDB

revoke read, write on directory test_dir from ksun; 
revoke create any directory from ksun;

begin
  sys.dbms_sqldiag_internal.i_create_patch(
    sql_text  => 'SELECT NAME, PATH, READ, WRITE, EXECUTE FROM SYS.LOADER_DIR_OBJS'
   ,hint_text => 'CARDINALITY(@"SEL$2" "D"@"SEL$2", 2) CARDINALITY(@"SEL$2" "O"@"SEL$2", 3) '||
                 'CARDINALITY(@"SEL$0C7B6CF6" "D"@"SEL$6" 4) '||
                 'USE_HASH(@"SEL$2" "D"@"SEL$2" O@SEL$2) USE_HASH(@"SEL$0C7B6CF6" "D"@"SEL$6")'
   ,name      => 'SQLPATCH123');
end;
/

select name, created, status from dba_sql_patches where name in ('SQLPATCH123');

  NAME        CREATED             STATUS
  ----------- ------------------- -------
  SQLPATCH123 9-FEB-2018 11:15:22 ENABLED

select dbms_lob.substr(comp_data, 80, 1) hint, signature from sys.SQLOBJ$DATA t;

  HINT                                                                                         SIGNATURE
  -------------------------------------------------------------------------------- ---------------------
  <outline_data><hint><![CDATA[CARDINALITY(@"SEL$2" "D"@"SEL$2", 2) CARDINALITY(@"    516452634368408982
  <outline_data><hint><![CDATA[      IGNORE_OPTIM_EMBEDDED_HINTS                     9073911181602513588

alter system flush shared_pool;

Make the test:

sqlplus ksun/test123@TESTDB

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

no rows selected
Elapsed: 00:00:00.29

Although query is fast (less than 1 second), but no rows selected. Therefore, it is not possible to bypass Oracle Security by SQL Patch.

Xplan confirms that the SQL Patch was applied (also indicated in Note: SQL patch "SQLPATCH123") because it is different with that of Test-4.

sqlplus admin/admin123@TESTDB

select * from table(dbms_xplan.display_cursor('f5ra7dru5fk5n', null, 'ADVANCED ALLSTATS LAST'));

SQL_ID  f5ra7dru5fk5n, child number 0
-------------------------------------
SELECT NAME, PATH, READ, WRITE, EXECUTE FROM SYS.LOADER_DIR_OBJS
 
Plan hash value: 153645881
 
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name            | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                 |        |       |  3522 (100)|          |       |       |          |
|   1 |  VIEW                                      | LOADER_DIR_OBJS |      3 |  6312 |  3522   (1)| 00:00:01 |       |       |          |
|   2 |   UNION-ALL                                |                 |        |       |            |          |       |       |          |
|*  3 |    HASH JOIN                               |                 |      2 |   176 |  3515   (0)| 00:00:01 |   964K|   964K| 1297K (0)|
|   4 |     FIXED TABLE FULL                       | X$DIR           |      2 |   116 |     0   (0)|          |       |       |          |
|*  5 |     INDEX FAST FULL SCAN                   | I_OBJ2          |      3 |    90 |  3515   (0)| 00:00:01 |       |       |          |
|*  6 |      FIXED TABLE FULL                      | X$KZSPR         |      1 |     7 |     0   (0)|          |       |       |          |
|   7 |    HASH GROUP BY                           |                 |      1 |   105 |     7  (15)| 00:00:01 |   831K|   831K|          |
|*  8 |     FILTER                                 |                 |        |       |            |          |       |       |          |
|*  9 |      HASH JOIN SEMI                        |                 |      1 |   105 |     6   (0)| 00:00:01 |  1927K|  1186K| 2361K (0)|
|  10 |       NESTED LOOPS                         |                 |      1 |   101 |     6   (0)| 00:00:01 |       |       |          |
|  11 |        NESTED LOOPS                        |                 |      4 |   352 |     4   (0)| 00:00:01 |       |       |          |
|  12 |         FIXED TABLE FULL                   | X$DIR           |      4 |   232 |     0   (0)|          |       |       |          |
|  13 |         TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$            |      1 |    30 |     1   (0)| 00:00:01 |       |       |          |
|* 14 |          INDEX RANGE SCAN                  | I_OBJ1          |      1 |       |     1   (0)| 00:00:01 |       |       |          |
|* 15 |        INDEX RANGE SCAN                    | I_OBJAUTH1      |      1 |    13 |     1   (0)| 00:00:01 |       |       |          |
|  16 |       VIEW                                 | VW_NSO_1        |     15 |    60 |     0   (0)|          |       |       |          |
|  17 |        FIXED TABLE FULL                    | X$KZSRO         |     15 |    60 |     0   (0)|          |       |       |          |
|* 18 |      FIXED TABLE FULL                      | X$KZSPR         |      1 |     7 |     0   (0)|          |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SET$1        / LOADER_DIR_OBJS@SEL$1
   2 - SET$1       
   3 - SEL$2       
   4 - SEL$2        / D@SEL$2
   5 - SEL$2        / O@SEL$2
   6 - SEL$C8360722 / X$KZSPR@SEL$5
   7 - SEL$0C7B6CF6
  12 - SEL$0C7B6CF6 / D@SEL$6
  13 - SEL$0C7B6CF6 / O@SEL$6
  14 - SEL$0C7B6CF6 / O@SEL$6
  15 - SEL$0C7B6CF6 / OA@SEL$6
  16 - SEL$3C459230 / VW_NSO_1@SEL$0C7B6CF6
  17 - SEL$3C459230 / X$KZSRO@SEL$7
  18 - SEL$2F35337B / X$KZSPR@SEL$10

Note
-----
   - SQL patch "SQLPATCH123" used for this statement

After above test, drop used SQL patch:

sqlplus admin/admin123@TESTDB

exec dbms_sqldiag.drop_sql_patch('SQLPATCH123');

6. Discussions

In summary:

    With System privilege (Test-1 and Test-3), queries on external table and SYS.LOADER_DIR_OBJS are fast.
    Without System privilege (Test-2 and Test-4), queries are slow.
    Without System privilege (Test-5), but with SQL Patch, query is fast, but no rows selected.

Therefore, above 5 Tests showed that query result and performance depend on faultless System privilege.

Actually, the query on SYS.LOADER_DIR_OBJS:

  select name, path, read, write, execute from sys.loader_dir_objs;

is a recursive statement when accessing external table:

  select * from testt1_ext;

The performance is caused by the recursive statement.

In case of missing System privilege, "SQL Patch" can be used to manipulate its xplan, and makes it fast. But the result is different in comparing to query with System privilege.

Looking the difference of SQL Trace xplan between Test-1 (fast) and Test-2 (slow), we can see that subquery on X$KZSRO in fast xplan returns 0 rows, but in slow xplan returns 2 rows, and hence generates expensive NESTED LOOPS.

Here the definition of SYS.LOADER_DIR_OBJS:

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;