查看绑定变量值
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)