ITPUB论坛-中国最专业的IT技术社区

 
 注册
热搜:
查看: 1280|回复: 5

[每日一题] PL/SQL Challenge 每日一题:2018-3-13 结果缓存中的会话依赖性

[复制链接]
论坛徽章:
486
秀才
日期:2015-09-09 10:33:01秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21
跳转到指定楼层
1#
发表于 2018-3-16 04:51 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
本帖最后由 newkid 于 2018-3-17 05:05 编辑

(原发表于 2011-9-14)

最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

每两周的优胜者可获得itpub奖励的技术图书一本。

以往旧题索引:
/forum.php?m ... =typeid&typeid=1808

原始出处:


作者:Steven Feuerstein

运行环境:SQLPLUS, SERVEROUTPUT已打开
注:本题给出答案时候要求给予简要说明才能得到奖品

我在HR模式下执行了下列语句:

CREATE TABLE plch_employees
(
   employee_id     INTEGER
, last_name       VARCHAR2 (100)
, first_name      VARCHAR2 (100)
, date_of_birth   DATE
, salary          NUMBER
)
/

BEGIN
   INSERT INTO plch_employees
        VALUES (100
              , 'Feuerstein'
              , 'Steven'
              , DATE '1958-09-23'
              , 100000);

   INSERT INTO plch_employees
        VALUES (200
              , 'Ellison'
              , 'Larry'
              , DATE '1954-05-10'
              , 10000000000000000);

   COMMIT;
END;
/

CREATE OR REPLACE FUNCTION plch_dob (
   employee_id_in IN plch_employees.employee_id%TYPE)
   RETURN VARCHAR2
   RESULT_CACHE
IS
   l_return   DATE;
BEGIN
   SELECT date_of_birth
     INTO l_return
     FROM plch_employees
    WHERE employee_id = employee_id_in;

   RETURN TO_CHAR (l_return);
END;
/

我允许SCOTT用户执行plch_dob函数:

GRANT EXECUTE ON plch_dob TO scott
/


然后我在同样的HR模式下执行了这些语句:

ALTER SESSION SET nls_date_format='YYYY-MM-DD'
/

BEGIN
   DBMS_OUTPUT.put_line (plch_dob (100));
END;
/

然后我以SCOTT用户连接,并且执行了这些语句:

ALTER SESSION SET nls_date_format='YYYY-MON-DD'
/

BEGIN
   DBMS_OUTPUT.put_line (hr.plch_dob (100));
END;
/

在屏幕上会显示什么?

(A)
1958-09-23
1958-SEP-23

(B)
1958-SEP-23
1958-SEP-23

(C)
1958-09-23
1958-09-23


(D)
1958-23-09
1958-23-09

论坛徽章:
0
2#
发表于 2018-3-16 08:10 | 只看该作者
在屏幕上会显示(B)

使用道具 举报

回复
论坛徽章:
0
3#
发表于 2018-3-16 08:40 | 只看该作者
23-SEP-58

使用道具 举报

回复
认证徽章
论坛徽章:
10
林肯
日期:2013-07-30 18:00:55技术图书徽章
日期:2018-03-01 10:21:49秀才
日期:2018-03-01 10:21:25秀才
日期:2018-03-01 10:05:18秀才
日期:2015-12-14 15:09:382015年新春福章
日期:2015-03-06 11:58:18懒羊羊
日期:2015-03-04 14:52:11马上有对象
日期:2015-01-16 13:26:19优秀写手
日期:2014-10-23 06:00:14秀才
日期:2018-03-01 10:21:49
4#
发表于 2018-3-16 09:56 | 只看该作者
怎么感觉是个C啊。scott执行的时候不是继承了HR的属性吗?

使用道具 举报

回复
招聘 : 系统分析师
论坛徽章:
479
法拉利
日期:2014-01-25 00:27:172014年世界杯参赛球队: 伊朗
日期:2014-05-23 10:41:312014年世界杯参赛球队: 比利时
日期:2014-06-17 12:09:43itpub13周年纪念徽章
日期:2014-09-28 10:55:55itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29慢羊羊
日期:2015-02-09 17:04:38
5#
发表于 2018-3-16 10:24 | 只看该作者
应该是C
对于同样的函数,传入同样的参数,只要函数满足result cache的限制条件,并且no side effect,那么返回值是一定的。

