ITPub博客

首页 > Linux操作系统 > Linux操作系统 > _optimizer_mjc_enabled

_optimizer_mjc_enabled

原创 Linux操作系统 作者:yyp2009 时间:2011-05-24 01:14:22 0 删除 编辑

SQL> select * from  hr.employees,hr.departments,hr.JOBS;

已选择54891行。

已用时间:  00: 00: 01.32

执行计划
----------------------------------------------------------
Plan hash value: 4263706148

-------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             | 54891 |  6486K|   595   (2)| 00:00:08 |
|   1 |  MERGE JOIN CARTESIAN |             | 54891 |  6486K|   595   (2)| 00:00:08 |
|   2 |   MERGE JOIN CARTESIAN|             |   513 | 27189 |    25   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | JOBS        |    19 |   627 |     3   (0)| 00:00:01 |
|   4 |    BUFFER SORT        |             |    27 |   540 |    22   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL | DEPARTMENTS |    27 |   540 |     1   (0)| 00:00:01 |
|   6 |   BUFFER SORT         |             |   107 |  7276 |   593   (2)| 00:00:08 |
|   7 |    TABLE ACCESS FULL  | EMPLOYEES   |   107 |  7276 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         39  consistent gets
          0  physical reads
          0  redo size
    5436618  bytes sent via SQL*Net to client
      40634  bytes received via SQL*Net from client
       3661  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      54891  rows processed

SQL>  select * from  hr.employees a,hr.departments b,hr.JOBS c;

已选择54891行。

已用时间:  00: 00: 01.32

执行计划
----------------------------------------------------------
Plan hash value: 4263706148

-------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             | 54891 |  6486K|   595   (2)| 00:00:08 |
|   1 |  MERGE JOIN CARTESIAN |             | 54891 |  6486K|   595   (2)| 00:00:08 |
|   2 |   MERGE JOIN CARTESIAN|             |   513 | 27189 |    25   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | JOBS        |    19 |   627 |     3   (0)| 00:00:01 |
|   4 |    BUFFER SORT        |             |    27 |   540 |    22   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL | DEPARTMENTS |    27 |   540 |     1   (0)| 00:00:01 |
|   6 |   BUFFER SORT         |             |   107 |  7276 |   593   (2)| 00:00:08 |
|   7 |    TABLE ACCESS FULL  | EMPLOYEES   |   107 |  7276 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         39  consistent gets
          0  physical reads
          0  redo size
    5436618  bytes sent via SQL*Net to client
      40634  bytes received via SQL*Net from client
       3661  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      54891  rows processed

SQL>  select * from  hr.employees a,hr.departments b,hr.JOBS c
  2   where a.DEPARTMENT_ID=b.DEPARTMENT_ID
  3   and a. JOB_ID=c.JOB_ID;

已选择106行。

已用时间:  00: 00: 00.01

执行计划
----------------------------------------------------------
Plan hash value: 3066132443

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |   106 | 12826 |     5   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |             |   106 | 12826 |     5   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |             |   106 |  9328 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL          | EMPLOYEES   |   107 |  7276 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |    20 |     1   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | JOBS        |     1 |    33 |     1   (0)| 00:00:01 |
|*  7 |    INDEX UNIQUE SCAN          | JOB_ID_PK   |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   5 - access("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")
   7 - access("A"."JOB_ID"="C"."JOB_ID")


统计信息
----------------------------------------------------------
          8  recursive calls
          0  db block gets
        440  consistent gets
          1  physical reads
          0  redo size
      12739  bytes sent via SQL*Net to client
        462  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        106  rows processed

SQL> alter session set "_optimizer_mjc_enabled" = false;

会话已更改。

已用时间:  00: 00: 00.00
SQL>
SQL>  select * from  hr.employees a,hr.departments b,hr.JOBS c;

已选择54891行。

已用时间:  00: 00: 01.32

执行计划
----------------------------------------------------------
Plan hash value: 4237883665

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             | 54891 |  6486K|   595   (2)| 00:00:08 |
|   1 |  NESTED LOOPS       |             | 54891 |  6486K|   595   (2)| 00:00:08 |
|   2 |   NESTED LOOPS      |             |   513 | 27189 |    25   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| JOBS        |    19 |   627 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| DEPARTMENTS |    27 |   540 |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL | EMPLOYEES   |   107 |  7276 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       7835  consistent gets
          0  physical reads
          0  redo size
    5436618  bytes sent via SQL*Net to client
      40634  bytes received via SQL*Net from client
       3661  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      54891  rows processed

SQL> select * from dba_objects,dba_segments,dba_extents where 1=0;

未选定行

已用时间:  00: 00: 00.18

