merge join cartesian产生的一种情况

查看绑定变量值

dba_hist_sqlbind

查看快照历史

dba_hist_snapshot

查看sql所用的优化器的环境

 v$sql_optimizer_env

查看工作区的使用情况

select * from v$sql_workarea;

产生cartesian的一种情况是使用了ordered提示

SQL> select /*+ ordered*/ count(*) from t_user u,t_datafiles d,t_tablespaces ts where ts.tablespace_name=d.tablespace_name and u.default_tablespace=ts.tab
espace_name;




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


----------------------------------------------------------------------------------------
| Id  | Operation              | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |               |     1 |    51 |    29   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE        |               |     1 |    51 |            |          |
|*  2 |   HASH JOIN            |               |    18 |   918 |    29   (4)| 00:00:01 |
|   3 |    MERGE JOIN CARTESIAN|               |    92 |  3128 |    25   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL  | T_USER        |    23 |   391 |     3   (0)| 00:00:01 |
|   5 |     BUFFER SORT        |               |     4 |    68 |    22   (0)| 00:00:01 |
|   6 |      TABLE ACCESS FULL | T_DATAFILES   |     4 |    68 |     1   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL   | T_TABLESPACES |     5 |    85 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------


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


   2 - access("TS"."TABLESPACE_NAME"="D"."TABLESPACE_NAME" AND
              "U"."DEFAULT_TABLESPACE"="TS"."TABLESPACE_NAME")


Note
-----
   - dynamic sampling used for this statement




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