也就是说,会话级别的特别设置,并不会影响到函数的返回值,除非我们将会话级别的设置作为输入参数传递给函数,并在函数中有所处理,才会让同样的函数调用返回同样的值。(实际上会话设置作为输入参数的default值,调用已经不一样了,只不过我们在调用时,可以不写那个default参数,就看起来是同样的调用)


另外,no side effect并不是result cache必须的,但是假如函数调用了sysdate这样的函数(产生了side effect),可能会导致同样的输入返回不同的值,但因为result cache的存在,实际会返回一样的值,这就会导致逻辑上的错误。

使用道具 举报

回复
论坛徽章:
486
秀才
日期:2015-09-09 10:33:01秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21秀才
日期:2016-01-06 14:10:21
6#
 楼主| 发表于 2018-3-17 05:05 | 只看该作者
答案C, 5楼得奖。

Oracle 提供了几种方法,让你能够在你的PL/SQL中利用缓存机制来改善应用的性能,包括:

DETERMINISTIC 函数
PGA 缓存 (包)
函数结果缓存(Function Result Cache)

当你将一个函数声明为确定性的(DETERMINISTIC), 那么如果你在SQL语句中执行了那个函数,ORACLE会在SQL执行期间缓存输入值和输出值。如果函数被用相同的输入值调用多次,ORACLE只会在第一次执行函数体。

PGA缓存利用了这样的事实,即包级别的数据是可持续的;ORACLE在不同的服务调用之间在PGA中保留你会话的值,或者状态。这意味着你可以将你需要多次使用的数据在包变量中“缓存”起来并且快速获取。缺点是这个缓存是针对会话的,必须写程序来管理。

11g新引入的函数结果缓存是一种跨会话的缓存,保存在SGA中并且由ORACLE自动维护(确保数据是干净的,除非你误用了这个特性)。这是一种快速并且可靠的方法,可以避免反复查询同样的数据行(假设数据没有发生变化)。
==================================
PL/SQL 函数结果缓存机制提供了一种方法来将PL/SQL 函数的结果缓存在SGA里面,这些结果对运行你运用的任何会话都是可见的。缓存机制高效而且易于使用,并且使你从设计、开发自己的缓存、缓存管理机制中解脱出来。

注意:函数缓存功能只在ORACLE数据库企业版中提供。在标准版中这项功能被屏蔽了。

为了打开函数结果缓存,你所需要做的一切只是把RESULT_CACHE子句加到你的函数头(如果是在包中,则包头和包体都要加)。

当一个结果缓存函数被调用,系统就会检查缓存。如果缓存里有上次调用结果而且参数值一样,则系统直接吧缓存结果返回给调用者,而不会执行函数体。如果缓存里没有该结果,则系统执行函数体并把结果加入到缓存(针对这一组参数值),然后把控制权返回给调用者。

注意:如果数据库参数RESULT_CACHE_MAX_SIZE被设为0, 则结果缓存功能将被关闭。为了确定这个功能是否开启,你可以执行如下查询或匿名块(你必须拥有DBMS_RESULT_CACHE包的执行权限):

SELECT DBMS_RESULT_CACHE.status FROM DUAL
/

BEGIN
   DBMS_OUTPUT.put_line (DBMS_RESULT_CACHE.status);
END;
/

此外,你可以在一个拥有足够权限(SYSDBA肯定可以)的用户下执行这个命令来查看所有和结果缓存相关的数据库参数:
SQL> SHOW PARAMETER RESULT_CACHE

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
client_result_cache_lag              big integer 3000
client_result_cache_size             big integer 0
result_cache_max_result              integer     5
result_cache_max_size                big integer 3168K
result_cache_mode                    string      MANUAL
result_cache_remote_expiration       integer     0


======================================================
如果一个函数依赖于某种设置,而该设置可能是每个会话各不相同(例如包的状态,NLS_DATE_FORMAT和时区),那么仅仅当你能够将函数修改成能够处理各种各样的设置,你才能将它写成结果缓存。