执行计划
----------------------------------------------------------
Plan hash value: 1069887050

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                  |  1195M|   775G|  3644K  (6)| 12:08:50 |
|   1 |  NESTED LOOPS                          |                  |  1195M|   775G|  3644K  (6)| 12:08:50 |
|   2 |   NESTED LOOPS                         |                  | 24772 |    12M| 11240   (2)| 00:02:15 |
|   3 |    VIEW                                | DBA_EXTENTS      |    11 |  2002 |    28   (0)| 00:00:01 |
|   4 |     UNION-ALL                          |                  |       |       |            |       |
|   5 |      NESTED LOOPS                      |                  |     1 |   256 |    15   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                     |                  |     1 |   247 |    15   (0)| 00:00:01 |
|   7 |        VIEW                            | SYS_DBA_SEGS     |     3 |   468 |    12   (0)| 00:00:01 |
|   8 |         UNION-ALL                      |                  |       |       |            |       |
|   9 |          NESTED LOOPS OUTER            |                  |     1 |   161 |    12   (0)| 00:00:01 |
|  10 |           NESTED LOOPS                 |                  |     1 |   147 |    11   (0)| 00:00:01 |
|  11 |            NESTED LOOPS                |                  |     1 |   109 |    10   (0)| 00:00:01 |
|  12 |             NESTED LOOPS               |                  |     1 |    94 |     9   (0)| 00:00:01 |
|  13 |              NESTED LOOPS              |                  |     1 |    88 |     9   (0)| 00:00:01 |
|  14 |               VIEW                     | SYS_OBJECTS      |     9 |   621 |     5 (100)| 00:00:01 |
|  15 |                UNION-ALL               |                  |       |       |            |       |
|* 16 |                 FILTER                 |                  |       |       |            |       |
|* 17 |                  TABLE ACCESS FULL     | TAB$             |  1606 | 36938 |   192   (1)| 00:00:03 |
|* 18 |                 FILTER                 |                  |       |       |            |       |
|  19 |                  TABLE ACCESS FULL     | TABPART$         |   146 |  2336 |     3   (0)| 00:00:01 |
|* 20 |                 FILTER                 |                  |       |       |            |       |
|  21 |                  TABLE ACCESS FULL     | CLU$             |    10 |   130 |   192   (1)| 00:00:03 |
|* 22 |                 FILTER                 |                  |       |       |            |       |
|* 23 |                  TABLE ACCESS FULL     | IND$             |  2360 | 44840 |   192   (1)| 00:00:03 |
|* 24 |                 FILTER                 |                  |       |       |            |       |
|  25 |                  TABLE ACCESS FULL     | INDPART$         |   295 |  4720 |     4   (0)| 00:00:01 |
|* 26 |                 FILTER                 |                  |       |       |            |       |
|* 27 |                  TABLE ACCESS FULL     | LOB$             |   565 | 11300 |   191   (1)| 00:00:03 |
|* 28 |                 FILTER                 |                  |       |       |            |       |
|  29 |                  TABLE ACCESS FULL     | TABSUBPART$      |     1 |    52 |     2   (0)| 00:00:01 |
|* 30 |                 FILTER                 |                  |       |       |            |       |
|  31 |                  TABLE ACCESS FULL     | INDSUBPART$      |     1 |    52 |     2   (0)| 00:00:01 |
|* 32 |                 FILTER                 |                  |       |       |            |       |
|  33 |                  TABLE ACCESS FULL     | LOBFRAG$         |     1 |    16 |     2   (0)| 00:00:01 |
|* 34 |               TABLE ACCESS CLUSTER     | SEG$             |     1 |    19 |     1   (0)| 00:00:01 |
|* 35 |                INDEX UNIQUE SCAN       | I_FILE#_BLOCK#   |     1 |       |     0   (0)| 00:00:01 |
|* 36 |              INDEX UNIQUE SCAN         | I_FILE2          |     1 |     6 |     0   (0)| 00:00:01 |
|  37 |             TABLE ACCESS CLUSTER       | TS$              |     1 |    15 |     1   (0)| 00:00:01 |
|* 38 |              INDEX UNIQUE SCAN         | I_TS#            |     1 |       |     0   (0)| 00:00:01 |
|* 39 |            TABLE ACCESS BY INDEX ROWID | OBJ$             |     1 |    38 |     1   (0)| 00:00:01 |
|* 40 |             INDEX UNIQUE SCAN          | I_OBJ1           |     1 |       |     0   (0)| 00:00:01 |
|  41 |           TABLE ACCESS CLUSTER         | USER$            |     1 |    14 |     1   (0)| 00:00:01 |
|* 42 |            INDEX UNIQUE SCAN           | I_USER#          |     1 |       |     0   (0)| 00:00:01 |
|* 43 |          FILTER                        |                  |       |       |            |       |
|  44 |           NESTED LOOPS                 |                  |     1 |    81 |    15   (0)| 00:00:01 |
|  45 |            NESTED LOOPS OUTER          |                  |     1 |    66 |    14   (0)| 00:00:01 |
|  46 |             NESTED LOOPS               |                  |     1 |    52 |    13   (0)| 00:00:01 |
|  47 |              NESTED LOOPS              |                  |     1 |    46 |    13   (0)| 00:00:01 |
|* 48 |               TABLE ACCESS FULL        | UNDO$            |    11 |   264 |     2   (0)| 00:00:01 |
|* 49 |               TABLE ACCESS CLUSTER     | SEG$             |     1 |    22 |     1   (0)| 00:00:01 |
|* 50 |                INDEX UNIQUE SCAN       | I_FILE#_BLOCK#   |     1 |       |     0   (0)| 00:00:01 |
|* 51 |              INDEX UNIQUE SCAN         | I_FILE2          |     1 |     6 |     0   (0)| 00:00:01 |
|  52 |             TABLE ACCESS CLUSTER       | USER$            |     1 |    14 |     1   (0)| 00:00:01 |
|* 53 |              INDEX UNIQUE SCAN         | I_USER#          |     1 |       |     0   (0)| 00:00:01 |
|  54 |            TABLE ACCESS CLUSTER        | TS$              |     1 |    15 |     1   (0)| 00:00:01 |
|* 55 |             INDEX UNIQUE SCAN          | I_TS#            |     1 |       |     0   (0)| 00:00:01 |
|* 56 |          FILTER                        |                  |       |       |            |       |
|  57 |           NESTED LOOPS                 |                  |     1 |    60 |    36   (3)| 00:00:01 |
|  58 |            NESTED LOOPS                |                  |     1 |    45 |    35   (3)| 00:00:01 |
|  59 |             NESTED LOOPS OUTER         |                  |     1 |    36 |    34   (3)| 00:00:01 |
|* 60 |              TABLE ACCESS FULL         | SEG$             |     1 |    22 |    33   (4)| 00:00:01 |
|  61 |              TABLE ACCESS CLUSTER      | USER$            |     1 |    14 |     1   (0)| 00:00:01 |
|* 62 |               INDEX UNIQUE SCAN        | I_USER#          |     1 |       |     0   (0)| 00:00:01 |
|  63 |             TABLE ACCESS BY INDEX ROWID| FILE$            |     1 |     9 |     1   (0)| 00:00:01 |
|* 64 |              INDEX UNIQUE SCAN         | I_FILE2          |     1 |       |     0   (0)| 00:00:01 |
|  65 |            TABLE ACCESS CLUSTER        | TS$              |     1 |    15 |     1   (0)| 00:00:01 |
|* 66 |             INDEX UNIQUE SCAN          | I_TS#            |     1 |       |     0   (0)| 00:00:01 |
|  67 |        TABLE ACCESS CLUSTER            | UET$             |     1 |    91 |     1   (0)| 00:00:01 |
|* 68 |         INDEX UNIQUE SCAN              | I_FILE#_BLOCK#   |     1 |       |     0   (0)| 00:00:01 |
|  69 |       TABLE ACCESS BY INDEX ROWID      | FILE$            |     1 |     9 |     0   (0)| 00:00:01 |
|* 70 |        INDEX UNIQUE SCAN               | I_FILE2          |     1 |       |     0   (0)| 00:00:01 |
|  71 |      NESTED LOOPS                      |                  |    10 |  2560 |    13   (0)| 00:00:01 |
|  72 |       NESTED LOOPS                     |                  |    10 |  2470 |    12   (0)| 00:00:01 |
|  73 |        VIEW                            | SYS_DBA_SEGS     |     3 |   468 |    12   (0)| 00:00:01 |
|  74 |         UNION-ALL                      |                  |       |       |            |       |
|  75 |          NESTED LOOPS OUTER            |                  |     1 |   161 |    12   (0)| 00:00:01 |
|  76 |           NESTED LOOPS                 |                  |     1 |   147 |    11   (0)| 00:00:01 |
|  77 |            NESTED LOOPS                |                  |     1 |   109 |    10   (0)| 00:00:01 |
|  78 |             NESTED LOOPS               |                  |     1 |    94 |     9   (0)| 00:00:01 |
|  79 |              NESTED LOOPS              |                  |     1 |    88 |     9   (0)| 00:00:01 |
|  80 |               VIEW                     | SYS_OBJECTS      |     9 |   621 |     5 (100)| 00:00:01 |
|  81 |                UNION-ALL               |                  |       |       |            |       |
|* 82 |                 FILTER                 |                  |       |       |            |       |
|* 83 |                  TABLE ACCESS FULL     | TAB$             |  1606 | 36938 |   192   (1)| 00:00:03 |
|* 84 |                 FILTER                 |                  |       |       |            |       |
|  85 |                  TABLE ACCESS FULL     | TABPART$         |   146 |  2336 |     3   (0)| 00:00:01 |
|* 86 |                 FILTER                 |                  |       |       |            |       |
|  87 |                  TABLE ACCESS FULL     | CLU$             |    10 |   130 |   192   (1)| 00:00:03 |
|* 88 |                 FILTER                 |                  |       |       |            |       |
|* 89 |                  TABLE ACCESS FULL     | IND$             |  2360 | 44840 |   192   (1)| 00:00:03 |
|* 90 |                 FILTER                 |                  |       |       |            |       |
|  91 |                  TABLE ACCESS FULL     | INDPART$         |   295 |  4720 |     4   (0)| 00:00:01 |
|* 92 |                 FILTER                 |                  |       |       |            |       |
|* 93 |                  TABLE ACCESS FULL     | LOB$             |   565 | 11300 |   191   (1)| 00:00:03 |
|* 94 |                 FILTER                 |                  |       |       |            |       |
|  95 |                  TABLE ACCESS FULL     | TABSUBPART$      |     1 |    52 |     2   (0)| 00:00:01 |
|* 96 |                 FILTER                 |                  |       |       |            |       |
|  97 |                  TABLE ACCESS FULL     | INDSUBPART$      |     1 |    52 |     2   (0)| 00:00:01 |
|* 98 |                 FILTER                 |                  |       |       |            |       |
|  99 |                  TABLE ACCESS FULL     | LOBFRAG$         |     1 |    16 |     2   (0)| 00:00:01 |
|*100 |               TABLE ACCESS CLUSTER     | SEG$             |     1 |    19 |     1   (0)| 00:00:01 |
|*101 |                INDEX UNIQUE SCAN       | I_FILE#_BLOCK#   |     1 |       |     0   (0)| 00:00:01 |
|*102 |              INDEX UNIQUE SCAN         | I_FILE2          |     1 |     6 |     0   (0)| 00:00:01 |
| 103 |             TABLE ACCESS CLUSTER       | TS$              |     1 |    15 |     1   (0)| 00:00:01 |
|*104 |              INDEX UNIQUE SCAN         | I_TS#            |     1 |       |     0   (0)| 00:00:01 |
|*105 |            TABLE ACCESS BY INDEX ROWID | OBJ$             |     1 |    38 |     1   (0)| 00:00:01 |
|*106 |             INDEX UNIQUE SCAN          | I_OBJ1           |     1 |       |     0   (0)| 00:00:01 |
| 107 |           TABLE ACCESS CLUSTER         | USER$            |     1 |    14 |     1   (0)| 00:00:01 |
|*108 |            INDEX UNIQUE SCAN           | I_USER#          |     1 |       |     0   (0)| 00:00:01 |
|*109 |          FILTER                        |                  |       |       |            |       |
| 110 |           NESTED LOOPS                 |                  |     1 |    81 |    15   (0)| 00:00:01 |
| 111 |            NESTED LOOPS OUTER          |                  |     1 |    66 |    14   (0)| 00:00:01 |
| 112 |             NESTED LOOPS               |                  |     1 |    52 |    13   (0)| 00:00:01 |
| 113 |              NESTED LOOPS              |                  |     1 |    46 |    13   (0)| 00:00:01 |
|*114 |               TABLE ACCESS FULL        | UNDO$            |    11 |   264 |     2   (0)| 00:00:01 |
|*115 |               TABLE ACCESS CLUSTER     | SEG$             |     1 |    22 |     1   (0)| 00:00:01 |
|*116 |                INDEX UNIQUE SCAN       | I_FILE#_BLOCK#   |     1 |       |     0   (0)| 00:00:01 |
|*117 |              INDEX UNIQUE SCAN         | I_FILE2          |     1 |     6 |     0   (0)| 00:00:01 |
| 118 |             TABLE ACCESS CLUSTER       | USER$            |     1 |    14 |     1   (0)| 00:00:01 |
|*119 |              INDEX UNIQUE SCAN         | I_USER#          |     1 |       |     0   (0)| 00:00:01 |
| 120 |            TABLE ACCESS CLUSTER        | TS$              |     1 |    15 |     1   (0)| 00:00:01 |
|*121 |             INDEX UNIQUE SCAN          | I_TS#            |     1 |       |     0   (0)| 00:00:01 |
|*122 |          FILTER                        |                  |       |       |            |       |
| 123 |           NESTED LOOPS                 |                  |     1 |    60 |    36   (3)| 00:00:01 |
| 124 |            NESTED LOOPS                |                  |     1 |    45 |    35   (3)| 00:00:01 |
| 125 |             NESTED LOOPS OUTER         |                  |     1 |    36 |    34   (3)| 00:00:01 |
|*126 |              TABLE ACCESS FULL         | SEG$             |     1 |    22 |    33   (4)| 00:00:01 |
| 127 |              TABLE ACCESS CLUSTER      | USER$            |     1 |    14 |     1   (0)| 00:00:01 |
|*128 |               INDEX UNIQUE SCAN        | I_USER#          |     1 |       |     0   (0)| 00:00:01 |
| 129 |             TABLE ACCESS BY INDEX ROWID| FILE$            |     1 |     9 |     1   (0)| 00:00:01 |
|*130 |              INDEX UNIQUE SCAN         | I_FILE2          |     1 |       |     0   (0)| 00:00:01 |
| 131 |            TABLE ACCESS CLUSTER        | TS$              |     1 |    15 |     1   (0)| 00:00:01 |
|*132 |             INDEX UNIQUE SCAN          | I_TS#            |     1 |       |     0   (0)| 00:00:01 |
|*133 |        FIXED TABLE FIXED INDEX         | X$KTFBUE (ind:1) |     3 |   273 |     0   (0)| 00:00:01 |
| 134 |       TABLE ACCESS BY INDEX ROWID      | FILE$            |     1 |     9 |     1   (0)| 00:00:01 |
|*135 |        INDEX UNIQUE SCAN               | I_FILE2          |     1 |       |     0   (0)| 00:00:01 |
| 136 |    VIEW                                | SYS_DBA_SEGS     |  2252 |   743K|  1019   (2)| 00:00:13 |
| 137 |     UNION-ALL                          |                  |       |       |            |       |
|*138 |      HASH JOIN RIGHT OUTER             |                  |  1009 |   201K|   962   (2)| 00:00:12 |
| 139 |       TABLE ACCESS FULL                | USER$            |    63 |   882 |     2   (0)| 00:00:01 |
|*140 |       HASH JOIN                        |                  |  1009 |   188K|   960   (2)| 00:00:12 |
|*141 |        HASH JOIN                       |                  |  1009 |   148K|   820   (1)| 00:00:10 |
| 142 |         TABLE ACCESS FULL              | TS$              |     7 |   126 |     3   (0)| 00:00:01 |
| 143 |         NESTED LOOPS                   |                  |  1009 |   131K|   817   (1)| 00:00:10 |
|*144 |          HASH JOIN                     |                  |  1009 |   122K|   814   (1)| 00:00:10 |
| 145 |           TABLE ACCESS FULL            | SEG$             |  4668 |   250K|    33   (4)| 00:00:01 |
| 146 |           VIEW                         | SYS_OBJECTS      |  4985 |   335K|   781   (1)| 00:00:10 |
| 147 |            UNION-ALL                   |                  |       |       |            |       |
|*148 |             TABLE ACCESS FULL          | TAB$             |  1606 | 36938 |   192   (1)| 00:00:03 |
| 149 |             TABLE ACCESS FULL          | TABPART$         |   146 |  2336 |     3   (0)| 00:00:01 |
| 150 |             TABLE ACCESS FULL          | CLU$             |    10 |   130 |   192   (1)| 00:00:03 |
|*151 |             TABLE ACCESS FULL          | IND$             |  2360 | 44840 |   192   (1)| 00:00:03 |
| 152 |             TABLE ACCESS FULL          | INDPART$         |   295 |  4720 |     4   (0)| 00:00:01 |
|*153 |             TABLE ACCESS FULL          | LOB$             |   565 | 11300 |   191   (1)| 00:00:03 |
| 154 |             TABLE ACCESS FULL          | TABSUBPART$      |     1 |    52 |     2   (0)| 00:00:01 |
| 155 |             TABLE ACCESS FULL          | INDSUBPART$      |     1 |    52 |     2   (0)| 00:00:01 |
| 156 |             TABLE ACCESS FULL          | LOBFRAG$         |     1 |    16 |     2   (0)| 00:00:01 |
| 157 |          TABLE ACCESS BY INDEX ROWID   | FILE$            |     1 |     9 |     1   (0)| 00:00:01 |
|*158 |           INDEX UNIQUE SCAN            | I_FILE2          |     1 |       |     0   (0)| 00:00:01 |
| 159 |        TABLE ACCESS FULL               | OBJ$             | 51427 |  2008K|   138   (3)| 00:00:02 |
| 160 |      NESTED LOOPS                      |                  |     1 |   126 |    16   (0)| 00:00:01 |
| 161 |       NESTED LOOPS                     |                  |     1 |   108 |    15   (0)| 00:00:01 |
| 162 |        NESTED LOOPS OUTER              |                  |     1 |    99 |    14   (0)| 00:00:01 |
| 163 |         NESTED LOOPS                   |                  |     1 |    85 |    13   (0)| 00:00:01 |
|*164 |          TABLE ACCESS FULL             | UNDO$            |    11 |   297 |     2   (0)| 00:00:01 |
|*165 |          TABLE ACCESS CLUSTER          | SEG$             |     1 |    58 |     1   (0)| 00:00:01 |
|*166 |           INDEX UNIQUE SCAN            | I_FILE#_BLOCK#   |     1 |       |     0   (0)| 00:00:01 |
| 167 |         TABLE ACCESS CLUSTER           | USER$            |     1 |    14 |     1   (0)| 00:00:01 |
|*168 |          INDEX UNIQUE SCAN             | I_USER#          |     1 |       |     0   (0)| 00:00:01 |
| 169 |        TABLE ACCESS BY INDEX ROWID     | FILE$            |     1 |     9 |     1   (0)| 00:00:01 |
|*170 |         INDEX UNIQUE SCAN              | I_FILE2          |     1 |       |     0   (0)| 00:00:01 |
| 171 |       TABLE ACCESS CLUSTER             | TS$              |     1 |    18 |     1   (0)| 00:00:01 |
|*172 |        INDEX UNIQUE SCAN               | I_TS#            |     1 |       |     0   (0)| 00:00:01 |
|*173 |      HASH JOIN RIGHT OUTER             |                  |  1242 |   122K|    41   (5)| 00:00:01 |
| 174 |       TABLE ACCESS FULL                | USER$            |    63 |   882 |     2   (0)| 00:00:01 |
|*175 |       HASH JOIN                        |                  |  1242 |   105K|    39   (6)| 00:00:01 |
| 176 |        TABLE ACCESS FULL               | TS$              |     7 |   126 |     3   (0)| 00:00:01 |
|*177 |        HASH JOIN                       |                  |  1242 | 85698 |    35   (3)| 00:00:01 |
| 178 |         TABLE ACCESS FULL              | FILE$            |     5 |    45 |     2   (0)| 00:00:01 |
|*179 |         TABLE ACCESS FULL              | SEG$             |  1242 | 74520 |    32   (0)| 00:00:01 |
| 180 |   VIEW                                 | DBA_OBJECTS      | 48251 |  8340K|   147   (6)| 00:00:02 |
| 181 |    UNION-ALL                           |                  |       |       |            |       |
|*182 |     FILTER                             |                  |       |       |            |       |
|*183 |      HASH JOIN                         |                  | 51423 |  4670K|   145   (6)| 00:00:02 |
| 184 |       TABLE ACCESS FULL                | USER$            |    63 |   882 |     2   (0)| 00:00:01 |
|*185 |       TABLE ACCESS FULL                | OBJ$             | 51423 |  3967K|   141   (5)| 00:00:02 |
|*186 |      TABLE ACCESS BY INDEX ROWID       | IND$             |     1 |     8 |     2   (0)| 00:00:01 |
|*187 |       INDEX UNIQUE SCAN                | I_IND1           |     1 |       |     1   (0)| 00:00:01 |
| 188 |     TABLE ACCESS BY INDEX ROWID        | LINK$            |     1 |    88 |     0   (0)| 00:00:01 |
| 189 |      NESTED LOOPS                      |                  |     1 |   102 |     2   (0)| 00:00:01 |
| 190 |       TABLE ACCESS FULL                | USER$            |    63 |   882 |     2   (0)| 00:00:01 |
|*191 |       INDEX RANGE SCAN                 | I_LINK1          |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

  16 - filter(NULL IS NOT NULL)
  17 - filter(BITAND("T"."PROPERTY",1024)=0)
