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.dname

From 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 number

as

begin

  dbms_application_info.set_client_info(userenv('client_info')+1 );

  return length(x);

end;

create or replace function f_d( x invarchar2 ) return number

DETERMINISTIC

as

begin

  dbms_application_info.set_client_info(userenv('client_info')+1 );

  return length(x);

end;

create or replace function f_rc( x invarchar2 ) return number

result_cache

as

begin

  dbms_application_info.set_client_info(userenv('client_info')+1 );

  return length(x);

end;

set timing off

SET FEEDBACK OFF

set serveroutput on;

set arraysize 150

set linesize 120

set pagesize 150

variable v_start number;

col client_info format a10

select * from v$version;

--ordinary function

execdbms_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 function

execdbms_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 cache

execdbms_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 Subquery

execdbms_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是个好习惯。