Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › PL/SQL Function Result Cache

tom_fu

29 积分	0 好友	5 主题

发消息

[RAC性能调优] PL/SQL Function Result Cache

1^#

发表于 2013-7-12 22:13:24 | 查看: 4421| 回复: 9

本帖最后由 tom_fu 于 2013-7-12 22:14 编辑

http://www.oracle-base.com/articles/11g/cross-session-plsql-function-result-cache-11gr1.php
PL/SQL Function Result Cache 我在11.2.0.3测试更新数据后缓存为何没有变化，函数定义中加了RESULT_CACHE RELIES_ON (res_cache_test_tab)的

SQL> SET SERVEROUTPUT ON
SQL> EXEC run_test;
First Loop: 1013 hsecs
Second Loop: 0 hsecs
SQL> pause

SQL> EXEC run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs
SQL> SET SERVEROUTPUT OFF
SQL> pause
SQL> EXEC run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs
SQL> SET SERVEROUTPUT OFF
SQL> pause

SQL> UPDATE res_cache_test_tab
2 SET value = value * 10;
SQL> COMMIT;
SQL> pause

SQL> SET SERVEROUTPUT ON
SQL> EXEC run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

分享0

收藏0 回复只看该作者道具举报

26856649

2^#

发表于 2013-7-13 15:59:15

SQL> select * from v$version where rownum <=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
SQL>
CREATE OR REPLACE FUNCTION get_value (p_in IN NUMBER)
RETURN NUMBER
RESULT_CACHE
AS
l_value res_cache_test_tab.value%TYPE;
BEGIN
SELECT value
INTO l_value
FROM res_cache_test_tab
WHERE id = p_in;
-- Pause for 1 second.
DBMS_LOCK.sleep(1);
RETURN l_value;
END get_value;
17 /
函数已创建。
UPDATE res_cache_test_tab
SET value = value * 10;
已更新10行。
COMMIT;
提交完成。
SQL> SQL> EXEC run_test;
First Loop: 1001 hsecs
Second Loop: 0 hsecs
PL/SQL 过程已成功完成。
UPDATE res_cache_test_tab
SET value = value * 10;
已更新10行。
COMMIT;
提交完成。
SQL> SQL> EXEC run_test;
First Loop: 1002 hsecs
Second Loop: 0 hsecs
PL/SQL 过程已成功完成。
SQL> EXEC run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs
PL/SQL 过程已成功完成。
CREATE OR REPLACE FUNCTION get_value (p_in IN NUMBER)
RETURN NUMBER
RESULT_CACHE RELIES_ON (res_cache_test_tab)
AS
l_value res_cache_test_tab.value%TYPE;
BEGIN
SELECT value
INTO l_value
FROM res_cache_test_tab
WHERE id = p_in;
-- Pause for 1 second.
DBMS_LOCK.sleep(1);
RETURN l_value;
END get_value;
17 /
函数已创建。
SQL> EXEC run_test;
First Loop: 1001 hsecs
Second Loop: 0 hsecs
PL/SQL 过程已成功完成。
SQL> EXEC run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs
PL/SQL 过程已成功完成。
UPDATE res_cache_test_tab
SET value = value * 10;
已更新10行。
COMMIT;
提交完成。
SQL>
SQL> EXEC run_test;
First Loop: 1001 hsecs
Second Loop: 0 hsecs
PL/SQL 过程已成功完成。
SQL> EXEC run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs
PL/SQL 过程已成功完成。
SQL>

复制代码

回复只看该作者道具举报

tom_fu

3^#

发表于 2013-7-13 16:50:00

