用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因该为2: 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
本文转自maclean_007 51CTO博客,原文链接:http://blog.51cto.com/maclean/1278927