何为buffer sort？

buffer sort is an optimization -- in this case, it is buffering the results
of
that full scan so as to avoid having to perform. LIOS on the data over and
over
and over in the cartesian join. the data may or may not actually be sorted
for
real.

在进行MERGE JOIN CARTESIAN操作时，有时会产生buffer sort，可以理解为在buffer中的排序：

SQL> create table big tablespace users as select * from dba_objects;

Table created.

SQL> create table small tablespace users as select * from dba_objects where rownum=1;

Table created.

SQL>
SQL>  exec dbms_stats.gather_table_stats(user,'big',cascade=>true);

PL/SQL procedure successfully completed.

SQL>   exec dbms_stats.gather_table_stats(user,'small',cascade=>true);

PL/SQL procedure successfully completed.

SQL> set autot traceonly
SQL> select * from big,small;

9459 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2081875483

------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |  9459 |  1422K|    32   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|       |  9459 |  1422K|    32   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | SMALL |     1 |    70 |     3   (0)| 00:00:01 |
|   3 |   BUFFER SORT        |       |  9459 |   775K|    29   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | BIG   |  9459 |   775K|    29   (0)| 00:00:01 |
------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
        761  recursive calls
          0  db block gets
        223  consistent gets
          0  physical reads
          0  redo size
     448751  bytes sent via SQL*Net to client
       7311  bytes received via SQL*Net from client
        632  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
       9459  rows processed

将隐含参数_optimizer_sortmerge_join_enabled设置为false，可以禁止改操作：

SQL>  alter session set "_optimizer_sortmerge_join_enabled"=false;

Session altered.

SQL>  select * from big,small;

9459 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 53924517

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |  9459 |  1422K|    32   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |       |  9459 |  1422K|    32   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| SMALL |     1 |    70 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| BIG   |  9459 |   775K|    29   (0)| 00:00:01 |
----------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        749  consistent gets
          0  physical reads
          0  redo size
     448751  bytes sent via SQL*Net to client
       7311  bytes received via SQL*Net from client
        632  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       9459  rows processed

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-623194/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-623194/