[转]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因该为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  | 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

 

Internal_Function with Encryption in SQL PLAN

Sometimes,the columns are decrypted as a result and decrypt functions (appears as INTERNAL_FUNCTION in the execution plan) are applied on them, which can lead to poor approximations of column selectivity, leading to improper plans. This happens mostly when the encrypted columns are using SALT to encrypt the data, but it can happen for other reasons as well, including bugs.

Bug:7147087 AFTER ENABLING TDE, EXECUTION PLAN CHANGES FOR THE WORSE

and it can be recognized from the following symptoms:

1. both tables participating in a join have encrypted columns.
2. there is at least a join condition with encrypted columns at both ends.
3. the second table has an index on the join column(s).
4. the INTERNAL_FUNCTION is applied to the encrypted columns in the join in the second table and the execution plan that used to be an INDEX UNIQUE SCAN on the unenecrypted columns turns into an INDEX RANGE SCAN or FULL TABLE SCAN.

Scenario 2: Pushed Predicates

The second known TDE performance bug is the one when the queries are using pushed predicates on encrypted columns inside explicit or implicit views and the encrypted column values are decrypted to filter out the values instead of encrypting the pushed predicates. This situation is met when:

1. external predicates are pushed into views
2. the execution plan presents predicate of the form INTERNAL_FUNCTION(column) = ;

On the other hand, INTERNAL_FUNCTION may consume more memory and cpu than normal

[oracle@rh2 admin]$ pwd
/s01/oracle/product/11.2.0/dbhome_1/network/admin
[oracle@rh2 admin]$ cat sqlnet.ora 
ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY=/s01/wallet)))
SQL> ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "oracle";
System altered.
SQL> conn maclean/maclean
Connected.
SQL> create table enctab (t1 int encrypt);
Table created.
SQL> select * from enctab;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3026244987
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |    47 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| ENCTAB |     1 |    47 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
0  consistent gets
0  physical reads
0  redo size
330  bytes sent via SQL*Net to client
512  bytes received via SQL*Net from client
1  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
0  rows processed
SQL> select * from enctab where t1=10;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3026244987
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |    47 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ENCTAB |     1 |    47 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INTERNAL_FUNCTION("T1")=10)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
0  consistent gets
0  physical reads
0  redo size
330  bytes sent via SQL*Net to client
512  bytes received via SQL*Net from client
1  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
0  rows processed
SQL> desc enctab;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
T1                                                 NUMBER(38) ENCRYPT
SQL> col WRL_PARAMETER for a20
SQL> set linesize 140
SQL> select * from V$ENCRYPTION_WALLET;
WRL_TYPE             WRL_PARAMETER        STATUS
-------------------- -------------------- ------------------
file                 /s01/wallet          OPEN

沪ICP备14014813号

沪公网安备 31010802001379号