_optimizer_mjc_enabled

最新推荐文章于 2021-12-16 14:08:41 发布
最新推荐文章于 2021-12-16 14:08:41 发布
阅读量763 收藏
点赞数

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13750068/viewspace-696110/

cppg67031
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
path_optimizer:车辆的实时路径规划
05-07
path_optimizer 该ROS软件包为非完整车辆生成了可行的路径。模拟视频（1）（2）运行演示0.安装依赖项并进行构建Ubuntu 16.04上的ROS动力学OpenCV 3 mkdir -p workspace/src && cd workspace/srcgit clone git@github...
Oracle大表添加新列后，新数据查询响应很慢，但旧数据响应很快
qq_49122165的博客
09-01 1076
Oracle大表添加新列后，新数据查询响应很慢，但旧数据响应很快
HINT避免MERGE JOIN/*+ OPT_PARAM(\'_optimizer_mjc_enabled\',\'false/true\')
Daniel's Blog
12-17 2944
--alter session set “_optimizer_ignore_hints”=TRUE;  -- select /*+ opt_param(‘parallel_execution_enabled’, ‘false’) */ .. --swap_join_input(tab) hint to swap the order of the tables going through t
Oracle常见操作和命令
qq_40907977的博客
09-11 2462
在 Oracle 中，下面哪个命令可以修改用户的默认密码有效期为无限制？(单选) A. ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 90； ORACLE 修改默认密码有效期 180 天为无限制 1、查看用户的 proifle 是哪个，一般是 default： sql>SELECT username,PROFILE FROM dba_users; 2、查看指定概要文件（如 default）的密码有效期设置： sql>SELECT * FRO.
ORACLE 11GR2常用参数(含隐含参数)设置
Neviller_Ma的博客
12-16 1928
一audit_trail 审计（Audit）用于监视用户所执行的数据库操作，审计记录可存在数据字典表，当数据库的审计是开启时，在语句执行阶段产生审计记录。由于审计表（AUD$）存放在SYSTEM表空间，因此为了不影响系统的性能，保护SYSTEM表空间，建议把AUD$移动到其他的表空间上，或者关闭审计。 参考命令：alter system set audit_trail='NONE' #INSTANCE# scope=spfile; 二_optimizer_adaptive_cursor...
Classroom_Optimizer
02-17
在这个项目中，"Classroom_Optimizer" 可能是为了提升教育环境的效率和体验而设计的一款工具或平台。 在HTML开发中，我们关注以下几个核心知识点： 1. **HTML结构**：HTML文档由一系列元素组成，每个元素都有其...
query_optimizer
04-01
在IT行业中，`query_optimizer` 是数据库管理系统中的一个重要组件，主要负责SQL查询的优化工作。在数据库处理大量数据时，高效的查询优化对于系统的性能至关重要。本文将深入探讨`query_optimizer` 的工作原理、...
Route_Optimizer_OLA
05-06
Route_Optimizer_OLA 两条路线A..B和C..D的路线优化 给定两条路线，算法解决了优化路线以产生相对于原始路线具有最小偏差和最大重叠的新路线的问题。 在解决拼车问题时特别有用。 假设给定两条路径A..B和C..D。 ...
Python库 | genetic_optimizer-0.1.2.tar.gz
05-16
资源分类：Python库 所属语言：Python 资源全名：genetic_optimizer-0.1.2.tar.gz 资源来源：官方 安装方法：https://lanzao.blog.csdn.net/article/details/101784059
MERGE JOIN CARTESIAN
dpsong的专栏
12-15 955
MERGE JOIN CARTESIAN是由参数_optimizer_mjc_enabled来控制，可以设置SESSION级别的_optimizer_mjc_enabled=false，也可以修改SYSTEM级别的_optimizer_mjc_enabled=false来关闭此功能。也可以在单个SQL级别使用HINT来禁止，/*+ OPT_PARAM(_optimizer_mjc_e
sql调优之执行计划之笛卡尔连接————MERGE JOIN CARTESIAN
热门推荐
yuzhenhuan01的专栏
08-29 1万+
生产环境中遭遇temp空间不足的报错 定位产生异常的语句如下：select * from (select t.*, rownum rn from (select p.video_id, c.path, c.hosts, t.sha1 from converttype c left join tasks...
oracle 优化器走出 merge join cartesian 导致的超长执行时间
一名数据库爱好者的专栏
11-03 1611
os: oracle 11.2.0.4 抓取线上系统一个慢sql分析时，发现执行计划里有 merge join cartesian。根据以往经验，大概率为不合理的执行计划，详细分析表之间的关联时，发现确实会导致超大结果集后再和其它表关联 hint 通过添加 hint 调整 select /*+ ordered use_nl(a b) */ 修改 _optimizer_mjc_enabled a...
隐藏hint，参数配置及相关优化
Richie's 残酷舞台
09-17 1196
Oracle数据库有很多隐藏的参数配置文件，hint这些基本上属于不太会用到，但某些可以加入到代码中临时改变session参数。达到避免错误执行计划的效果， 仅列出几个第一次遇见的和链接 1. 隐藏配置参数， 特点以 “_”下划线开头，+作用点（optimizer,db etc）,+ 实际控制属性 + enabled（开关） alter system set “_optimizer_carte...
oracle 执行计划中出现 merge join cartesian
一名数据库爱好者的专栏
06-09 1407
instance禁用 SQL> alter system set "_optimizer_mjc_enabled" = false; session禁用 SQL> alter session set "_optimizer_mjc_enabled" = false; sql添加 hint select/*+ OPT_PARAM('_optimizer_mjc_enabled','false') */
要命的MERGE JOIN CARTESIAN
heqiyu34的专栏
05-07 4178
一个应用，突然出现响应缓慢，查看一下等待时间，发现有很多的数据读取的等待，而且每次执行读取的BLOCK数量明显比之前多，看看SQL的执行计划，发现跟之前的执行计划不一样，多了一个MERGE JOIN CARTESIAN 的过程，把这些进程杀掉，刷新下SHARED POOL，然后等SQL重新请求进来被解析，执行计划已经正确了，下面来看看这个MERGE JOIN CARTESIAN 是何方神圣
Oracle中的merge join Cartesian
Laughing
04-23 8286
在执行计划中，有时会出现CARTESIAN笛卡尔乘积,简单的说一下什么叫cartesian?就是有两个集合，每个集合的任意一个成员都要与另外一个集合的任意一个成员有关联...下面是关于cartesian的一些实验:SQL> set linesize 2000SQL> select * from tab;TNAME                          TABTYPE CLUSTERID------------------------------ ------- ----------T      
rcwa_stochastic_optimizer针对的文献
最新发布
05-14
rcwa_stochastic_optimizer针对的文献主要是研究材料光学特性模拟算法的文献。在材料光学模拟中，利用RCWA（Rigorous Coupled-Wave Analysis）方法可以快速准确地计算出光在复杂结构材料中的散射和透射现象，包括...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值