……


统计信息
----------------------------------------------------------
        572  recursive calls
          0  db block gets
        118  consistent gets
          3  physical reads
          0  redo size
       2802  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
         19  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>
SQL>
SQL> alter session set "_optimizer_mjc_enabled" = true;

会话已更改。

已用时间:  00: 00: 00.00
SQL> select * from dba_objects,dba_segments,dba_extents where 1=0;

未选定行

已用时间:  00: 00: 00.00

执行计划
----------------------------------------------------------
Plan hash value: 1612522449

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                  |  1195M|   775G|  3644K  (6)| 12:08:50 |
|   1 |  MERGE JOIN CARTESIAN                  |                  |  1195M|   775G|  3644K  (6)| 12:08:50 |
|   2 |   MERGE JOIN CARTESIAN                 |                  | 24772 |    12M| 11240   (2)| 00:02:15 |
|   3 |    VIEW                                | DBA_EXTENTS      |    11 |  2002 |    28   (0)| 00:00:01 |
|   4 |     UNION-ALL                          |                  |       |       |            |       |
|   5 |      NESTED LOOPS                      |                  |     1 |   256 |    15   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                     |                  |     1 |   247 |    15   (0)| 00:00:01 |
|   7 |        VIEW                            | SYS_DBA_SEGS     |     3 |   468 |    12   (0)| 00:00:01 |
|   8 |         UNION-ALL                      |                  |       |       |            |       |
|   9 |          NESTED LOOPS OUTER            |                  |     1 |   161 |    12   (0)| 00:00:01 |
|  10 |           NESTED LOOPS                 |                  |     1 |   147 |    11   (0)| 00:00:01 |
|  11 |            NESTED LOOPS                |                  |     1 |   109 |    10   (0)| 00:00:01 |
|  12 |             NESTED LOOPS               |                  |     1 |    94 |     9   (0)| 00:00:01 |
|  13 |              NESTED LOOPS              |                  |     1 |    88 |     9   (0)| 00:00:01 |
|  14 |               VIEW                     | SYS_OBJECTS      |     9 |   621 |     5 (100)| 00:00:01 |
|  15 |                UNION-ALL               |                  |       |       |            |       |
|* 16 |                 FILTER                 |                  |       |       |            |       |
|* 17 |                  TABLE ACCESS FULL     | TAB$             |  1606 | 36938 |   192   (1)| 00:00:03 |
|* 18 |                 FILTER                 |                  |       |       |            |       |
|  19 |                  TABLE ACCESS FULL     | TABPART$         |   146 |  2336 |     3   (0)| 00:00:01 |
|* 20 |                 FILTER                 |                  |       |       |            |       |
|  21 |                  TABLE ACCESS FULL     | CLU$             |    10 |   130 |   192   (1)| 00:00:03 |
|* 22 |                 FILTER                 |                  |       |       |            |       |
|* 23 |                  TABLE ACCESS FULL     | IND$             |  2360 | 44840 |   192   (1)| 00:00:03 |
|* 24 |                 FILTER                 |                  |       |       |            |       |
|  25 |                  TABLE ACCESS FULL     | INDPART$         |   295 |  4720 |     4   (0)| 00:00:01 |
|* 26 |                 FILTER                 |                  |       |       |            |       |
|* 27 |                  TABLE ACCESS FULL     | LOB$             |   565 | 11300 |   191   (1)| 00:00:03 |
|* 28 |                 FILTER                 |                  |       |       |            |       |
|  29 |                  TABLE ACCESS FULL     | TABSUBPART$      |     1 |    52 |     2   (0)| 00:00:01 |
|* 30 |                 FILTER                 |                  |       |       |            |       |
|  31 |                  TABLE ACCESS FULL     | INDSUBPART$      |     1 |    52 |     2   (0)| 00:00:01 |
|* 32 |                 FILTER                 |                  |       |       |            |       |
|  33 |                  TABLE ACCESS FULL     | LOBFRAG$         |     1 |    16 |     2   (0)| 00:00:01 |
|* 34 |               TABLE ACCESS CLUSTER     | SEG$             |     1 |    19 |     1   (0)| 00:00:01 |
|* 35 |                INDEX UNIQUE SCAN       | I_FILE#_BLOCK#   |     1 |       |     0   (0)| 00:00:01 |
|* 36 |              INDEX UNIQUE SCAN         | I_FILE2          |     1 |     6 |     0   (0)| 00:00:01 |
|  37 |             TABLE ACCESS CLUSTER       | TS$              |     1 |    15 |     1   (0)| 00:00:01 |
|* 38 |              INDEX UNIQUE SCAN         | I_TS#            |     1 |       |     0   (0)| 00:00:01 |
|* 39 |            TABLE ACCESS BY INDEX ROWID | OBJ$             |     1 |    38 |     1   (0)| 00:00:01 |
|* 40 |             INDEX UNIQUE SCAN          | I_OBJ1           |     1 |       |     0   (0)| 00:00:01 |
|  41 |           TABLE ACCESS CLUSTER         | USER$            |     1 |    14 |     1   (0)| 00:00:01 |
|* 42 |            INDEX UNIQUE SCAN           | I_USER#          |     1 |       |     0   (0)| 00:00:01 |
|* 43 |          FILTER                        |                  |       |       |            |       |
|  44 |           NESTED LOOPS                 |                  |     1 |    81 |    15   (0)| 00:00:01 |
|  45 |            NESTED LOOPS OUTER          |                  |     1 |    66 |    14   (0)| 00:00:01 |
|  46 |             NESTED LOOPS               |                  |     1 |    52 |    13   (0)| 00:00:01 |
|  47 |              NESTED LOOPS              |                  |     1 |    46 |    13   (0)| 00:00:01 |
|* 48 |               TABLE ACCESS FULL        | UNDO$            |    11 |   264 |     2   (0)| 00:00:01 |
|* 49 |               TABLE ACCESS CLUSTER     | SEG$             |     1 |    22 |     1   (0)| 00:00:01 |
|* 50 |                INDEX UNIQUE SCAN       | I_FILE#_BLOCK#   |     1 |       |     0   (0)| 00:00:01 |
|* 51 |              INDEX UNIQUE SCAN         | I_FILE2          |     1 |     6 |     0   (0)| 00:00:01 |
|  52 |             TABLE ACCESS CLUSTER       | USER$            |     1 |    14 |     1   (0)| 00:00:01 |
|* 53 |              INDEX UNIQUE SCAN         | I_USER#          |     1 |       |     0   (0)| 00:00:01 |
|  54 |            TABLE ACCESS CLUSTER        | TS$              |     1 |    15 |     1   (0)| 00:00:01 |
|* 55 |             INDEX UNIQUE SCAN          | I_TS#            |     1 |       |     0   (0)| 00:00:01 |
|* 56 |          FILTER                        |                  |       |       |            |       |
|  57 |           NESTED LOOPS                 |                  |     1 |    60 |    36   (3)| 00:00:01 |
|  58 |            NESTED LOOPS                |                  |     1 |    45 |    35   (3)| 00:00:01 |
|  59 |             NESTED LOOPS OUTER         |                  |     1 |    36 |    34   (3)| 00:00:01 |
|* 60 |              TABLE ACCESS FULL         | SEG$             |     1 |    22 |    33   (4)| 00:00:01 |
|  61 |              TABLE ACCESS CLUSTER      | USER$            |     1 |    14 |     1   (0)| 00:00:01 |
|* 62 |               INDEX UNIQUE SCAN        | I_USER#          |     1 |       |     0   (0)| 00:00:01 |
|  63 |             TABLE ACCESS BY INDEX ROWID| FILE$            |     1 |     9 |     1   (0)| 00:00:01 |
|* 64 |              INDEX UNIQUE SCAN         | I_FILE2          |     1 |       |     0   (0)| 00:00:01 |
|  65 |            TABLE ACCESS CLUSTER        | TS$              |     1 |    15 |     1   (0)| 00:00:01 |
|* 66 |             INDEX UNIQUE SCAN          | I_TS#            |     1 |       |     0   (0)| 00:00:01 |
|  67 |        TABLE ACCESS CLUSTER            | UET$             |     1 |    91 |     1   (0)| 00:00:01 |
|* 68 |         INDEX UNIQUE SCAN              | I_FILE#_BLOCK#   |     1 |       |     0   (0)| 00:00:01 |
|  69 |       TABLE ACCESS BY INDEX ROWID      | FILE$            |     1 |     9 |     0   (0)| 00:00:01 |
|* 70 |        INDEX UNIQUE SCAN               | I_FILE2          |     1 |       |     0   (0)| 00:00:01 |
|  71 |      NESTED LOOPS                      |                  |    10 |  2560 |    13   (0)| 00:00:01 |
|  72 |       NESTED LOOPS                     |                  |    10 |  2470 |    12   (0)| 00:00:01 |
|  73 |        VIEW                            | SYS_DBA_SEGS     |     3 |   468 |    12   (0)| 00:00:01 |
|  74 |         UNION-ALL                      |                  |       |       |            |       |
|  75 |          NESTED LOOPS OUTER            |                  |     1 |   161 |    12   (0)| 00:00:01 |
|  76 |           NESTED LOOPS                 |                  |     1 |   147 |    11   (0)| 00:00:01 |
|  77 |            NESTED LOOPS                |                  |     1 |   109 |    10   (0)| 00:00:01 |
|  78 |             NESTED LOOPS               |                  |     1 |    94 |     9   (0)| 00:00:01 |
|  79 |              NESTED LOOPS              |                  |     1 |    88 |     9   (0)| 00:00:01 |
|  80 |               VIEW                     | SYS_OBJECTS      |     9 |   621 |     5 (100)| 00:00:01 |
|  81 |                UNION-ALL               |                  |       |       |            |       |
|* 82 |                 FILTER                 |                  |       |       |            |       |
|* 83 |                  TABLE ACCESS FULL     | TAB$             |  1606 | 36938 |   192   (1)| 00:00:03 |
|* 84 |                 FILTER                 |                  |       |       |            |       |
|  85 |                  TABLE ACCESS FULL     | TABPART$         |   146 |  2336 |     3   (0)| 00:00:01 |
|* 86 |                 FILTER                 |                  |       |       |            |       |
|  87 |                  TABLE ACCESS FULL     | CLU$             |    10 |   130 |   192   (1)| 00:00:03 |
|* 88 |                 FILTER                 |                  |       |       |            |       |
|* 89 |                  TABLE ACCESS FULL     | IND$             |  2360 | 44840 |   192   (1)| 00:00:03 |
|* 90 |                 FILTER                 |                  |       |       |            |       |
|  91 |                  TABLE ACCESS FULL     | INDPART$         |   295 |  4720 |     4   (0)| 00:00:01 |
|* 92 |                 FILTER                 |                  |       |       |            |       |
|* 93 |                  TABLE ACCESS FULL     | LOB$             |   565 | 11300 |   191   (1)| 00:00:03 |
|* 94 |                 FILTER                 |                  |       |       |            |       |
|  95 |                  TABLE ACCESS FULL     | TABSUBPART$      |     1 |    52 |     2   (0)| 00:00:01 |
|* 96 |                 FILTER                 |                  |       |       |            |       |
|  97 |                  TABLE ACCESS FULL     | INDSUBPART$      |     1 |    52 |     2   (0)| 00:00:01 |
|* 98 |                 FILTER                 |                  |       |       |            |       |
|  99 |                  TABLE ACCESS FULL     | LOBFRAG$         |     1 |    16 |     2   (0)| 00:00:01 |
|*100 |               TABLE ACCESS CLUSTER     | SEG$             |     1 |    19 |     1   (0)| 00:00:01 |
|*101 |                INDEX UNIQUE SCAN       | I_FILE#_BLOCK#   |     1 |       |     0   (0)| 00:00:01 |
|*102 |              INDEX UNIQUE SCAN         | I_FILE2          |     1 |     6 |     0   (0)| 00:00:01 |
| 103 |             TABLE ACCESS CLUSTER       | TS$              |     1 |    15 |     1   (0)| 00:00:01 |
|*104 |              INDEX UNIQUE SCAN         | I_TS#            |     1 |       |     0   (0)| 00:00:01 |
|*105 |            TABLE ACCESS BY INDEX ROWID | OBJ$             |     1 |    38 |     1   (0)| 00:00:01 |
|*106 |             INDEX UNIQUE SCAN          | I_OBJ1           |     1 |       |     0   (0)| 00:00:01 |
| 107 |           TABLE ACCESS CLUSTER         | USER$            |     1 |    14 |     1   (0)| 00:00:01 |
|*108 |            INDEX UNIQUE SCAN           | I_USER#          |     1 |       |     0   (0)| 00:00:01 |
|*109 |          FILTER                        |                  |       |       |            |       |
| 110 |           NESTED LOOPS                 |                  |     1 |    81 |    15   (0)| 00:00:01 |
| 111 |            NESTED LOOPS OUTER          |                  |     1 |    66 |    14   (0)| 00:00:01 |
| 112 |             NESTED LOOPS               |                  |     1 |    52 |    13   (0)| 00:00:01 |
| 113 |              NESTED LOOPS              |                  |     1 |    46 |    13   (0)| 00:00:01 |
|*114 |               TABLE ACCESS FULL        | UNDO$            |    11 |   264 |     2   (0)| 00:00:01 |
|*115 |               TABLE ACCESS CLUSTER     | SEG$             |     1 |    22 |     1   (0)| 00:00:01 |
|*116 |                INDEX UNIQUE SCAN       | I_FILE#_BLOCK#   |     1 |       |     0   (0)| 00:00:01 |
|*117 |              INDEX UNIQUE SCAN         | I_FILE2          |     1 |     6 |     0   (0)| 00:00:01 |
| 118 |             TABLE ACCESS CLUSTER       | USER$            |     1 |    14 |     1   (0)| 00:00:01 |
|*119 |              INDEX UNIQUE SCAN         | I_USER#          |     1 |       |     0   (0)| 00:00:01 |
| 120 |            TABLE ACCESS CLUSTER        | TS$              |     1 |    15 |     1   (0)| 00:00:01 |
|*121 |             INDEX UNIQUE SCAN          | I_TS#            |     1 |       |     0   (0)| 00:00:01 |
|*122 |          FILTER                        |                  |       |       |            |       |
| 123 |           NESTED LOOPS                 |                  |     1 |    60 |    36   (3)| 00:00:01 |
| 124 |            NESTED LOOPS                |                  |     1 |    45 |    35   (3)| 00:00:01 |
| 125 |             NESTED LOOPS OUTER         |                  |     1 |    36 |    34   (3)| 00:00:01 |
|*126 |              TABLE ACCESS FULL         | SEG$             |     1 |    22 |    33   (4)| 00:00:01 |
| 127 |              TABLE ACCESS CLUSTER      | USER$            |     1 |    14 |     1   (0)| 00:00:01 |
|*128 |               INDEX UNIQUE SCAN        | I_USER#          |     1 |       |     0   (0)| 00:00:01 |
| 129 |             TABLE ACCESS BY INDEX ROWID| FILE$            |     1 |     9 |     1   (0)| 00:00:01 |
|*130 |              INDEX UNIQUE SCAN         | I_FILE2          |     1 |       |     0   (0)| 00:00:01 |
| 131 |            TABLE ACCESS CLUSTER        | TS$              |     1 |    15 |     1   (0)| 00:00:01 |
|*132 |             INDEX UNIQUE SCAN          | I_TS#            |     1 |       |     0   (0)| 00:00:01 |
|*133 |        FIXED TABLE FIXED INDEX         | X$KTFBUE (ind:1) |     3 |   273 |     0   (0)| 00:00:01 |
| 134 |       TABLE ACCESS BY INDEX ROWID      | FILE$            |     1 |     9 |     1   (0)| 00:00:01 |
|*135 |        INDEX UNIQUE SCAN               | I_FILE2          |     1 |       |     0   (0)| 00:00:01 |
| 136 |    BUFFER SORT                         |                  |  2252 |   743K| 11240   (2)| 00:02:15 |
| 137 |     VIEW                               | SYS_DBA_SEGS     |  2252 |   743K|  1019   (2)| 00:00:13 |
| 138 |      UNION-ALL                         |                  |       |       |            |       |
|*139 |       HASH JOIN RIGHT OUTER            |                  |  1009 |   201K|   962   (2)| 00:00:12 |
| 140 |        TABLE ACCESS FULL               | USER$            |    63 |   882 |     2   (0)| 00:00:01 |
|*141 |        HASH JOIN                       |                  |  1009 |   188K|   960   (2)| 00:00:12 |
|*142 |         HASH JOIN                      |                  |  1009 |   148K|   820   (1)| 00:00:10 |
| 143 |          TABLE ACCESS FULL             | TS$              |     7 |   126 |     3   (0)| 00:00:01 |
| 144 |          NESTED LOOPS                  |                  |  1009 |   131K|   817   (1)| 00:00:10 |
|*145 |           HASH JOIN                    |                  |  1009 |   122K|   814   (1)| 00:00:10 |
| 146 |            TABLE ACCESS FULL           | SEG$             |  4668 |   250K|    33   (4)| 00:00:01 |
| 147 |            VIEW                        | SYS_OBJECTS      |  4985 |   335K|   781   (1)| 00:00:10 |
| 148 |             UNION-ALL                  |                  |       |       |            |       |
|*149 |              TABLE ACCESS FULL         | TAB$             |  1606 | 36938 |   192   (1)| 00:00:03 |
| 150 |              TABLE ACCESS FULL         | TABPART$         |   146 |  2336 |     3   (0)| 00:00:01 |
| 151 |              TABLE ACCESS FULL         | CLU$             |    10 |   130 |   192   (1)| 00:00:03 |
|*152 |              TABLE ACCESS FULL         | IND$             |  2360 | 44840 |   192   (1)| 00:00:03 |
| 153 |              TABLE ACCESS FULL         | INDPART$         |   295 |  4720 |     4   (0)| 00:00:01 |
|*154 |              TABLE ACCESS FULL         | LOB$             |   565 | 11300 |   191   (1)| 00:00:03 |
| 155 |              TABLE ACCESS FULL         | TABSUBPART$      |     1 |    52 |     2   (0)| 00:00:01 |
| 156 |              TABLE ACCESS FULL         | INDSUBPART$      |     1 |    52 |     2   (0)| 00:00:01 |
| 157 |              TABLE ACCESS FULL         | LOBFRAG$         |     1 |    16 |     2   (0)| 00:00:01 |
| 158 |           TABLE ACCESS BY INDEX ROWID  | FILE$            |     1 |     9 |     1   (0)| 00:00:01 |
|*159 |            INDEX UNIQUE SCAN           | I_FILE2          |     1 |       |     0   (0)| 00:00:01 |
| 160 |         TABLE ACCESS FULL              | OBJ$             | 51427 |  2008K|   138   (3)| 00:00:02 |
| 161 |       NESTED LOOPS                     |                  |     1 |   126 |    16   (0)| 00:00:01 |
| 162 |        NESTED LOOPS                    |                  |     1 |   108 |    15   (0)| 00:00:01 |
| 163 |         NESTED LOOPS OUTER             |                  |     1 |    99 |    14   (0)| 00:00:01 |
| 164 |          NESTED LOOPS                  |                  |     1 |    85 |    13   (0)| 00:00:01 |
|*165 |           TABLE ACCESS FULL            | UNDO$            |    11 |   297 |     2   (0)| 00:00:01 |
|*166 |           TABLE ACCESS CLUSTER         | SEG$             |     1 |    58 |     1   (0)| 00:00:01 |
|*167 |            INDEX UNIQUE SCAN           | I_FILE#_BLOCK#   |     1 |       |     0   (0)| 00:00:01 |
| 168 |          TABLE ACCESS CLUSTER          | USER$            |     1 |    14 |     1   (0)| 00:00:01 |
|*169 |           INDEX UNIQUE SCAN            | I_USER#          |     1 |       |     0   (0)| 00:00:01 |
| 170 |         TABLE ACCESS BY INDEX ROWID    | FILE$            |     1 |     9 |     1   (0)| 00:00:01 |
|*171 |          INDEX UNIQUE SCAN             | I_FILE2          |     1 |       |     0   (0)| 00:00:01 |
| 172 |        TABLE ACCESS CLUSTER            | TS$              |     1 |    18 |     1   (0)| 00:00:01 |
|*173 |         INDEX UNIQUE SCAN              | I_TS#            |     1 |       |     0   (0)| 00:00:01 |
|*174 |       HASH JOIN RIGHT OUTER            |                  |  1242 |   122K|    41   (5)| 00:00:01 |
| 175 |        TABLE ACCESS FULL               | USER$            |    63 |   882 |     2   (0)| 00:00:01 |
|*176 |        HASH JOIN                       |                  |  1242 |   105K|    39   (6)| 00:00:01 |
| 177 |         TABLE ACCESS FULL              | TS$              |     7 |   126 |     3   (0)| 00:00:01 |
|*178 |         HASH JOIN                      |                  |  1242 | 85698 |    35   (3)| 00:00:01 |
| 179 |          TABLE ACCESS FULL             | FILE$            |     5 |    45 |     2   (0)| 00:00:01 |
|*180 |          TABLE ACCESS FULL             | SEG$             |  1242 | 74520 |    32   (0)| 00:00:01 |
| 181 |   BUFFER SORT                          |                  | 48251 |  8340K|  3643K  (6)| 12:08:38 |
| 182 |    VIEW                                | DBA_OBJECTS      | 48251 |  8340K|   147   (6)| 00:00:02 |
| 183 |     UNION-ALL                          |                  |       |       |            |       |
|*184 |      FILTER                            |                  |       |       |            |       |
|*185 |       HASH JOIN                        |                  | 51423 |  4670K|   145   (6)| 00:00:02 |
| 186 |        TABLE ACCESS FULL               | USER$            |    63 |   882 |     2   (0)| 00:00:01 |
|*187 |        TABLE ACCESS FULL               | OBJ$             | 51423 |  3967K|   141   (5)| 00:00:02 |
|*188 |       TABLE ACCESS BY INDEX ROWID      | IND$             |     1 |     8 |     2   (0)| 00:00:01 |
|*189 |        INDEX UNIQUE SCAN               | I_IND1           |     1 |       |     1   (0)| 00:00:01 |
| 190 |      TABLE ACCESS BY INDEX ROWID       | LINK$            |     1 |    88 |     0   (0)| 00:00:01 |
| 191 |       NESTED LOOPS                     |                  |     1 |   102 |     2   (0)| 00:00:01 |
| 192 |        TABLE ACCESS FULL               | USER$            |    63 |   882 |     2   (0)| 00:00:01 |
|*193 |        INDEX RANGE SCAN                | I_LINK1          |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

  16 - filter(NULL IS NOT NULL)
  17 - filter(BITAND("T"."PROPERTY",1024)=0)
  18 - filter(NULL IS NOT NULL)
  20 - filter(NULL IS NOT NULL)
 ……


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       2802  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13750068/viewspace-696110/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 短歌行
请登录后发表评论 登录
全部评论

注册时间:2008-10-17

  • 博文量
    330
  • 访问量
    1043911