用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中执行计划操作的取名有时真的太淘气了。
至关心的我的朋友们: 近来更新甚少是因为忙别的事情去了,忙到调不动DBWR,但是不代表LGWR不工作、Dirty Buffer不产生了,相反它们忙得很、多得不得了。
oracle执行计划：BUFFER SORT是BUFFER却不是SORT
原文：http://www.cnblogs.com/bolang100/p/6397520.html