执行计划补充

优化sql需要建立在对执行计划深入理解的基础上，关于大部分的执行计划大家都有过接触，下面借助资料和测试用例补充一些比较少见的执行计划。 SQL create table tab001 as select * from dba_objects; SQL create index ind_owner001 on tab001(owner); SQL c

优化sql需要建立在对执行计划深入理解的基础上，关于大部分的执行计划大家都有过接触，下面借助资料和测试用例补充一些比较少见的执行计划。

SQL> create table tab001 as select * from dba_objects;
SQL> create index ind_owner001 on tab001(owner);
SQL> create index ind_object_type001 on tab001(object_type);
SQL> exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'TAB001',cascade=>true);

多个b tree索引转换为bitmap，然互bitmap and或者bitmap or后再转换为rowid回表：

SQL> select * from tab001 where object_type='LOB' and owner='MDSYS';

215 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1145989034

-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21 | 2058 | 13 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TAB001 | 21 | 2058 | 13 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP AND | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 5 | INDEX RANGE SCAN | IND_OBJECT_TYPE001 | 1000 | | 3 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 7 | INDEX RANGE SCAN | IND_OWNER001 | 1000 | | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

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

5 - access("OBJECT_TYPE"='LOB')
7 - access("OWNER"='MDSYS')

SQL> select * from tab001 where object_type='LOB' or owner='MDSYS';

2808 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4013421176

-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2803 | 268K| 291 (0)| 00:00:04 |
| 1 | TABLE ACCESS BY INDEX ROWID | TAB001 | 2803 | 268K| 291 (0)| 00:00:04 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP OR | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 5 | INDEX RANGE SCAN | IND_OWNER001 | | | 5 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 7 | INDEX RANGE SCAN | IND_OBJECT_TYPE001 | | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

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

5 - access("OWNER"='MDSYS')
7 - access("OBJECT_TYPE"='LOB')

看下视图合并，视图合并可以将视图外面的谓词条件推入到视图内部。

SQL> create view view_tab001 as select * from tab001 where object_name='OPW';

View created.

SQL> select * from view_tab001 where object_type='CLUSTER';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2567763011

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TAB001 | 1 | 98 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJECT_TYPE001 | 16 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME"='OPW')
2 - access("OBJECT_TYPE"='CLUSTER')

这里视图由于rownum的限制，导致无法将谓词推进到视图内部

SQL> create or replace view view_tab001 as select * from tab001 where object_name='OPW' and rownum
View created.

SQL> select * from view_tab001 where object_type='CLUSTER';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 770467758

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 414 | 344 (1)| 00:00:05 |
|* 1 | VIEW | VIEW_TAB001 | 2 | 414 | 344 (1)| 00:00:05 |
|* 2 | COUNT STOPKEY | | | | | |
|* 3 | TABLE ACCESS FULL| TAB001 | 2 | 196 | 344 (1)| 00:00:05 |
-----------------------------------------------------------------------------------

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

