Home » RDBMS Server » Performance Tuning » Slow Join between dba_tab_cols and dba_types
Slow Join between dba_tab_cols and dba_types [message #570706] Wed, 14 November 2012 12:58 Go to next message
ocdeveloper
Messages: 9
Registered: November 2010
Junior Member
The product I work on requires a query to tell us what tables are dependent on certain types.

SELECT dba_tab_cols.owner,
dba_tab_cols.table_name,
dba_tab_cols.data_type_owner,
dba_tab_cols.data_type
FROM dba_tab_cols
JOIN dba_types
ON dba_types.owner      = dba_tab_cols.data_type_owner
AND dba_types.type_name = dba_tab_cols.data_type
WHERE (dba_types.owner IN ('SCHEMA1', 'SCHEMA2'......))


I find this query to be pretty slow. I think it is because data_type_owner in dba_tab_cols is not indexed. Is there an alternative you can suggest? Adding an index is not an
option because users expect our product to read-only.

Thanks,
Neil
Re: Slow Join between dba_tab_cols and dba_types [message #570707 is a reply to message #570706] Wed, 14 November 2012 13:20 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
non-trivial EXPLAIN PLAN


Execution Plan
----------------------------------------------------------
Plan hash value: 3095747581

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                      |    90 | 16650 |       |  2496   (1)| 00:00:30 |
|   1 |  NESTED LOOPS                           |                      |     1 |    30 |       |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                      | I_OBJ1               |     1 |     8 |       |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS CLUSTER                  | USER$                |     1 |    22 |       |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN                    | I_USER#              |     1 |       |       |     0   (0)| 00:00:01 |
|   5 |  TABLE ACCESS BY INDEX ROWID            | OBJ$                 |     1 |    30 |       |     3   (0)| 00:00:01 |
|*  6 |   INDEX RANGE SCAN                      | I_OBJ1               |     1 |       |       |     2   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID           | OBJ$                 |     1 |    30 |       |     3   (0)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN                     | I_OBJ1               |     1 |       |       |     2   (0)| 00:00:01 |
|   9 |    TABLE ACCESS BY INDEX ROWID          | OBJ$                 |     1 |    30 |       |     3   (0)| 00:00:01 |
|* 10 |     INDEX RANGE SCAN                    | I_OBJ1               |     1 |       |       |     2   (0)| 00:00:01 |
|  11 |     TABLE ACCESS BY INDEX ROWID         | OBJ$                 |     1 |    30 |       |     3   (0)| 00:00:01 |
|* 12 |      INDEX RANGE SCAN                   | I_OBJ1               |     1 |       |       |     2   (0)| 00:00:01 |
|  13 |      TABLE ACCESS BY INDEX ROWID        | OBJ$                 |     1 |    30 |       |     3   (0)| 00:00:01 |
|* 14 |       INDEX RANGE SCAN                  | I_OBJ1               |     1 |       |       |     2   (0)| 00:00:01 |
|* 15 |  HASH JOIN                              |                      |    90 | 16650 |       |  2496   (1)| 00:00:30 |
|  16 |   VIEW                                  | DBA_TYPES            |   123 |  4182 |       |   702   (2)| 00:00:09 |
|  17 |    SORT UNIQUE                          |                      |   123 | 20767 |       |   702   (2)| 00:00:09 |
|  18 |     UNION-ALL                           |                      |       |       |       |            |          |
|* 19 |      FILTER                             |                      |       |       |       |            |          |
|* 20 |       HASH JOIN RIGHT OUTER             |                      |   123 | 20787 |       |   700   (1)| 00:00:09 |
|  21 |        TABLE ACCESS FULL                | USER$                |   109 |  1853 |       |     4   (0)| 00:00:01 |
|* 22 |        HASH JOIN OUTER                  |                      |   123 | 18696 |       |   695   (1)| 00:00:09 |
|* 23 |         HASH JOIN                       |                      |   123 | 15990 |       |   421   (1)| 00:00:06 |
|  24 |          INDEX FULL SCAN                | I_USER2              |   109 |  1308 |       |     1   (0)| 00:00:01 |
|  25 |          NESTED LOOPS                   |                      |       |       |       |            |          |
|  26 |           NESTED LOOPS                  |                      |   123 | 14514 |       |   419   (1)| 00:00:06 |
|* 27 |            HASH JOIN                    |                      |   169 | 10309 |       |   276   (1)| 00:00:04 |
|  28 |             INLIST ITERATOR             |                      |       |       |       |            |          |
|  29 |              TABLE ACCESS BY INDEX ROWID| USER$                |     2 |    34 |       |     3   (0)| 00:00:01 |
|* 30 |               INDEX UNIQUE SCAN         | I_USER1              |     2 |       |       |     1   (0)| 00:00:01 |
|* 31 |             TABLE ACCESS FULL           | OBJ$                 |  3385 |   145K|       |   272   (1)| 00:00:04 |
|* 32 |            INDEX UNIQUE SCAN            | I_TYPE2              |     1 |       |       |     0   (0)| 00:00:01 |
|* 33 |           TABLE ACCESS BY INDEX ROWID   | TYPE$                |     1 |    57 |       |     1   (0)| 00:00:01 |
|  34 |         VIEW                            | _CURRENT_EDITION_OBJ | 75545 |  1623K|       |   274   (1)| 00:00:04 |
|* 35 |          FILTER                         |                      |       |       |       |            |          |
|* 36 |           HASH JOIN                     |                      | 76172 |  4016K|       |   274   (1)| 00:00:04 |
|  37 |            INDEX FULL SCAN              | I_USER2              |   109 |  1308 |       |     1   (0)| 00:00:01 |
|  38 |            TABLE ACCESS FULL            | OBJ$                 | 76172 |  3124K|       |   272   (1)| 00:00:04 |
|  39 |           NESTED LOOPS                  |                      |     1 |    18 |       |     2   (0)| 00:00:01 |
|* 40 |            INDEX SKIP SCAN              | I_USER2              |     1 |     9 |       |     1   (0)| 00:00:01 |
|* 41 |            INDEX RANGE SCAN             | I_OBJ4               |     1 |     9 |       |     1   (0)| 00:00:01 |
|  42 |       NESTED LOOPS                      |                      |     1 |    18 |       |     2   (0)| 00:00:01 |
|* 43 |        INDEX SKIP SCAN                  | I_USER2              |     1 |     9 |       |     1   (0)| 00:00:01 |
|* 44 |        INDEX RANGE SCAN                 | I_OBJ4               |     1 |     9 |       |     1   (0)| 00:00:01 |
|* 45 |      FILTER                             |                      |       |       |       |            |          |
|* 46 |       FILTER                            |                      |       |       |       |            |          |
|  47 |        MERGE JOIN CARTESIAN             |                      |     1 |   149 |       |  1100   (1)| 00:00:14 |
|  48 |         NESTED LOOPS OUTER              |                      |     1 |   149 |       |  1099   (1)| 00:00:14 |
|* 49 |          HASH JOIN OUTER                |                      |     1 |   132 |       |  1098   (1)| 00:00:14 |
|  50 |           NESTED LOOPS                  |                      |     1 |   110 |       |   823   (1)| 00:00:10 |
|  51 |            NESTED LOOPS                 |                      |     1 |    98 |       |   822   (1)| 00:00:10 |
|* 52 |             TABLE ACCESS FULL           | TYPE$                |     1 |    57 |       |   820   (1)| 00:00:10 |
|* 53 |             TABLE ACCESS BY INDEX ROWID | OBJ$                 |     1 |    41 |       |     2   (0)| 00:00:01 |
|* 54 |              INDEX RANGE SCAN           | I_OBJ3               |     1 |       |       |     1   (0)| 00:00:01 |
|* 55 |            INDEX RANGE SCAN             | I_USER2              |     1 |    12 |       |     1   (0)| 00:00:01 |
|  56 |           VIEW                          | _CURRENT_EDITION_OBJ | 75545 |  1623K|       |   274   (1)| 00:00:04 |
|* 57 |            FILTER                       |                      |       |       |       |            |          |
|* 58 |             HASH JOIN                   |                      | 76172 |  4016K|       |   274   (1)| 00:00:04 |
|  59 |              INDEX FULL SCAN            | I_USER2              |   109 |  1308 |       |     1   (0)| 00:00:01 |
|  60 |              TABLE ACCESS FULL          | OBJ$                 | 76172 |  3124K|       |   272   (1)| 00:00:04 |
|  61 |             NESTED LOOPS                |                      |     1 |    18 |       |     2   (0)| 00:00:01 |
|* 62 |              INDEX SKIP SCAN            | I_USER2              |     1 |     9 |       |     1   (0)| 00:00:01 |
|* 63 |              INDEX RANGE SCAN           | I_OBJ4               |     1 |     9 |       |     1   (0)| 00:00:01 |
|  64 |          TABLE ACCESS CLUSTER           | USER$                |     1 |    17 |       |     1   (0)| 00:00:01 |
|* 65 |           INDEX UNIQUE SCAN             | I_USER#              |     1 |       |       |     0   (0)| 00:00:01 |
|  66 |         BUFFER SORT                     |                      |   109 |       |       |  1099   (1)| 00:00:14 |
|  67 |          INDEX FULL SCAN                | I_USER1              |   109 |       |       |     1   (0)| 00:00:01 |
|  68 |       NESTED LOOPS                      |                      |     1 |    18 |       |     2   (0)| 00:00:01 |
|* 69 |        INDEX SKIP SCAN                  | I_USER2              |     1 |     9 |       |     1   (0)| 00:00:01 |
|* 70 |        INDEX RANGE SCAN                 | I_OBJ4               |     1 |     9 |       |     1   (0)| 00:00:01 |
|* 71 |   VIEW                                  | DBA_TAB_COLS         |  7302 |  1076K|       |  1793   (1)| 00:00:22 |
|* 72 |    FILTER                               |                      |       |       |       |            |          |
|* 73 |     HASH JOIN RIGHT OUTER               |                      | 98623 |    18M|       |  1793   (1)| 00:00:22 |
|  74 |      INDEX FAST FULL SCAN               | I_HH_OBJ#_INTCOL#    | 32935 |   289K|       |    38   (0)| 00:00:01 |
|* 75 |      HASH JOIN RIGHT OUTER              |                      | 98623 |    17M|       |  1754   (1)| 00:00:22 |
|  76 |       TABLE ACCESS FULL                 | USER$                |   109 |  3706 |       |     4   (0)| 00:00:01 |
|* 77 |       HASH JOIN RIGHT OUTER             |                      | 98623 |    14M|       |  1749   (1)| 00:00:21 |
|* 78 |        TABLE ACCESS FULL                | OBJ$                 |  2827 | 96118 |       |   272   (1)| 00:00:04 |
|* 79 |        HASH JOIN RIGHT OUTER            |                      | 98623 |    11M|       |  1476   (1)| 00:00:18 |
|  80 |         TABLE ACCESS FULL               | COLTYPE$             |  3109 | 87052 |       |   456   (0)| 00:00:06 |
|* 81 |         HASH JOIN                       |                      | 98623 |  8571K|       |  1019   (1)| 00:00:13 |
|  82 |          TABLE ACCESS FULL              | USER$                |   109 |  1853 |       |     4   (0)| 00:00:01 |
|* 83 |          HASH JOIN                      |                      | 98623 |  6934K|       |  1014   (1)| 00:00:13 |
|  84 |           INDEX FULL SCAN               | I_USER2              |   109 |  1308 |       |     1   (0)| 00:00:01 |
|* 85 |           HASH JOIN                     |                      | 98623 |  5778K|  3088K|  1013   (1)| 00:00:13 |
|  86 |            TABLE ACCESS FULL            | COL$                 | 98623 |  1926K|       |   458   (1)| 00:00:06 |
|  87 |            INDEX FAST FULL SCAN         | I_OBJ2               | 76172 |  2975K|       |   216   (0)| 00:00:03 |
|* 88 |     TABLE ACCESS CLUSTER                | TAB$                 |     1 |    13 |       |     2   (0)| 00:00:01 |
|* 89 |      INDEX UNIQUE SCAN                  | I_OBJ#               |     1 |       |       |     1   (0)| 00:00:01 |
|  90 |     NESTED LOOPS                        |                      |     1 |    18 |       |     2   (0)| 00:00:01 |
|* 91 |      INDEX SKIP SCAN                    | I_USER2              |     1 |     9 |       |     1   (0)| 00:00:01 |
|* 92 |      INDEX RANGE SCAN                   | I_OBJ4               |     1 |     9 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("O"."OBJ#"=:B1)
   4 - access("O"."OWNER#"="USER#")
   6 - access("O"."OBJ#"=:B1)
   8 - access("O"."OBJ#"=:B1)
  10 - access("O"."OBJ#"=:B1)
  12 - access("O"."OBJ#"=:B1)
  14 - access("O"."OBJ#"=:B1)
  15 - access("DBA_TYPES"."OWNER"="DBA_TAB_COLS"."DATA_TYPE_OWNER" AND
              "DBA_TYPES"."TYPE_NAME"="DBA_TAB_COLS"."DATA_TYPE")
  19 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND
              "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND
              "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR
              "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR
              "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
              (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR "U"."TYPE#"=2 AND
              "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR  EXISTS (SELECT 0 FROM SYS."USER$"
              "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B1 AND
              "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))
  20 - access("SO"."OWNER#"="SU"."USER#"(+))
  22 - access("T"."SUPERTOID"="SO"."OID$"(+))
  23 - access("O"."OWNER#"="U"."USER#")
  27 - access("O"."SPARE3"="U"."USER#")
  30 - access("U"."NAME"='USER1' OR "U"."NAME"='USER2')
  31 - filter("O"."OID$" IS NOT NULL AND "O"."SUBNAME" IS NULL AND "O"."TYPE#"<>10)
  32 - access("O"."OID$"="T"."TVOID")
  33 - filter(BITAND("T"."PROPERTIES",2048)=0 AND BITAND("T"."PROPERTIES",64)<>64)
  35 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND
              "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND
              "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR
              "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR
              "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
              (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR "U"."TYPE#"=2 AND
              "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR  EXISTS (SELECT 0 FROM SYS."USER$"
              "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B1 AND
              "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))
  36 - access("O"."OWNER#"="U"."USER#")
  40 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
  41 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
  43 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
  44 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
  45 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND
              "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND
              "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR
              "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR
              "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
              (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR "U"."TYPE#"=2 AND
              "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR  EXISTS (SELECT 0 FROM SYS."USER$"
              "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B1 AND
              "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))
  46 - filter(NULL IS NOT NULL)
  49 - access("T"."SUPERTOID"="SO"."OID$"(+))
  52 - filter(BITAND("T"."PROPERTIES",64)=64 AND BITAND("T"."PROPERTIES",2048)=0)
  53 - filter("O"."SUBNAME" IS NULL AND "O"."TYPE#"<>10)
  54 - access("O"."OID$"="T"."TVOID")
       filter("O"."OID$" IS NOT NULL)
  55 - access("O"."OWNER#"="U"."USER#")
  57 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND
              "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND
              "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR
              "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR
              "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
              (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR "U"."TYPE#"=2 AND
              "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR  EXISTS (SELECT 0 FROM SYS."USER$"
              "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B1 AND
              "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))
  58 - access("O"."OWNER#"="U"."USER#")
  62 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
  63 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
  65 - access("SO"."OWNER#"="SU"."USER#"(+))
  69 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
  70 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
  71 - filter("DBA_TAB_COLS"."DATA_TYPE_OWNER"='USER1' OR "DBA_TAB_COLS"."DATA_TYPE_OWNER"='USER2')
  72 - filter((("O"."TYPE#"=3 OR "O"."TYPE#"=4) OR "O"."TYPE#"=2 AND  NOT EXISTS (SELECT 0 FROM "SYS"."TAB$"
              "T" WHERE "T"."OBJ#"=:B1 AND (BITAND("T"."PROPERTY",8192)=8192 OR BITAND("T"."PROPERTY",512)=512))) AND
              ("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10
              AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND
              "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR
              "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR
              "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
              (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR "U"."TYPE#"=2 AND
              "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR  EXISTS (SELECT 0 FROM SYS."USER$"
              "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B2 AND
              "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))))
  73 - access("C"."OBJ#"="H"."OBJ#"(+) AND "C"."INTCOL#"="H"."INTCOL#"(+))
  75 - access("OT"."OWNER#"="USER#"(+))
  77 - access("AC"."TOID"="OT"."OID$"(+))
  78 - filter("OT"."TYPE#"(+)=13)
  79 - access("C"."OBJ#"="AC"."OBJ#"(+) AND "C"."INTCOL#"="AC"."INTCOL#"(+))
  81 - access("O"."SPARE3"="U"."USER#")
  83 - access("O"."OWNER#"="U"."USER#")
  85 - access("O"."OBJ#"="C"."OBJ#")
  88 - filter(BITAND("T"."PROPERTY",8192)=8192 OR BITAND("T"."PROPERTY",512)=512)
  89 - access("T"."OBJ#"=:B1)
  91 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
  92 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1009  consistent gets
        995  physical reads
          0  redo size
        481  bytes sent via SQL*Net to client
        408  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> 


[Updated on: Wed, 14 November 2012 13:42]

Report message to a moderator

Previous Topic: Alternate Query instead of querying the table twice
Next Topic: dba_hist_sqlstat
Goto Forum:
  


Current Time: Thu Mar 28 16:33:47 CDT 2024