[转]BUFFER SORT是BUFFER却不是SORT

用AUTOTRACE查看执行的计划的同学常问到执行计划里的BUFFER SORT是什么意思,这里为什么要排序呢?

BUFFER SORT不是一种排序,而是一种临时表的创建方式。

BUFFER是执行计划想要表达的重点,是其操作: 在内存中存放一张临时表。

SORT修饰BUFFER,表示具体在内存的什么地方存放临时表: 在PGA的SQL工作区里的排序区。

至少有一种方法可以说服对此表示怀疑的人们,就是查询V$SQL_PLAN_STATISTICS_ALL.PROJECTION字段。

将STATISTICS_LEVEL设置为ALL先,然后执行真-排序命令,比如:select hire_date,salary from hr.employees order by hire_date

然后查看其V$SQL_PLAN_STATISTICS_ALL.PROJECTION字段:

SYS@br//scripts> select projection from v$sql_plan_statistics_all where 
sql_id=(select sql_id from v$sql where sql_text='select hire_date,salary from hr.employees order by hire_date') 
and operation='SORT' and options='ORDER BY';

PROJECTION
----------------------------------------------------------------
(#keys=1) "HIRE_DATE"[DATE,7], "SALARY"[NUMBER,22]

1 row selected.

其中开头的#keys表示返回的结果中排序的字段数量。

再执行一句真-排序命令:select hire_date,salary from hr.employees order by salary,hire_date

然后查看其V$SQL_PLAN_STATISTICS_ALL.PROJECTION字段,#keys因该为２:

SYS@br//scripts> select projection from v$sql_plan_statistics_all where 
sql_id=(select sql_id from v$sql where sql_text='select hire_date,salary from 
hr.employees order by salary,hire_date') and operation='SORT' and options='ORDER BY';

PROJECTION
------------------------------------------------------------------------------------
(#keys=2) "SALARY"[NUMBER,22], "HIRE_DATE"[DATE,7]

1 row selected.

来看看我们萌萌的BUFFER SORT的表现吧~

执行下面这个查询,它使用了所谓的BUFFER SORT:

select ch.channel_class,c.cust_city,sum(s.amount_sold) sales_amount
from sh.sales s,sh.customers c,sh.channels ch
where s.cust_id=c.cust_id and s.channel_id=ch.channel_id and
c.cust_state_province='CA' and
ch.channel_desc='Internet'
group by ch.channel_class,c.cust_city

附上其执行计划,Id为5的Operation是BUFFER SORT:
execution Plan
----------------------------------------------------------
Plan hash value: 3047021169

----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |   133 |  7980 |   902   (2)| 00:00:11 |       |       |
|   1 |  HASH GROUP BY         |           |   133 |  7980 |   902   (2)| 00:00:11 |       |       |
|*  2 |   HASH JOIN            |           | 12456 |   729K|   901   (2)| 00:00:11 |       |       |
|   3 |    MERGE JOIN CARTESIAN|           |   383 | 18001 |   408   (1)| 00:00:05 |       |       |
|*  4 |     TABLE ACCESS FULL  | CHANNELS  |     1 |    21 |     3   (0)| 00:00:01 |       |       |
|   5 |     BUFFER SORT        |           |   383 |  9958 |   405   (1)| 00:00:05 |       |       |
|*  6 |      TABLE ACCESS FULL | CUSTOMERS |   383 |  9958 |   405   (1)| 00:00:05 |       |       |
|   7 |    PARTITION RANGE ALL |           |   918K|    11M|   489   (2)| 00:00:06 |     1 |    28 |
|   8 |     TABLE ACCESS FULL  | SALES     |   918K|    11M|   489   (2)| 00:00:06 |     1 |    28 |
----------------------------------------------------------------------------------------------------

查看其V$SQL_PLAN_STATISTICS_ALL.PROJECTION字段:

SYS@br//scripts> select distinct projection from v$sql_plan_statistics_all where sql_id in 
(select distinct sql_id from v$sql where sql_text like 
'%where s.cust_id=c.cust_id and s.channel_id=ch.channel_id and%') 
and operation='BUFFER' and options='SORT';

PROJECTION
-------------------------------------------------------------------------
(#keys=0) "C"."CUST_ID"[NUMBER,22], "C"."CUST_CITY"[VARCHAR2,30]

1 row selected.

结果#keys等于0,是0啊… 0意味着该操作根据0个字段排序,那就是没有排序咯。

同样显示SORT但是不SORT打着左灯向右转的还有著名的SORT AGGREGATE。

只能这样说,AUTOTRACE中执行计划操作的取名有时真的太淘气了。

转自包光磊的博客：http://blogs.oracle.com/toddbao/entry/buffer_sort%E6%98%AFbuffer%E5%8D%B4%E4%B8%8D%E6%98%AFsort