1 - filter("OBJECT_TYPE"='CLUSTER')
2 - filter(ROWNUM 3 - filter("OBJECT_NAME"='OPW')


Statistics
----------------------------------------------------------
18 recursive calls
0 db block gets
1258 consistent gets
0 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed

filter是过滤的意思，这里我们列出一个子查询中的filter来简单说明下：

SQL> select * from table(dbms_xplan.display_cursor('8uyp666q4gvg6',null,'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8uyp666q4gvg6, child number 0
-------------------------------------
select /*+gather_plan_statistics*/* from tab001 a where a.object_type
in (select /*+no_unnest*/object_type from tab002 b where b.object_id
Plan hash value: 1420963810

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 7822 |00:00:00.01 | 1862 |
|* 1 | FILTER | | 1 | | 7822 |00:00:00.01 | 1862 |
| 2 | TABLE ACCESS FULL | TAB001 | 1 | 86311 | 86311 |00:00:00.01 | 1751 |
|* 3 | TABLE ACCESS BY INDEX ROWID| TAB002 | 53 | 1 | 3 |00:00:00.01 | 111 |
|* 4 | INDEX RANGE SCAN | IND_OBJID_TAB002 | 53 | 8 | 406 |00:00:00.01 | 58 |
-----------------------------------------------------------------------------------------------------------

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

1 - filter( IS NOT NULL)
3 - filter("OBJECT_TYPE"=:B1)
4 - access("B"."OBJECT_ID"

tab001表返回了86311行数据，但是tab002表的ind_objid_tab002只走了53次index range scan，这里的filter是一种改良的nested loops，会对驱动结果集的做一个去重的动作，详细的可以参考之前写的文章 http://www.dbaxiaoyu.com/archives/2360

union和union all执行计划的区别：

SQL> select object_id,object_name from tab001 a union all select object_id,object_name from tab002 b;

Execution Plan
----------------------------------------------------------
Plan hash value: 910399089

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 172K| 5057K| 687 (1)| 00:00:09 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS FULL| TAB001 | 86311 | 2528K| 344 (1)| 00:00:05 |
| 3 | TABLE ACCESS FULL| TAB002 | 86315 | 2528K| 344 (1)| 00:00:05 |
-----------------------------------------------------------------------------

SQL> select object_id,object_name from tab001 a union select object_id,object_name from tab002 b;

Execution Plan
----------------------------------------------------------
Plan hash value: 4025140749

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 172K| 5057K| | 2099 (1)| 00:00:26 |
| 1 | SORT UNIQUE | | 172K| 5057K| 6800K| 2099 (1)| 00:00:26 |
| 2 | UNION-ALL | | | | | | |
| 3 | TABLE ACCESS FULL| TAB001 | 86311 | 2528K| | 344 (1)| 00:00:05 |
| 4 | TABLE ACCESS FULL| TAB002 | 86315 | 2528K| | 344 (1)| 00:00:05 |
--------------------------------------------------------------------------------------

union和union all相比会多一个排序的动作，union对应的执行计划也确实多出了sort unique的执行计划。

connect by是oracle数据库中的层次关键字,对于这类查询xiaoyu接触的并不多，简单先讲讲connect by递归查询的业务逻辑含义

比如start with object_id=3 connect by prior object_id=data_object_id的含义为：取数据从满足start with object_id=3 数据开始，当然表中可能有多行满足这个条件，然后再找只要表中的下一行数据的data_object_id等于上一行的object_id就满足，依次递归的查询直到表中没有满足条件的数据后返回数据。

SQL> create table tab_conn01 as select object_id,lag(object_id,1)over(order by object_id) data_object_id,object_name from tab001 where object_id SQL> exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'TAB_CONN01’);

SQL> select * from tab_conn01;

OBJECT_ID DATA_OBJECT_ID OBJECT_NAME
---------- -------------- ------------------------------
2 C_OBJ#
3 2 I_OBJ#
4 3 TAB$
5 4 CLU$
6 5 C_TS#
7 6 I_TS#
8 7 C_FILE#_BLOCK#
9 8 I_FILE#_BLOCK#
10 9 C_USER#

9 rows selected.

SQL> select a.*,level from tab_conn01 a start with object_id=3 connect by prior object_id=data_object_id;

OBJECT_ID DATA_OBJECT_ID OBJECT_NAME LEVEL
---------- -------------- -------------------- ----------
3 2 I_OBJ# 1
4 3 TAB$ 2
5 4 CLU$ 3
6 5 C_TS# 4
7 6 I_TS# 5
8 7 C_FILE#_BLOCK# 6
9 8 I_FILE#_BLOCK# 7
10 9 C_USER# 8

8 rows selected.


SQL> select a.* ,level from tab_conn01 a start with object_id=3 connect by object_id= prior data_object_id;

OBJECT_ID DATA_OBJECT_ID OBJECT_NAME LEVEL
---------- -------------- -------------------- ----------
3 2 I_OBJ# 1
2 C_OBJ# 2

关于connect by层次查询的业务逻辑就不再叙述，下面我们关注下这个查询的执行计划：

SQL> select * from tab_conn01 start with object_id=3 connect by prior object_id=data_object_id;

8 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 896851045

------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 184 | 135 (1)| 00:00:02 |
|* 1 | CONNECT BY NO FILTERING WITH START-WITH| | | | | |
| 2 | TABLE ACCESS FULL | TAB_CONN01 | 9 | 126 | 134 (0)| 00:00:02 |
------------------------------------------------------------------------------------------------------

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

1 - access("TAB_CONN01"."DATA_OBJECT_ID"=PRIOR "TAB_CONN01"."OBJECT_ID")
filter("OBJECT_ID"=3)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
476 consistent gets
0 physical reads
0 redo size
869 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
8 rows processed

如果表中的数据稍微多点，然后递归查询返回的结果集多点,cbo就可能选择另一种执行计划

SQL> create table tab_conn02 as select object_id,lag(object_id,1)over(order by object_id) data_object_id,object_name from dba_objects;

Table created.

SQL> exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'TAB_CONN02');

PL/SQL procedure successfully completed.

SQL> select * from tab_conn02 start with object_id=3 connect by prior object_id=data_object_id;

86318 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3789322130

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 184 | 406 (1)| 00:00:05 |
|* 1 | CONNECT BY WITH FILTERING| | | | | |
|* 2 | TABLE ACCESS FULL | TAB_CONN02 | 1 | 35 | 135 (1)| 00:00:02 |
|* 3 | HASH JOIN | | 1 | 48 | 269 (1)| 00:00:04 |
| 4 | CONNECT BY PUMP | | | | | |
| 5 | TABLE ACCESS FULL | TAB_CONN02 | 86319 | 2950K| 135 (1)| 00:00:02 |
----------------------------------------------------------------------------------------

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

1 - access("TAB_CONN02"."DATA_OBJECT_ID"=PRIOR "TAB_CONN02"."OBJECT_ID")
2 - filter("OBJECT_ID"=3)
3 - access("connect$_by$_pump$_002"."prior object_id"="DATA_OBJECT_ID")


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
41087844 consistent gets
0 physical reads
0 redo size
4137925 bytes sent via SQL*Net to client
63817 bytes received via SQL*Net from client
5756 SQL*Net roundtrips to/from client
86320 sorts (memory)
0 sorts (disk)
86318 rows processed

cob预估上述这种执行计划相对高效，但是加上NO_CONNECT_BY_FILTERING的hint后我们观察实际表扫描后CONNECT BY NO FILTERING WITH START-WITH往往更加高效

SQL> select /*+NO_CONNECT_BY_FILTERING*/ * from tab_conn02 a start with object_id=3 connect by prior object_id=data_object_id;

86318 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 969721844

------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 184 | 938 (86)| 00:00:12 |
|* 1 | CONNECT BY NO FILTERING WITH START-WITH| | | | | |
| 2 | TABLE ACCESS FULL | TAB_CONN02 | 86319 | 2950K| 135 (1)| 00:00:02 |
------------------------------------------------------------------------------------------------------

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

1 - access("A"."DATA_OBJECT_ID"=PRIOR "A"."OBJECT_ID")
filter("OBJECT_ID"=3)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
476 consistent gets
0 physical reads
0 redo size
4137925 bytes sent via SQL*Net to client
63817 bytes received via SQL*Net from client
5756 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
86318 rows processed

逻辑读从之前的四千多万的降低到了476，对于优化器的评估cost成本为什么和实际的sql的资源消耗如此大的差别，这种糟糕的执行计划究竟是如何工作的也没有找到合适的资料来进行解释，一般而言碰见这种connect by的sql语句反复多次扫描表段的sql语句都可以尝试添加hint no_connect_by_filteringl来让表能否扫描一次完成查询。

跟sort有关的执行计划：

SQL> select sum(object_id) from tab_conn01 ;


Execution Plan
----------------------------------------------------------
Plan hash value: 1562061203

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 134 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| TAB_CONN01 | 9 | 27 | 134 (0)| 00:00:02 |
---------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
504 consistent gets
12 physical reads
0 redo size
532 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select avg(object_id) from tab_conn01;


Execution Plan
----------------------------------------------------------
Plan hash value: 1562061203

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 134 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| TAB_CONN01 | 9 | 27 | 134 (0)| 00:00:02 |
---------------------------------------------------------------------------------


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

SQL> select min(object_id) from tab_conn01;


Execution Plan
----------------------------------------------------------
Plan hash value: 1562061203

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 134 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| TAB_CONN01 | 9 | 27 | 134 (0)| 00:00:02 |
---------------------------------------------------------------------------------


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

SQL> select max(object_id) from tab_conn01;


Execution Plan
----------------------------------------------------------
Plan hash value: 1562061203

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 134 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| TAB_CONN01 | 9 | 27 | 134 (0)| 00:00:02 |
---------------------------------------------------------------------------------


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

从执行计划中看见了sort aggregate一般对应于聚合函数，statistics部分sort非0的值，也有0值，其实根据statistics部分的sort来判断一个sql是否排序是不正确的。

SQL> alter system flush shared_pool;

System altered.

SQL> select sum(object_id) from tab_conn01 ;

Execution Plan
----------------------------------------------------------
Plan hash value: 1562061203

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 134 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| TAB_CONN01 | 9 | 27 | 134 (0)| 00:00:02 |
---------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
504 consistent gets
0 physical reads
0 redo size
532 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed

这里的sort部分是5，貌似都是在内存中排序，其实了sum求和是完全不需要排序的,同样的min、max和avg都是不需要排序的，有兴趣的同学可以用上面的方法验证下。

SQL> select sql_id from v$sql where sql_text like 'select sum(object_id) from tab_conn01%';

SQL_ID
-------------
3tnr9w2gb10t3

SQL> select * from table(dbms_xplan.display_cursor('3tnr9w2gb10t3',null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3tnr9w2gb10t3, child number 0
-------------------------------------
select sum(object_id) from tab_conn01

Plan hash value: 1562061203

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 134 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| TAB_CONN01 | 9 | 27 | 134 (0)| 00:00:02 |
---------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / TAB_CONN01@SEL$1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TAB_CONN01"@"SEL$1")
END_OUTLINE_DATA
*/

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=0) SUM("OBJECT_ID")[22]
2 - "OBJECT_ID"[NUMBER,22]

这里的Column Projection Information (identified by operation id):的 1 – (#keys=0) SUM(“OBJECT_ID”)[22]的#keys是0，这个表示这个sql是不完全不需要排序的。

order by一般对应于执行计划sort order by，如果没有索引可以利用这个排序是无法避免的，同样在Column Projection Information (identified by operation id): 1 – (#keys=1) “TAB_CONN01″.”OBJECT_ID”[NUMBER,22]的#keys是1。

SQL> select * from tab_conn01 order by object_id;

OBJECT_ID DATA_OBJECT_ID OBJECT_NAME
---------- -------------- ------------------------------
2 C_OBJ#
3 2 I_OBJ#
4 3 TAB$
5 4 CLU$
6 5 C_TS#
7 6 I_TS#
8 7 C_FILE#_BLOCK#
9 8 I_FILE#_BLOCK#
10 9 C_USER#

9 rows selected.
SQL> select sql_id from v$sql where sql_text like 'select * from tab_conn01 order by object_id%';

SQL_ID
-------------
3qzxfg5ycpz6t

SQL> select * from table(dbms_xplan.display_cursor('3qzxfg5ycpz6t',null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3qzxfg5ycpz6t, child number 0
-------------------------------------
select * from tab_conn01 order by object_id

Plan hash value: 525840827

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 135 (100)| |
| 1 | SORT ORDER BY | | 9 | 126 | 135 (1)| 00:00:02 |
| 2 | TABLE ACCESS FULL| TAB_CONN01 | 9 | 126 | 134 (0)| 00:00:02 |
---------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / TAB_CONN01@SEL$1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TAB_CONN01"@"SEL$1")
END_OUTLINE_DATA
*/

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=1) "TAB_CONN01"."OBJECT_ID"[NUMBER,22],
"TAB_CONN01"."OBJECT_NAME"[VARCHAR2,128],
"TAB_CONN01"."DATA_OBJECT_ID"[NUMBER,22]
2 - "TAB_CONN01"."OBJECT_ID"[NUMBER,22],
"TAB_CONN01"."DATA_OBJECT_ID"[NUMBER,22],
"TAB_CONN01"."OBJECT_NAME"[VARCHAR2,128]


44 rows selected.

来看看排序连接中的排序：

SQL> select /*+use_merge(a b)*/* from tab_conn01 a,tab_conn01 b where a.object_id=b.object_id;

9 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2830733959

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 252 | 270 (1)| 00:00:04 |
| 1 | MERGE JOIN | | 9 | 252 | 270 (1)| 00:00:04 |
| 2 | SORT JOIN | | 9 | 126 | 135 (1)| 00:00:02 |
| 3 | TABLE ACCESS FULL| TAB_CONN01 | 9 | 126 | 134 (0)| 00:00:02 |
|* 4 | SORT JOIN | | 9 | 126 | 135 (1)| 00:00:02 |
| 5 | TABLE ACCESS FULL| TAB_CONN01 | 9 | 126 | 134 (0)| 00:00:02 |
----------------------------------------------------------------------------------

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

4 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
filter("A"."OBJECT_ID"="B"."OBJECT_ID")


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
952 consistent gets
0 physical reads
0 redo size
1231 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
9 rows processed

再来看一个笛卡尔积中涉及的buffer sort，这里的buffer sort也不是代表的排序，而是将查询返回的数据load到pga中。

SQL> select * from tab_conn01 a,tab_conn01 b;

81 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1615069149

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 81 | 2268 | 1329 (1)| 00:00:16 |
| 1 | MERGE JOIN CARTESIAN| | 81 | 2268 | 1329 (1)| 00:00:16 |
| 2 | TABLE ACCESS FULL | TAB_CONN01 | 9 | 126 | 134 (0)| 00:00:02 |
| 3 | BUFFER SORT | | 9 | 126 | 1195 (1)| 00:00:15 |
| 4 | TABLE ACCESS FULL | TAB_CONN01 | 9 | 126 | 133 (0)| 00:00:02 |
-----------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
958 consistent gets
0 physical reads
0 redo size
3525 bytes sent via SQL*Net to client
578 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
81 rows processed

SQL> select sql_id from v$sql where sql_text like 'select * from tab_conn01 a,tab_conn01 b%';

SQL_ID
-------------
5t664aq72ydva

SQL> select * from table(dbms_xplan.display_cursor('5t664aq72ydva',null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5t664aq72ydva, child number 0
-------------------------------------
select * from tab_conn01 a,tab_conn01 b

Plan hash value: 1615069149

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1329 (100)| |
| 1 | MERGE JOIN CARTESIAN| | 81 | 2268 | 1329 (1)| 00:00:16 |
| 2 | TABLE ACCESS FULL | TAB_CONN01 | 9 | 126 | 134 (0)| 00:00:02 |
| 3 | BUFFER SORT | | 9 | 126 | 1195 (1)| 00:00:15 |
| 4 | TABLE ACCESS FULL | TAB_CONN01 | 9 | 126 | 133 (0)| 00:00:02 |
-----------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
2 - SEL$1 / A@SEL$1
4 - SEL$1 / B@SEL$1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "A"@"SEL$1")
FULL(@"SEL$1" "B"@"SEL$1")
LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")
USE_MERGE_CARTESIAN(@"SEL$1" "B"@"SEL$1")
END_OUTLINE_DATA
*/

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "A"."OBJECT_ID"[NUMBER,22], "A"."DATA_OBJECT_ID"[NUMBER,22],
"A"."OBJECT_NAME"[VARCHAR2,128], "B"."OBJECT_ID"[NUMBER,22],
"B"."DATA_OBJECT_ID"[NUMBER,22], "B"."OBJECT_NAME"[VARCHAR2,128]
2 - "A"."OBJECT_ID"[NUMBER,22], "A"."DATA_OBJECT_ID"[NUMBER,22],
"A"."OBJECT_NAME"[VARCHAR2,128]
3 - (#keys=0) "B"."OBJECT_ID"[NUMBER,22],
"B"."DATA_OBJECT_ID"[NUMBER,22], "B"."OBJECT_NAME"[VARCHAR2,128]
4 - "B"."OBJECT_ID"[NUMBER,22], "B"."DATA_OBJECT_ID"[NUMBER,22],
"B"."OBJECT_NAME"[VARCHAR2,128]

在oracle 10g后，group by和distinct可以用hash group by和hash unique执行计划来完成查询，取代了原来的sort unique和sort group by的执行计划，新的算法不需要排序就可以完成查询。

SQL> select distinct object_id from tab_conn01;

OBJECT_ID
----------
6
2
4
5
8
3
7
9
10

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3127877997

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 27 | 135 (1)| 00:00:02 |
| 1 | HASH UNIQUE | | 9 | 27 | 135 (1)| 00:00:02 |
| 2 | TABLE ACCESS FULL| TAB_CONN01 | 9 | 27 | 134 (0)| 00:00:02 |
---------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
476 consistent gets
0 physical reads
0 redo size
637 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed

SQL> select object_id from tab_conn01 group by object_id;

OBJECT_ID
----------
6
2
4
5
8
3
7
9
10

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1614676255

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 27 | 135 (1)| 00:00:02 |
| 1 | HASH GROUP BY | | 9 | 27 | 135 (1)| 00:00:02 |
| 2 | TABLE ACCESS FULL| TAB_CONN01 | 9 | 27 | 134 (0)| 00:00:02 |
---------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
476 consistent gets
0 physical reads
0 redo size
637 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed


SQL> select object_id from tab_conn01 group by object_id order by object_id;

OBJECT_ID
----------
&nb