[20111230]11Gr2 result cache[1].txt

11G的result cache是一个很吸引人的特性，可以大幅减少逻辑读取，特别对于一些经常执行的语句，而结果不是经常变化的，效果不错，我的测试遇到一个小问题。

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 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

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 4000K
result_cache_mode                    string      MANUAL
result_cache_remote_expiration       integer     0

SQL> show parameter memory%target
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------------------------------------
memory_max_target                    big integer 1552M
memory_target                        big integer 1552M

--可以看出我的机器目前result_cache_max_size=4000K,memory_max_target,memory_target=1552.

我增加MEMORY_MAX_TARGET,MEMORY_TARGET到2800M。

$ cat inittest.ora
SPFILE='+DATA/test/spfiletest.ora'
MEMORY_MAX_TARGET=2800M
MEMORY_TARGET=2800M

--注:我的/etc/fstab定义
tmpfs                   /dev/shm                tmpfs   defaults,size=3G        0 0

大于3G,会出现
11:20:50 SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system

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

SQL> show parameter memory%target
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------------------------------------
memory_max_target                    big integer 2800M
memory_target                        big integer 2800M

--可以发现result_cache_max_size增加到7M,应该这个也是根据内存大小动态调整的。如果机器内存很大，配置memory_*target很大，
result_cache_max_size也应该随着变大。(现在内存16G的服务器应该很多)

1.开始测试:
SQL> alter system set result_cache_max_size=24m;
System altered.
SQL> alter system set result_cache_max_result=100;
System altered.
--result_cache_max_size表示result_cache占用共享池的内存大小，result_cache_max_result指每个会话占用的百分比，缺省是5%，
我测试方便设置占用100.

SQL> create table t as select rownum id, rpad('*', 100, '*') name from dual connect by level
SQL> set autot traceonly --主要目的不是要看执行计划，而是避免滚动时间太长了。
SQL> select /*+ result_cache*/ * from t;

300000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |   276K|    17M|  1032   (1)| 00:00:13 |
|   1 |  RESULT CACHE      | 355yk153mq10cgh9jctsjdpr94 |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T                          |   276K|    17M|  1032   (1)| 00:00:13 |
-------------------------------------------------------------------------------------------------

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

   1 - column-count=2; dependencies=(SCOTT.T); name="select /*+ result_cache*/ * from t"

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
         68  recursive calls
          0  db block gets
       6196  consistent gets
       4616  physical reads
          0  redo size
    3262015  bytes sent via SQL*Net to client
      17009  bytes received via SQL*Net from client
       1501  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     300000  rows processed

--第2次执行
SQL> select /*+ result_cache*/ * from t;

300000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |   276K|    17M|  1032   (1)| 00:00:13 |
|   1 |  RESULT CACHE      | 355yk153mq10cgh9jctsjdpr94 |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T                          |   276K|    17M|  1032   (1)| 00:00:13 |
-------------------------------------------------------------------------------------------------

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

   1 - column-count=2; dependencies=(SCOTT.T); name="select /*+ result_cache*/ * from t"

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       6124  consistent gets
          0  physical reads
          0  redo size
    3262015  bytes sent via SQL*Net to client
      17009  bytes received via SQL*Net from client
       1501  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     300000  rows processed
--第2次执行,可以发现我的机器result_cache_max_size=24m,还是太小，存在一定的逻辑读，而且应该没有result cache。

2.打开另外的会话执行：
SQL> set timing on
SQL> select count(*) from v$result_cache_memory;
  COUNT(*)
----------
     24576

Elapsed: 00:00:23.55

24秒！仅仅24XXX，需要24秒才能完成，result_cache_max_size=24m，如果更大当result cache填满时，需要的时间不是更长！

再次执行select count(*) from v$result_cache_memory;
在会话1执行select /*+ result_cache*/ * from t;你会发现系统挂起！
如果那个管理人员访问了v$result_cache_memory视图，比如我经常使用toad，不小心点击了v$result_cache_memory视图，前面如果使用
result cache的语句不是全部挂起了吗。

3.接着测试：
在会话1执行：
SQL> select /*+ result_cache*/ count(*) from dept;

在会话2执行：
SQL> select count(*) from v$result_cache_memory;
  COUNT(*)
----------
     24576

Elapsed: 00:00:00.03
--居然发现这次变快了。

