SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL Response time is very high

RengudiSep 27 2016 — edited Oct 5 2016

Hi Gurus,

I am working in oracle 11g R2.

I have slow response time on the following query. Which is generic query

  SELECT TABLESPACE_NAME, SUM (bytes) / 1024 / 1024 / 1024
    FROM dba_data_files
   WHERE TABLESPACE_NAME NOT IN (SELECT TABLESPACE_NAME
                                   FROM dba_segments a, dba_users b
                                  WHERE        b.username = a.owner
                                           AND (   SUBSTR (b.account_status,
                                                           1,
                                                           6) <> 'LOCKED'
                                                OR SUBSTR (b.account_status,
                                                           -6,
                                                           6) <> 'LOCKED')
                                        OR     username IN (SELECT GRANTOR
                                                              FROM DBA_TAB_PRIVS)
                                           AND TABLESPACE_NAME IS NOT NULL
                                 UNION
                                 SELECT DEFAULT_TABLESPACE TABLESPACE_NAME
                                   FROM dba_users
                                  WHERE    (   SUBSTR (account_status, 1, 6) <>
                                                  'LOCKED'
                                            OR SUBSTR (account_status, -6, 6) <>
                                                  'LOCKED')
                                        OR     username IN (SELECT GRANTOR
                                                              FROM DBA_TAB_PRIVS)
                                           AND DEFAULT_TABLESPACE IS NOT NULL
                                 UNION
                                 SELECT TABLESPACE_NAME
                                   FROM dba_tables a, dba_users b
                                  WHERE        b.username = a.owner
                                           AND (   SUBSTR (b.account_status,
                                                           1,
                                                           6) <> 'LOCKED'
                                                OR SUBSTR (b.account_status,
                                                           -6,
                                                           6) <> 'LOCKED')
                                        OR     username IN (SELECT GRANTOR
                                                              FROM DBA_TAB_PRIVS)
                                           AND TABLESPACE_NAME IS NOT NULL
                                 UNION
                                 SELECT TABLESPACE_NAME
                                   FROM dba_indexes a, dba_users b
                                  WHERE        b.username = a.owner
                                           AND (   SUBSTR (b.account_status,
                                                           1,
                                                           6) <> 'LOCKED'
                                                OR SUBSTR (b.account_status,
                                                           -6,
                                                           6) <> 'LOCKED')
                                        OR     username IN (SELECT GRANTOR
                                                              FROM DBA_TAB_PRIVS)
                                           AND TABLESPACE_NAME IS NOT NULL)
GROUP BY TABLESPACE_NAME;

Explain Plan output

