Oracle 执行计划中的buffer sort

朴灿烈づ我的快乐病毒、 2022-08-06 05:30 223阅读 0赞

Oracle 执行计划中的buffer sort实际上没有排序，只是把数据加载到内存，不扫描多次表。

\--制造数据

drop table test purge;  
drop table test1 purge;  
create table test as select \* from dba\_objects where rownum<10;  
create table test1 as select \* from dba\_objects where rownum<10;  
create index ind\_t\_object\_id on test(object\_id);  
create index ind\_t1\_object\_id on test1(object\_id);  
exec dbms\_stats.gather\_table\_stats(user,'test',cascade => true);  
exec dbms\_stats.gather\_table\_stats(user,'test1',cascade => true);  
\--执行SQL  
select /\*+gg\_test\*/\* from test t, test1 t1 where t.object\_name like 'T%';

\----------------------------------------------------------  
Plan hash value: 702683263  
\------------------------------------------------------------------------------  
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |  
\------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT     |       |     9 |  1350 |     6   (0)| 00:00:01 |  
|   1 |  MERGE JOIN CARTESIAN|       |     9 |  1350 |     6   (0)| 00:00:01 |  
|\*  2 |   TABLE ACCESS FULL  | TEST  |     1 |    75 |     3   (0)| 00:00:01 |  
|   3 |   BUFFER SORT        |       |     9 |   675 |     3   (0)| 00:00:01 |  
|   4 |    TABLE ACCESS FULL | TEST1 |     9 |   675 |     3   (0)| 00:00:01 |  
\------------------------------------------------------------------------------  
Predicate Information (identified by operation id):  
\---------------------------------------------------  
   2 - filter("T"."OBJECT\_NAME" LIKE 'T%')  
统计信息  
\----------------------------------------------------------  
          1  recursive calls  
          0  db block gets  
          3  consistent gets  
          0  physical reads  
          0  redo size  
       1991  bytes sent via SQL\*Net to client  
        348  bytes received via SQL\*Net from client  
          1  SQL\*Net roundtrips to/from client  
          0  sorts (memory)  
          0  sorts (disk)  
          0  rows processed

\--\#keys=0表示没有排序，[Oracle中如何判断SQL是否真正做了排序][Oracle_SQL]  
select projection  
  from v$sql\_plan\_statistics\_all  
 where sql\_id in(select sql\_id from v$sql where   
   sql\_text like'select /\*+gg\_test\*/\* from test t, test1 t1 where t.object\_name like ''T%'' ')  
and operation='BUFFER' and options='SORT';

(\#keys=0) "T1"."OWNER"\[VARCHAR2,30\], "T1"."OBJECT\_NAME"\[VARCHAR2,128\], "T1"."SUBOBJECT\_NAME"\[VARCHAR2,30\], "T1"."OBJECT\_ID"\[NUMBER,22\], "T1"."DATA\_OBJECT\_ID"\[NUMBER,22\], "T1"."OBJECT\_TYPE"\[VARCHAR2,19\], "T1"."CREATED"\[DATE,7\], "T1"."LAST\_DDL\_TIME"\[DATE,7\], "T1"."TIMESTAMP"\[VARCHAR2,19\], "T1"."STATUS"\[VARCHAR2,7\], "T1"."TEMPORARY"\[VARCHAR2,1\], "T1"."GENERATED"\[VARCHAR2,1\], "T1"."SECONDARY"\[VARCHAR2,1\], "T1"."NAMESPACE"\[NUMBER,22\], "T1"."EDITION\_NAME"\[VARCHAR2,30\]

[Oracle_SQL]: http://blog.csdn.net/stevendbaguo/article/details/42168261