查看视图结构:
SQL> desc v$result_cache_memory;
 Name          Null?    Type
 ------------ -------- -----------
 ID                    NUMBER
 CHUNK                 NUMBER
 OFFSET                NUMBER
 FREE                  VARCHAR2(3)
 OBJECT_ID             NUMBER
 POSITION              NUMBER

SQL> select * from v$result_cache_memory where free='NO';
        ID      CHUNK     OFFSET FRE  OBJECT_ID   POSITION
---------- ---------- ---------- --- ---------- ----------
         0          0          0 NO           0          0
         2          0          2 NO           2          0
         4          0          4 NO           4          0
     24448        764          0 NO       24448          0
     24449        764          1 NO       24449          0

--做一个推测,应该是执行select /*+ result_cache*/ count(*) from dept;后释放了原来的占用的result cache memory。

再次执行会话1:
SQL> select /*+ result_cache*/ * from t;

会话2:
SQL> select count(*) from v$result_cache_memory where free='NO';
  COUNT(*)
----------
     24576
--从这里结果可以得出result cache memory有被填满。

总结：
可以发现如果设置result_cache_max_size很大,而且填满，访问视图v$result_cache_memory将是出现可怕的情况，许多与result cache有关的sql语句会挂起！如果你将数据库result_cache_mode=force,情况可能更糟！关于什么原因导致挂起，以后再写！

时间： 2024-07-30 11:05:15

[20111230]11Gr2 result cache[1].txt的相关文章

[20150924]result cache problem.txt

[20150924]result cache problem.txt --昨天看了连接,看到一个关于result cache的例子,重复测试看看: --链接 https://jonathanlewis.wordpress.com/2015/09/22/result-cache/ 1.环境: SCOTT@test> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ --

[20160919]Result cache问题.txt

[20160919]Result cache问题.txt --看了链接http://blog.dbi-services.com/result-cache-side-effects-on-number-of-calls/,重复测试: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -----------

[20141223]result cache 3.txt

[20141223]result cache 3.txt --上午的测试有一些问题,做一些更正. SCOTT@test> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86

[20151015]关于result cache 2.txt

[20151015]关于result cache 2.txt --11G 开始支持result cache,把执行的结果保存在共享池,从而一定程度减少逻辑读,而那些对象或者那些语句适合做result cache呢? --自己在前一段时间想把一些表设置RESULT_CACHE为MODE FORCE,访问这些对象时,可以利用result cache模式. --另外我本来想一些语句通过sql patch的方式加入提示/*+ result_cache */ ,结果不成功. --参考链接:http://b

[20141219]result cache与view.txt

[20141219]result cache与view.txt --result cache是11g的新特性,能一定程度减少逻辑读,我个人的感觉特别适合很少修改,经常访问的小表,而应用中经常扫描的表, --我经常把这种应用模式叫刷屏软件.... --前一阵子我在做优化工作中,遇到的一些问题,做一些总结: SCOTT@test> @ver1 PORT_STRING          VERSION        BANNER -------------------- --------------

[20151014]关于result cache.txt

[20151014]关于result cache.txt --11G 开始支持result cache,把执行的结果保存在共享池,从而一定程度减少逻辑读,而那些对象或者那些语句适合做result cache呢? --自己在前一段时间想把一些表设置RESULT_CACHE为MODE FORCE,访问这些对象时,可以利用result cache模式. --另外我本来想一些语句通过sql patch的方式加入提示/*+ result_cache */ ,结果不成功. --参考链接:http://blo

[20111230]11Gr2审计.txt

[20111230]11Gr2审计.txt 昨天查看v$open_cursor视图,无意中发现如下语句:INSERT INTO SYS.aud$            (sessionid, entryid, STATEMENT, ntimestamp#, userid, userhost, terminal, action#, returncode, obj$creator,             obj$name, auth$privileges, auth$grantee, new$ow

【11gR2新特性】result cache 的三种模式

yang@rac1>show parameter result NAME                                 TYPE        VALUE ------------------------------------ ----------- ------- client_result_cache_lag              big integer 3000 client_result_cache_size             big integer 0 r

1223 result cache,sql profile,sql patch

[20141223]result cache 与sql profile,sql patch.txt --前面blog已经提到result cache的好处与缺点,对于第三方优化,sql profile可以改变稳定执行计划,是否可以通过改变提示来稳定 --执行计划,这样对频繁执行的语句较少逻辑读,提高服务器响应有积极意义. --sql patch 也具有相似的作用,看看这种方式是否可行. SCOTT@test> @ver1 PORT_STRING                    VERSIO