Plan hash value: 961979550
------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                         |     9 |   270 |       | 64936   (1)| 00:13:00 |
|   1 |  HASH GROUP BY                             |                         |     9 |   270 |       | 64936   (1)| 00:13:00 |
|*  2 |   FILTER                                   |                         |       |       |       |            |          |
|   3 |    VIEW                                    | DBA_DATA_FILES          |   177 |  5310 |       |    15  (14)| 00:00:01 |
|   4 |     UNION-ALL                              |                         |       |       |       |            |          |
|*  5 |      HASH JOIN                             |                         |     1 |    89 |       |     4  (25)| 00:00:01 |
|   6 |       NESTED LOOPS                         |                         |     1 |    38 |       |     3   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                        |                         |     1 |    22 |       |     2   (0)| 00:00:01 |
|*  8 |         TABLE ACCESS FULL                  | FILE$                   |     1 |    18 |       |     2   (0)| 00:00:01 |
|*  9 |         FIXED TABLE FIXED INDEX            | X$KCCFE (ind:1)         |     1 |     4 |       |     0   (0)| 00:00:01 |
|  10 |        TABLE ACCESS CLUSTER                | TS$                     |     1 |    16 |       |     1   (0)| 00:00:01 |
|* 11 |         INDEX UNIQUE SCAN                  | I_TS#                   |     1 |       |       |     0   (0)| 00:00:01 |
|* 12 |       FIXED TABLE FULL                     | X$KCCFN                 |   176 |  8976 |       |     0   (0)| 00:00:01 |
|  13 |      NESTED LOOPS                          |                         |   176 | 16720 |       |    11  (10)| 00:00:01 |
|* 14 |       HASH JOIN                            |                         |   176 | 16016 |       |    11  (10)| 00:00:01 |
|  15 |        TABLE ACCESS FULL                   | TS$                     |    21 |   336 |       |     8   (0)| 00:00:01 |
|  16 |        NESTED LOOPS                        |                         |   176 | 13200 |       |     3  (34)| 00:00:01 |
|* 17 |         HASH JOIN                          |                         |   176 | 10912 |       |     3  (34)| 00:00:01 |
|* 18 |          TABLE ACCESS FULL                 | FILE$                   |   197 |  2167 |       |     2   (0)| 00:00:01 |
|* 19 |          FIXED TABLE FULL                  | X$KCCFN                 |   176 |  8976 |       |     0   (0)| 00:00:01 |
|* 20 |         FIXED TABLE FIXED INDEX            | X$KTFBHC (ind:1)        |     1 |    13 |       |     0   (0)| 00:00:01 |
|* 21 |       FIXED TABLE FIXED INDEX              | X$KCCFE (ind:1)         |     1 |     4 |       |     0   (0)| 00:00:01 |
|  22 |    SORT UNIQUE                             |                         | 12723 |  1704K|  1640K| 65272   (5)| 00:13:04 |
|  23 |     UNION-ALL                              |                         |       |       |       |            |          |
|  24 |      CONCATENATION                         |                         |       |       |       |            |          |
|* 25 |       FILTER                               |                         |       |       |       |            |          |
|  26 |        MERGE JOIN CARTESIAN                |                         | 11800 |  1521K|       | 60669   (1)| 00:12:09 |
|* 27 |         HASH JOIN                          |                         |    33 |  3234 |       |    32  (10)| 00:00:01 |
|* 28 |          HASH JOIN                         |                         |    33 |  3135 |       |    24  (13)| 00:00:01 |
|* 29 |           HASH JOIN                        |                         |    33 |  3036 |       |    15  (14)| 00:00:01 |
|* 30 |            HASH JOIN                       |                         |    33 |  2409 |       |    13  (16)| 00:00:01 |
|* 31 |             HASH JOIN OUTER                |                         |    33 |  2310 |       |    10  (10)| 00:00:01 |
|* 32 |              HASH JOIN                     |                         |    33 |  1485 |       |     8  (13)| 00:00:01 |
|  33 |               MERGE JOIN CARTESIAN         |                         |     2 |    36 |       |     4   (0)| 00:00:01 |
|* 34 |                TABLE ACCESS FULL           | PROFILE$                |     1 |     9 |       |     2   (0)| 00:00:01 |
|  35 |                BUFFER SORT                 |                         |     2 |    18 |       |     2   (0)| 00:00:01 |
|* 36 |                 TABLE ACCESS FULL          | PROFILE$                |     2 |    18 |       |     2   (0)| 00:00:01 |
|* 37 |               TABLE ACCESS FULL            | USER$                   |    52 |  1404 |       |     3   (0)| 00:00:01 |
|* 38 |              TABLE ACCESS FULL             | RESOURCE_GROUP_MAPPING$ |     1 |    25 |       |     2   (0)| 00:00:01 |
|  39 |             TABLE ACCESS FULL              | PROFNAME$               |     3 |     9 |       |     2   (0)| 00:00:01 |
|  40 |            TABLE ACCESS FULL               | USER_ASTATUS_MAP        |     9 |   171 |       |     2   (0)| 00:00:01 |
|  41 |           TABLE ACCESS FULL                | TS$                     |    21 |    63 |       |     8   (0)| 00:00:01 |
|  42 |          TABLE ACCESS FULL                 | TS$                     |    21 |    63 |       |     8   (0)| 00:00:01 |
|  43 |         BUFFER SORT                        |                         |   358 | 12172 |       | 60661   (1)| 00:12:08 |
|* 44 |          VIEW                              | SYS_DBA_SEGS            |   358 | 12172 |       |  1837   (1)| 00:00:23 |
|  45 |           UNION-ALL                        |                         |       |       |       |            |          |
|* 46 |            HASH JOIN RIGHT OUTER           |                         |   182 | 21476 |       |  1688   (1)| 00:00:21 |
|  47 |             TABLE ACCESS FULL              | USER$                   |    97 |  1455 |       |     3   (0)| 00:00:01 |
|  48 |             NESTED LOOPS                   |                         |   182 | 18746 |       |  1685   (1)| 00:00:21 |
|  49 |              NESTED LOOPS                  |                         |   182 | 16744 |       |  1503   (1)| 00:00:19 |
|* 50 |               HASH JOIN                    |                         |   182 | 15470 |       |  1503   (1)| 00:00:19 |
|  51 |                NESTED LOOPS                |                         |   711 | 19197 |       |    71   (0)| 00:00:01 |
|  52 |                 TABLE ACCESS BY INDEX ROWID| TS$                     |     1 |    12 |       |     1   (0)| 00:00:01 |
|* 53 |                  INDEX UNIQUE SCAN         | I_TS1                   |     1 |       |       |     0   (0)| 00:00:01 |
|  54 |                 TABLE ACCESS CLUSTER       | SEG$                    |   711 | 10665 |       |    70   (0)| 00:00:01 |
|* 55 |                  INDEX RANGE SCAN          | I_FILE#_BLOCK#          |     1 |       |       |     3   (0)| 00:00:01 |
|  56 |                VIEW                        | SYS_OBJECTS             | 10173 |   576K|       |  1431   (1)| 00:00:18 |
|  57 |                 UNION-ALL                  |                         |       |       |       |            |          |
|* 58 |                  TABLE ACCESS FULL         | TAB$                    |  2851 | 65573 |       |   354   (0)| 00:00:05 |
|  59 |                  TABLE ACCESS FULL         | TABPART$                |   252 |  4284 |       |     5   (0)| 00:00:01 |
|  60 |                  TABLE ACCESS FULL         | CLU$                    |    10 |   130 |       |   354   (0)| 00:00:05 |
|* 61 |                  TABLE ACCESS FULL         | IND$                    |  6012 |   111K|       |   354   (0)| 00:00:05 |
|  62 |                  TABLE ACCESS FULL         | INDPART$                |   270 |  4590 |       |     6   (0)| 00:00:01 |
|* 63 |                  TABLE ACCESS FULL         | LOB$                    |   775 | 15500 |       |   351   (0)| 00:00:05 |
|  64 |                  TABLE ACCESS FULL         | TABSUBPART$             |     1 |    52 |       |     2   (0)| 00:00:01 |
|  65 |                  TABLE ACCESS FULL         | INDSUBPART$             |     1 |    52 |       |     2   (0)| 00:00:01 |
|  66 |                  TABLE ACCESS FULL         | LOBFRAG$                |     1 |    16 |       |     2   (0)| 00:00:01 |
|* 67 |               INDEX UNIQUE SCAN            | I_FILE2                 |     1 |     7 |       |     0   (0)| 00:00:01 |
|* 68 |              TABLE ACCESS BY INDEX ROWID   | OBJ$                    |     1 |    11 |       |     1   (0)| 00:00:01 |
|* 69 |               INDEX UNIQUE SCAN            | I_OBJ1                  |     1 |       |       |     0   (0)| 00:00:01 |
|  70 |            NESTED LOOPS                    |                         |     1 |    65 |       |    75   (2)| 00:00:01 |
|  71 |             NESTED LOOPS OUTER             |                         |     1 |    58 |       |    75   (2)| 00:00:01 |
|* 72 |              HASH JOIN                     |                         |     1 |    43 |       |    74   (2)| 00:00:01 |
|  73 |               NESTED LOOPS                 |                         |    10 |   300 |       |    71   (0)| 00:00:01 |
|  74 |                TABLE ACCESS BY INDEX ROWID | TS$                     |     1 |    12 |       |     1   (0)| 00:00:01 |
|* 75 |                 INDEX UNIQUE SCAN          | I_TS1                   |     1 |       |       |     0   (0)| 00:00:01 |
|* 76 |                TABLE ACCESS CLUSTER        | SEG$                    |    10 |   180 |       |    70   (0)| 00:00:01 |
|* 77 |                 INDEX RANGE SCAN           | I_FILE#_BLOCK#          |     1 |       |       |     3   (0)| 00:00:01 |
|* 78 |               TABLE ACCESS FULL            | UNDO$                   |   142 |  1846 |       |     2   (0)| 00:00:01 |
|  79 |              TABLE ACCESS CLUSTER          | USER$                   |     1 |    15 |       |     1   (0)| 00:00:01 |
|* 80 |               INDEX UNIQUE SCAN            | I_USER#                 |     1 |       |       |     0   (0)| 00:00:01 |
|* 81 |             INDEX UNIQUE SCAN              | I_FILE2                 |     1 |     7 |       |     0   (0)| 00:00:01 |
|  82 |            NESTED LOOPS                    |                         |   175 |  8225 |       |    75   (2)| 00:00:01 |
|* 83 |             HASH JOIN OUTER                |                         |   175 |  7000 |       |    75   (2)| 00:00:01 |
|  84 |              NESTED LOOPS                  |                         |   175 |  4375 |       |    71   (0)| 00:00:01 |
|  85 |               TABLE ACCESS BY INDEX ROWID  | TS$                     |     1 |    12 |       |     1   (0)| 00:00:01 |
|* 86 |                INDEX UNIQUE SCAN           | I_TS1                   |     1 |       |       |     0   (0)| 00:00:01 |
|* 87 |               TABLE ACCESS CLUSTER         | SEG$                    |   175 |  2275 |       |    70   (0)| 00:00:01 |
|* 88 |                INDEX RANGE SCAN            | I_FILE#_BLOCK#          |     1 |       |       |     3   (0)| 00:00:01 |
|  89 |              TABLE ACCESS FULL             | USER$                   |    97 |  1455 |       |     3   (0)| 00:00:01 |
|* 90 |             INDEX UNIQUE SCAN              | I_FILE2                 |     1 |     7 |       |     0   (0)| 00:00:01 |
|* 91 |        HASH JOIN                           |                         |   774 | 37152 |       |   242   (2)| 00:00:03 |
|  92 |         TABLE ACCESS FULL                  | USER$                   |    97 |   291 |       |     3   (0)| 00:00:01 |
|* 93 |         HASH JOIN                          |                         |   774 | 34830 |       |   238   (1)| 00:00:03 |
|* 94 |          HASH JOIN                         |                         |   774 | 28638 |       |    31   (4)| 00:00:01 |
|  95 |           TABLE ACCESS FULL                | USER$                   |    97 |   291 |       |     3   (0)| 00:00:01 |
|* 96 |           HASH JOIN                        |                         |   774 | 26316 |       |    28   (4)| 00:00:01 |
|  97 |            INDEX FULL SCAN                 | I_TABLE_PRIVILEGE_MAP   |    24 |    72 |       |     1   (0)| 00:00:01 |
|  98 |            NESTED LOOPS                    |                         |   774 | 23994 |       |    26   (0)| 00:00:01 |
|  99 |             TABLE ACCESS BY INDEX ROWID    | USER$                   |     1 |    15 |       |     1   (0)| 00:00:01 |
|*100 |              INDEX UNIQUE SCAN             | I_USER1                 |     1 |       |       |     0   (0)| 00:00:01 |
|*101 |             TABLE ACCESS FULL              | OBJAUTH$                |   774 | 12384 |       |    25   (0)| 00:00:01 |
| 102 |          TABLE ACCESS FULL                 | OBJ$                    | 62067 |   484K|       |   207   (1)| 00:00:03 |
|*103 |       FILTER                               |                         |       |       |       |            |          |
|*104 |        HASH JOIN                           |                         |    27 |  3564 |       |  1867   (1)| 00:00:23 |
| 105 |         NESTED LOOPS                       |                         |     7 |   686 |       |    29   (7)| 00:00:01 |
| 106 |          NESTED LOOPS                      |                         |     7 |   665 |       |    22  (10)| 00:00:01 |
|*107 |           HASH JOIN OUTER                  |                         |     7 |   644 |       |    15  (14)| 00:00:01 |
|*108 |            HASH JOIN                       |                         |     7 |   469 |       |    13  (16)| 00:00:01 |
|*109 |             HASH JOIN                      |                         |     7 |   448 |       |    10  (10)| 00:00:01 |
|*110 |              HASH JOIN                     |                         |     9 |   495 |       |     8  (13)| 00:00:01 |
| 111 |               MERGE JOIN CARTESIAN         |                         |     1 |    28 |       |     4   (0)| 00:00:01 |
|*112 |                TABLE ACCESS FULL           | PROFILE$                |     1 |     9 |       |     2   (0)| 00:00:01 |
| 113 |                BUFFER SORT                 |                         |     1 |    19 |       |     2   (0)| 00:00:01 |
|*114 |                 TABLE ACCESS FULL          | USER_ASTATUS_MAP        |     1 |    19 |       |     2   (0)| 00:00:01 |
|*115 |               TABLE ACCESS FULL            | USER$                   |    52 |  1404 |       |     3   (0)| 00:00:01 |
|*116 |              TABLE ACCESS FULL             | PROFILE$                |     2 |    18 |       |     2   (0)| 00:00:01 |
| 117 |             TABLE ACCESS FULL              | PROFNAME$               |     3 |     9 |       |     2   (0)| 00:00:01 |
|*118 |            TABLE ACCESS FULL               | RESOURCE_GROUP_MAPPING$ |     1 |    25 |       |     2   (0)| 00:00:01 |
| 119 |           TABLE ACCESS CLUSTER             | TS$                     |     1 |     3 |       |     1   (0)| 00:00:01 |
|*120 |            INDEX UNIQUE SCAN               | I_TS#                   |     1 |       |       |     0   (0)| 00:00:01 |
| 121 |          TABLE ACCESS CLUSTER              | TS$                     |     1 |     3 |       |     1   (0)| 00:00:01 |
|*122 |           INDEX UNIQUE SCAN                | I_TS#                   |     1 |       |       |     0   (0)| 00:00:01 |
| 123 |         VIEW                               | SYS_DBA_SEGS            |   358 | 12172 |       |  1837   (1)| 00:00:23 |
| 124 |          UNION-ALL                         |                         |       |       |       |            |          |
|*125 |           HASH JOIN RIGHT OUTER            |                         |   182 | 21476 |       |  1688   (1)| 00:00:21 |
| 126 |            TABLE ACCESS FULL               | USER$                   |    97 |  1455 |       |     3   (0)| 00:00:01 |
| 127 |            NESTED LOOPS                    |                         |   182 | 18746 |       |  1685   (1)| 00:00:21 |
| 128 |             NESTED LOOPS                   |                         |   182 | 16744 |       |  1503   (1)| 00:00:19 |
|*129 |              HASH JOIN                     |                         |   182 | 15470 |       |  1503   (1)| 00:00:19 |
| 130 |               NESTED LOOPS                 |                         |   711 | 19197 |       |    71   (0)| 00:00:01 |
| 131 |                TABLE ACCESS BY INDEX ROWID | TS$                     |     1 |    12 |       |     1   (0)| 00:00:01 |
|*132 |                 INDEX UNIQUE SCAN          | I_TS1                   |     1 |       |       |     0   (0)| 00:00:01 |
| 133 |                TABLE ACCESS CLUSTER        | SEG$                    |   711 | 10665 |       |    70   (0)| 00:00:01 |
|*134 |                 INDEX RANGE SCAN           | I_FILE#_BLOCK#          |     1 |       |       |     3   (0)| 00:00:01 |
| 135 |               VIEW                         | SYS_OBJECTS             | 10173 |   576K|       |  1431   (1)| 00:00:18 |
| 136 |                UNION-ALL                   |                         |       |       |       |            |          |
|*137 |                 TABLE ACCESS FULL          | TAB$                    |  2851 | 65573 |       |   354   (0)| 00:00:05 |
| 138 |                 TABLE ACCESS FULL          | TABPART$                |   252 |  4284 |       |     5   (0)| 00:00:01 |
| 139 |                 TABLE ACCESS FULL          | CLU$                    |    10 |   130 |       |   354   (0)| 00:00:05 |
|*140 |                 TABLE ACCESS FULL          | IND$                    |  6012 |   111K|       |   354   (0)| 00:00:05 |
| 141 |                 TABLE ACCESS FULL          | INDPART$                |   270 |  4590 |       |     6   (0)| 00:00:01 |
|*142 |                 TABLE ACCESS FULL          | LOB$                    |   775 | 15500 |       |   351   (0)| 00:00:05 |
| 143 |                 TABLE ACCESS FULL          | TABSUBPART$             |     1 |    52 |       |     2   (0)| 00:00:01 |
| 144 |                 TABLE ACCESS FULL          | INDSUBPART$             |     1 |    52 |       |     2   (0)| 00:00:01 |
| 145 |                 TABLE ACCESS FULL          | LOBFRAG$                |     1 |    16 |       |     2   (0)| 00:00:01 |
|*146 |              INDEX UNIQUE SCAN             | I_FILE2                 |     1 |     7 |       |     0   (0)| 00:00:01 |
|*147 |             TABLE ACCESS BY INDEX ROWID    | OBJ$                    |     1 |    11 |       |     1   (0)| 00:00:01 |
|*148 |              INDEX UNIQUE SCAN             | I_OBJ1                  |     1 |       |       |     0   (0)| 00:00:01 |
| 149 |           NESTED LOOPS                     |                         |     1 |    65 |       |    75   (2)| 00:00:01 |
| 150 |            NESTED LOOPS OUTER              |                         |     1 |    58 |       |    75   (2)| 00:00:01 |
|*151 |             HASH JOIN                      |                         |     1 |    43 |       |    74   (2)| 00:00:01 |
| 152 |              NESTED LOOPS                  |                         |    10 |   300 |       |    71   (0)| 00:00:01 |
| 153 |               TABLE ACCESS BY INDEX ROWID  | TS$                     |     1 |    12 |       |     1   (0)| 00:00:01 |
|*154 |                INDEX UNIQUE SCAN           | I_TS1                   |     1 |       |       |     0   (0)| 00:00:01 |
|*155 |               TABLE ACCESS CLUSTER         | SEG$                    |    10 |   180 |       |    70   (0)| 00:00:01 |
|*156 |                INDEX RANGE SCAN            | I_FILE#_BLOCK#          |     1 |       |       |     3   (0)| 00:00:01 |
|*157 |              TABLE ACCESS FULL             | UNDO$                   |   142 |  1846 |       |     2   (0)| 00:00:01 |
| 158 |             TABLE ACCESS CLUSTER           | USER$                   |     1 |    15 |       |     1   (0)| 00:00:01 |
|*159 |              INDEX UNIQUE SCAN             | I_USER#                 |     1 |       |       |     0   (0)| 00:00:01 |
|*160 |            INDEX UNIQUE SCAN               | I_FILE2                 |     1 |     7 |       |     0   (0)| 00:00:01 |
| 161 |           NESTED LOOPS                     |                         |   175 |  8225 |       |    75   (2)| 00:00:01 |
|*162 |            HASH JOIN OUTER                 |                         |   175 |  7000 |       |    75   (2)| 00:00:01 |
| 163 |             NESTED LOOPS                   |                         |   175 |  4375 |       |    71   (0)| 00:00:01 |
| 164 |              TABLE ACCESS BY INDEX ROWID   | TS$                     |     1 |    12 |       |     1   (0)| 00:00:01 |
|*165 |               INDEX UNIQUE SCAN            | I_TS1                   |     1 |       |       |     0   (0)| 00:00:01 |
|*166 |              TABLE ACCESS CLUSTER          | SEG$                    |   175 |  2275 |       |    70   (0)| 00:00:01 |
|*167 |               INDEX RANGE SCAN             | I_FILE#_BLOCK#          |     1 |       |       |     3   (0)| 00:00:01 |
| 168 |             TABLE ACCESS FULL              | USER$                   |    97 |  1455 |       |     3   (0)| 00:00:01 |
|*169 |            INDEX UNIQUE SCAN               | I_FILE2                 |     1 |     7 |       |     0   (0)| 00:00:01 |
|*170 |        HASH JOIN                           |                         |   774 | 37152 |       |   242   (2)| 00:00:03 |
| 171 |         TABLE ACCESS FULL                  | USER$                   |    97 |   291 |       |     3   (0)| 00:00:01 |
|*172 |         HASH JOIN                          |                         |   774 | 34830 |       |   238   (1)| 00:00:03 |
|*173 |          HASH JOIN                         |                         |   774 | 28638 |       |    31   (4)| 00:00:01 |
| 174 |           TABLE ACCESS FULL                | USER$                   |    97 |   291 |       |     3   (0)| 00:00:01 |
|*175 |           HASH JOIN                        |                         |   774 | 26316 |       |    28   (4)| 00:00:01 |
| 176 |            INDEX FULL SCAN                 | I_TABLE_PRIVILEGE_MAP   |    24 |    72 |       |     1   (0)| 00:00:01 |
| 177 |            NESTED LOOPS                    |                         |   774 | 23994 |       |    26   (0)| 00:00:01 |
| 178 |             TABLE ACCESS BY INDEX ROWID    | USER$                   |     1 |    15 |       |     1   (0)| 00:00:01 |
|*179 |              INDEX UNIQUE SCAN             | I_USER1                 |     1 |       |       |     0   (0)| 00:00:01 |
|*180 |             TABLE ACCESS FULL              | OBJAUTH$                |   774 | 12384 |       |    25   (0)| 00:00:01 |
| 181 |          TABLE ACCESS FULL                 | OBJ$                    | 62067 |   484K|       |   207   (1)| 00:00:03 |
|*182 |      FILTER                                |                         |       |       |       |            |          |
|*183 |       HASH JOIN                            |                         |     3 |   321 |       |    18  (12)| 00:00:01 |
| 184 |        NESTED LOOPS                        |                         |     3 |   294 |       |    16  (13)| 00:00:01 |
|*185 |         HASH JOIN                          |                         |     3 |   285 |       |    13  (16)| 00:00:01 |
|*186 |          HASH JOIN OUTER                   |                         |     3 |   228 |       |    10  (10)| 00:00:01 |
|*187 |           HASH JOIN                        |                         |     3 |   153 |       |     8  (13)| 00:00:01 |
| 188 |            MERGE JOIN CARTESIAN            |                         |     2 |    48 |       |     4   (0)| 00:00:01 |
| 189 |             NESTED LOOPS                   |                         |     1 |    21 |       |     2   (0)| 00:00:01 |
| 190 |              TABLE ACCESS BY INDEX ROWID   | TS$                     |     1 |    12 |       |     1   (0)| 00:00:01 |
|*191 |               INDEX UNIQUE SCAN            | I_TS1                   |     1 |       |       |     0   (0)| 00:00:01 |
|*192 |              TABLE ACCESS BY INDEX ROWID   | PROFILE$                |     1 |     9 |       |     1   (0)| 00:00:01 |
|*193 |               INDEX RANGE SCAN             | I_PROFILE               |    17 |       |       |     0   (0)| 00:00:01 |
| 194 |             BUFFER SORT                    |                         |     3 |     9 |       |     3   (0)| 00:00:01 |
| 195 |              TABLE ACCESS FULL             | PROFNAME$               |     3 |     9 |       |     2   (0)| 00:00:01 |
|*196 |            TABLE ACCESS FULL               | USER$                   |    52 |  1404 |       |     3   (0)| 00:00:01 |
|*197 |           TABLE ACCESS FULL                | RESOURCE_GROUP_MAPPING$ |     1 |    25 |       |     2   (0)| 00:00:01 |
| 198 |          TABLE ACCESS FULL                 | USER_ASTATUS_MAP        |     9 |   171 |       |     2   (0)| 00:00:01 |
| 199 |         TABLE ACCESS CLUSTER               | TS$                     |     1 |     3 |       |     1   (0)| 00:00:01 |
|*200 |          INDEX UNIQUE SCAN                 | I_TS#                   |     1 |       |       |     0   (0)| 00:00:01 |
|*201 |        TABLE ACCESS FULL                   | PROFILE$                |     2 |    18 |       |     2   (0)| 00:00:01 |
|*202 |       HASH JOIN                            |                         |   774 | 37152 |       |   242   (2)| 00:00:03 |
| 203 |        TABLE ACCESS FULL                   | USER$                   |    97 |   291 |       |     3   (0)| 00:00:01 |
|*204 |        HASH JOIN                           |                         |   774 | 34830 |       |   238   (1)| 00:00:03 |
|*205 |         HASH JOIN                          |                         |   774 | 28638 |       |    31   (4)| 00:00:01 |
| 206 |          TABLE ACCESS FULL                 | USER$                   |    97 |   291 |       |     3   (0)| 00:00:01 |
|*207 |          HASH JOIN                         |                         |   774 | 26316 |       |    28   (4)| 00:00:01 |
| 208 |           INDEX FULL SCAN                  | I_TABLE_PRIVILEGE_MAP   |    24 |    72 |       |     1   (0)| 00:00:01 |
| 209 |           NESTED LOOPS                     |                         |   774 | 23994 |       |    26   (0)| 00:00:01 |
| 210 |            TABLE ACCESS BY INDEX ROWID     | USER$                   |     1 |    15 |       |     1   (0)| 00:00:01 |
|*211 |             INDEX UNIQUE SCAN              | I_USER1                 |     1 |       |       |     0   (0)| 00:00:01 |
|*212 |            TABLE ACCESS FULL               | OBJAUTH$                |   774 | 12384 |       |    25   (0)| 00:00:01 |
| 213 |         TABLE ACCESS FULL                  | OBJ$                    | 62067 |   484K|       |   207   (1)| 00:00:03 |
|*214 |      FILTER                                |                         |       |       |       |            |          |
|*215 |       HASH JOIN RIGHT OUTER                |                         |  5900 |  1296K|       |   954   (3)| 00:00:12 |
| 216 |        TABLE ACCE

This post has been answered by John Spencer on Sep 27 2016

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Oct 25 2016

Added on Sep 27 2016

19 comments

914 views