我测试结果更新数据后还是用缓存
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0    Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> DROP TABLE res_cache_test_tab PURGE;
SQL>
SQL> exec dbms_result_cache.flush;
SQL> set SERVEROUTPUT ON
SQL> exec dbms_result_cache.memory_report;
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size       = 1K bytes
Maximum Cache Size  = 6336K bytes (6336 blocks)
Maximum Result Size = 316K bytes (316 blocks)
[Memory]
Total Memory = 5352 bytes [0.001% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.001% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
SQL> set SERVEROUTPUT OFF
SQL> CREATE TABLE res_cache_test_tab (
  2 id    NUMBER,
  3 value NUMBER
  4  );
SQL>
SQL> BEGIN
  2 FOR i IN 1 .. 10 LOOP
  3    INSERT INTO res_cache_test_tab VALUES (i, i*10);
  4 END LOOP;
  5 COMMIT;
  6  END;
  7  /
SQL>
SQL> pause

SQL> CREATE OR REPLACE  FUNCTION get_value (p_in IN NUMBER)
  2 RETURN NUMBER
  3 RESULT_CACHE RELIES_ON (res_cache_test_tab)
  4  AS
  5 l_value res_cache_test_tab.value%TYPE;
  6  BEGIN
  7 SELECT value
  8 INTO    l_value
  9 FROM    res_cache_test_tab
10 WHERE  id = p_in;
11
12 -- Pause for 1 second.
13 DBMS_LOCK.sleep(1);
14
15 RETURN l_value;
16  END get_value;
17  /
SQL>
SQL> pause

SQL> CREATE OR REPLACE PROCEDURE run_test AS
  2 l_start NUMBER;
  3 l_loops NUMBER := 10;
  4 l_value res_cache_test_tab.value%TYPE;
  5  BEGIN
  6 l_start := DBMS_UTILITY.get_time;
  7
  8 FOR i IN 1 .. l_loops LOOP
  9    l_value := get_value(i);
10 END LOOP;
11
12 DBMS_OUTPUT.put_line('First Loop: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
13
14 l_start := DBMS_UTILITY.get_time;
15
16 FOR i IN 1 .. l_loops LOOP
17    l_value := get_value(i);
18 END LOOP;
19
20 DBMS_OUTPUT.put_line('Second Loop: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
21  END run_test;
22  /
SQL> pause

SQL>
SQL> SET SERVEROUTPUT ON
SQL> EXEC run_test;
First Loop: 1013 hsecs
Second Loop: 0 hsecs
SQL> pause

SQL> EXEC run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs
SQL> SET SERVEROUTPUT OFF
SQL> pause

SQL> UPDATE res_cache_test_tab
  2  SET value = value * 10;
SQL> COMMIT;
SQL> pause

SQL> SET SERVEROUTPUT ON
SQL> EXEC run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs
SQL> EXEC run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs
SQL> SET SERVEROUTPUT OFF
SQL>

回复只看该作者道具举报

Maclean Liu(刘相兵

4^#

发表于 2013-7-13 18:18:39

SQL> CREATE OR REPLACE  FUNCTION get_value (p_in IN NUMBER)
  2 RETURN NUMBER
  3 RESULT_CACHE RELIES_ON (res_cache_test_tab)
  4  AS
  5 l_value res_cache_test_tab.value%TYPE;
  6  BEGIN
  7 SELECT value
  8 INTO l_value
  9 FROM res_cache_test_tab
10 WHERE  id = p_in;
11
12 -- Pause for 1 second.
13 DBMS_LOCK.sleep(1);
14
15 RETURN l_value;
16  END get_value;
17  /

Function created.

SQL> EXEC run_test;
First Loop: 1001 hsecs
Second Loop: 0 hsecs

PL/SQL procedure successfully completed.

SQL> UPDATE res_cache_test_tab
  2  SET value = value * 10;
COMMIT;

10 rows updated.

SQL>
Commit complete.

SQL>
SQL> EXEC run_test;
First Loop: 1001 hsecs
Second Loop: 0 hsecs

PL/SQL procedure successfully completed.

我的测试中没有遇到该问题

请你把get_value  和run_test 在你环境中的DDL 贴出来，以及运行的版本和使用的USER

回复只看该作者道具举报

tom_fu

5^#

发表于 2013-7-13 20:31:01

[oracle@11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jul 7 15:53:09 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option

SQL> @/home/oracle/a.sql
SQL> SET ARRAYSIZE 2000
SQL>
SQL> DROP TABLE res_cache_test_tab PURGE;
DROP TABLE res_cache_test_tab PURGE
         *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL>
SQL> exec dbms_result_cache.flush;
SQL> set SERVEROUTPUT ON
SQL> exec dbms_result_cache.memory_report;
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size       = 1K bytes
Maximum Cache Size  = 2M bytes (2K blocks)
Maximum Result Size = 102K bytes (102 blocks)
[Memory]
Total Memory = 5352 bytes [0.002% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.002% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
SQL> set SERVEROUTPUT OFF
SQL> CREATE TABLE res_cache_test_tab (
  2 id    NUMBER,
  3 value NUMBER
  4  );
SQL>
SQL> BEGIN
  2 FOR i IN 1 .. 10 LOOP
  3    INSERT INTO res_cache_test_tab VALUES (i, i*10);
  4 END LOOP;
  5 COMMIT;
  6  END;
  7  /
SQL> select * from res_cache_test_tab;

      ID    VALUE
---------- ----------
      1       10
      2       20
      3       30
      4       40
      5       50
      6       60
      7       70
      8       80
      9       90
      10       100
SQL> pause

SQL> CREATE OR REPLACE  FUNCTION get_value (p_in IN NUMBER)
  2 RETURN NUMBER
  3 RESULT_CACHE RELIES_ON (res_cache_test_tab)
  4  AS
  5 l_value res_cache_test_tab.value%TYPE;
  6  BEGIN
  7 SELECT value
  8 INTO    l_value
  9 FROM    res_cache_test_tab
10 WHERE  id = p_in;
11
12 -- Pause for 1 second.
13 DBMS_LOCK.sleep(1);
14
15 RETURN l_value;
16  END get_value;
17  /
SQL>
SQL> pause

SQL> CREATE OR REPLACE PROCEDURE run_test AS
  2 l_start NUMBER;
  3 l_loops NUMBER := 10;
  4 l_value res_cache_test_tab.value%TYPE;
  5  BEGIN
  6 l_start := DBMS_UTILITY.get_time;
  7
  8 FOR i IN 1 .. l_loops LOOP
  9    l_value := get_value(i);
10 END LOOP;
11 dbms_output.put_line(l_value );
12 DBMS_OUTPUT.put_line('First Loop: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
13
14 l_start := DBMS_UTILITY.get_time;
15
16 FOR i IN 1 .. l_loops LOOP
17    l_value := get_value(i);
18 END LOOP;
19
20 DBMS_OUTPUT.put_line('Second Loop: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
21  END run_test;
22  /
SQL> pause

SQL>
SQL> SET SERVEROUTPUT ON
SQL> EXEC run_test;

100
First Loop: 1003 hsecs
Second Loop: 0 hsecs
SQL> pause
SQL> EXEC run_test;
100
First Loop: 0 hsecs
Second Loop: 0 hsecs
SQL> SET SERVEROUTPUT OFF
SQL> pause

SQL> UPDATE res_cache_test_tab SET value = value * 10;
SQL> commit;
SQL> select * from res_cache_test_tab;

      ID    VALUE
---------- ----------
      1       100
      2       200
      3       300
      4       400
      5       500
      6       600
      7       700
      8       800
      9       900
      10    1000
SQL> pause

SQL> SET SERVEROUTPUT ON
SQL> EXEC run_test;
100
First Loop: 0 hsecs
Second Loop: 0 hsecs
SQL> EXEC run_test;
100
First Loop: 0 hsecs
Second Loop: 0 hsecs
SQL> SET SERVEROUTPUT OFF
SQL>

SQL> show parameter result

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 2M
result_cache_mode                   string    MANUAL
result_cache_remote_expiration    integer    0
函数定义如上输出所示
SQL> CREATE OR REPLACE PROCEDURE run_test AS
  2 l_start NUMBER;
  3 l_loops NUMBER := 10;
  4 l_value res_cache_test_tab.value%TYPE;
  5  BEGIN
  6 l_start := DBMS_UTILITY.get_time;
  7
  8 FOR i IN 1 .. l_loops LOOP
  9    l_value := get_value(i);
10 END LOOP;
11 dbms_output.put_line(l_value );
12 DBMS_OUTPUT.put_line('First Loop: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
13
14 l_start := DBMS_UTILITY.get_time;
15
16 FOR i IN 1 .. l_loops LOOP
17    l_value := get_value(i);
18 END LOOP;
19
20 DBMS_OUTPUT.put_line('Second Loop: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
21  END run_test;

回复只看该作者道具举报

Maclean Liu(刘相兵

6^#

发表于 2013-7-13 22:20:42

tom_fu 发表于 2013-7-13 20:31
[oracle@11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jul 7 15:53:09 2 ...

你不够仔细，也喜欢忽视细节

仔细看我4楼的问题和你的操作你漏掉什么了？

回复只看该作者道具举报

Maclean Liu(刘相兵

7^#

发表于 2013-7-13 22:32:37

http://www.oracle-base.com/artic ... ult-cache-11gr1.php 作者原帖

CONN test/test 这个操作你认为是没意义的？

简单来说不要用 SYS或SYSTEM去测试这几个result cache的特性，这个文档也有明确说

回复只看该作者道具举报

tom_fu

8^#

发表于 2013-7-13 22:40:52

多谢！原来不能用sys和system

回复只看该作者道具举报

tom_fu

9^#

发表于 2013-7-14 09:58:19

并不是说sys和system都不行，这个函数缓存我觉得就是有问题

C:\Users\Tom>sqlplus system/oracle@l11g

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jul 14 09:51:36 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option

SQL> show user
USER is "SYSTEM"
SQL> @c:\a.sql
SQL> SET ARRAYSIZE 2000
SQL>
SQL> show parameter result

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 2M
result_cache_mode                   string    MANUAL
result_cache_remote_expiration    integer    0
SQL> pause

SQL> select dbms_result_cache.status() from dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------------------------------------------------------------
ENABLED
SQL> exec dbms_result_cache.flush;
SQL> pause
SQL> set autot on exp stat
SQL> select    /*+ RESULT_CACHE */ department_id, AVG(salary)
  2  from hr.employees
  3  group by department_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                      | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |                         | 11 | 77 |    4  (25)| 00:00:01 |
| 1 |  RESULT CACHE    | 1akffwbzpzduf01pch4018m270 |    |    |          |       |
| 2 | HASH GROUP BY    |                         | 11 | 77 |    4  (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPLOYEES                | 107 | 749 |    3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=2; dependencies=(HR.EMPLOYEES); name="select  /*+ RESULT_CACHE */ department_id, AVG(salary)
from hr.employees
group by department_id"

Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
      6  consistent gets
      6  physical reads
      0  redo size
      633  bytes sent via SQL*Net to client
      359  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      12  rows processed

SQL> set autot off
SQL> pause

SQL> SET SERVEROUTPUT ON
SQL> exec dbms_result_cache.memory_report;
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size       = 1K bytes
Maximum Cache Size  = 2M bytes (2K blocks)
Maximum Result Size = 102K bytes (102 blocks)
[Memory]
Total Memory = 169328 bytes [0.075% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.002% of the Shared Pool]
... Dynamic Memory = 163976 bytes [0.072% of the Shared Pool]
....... Overhead = 131208 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... SQL    = 1 blocks (1 count)
SQL> SET SERVEROUTPUT OFF
SQL> pause

SQL> set autot on exp stat
SQL> select    /*+ RESULT_CACHE */ department_id, AVG(salary)
  2  from hr.employees
  3  group by department_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                      | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |                         | 11 | 77 |    4  (25)| 00:00:01 |
| 1 |  RESULT CACHE    | 1akffwbzpzduf01pch4018m270 |    |    |          |       |
| 2 | HASH GROUP BY    |                         | 11 | 77 |    4  (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPLOYEES                | 107 | 749 |    3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=2; dependencies=(HR.EMPLOYEES); name="select  /*+ RESULT_CACHE */ department_id, AVG(salary)
from hr.employees
group by department_id"

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      0  consistent gets
      0  physical reads
      0  redo size
      633  bytes sent via SQL*Net to client
      359  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      12  rows processed

SQL> set autot off
SQL> pause

SQL> update hr.employees set salary=salary+10;
SQL> commit;
SQL> pause

SQL> set autot on exp stat
SQL> select    /*+ RESULT_CACHE */ department_id, AVG(salary)
  2  from hr.employees
  3  group by department_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904
--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                      | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |                         | 11 | 77 |    4  (25)| 00:00:01 |
| 1 |  RESULT CACHE    | 1akffwbzpzduf01pch4018m270 |    |    |          |       |
| 2 | HASH GROUP BY    |                         | 11 | 77 |    4  (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPLOYEES                | 107 | 749 |    3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=2; dependencies=(HR.EMPLOYEES); name="select  /*+ RESULT_CACHE */ department_id, AVG(salary)
from hr.employees
group by department_id"
Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      6  consistent gets
      0  physical reads
      0  redo size
      639  bytes sent via SQL*Net to client
      359  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      12  rows processed
SQL> set autot off
SQL> set autot on exp stat
SQL> select    /*+ RESULT_CACHE */ department_id, AVG(salary)
  2  from hr.employees
  3  group by department_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                      | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |                         | 11 | 77 |    4  (25)| 00:00:01 |
| 1 |  RESULT CACHE    | 1akffwbzpzduf01pch4018m270 |    |    |          |       |
| 2 | HASH GROUP BY    |                         | 11 | 77 |    4  (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPLOYEES                | 107 | 749 |    3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=2; dependencies=(HR.EMPLOYEES); name="select  /*+ RESULT_CACHE */ department_id, AVG(salary)
from hr.employees
group by department_id"

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      0  consistent gets
      0  physical reads
      0  redo size
      639  bytes sent via SQL*Net to client
      359  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      12  rows processed

SQL> set autot off
SQL> pause

回复只看该作者道具举报

Maclean Liu(刘相兵

10^#

发表于 2013-7-14 13:43:22

Results or data sets from objects/tables in the SYS or SYSTEM schema cannot be cached using this feature. Data from CURRVAL and NEXTVAL pseudo columns from database sequences cannot be cached using this feature.

文档的解释是SYS or SYSTEM不能使用该特性，实际测试是可以用到，但Oracle Corp不保证没有问题

http://www.oracle.com/technetwor ... che-rac-284280.html

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册