通常,这意味着你必须将这个会话的依赖性“暴露”在函数的参数列表中。你可以在下列代码中看到这个方法是如何工作的。当函数从SCOTT模式下被第二次调用时,返回的字符串格式是错的——因为函数体没有被执行(结果被缓存了)。

CONNECT hr/hr

SET SERVEROUTPUT ON

CREATE TABLE plch_employees (
   employee_id     INTEGER,
   last_name       VARCHAR2 (100),
   first_name      VARCHAR2 (100),
   date_of_birth   DATE)
/

BEGIN
   INSERT INTO plch_employees
        VALUES (100,
                'Feuerstein',
                'Steven',
                DATE '1958-09-23');

   INSERT INTO plch_employees
        VALUES (200,
                'Ellison',
                'Larry',
                DATE '1954-05-10');

   COMMIT;
END;
/

CREATE OR REPLACE FUNCTION plch_dob (
   employee_id_in IN plch_employees.employee_id%TYPE)
   RETURN VARCHAR2
   RESULT_CACHE
IS
   l_return   DATE;
BEGIN
   SELECT date_of_birth
     INTO l_return
     FROM plch_employees
    WHERE employee_id = employee_id_in;

   RETURN TO_CHAR (l_return);
END;
/

GRANT EXECUTE ON plch_dob TO scott
/

ALTER SESSION SET nls_date_format='YYYY-MM-DD'
/

BEGIN
   DBMS_OUTPUT.put_line (plch_dob (100));
END;
/

CONNECT scott/tiger

SET SERVEROUTPUT ON

ALTER SESSION SET nls_date_format='YYYY-MON-DD'
/

BEGIN
   DBMS_OUTPUT.put_line (hr.plch_dob (100));
END;
/

不幸的是,你会看到第二个代码块显示 "1958-09-23" 而不是 "1958-SEP-23", 因为第一次调用的结果被缓存了。

解决办法?将日期格式“暴露”在参数列表中,如下所示。然后每当函数被调用时,你必须提供这个格式。此外,如果可能的话,你可以将这个参数的缺省值设置为会话当前值。

CONNECT HR/HR

CREATE OR REPLACE FUNCTION plch_dob (
   employee_id_in   IN plch_employees.employee_id%TYPE,
   format_in        IN VARCHAR2)
   RETURN VARCHAR2
   RESULT_CACHE
IS
   l_return   DATE;
BEGIN
   SELECT date_of_birth
     INTO l_return
     FROM plch_employees
    WHERE employee_id = employee_id_in;

   RETURN TO_CHAR (l_return, format_in);
END;
/

plch_dob 函数用了TO_CHAR 函数来将一个DATE转换成VARCHAR2。它没有指定格式掩码,所以格式掩码被缺省为NLS_DATE_FORMAT所指定的格式。

HR 和 SCOTT 在会话中有不同的格式掩码设置,所以你会期待函数在每个会话返回不同的字符串。然而,因为plch_dob是一个结果缓存函数,而两次调用传递的参数值都相同,当函数第二次被调用时(从SCOTT模式),函数体没有被执行,因此返回的字符串和HR会话中返回的一样。

这个相当简单的场景指出了在对会话设置有依赖的函数中使用RESULT_CACHE的潜在风险,

解决这个plch_dob的行为不一致问题的方法包括:

将函数的返回类型修改为DATE, 让每个会话去调用TO_CHAR函数,于是就会正确地应用会话中的掩码设置。

不要依赖会话设置。当调用TO_CHAR(以及其他的有可能依赖会话设置的子程序),同时指定一个格式掩码,去除对NLS_DATE_FORMAT的依赖,如下:
TO_CHAR (l_return, 'YYYY-DD-MM');

将会话设置“暴露”到参数列表。对于plch_dob而言,增加如下的格式参数:

CREATE OR REPLACE FUNCTION plch_dob (
   employee_id_in   IN plch_employees.employee_id%TYPE
, format_in        IN VARCHAR2 DEFAULT 'YYYY-MM-DD')
   RETURN VARCHAR2
   RESULT_CACHE
IS
   l_return   DATE;
BEGIN
   SELECT date_of_birth
     INTO l_return
     FROM plch_employees
    WHERE employee_id = employee_id_in;

   RETURN TO_CHAR (l_return, format_in);
END;
/

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 |
  | | |
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表