版本：Oracle 11.2.0.4.0
操作系统：linux redhat 6.4
场景：
生产环境通过查看执行次数最多的Sql脚本 ，发现其中有一条Sql 执行了共2463031703 ，（虽然v$sqlarea中executions 字段含义 执行Sql的总数，总额超过所有子游标。但考虑到该Sql 最近上线,所以觉得可疑）。下面是通过将调用该条Sql的函数改写为确定性函数 以及 RESULT_CACHE （结果高速缓存）手段来实现具体优化步骤。
select *
from (select sql_text, executions, parsing_schema_name, LAST_ACTIVE_TIME
from v$sqlarea G
WHERE G.PARSING_SCHEMA_NAME = 'ADM'
order by executions desc)
where rownum < 2;
函数定义
function_heading [ DETERMINISTIC
| PIPELINED
| PARALLEL_ENABLE
| RESULT_CACHE [ relies_on_clause ]
]...
{ IS | AS } { [ declare_section ] body | call_spec | EXTERNAL }
理解 DETERMINISTIC VS RESULT_CACHE 区别
如果了解DETERMINISTIC功能，会发现DETERMINISTIC函数和RESULT_CACHE有功能类似之处。两种方法在函数输入相同的时候，都可以避免函数的再次执行。不过二者的实现方式不同，所以表现出来的很多特点也不相同。
DETERMINISTIC函数的主要意义是告诉Oracle，当这个函数输入的参数确定时，输出也是确定的。而Oracle利用了这个功能，因此在一次函数调用中出现多次重复调用时，对函数只调用一次。
而11g新增的RESULT_CACHE则不然，这个方法是通过将函数调用的结果保存在SGA的RESULT CACHE中来实现的。当Oracle执行一个函数时，如果在RESULT CACHE中可以找到相关的结果，那么Oracle就不会再次执行这个函数，而直接将结果返回。
了解了这两个功能的实现方法，也就清楚了二者的区别。对于DETERMINISTIC功能而言，只有一次调用中的函数重复执行，才能避免函数的多次执行。而对于RESULT_CACHE而言，由于结果被SGA进行缓存，随后的调用都可以利用这个结果，而且其他的SESSION也是可以共享这个结果的。
原始创建函数代码：
CREATE OR REPLACE FUNCTION ADM.CHECK_DESC(V_ID VARCHAR2, V_TAG_ID VARCHAR2)
RETURN VARCHAR2 IS
V_RETURN VARCHAR2(50);
BEGIN
SELECT DICT_NAME
INTO V_RETURN
FROM FCT_DICT
WHERE DICT_ID = V_ID
AND DICT_TAG_ID = V_TAG_ID;
RETURN V_RETURN;
END;
调用脚本：
SELECT ADM.CHECK_DESC(A12.SECUMARKET, 'SECUMARKET' ) CUSTCOL_19
FROM ADM.DIM_SECUMAIN A12;
递归调用：70180 刚好是返回次数。也是执行次数。
更改成确定性函数
CREATE OR REPLACE FUNCTION ADM.CHECK_DESC(V_ID VARCHAR2, V_TAG_ID VARCHAR2)
RETURN VARCHAR2 Deterministic IS
V_RETURN VARCHAR2(50);
BEGIN
SELECT DICT_NAME
INTO V_RETURN
FROM FCT_DICT
WHERE DICT_ID = V_ID
AND DICT_TAG_ID = V_TAG_ID;
RETURN V_RETURN;
END;
DETERMINISTIC函数的主要意义是告诉Oracle，当这个函数输入的参数确定时，输出也是确定的。而Oracle利用了这个功能，因此在一次函数调用中出现多次重复调用时，对函数只调用一次。
更改为RESULT CACHE 函数
经过分析该函数所使用到的表，是字典表，数据更新不是很频繁，考虑至此可以将其更些成RESULT_CACHE 函数 即 将结果集存放SGA中。 ---注意添加了RELIES_ON语句后，Oracle会根据依赖对象自动INVALIDATE结果集，从而保证RESULT CACHE的正确性。
CREATE OR REPLACE FUNCTION ADM.CHECK_DESC(V_ID VARCHAR2, V_TAG_ID VARCHAR2)
RETURN VARCHAR2 RESULT_CACHE RELIES_ON (FCT_DICT) IS
V_RETURN VARCHAR2(50);
BEGIN
SELECT DICT_NAME
INTO V_RETURN
FROM FCT_DICT
WHERE DICT_ID = V_ID
AND DICT_TAG_ID = V_TAG_ID;
RETURN V_RETURN;
END;
附件为对应10046事件文件
相关文档：Oracle SQL优化之高效的函数调用
待完善：
通过deterministic和result_cache可以提高，但我们总是应该使用标量子查询，因为deterinisitic和result_cache仅仅是缓存结果，并不能减少SQL和PLSQL上下文质检的切换，即总是会发生SQL引擎和PLSQL引擎的交互，并不会减少对CPU的消耗。标量子查询也可以作用于where子句以提高查询性能。--网上摘抄
测试脚本：
--1 创建表：T
CREATE TABLE T AS SELECT * FROM ALL_ALL_TABLES;
--2 创建函数：F_RETURN
CREATE OR REPLACE FUNCTION F_RETURN(v_OWNER varchar2,
v_tablespace_name varchar2)
RETURN varchar2 AS
V_RETURN varchar2(50);
BEGIN
SELECT T.TABLE_NAME TABLE_NAME1
INTO V_RETURN
FROM T
where t.owner = v_OWNER
and t.tablespace_name = v_tablespace_name
AND ROWNUM=1;
RETURN V_RETURN;
END;
--3 调用函数
SELECT F_RETURN(S.owner,S.tablespace_name) FROM ALL_ALL_TABLES S;
--4 测试执行次数
(可以执行两次，增长的执行次数，应该是调用函数里面执行子游标执行次数，也就是结果集)
select *
from (select sql_text, executions, parsing_schema_name, LAST_ACTIVE_TIME
from v$sqlarea G
WHERE G.PARSING_SCHEMA_NAME ='ADM'
order by executions desc)
where sql_text LIKE '%SELECT T.TABLE_NAME TABLE_NAME1%';
--5 改写成确定性函数
CREATE OR REPLACE FUNCTION F_RETURN(v_OWNER varchar2,
v_tablespace_name varchar2)
RETURN varchar2 Deterministic AS
V_RETURN varchar2(50);
BEGIN
SELECT T.TABLE_NAME
INTO V_RETURN
FROM T
where t.owner = v_OWNER
and t.tablespace_name = v_tablespace_name
AND ROWNUM = 1;
RETURN V_RETURN;
END;
--6 再次调用函数
SELECT F_RETURN(S.owner,S.tablespace_name) FROM ALL_ALL_TABLES S;
--7测试执行次数 。发现调用执行次数小于普通函数子游标执行次数
select *
from (select sql_text, executions, parsing_schema_name, LAST_ACTIVE_TIME
from v$sqlarea G
WHERE G.PARSING_SCHEMA_NAME ='ADM'
order by executions desc)
where sql_text LIKE '%SELECT T.TABLE_NAME TABLE_NAME1%';
