Oracle Scalar Subquery Cache

本文为翻译贴，原文章请见：

http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html

Tom Kyte介绍了Oracle的ScalarSubquery Cache特性，并将之与determinstic function, function result cache做了性能比较。

所谓Scalar Subquery意即标量子查询，至多只返回一行，一列数据的子查询，常常出现在Select部分。例：

Select emp.ename, (select dept.dname from dept where dept.deptno=emp.deptno)From emp;

与以上select语句等价的是：

Select emp.ename, dept.dnameFrom emp left outer join dept on(emp.deptno=dept.deptno).

因为标量子查询可以返回0条记录，故而在第二条语句中应该是左连接。在运行时，这条语句形如：

Select emp.ename, (select dept.dname from dept where dept.deptno=:deptno)From emp;

Oracle将循环地取出emp表中所有deptno，并将其作为绑定变量值传入标量子查询(Scalar Subquery)。在Oracle 10g以上版本中，Oracle会维护一个大小（Slot size）255的哈希表。这个哈希表的key是标量子查询的输入：绑定变量:deptno，value是标量子查询的输出：dept.dname。假如哈希表里已经存在某个deptno，那么Oracle就直接取value，而不需要重复执行标量子查询语句。

当出现哈希冲撞时，后来的deptno将不会占据该slot。但是Oracle维护一个”last_key_value”变量，当连续出现重复deptno时，即使有哈希冲撞，Oracle将取出last_key_value的值，而不需要重复执行标量子查询。

下面比较下标量子查询，deterministic函数, result_cache函数在执行时间方面的差异。

create or replace function f( x in varchar2) return numberasbegin  dbms_application_info.set_client_info(userenv('client_info')+1 );  return length(x);end;create or replace function f_d( x invarchar2 ) return numberDETERMINISTICasbegin  dbms_application_info.set_client_info(userenv('client_info')+1 );  return length(x);end;create or replace function f_rc( x invarchar2 ) return numberresult_cacheasbegin  dbms_application_info.set_client_info(userenv('client_info')+1 );  return length(x);end;set timing offSET FEEDBACK OFFset serveroutput on;set arraysize 150set linesize 120set pagesize 150variable v_start number;col client_info format a10select * from v$version;--ordinary functionexecdbms_application_info.set_client_info(0);exec :v_start := dbms_utility.get_cpu_time;set termout off;select owner, f(owner) from stage;set termout on;exec dbms_output.put_line('Ordinaryfunction');select dbms_utility.get_cpu_time-:v_startcpu_hsecs, userenv('client_info') client_info from dual;--Deterministic functionexecdbms_application_info.set_client_info(0);exec :v_start := dbms_utility.get_cpu_time;set termout off;select owner, f_d(owner) from stage; set termout on;exec dbms_output.put_line('Deterministicfunction');select dbms_utility.get_cpu_time-:v_startcpu_hsecs, userenv('client_info') client_info from dual;--Function result cacheexecdbms_application_info.set_client_info(0);exec :v_start := dbms_utility.get_cpu_time;set termout off;select owner, f_rc(owner) from stage; set termout on;exec dbms_output.put_line('Function resultcache');select dbms_utility.get_cpu_time-:v_startcpu_hsecs, userenv('client_info') client_info from dual;--Scalar Subqueryexecdbms_application_info.set_client_info(0);exec :v_start := dbms_utility.get_cpu_time; set termout off;select owner, (select f(owner) from dual)from stage; set termout on;exec dbms_output.put_line('ScalarSubquery');select dbms_utility.get_cpu_time-:v_startcpu_hsecs, userenv('client_info') client_info from dual;

BANNER

----------------------------------------------------------------------------

Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE   11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 -Production

NLSRTL Version 11.2.0.1.0 - Production

Ordinary function

 CPU_HSECS CLIENT_INF

---------- ----------

      202 74236

Deterministic function

 CPU_HSECS CLIENT_INF

---------- ----------

       25 996

Function result cache

 CPU_HSECS CLIENT_INF

---------- ----------

      145 0

Scalar Subquery

 CPU_HSECS CLIENT_INF

---------- ----------

       13 70
说明：这是第二次跑，f_rc部分数据有点失真。
结论：
1.标量子查询耗时最少
2.即使Function Result Cache能减少函数运算，但是耗时远大于Deterministic和Scalar Subquery，很可能是SQL-PL/SQL Context Switch造成的。
3.当结果集较大时，使用形如：select x, (select f(x) from dual) from table的SQL是个好习惯。