[20190214]11g Query Result Cache RC Latches补充.txt

[20190214]11g Query Result Cache RC Latches补充.txt --//上午测试链接:http://blog.itpub.net/267265/viewspace-2632907/ --//发现自己的一个错误,另外写一篇帖子更正. --//顺便复习result cache的相关内容:链接:https://blog.csdn.net/jolly10/article/details/81382644 查看SQL结果高速缓存字典信息 (G)V$RESULT_CACHE_STATISTICS : 列出各种高速缓存设置和内存使用量统计信息 (G)V$RESULT_CACHE_MEMORY : 列出所有内存块和相应的统计信息 (G)V$RESULT_CACHE_OBJECTS: 列出所有对象（高速缓存结果和依赖性）及其属性 (G)V$RESULT_CACHE_DEPENDENCY: 列出高速缓存结果之间的依赖性详细信息及依赖性 dbms_result_cache包可以监视和管理result cache 例如: dbms_result_cache.flush：清除result cache。 dbms_result_cache.invalidate(owner,name)：使某对象的result cache无效。 dbms_result_cache.status：显示result cache的状态。 dbms_result_cache.memory_report：显示result cache的内存使用状况。 1.环境: SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SCOTT@book> create table job_times ( sid   number, time_ela number); Table created. SCOTT@book> create table hc_t ( n number(*,0), v varchar2(200)) ; Table created. SCOTT@book> insert into hc_t select level, dbms_random.string('p', 200) from dual connect by level <= 10000; 10000 rows created. SCOTT@book> create unique index i_hc_t on hc_t(n); Index created. SCOTT@book> commit; Commit complete. --//分析表略. create or replace procedure do_rc(  p_iterations in number,p_max in number ) is  l_rowid  rowid;  l_n number; begin  insert into job_times   values (sys_context('userenv', 'sid'), dbms_utility.get_time)   returning rowid into l_rowid;  for i in 1 .. p_iterations  loop   l_n:=trunc(dbms_random.value(1, p_max));   for cur in (select /*+ result_cache */ * from hc_t where n=l_n)   loop    null;   end loop;  end loop;  update job_times set    time_ela=dbms_utility.get_time-time_ela   where rowid=l_rowid; end; / --//注:我加入参数p_max,限制取值范围. --//为了重复测试建立脚本. $ cat aa.sql delete from job_times; Commit ; declare  l_job number; begin  for i in 1 .. 4  loop   dbms_job.submit(    job => l_job,    what => 'do_rc(100000,&&1);'     );  end loop; end; / commit ; 2.开始测试: SCOTT@book> 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  1792K result_cache_mode                    string       MANUAL result_cache_remote_expiration       integer      0 SCOTT@book> exec dbms_result_cache.flush() PL/SQL procedure successfully completed. SCOTT@book> set serverout on SCOTT@book> 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          = 0 bytes Maximum Cache Size  = 0 bytes (0 blocks) Maximum Result Size = 0 bytes (0 blocks) [Memory] Total Memory = 40568 bytes [0.022% of the Shared Pool] ... Fixed Memory = 40568 bytes [0.022% of the Shared Pool] ... Dynamic Memory = 0 bytes [0.000% of the Shared Pool] PL/SQL procedure successfully completed. --//我前面测试忽略的result cache的大小. SCOTT@book> @ aa.sql 10000 4 rows deleted. Commit complete. PL/SQL procedure successfully completed. Commit complete. SCOTT@book> select count(*),avg(TIME_ELA),sum(TIME_ELA) from job_times ;   COUNT(*) AVG(TIME_ELA) SUM(TIME_ELA) ---------- ------------- -------------          4        4001.5         16006 --//以上我上午测试的结果.大约每个job需要40秒上下. SCOTT@book> 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  = 1792K bytes (1792 blocks) Maximum Result Size = 89K bytes (89 blocks) [Memory] Total Memory = 2003960 bytes [1.111% of the Shared Pool] ... Fixed Memory = 40568 bytes [0.022% of the Shared Pool] ... Dynamic Memory = 1963392 bytes [1.089% of the Shared Pool] ....... Overhead = 128384 bytes ....... Cache Memory = 1792K bytes (1792 blocks) ........... Unused Memory = 0 blocks ........... Used Memory = 1792 blocks ............... Dependencies = 1 blocks (1 count) ............... Results = 1791 blocks ................... SQL     = 1791 blocks (1791 count) PL/SQL procedure successfully completed. --//实际上我的环境仅仅能容纳1791个结果.也就是我的配置太小,共享池不够大.result_cache_max_result=5,仅仅使用共享池的5%. SCOTT@book> @ aa.sql 1791 4 rows deleted. Commit complete. PL/SQL procedure successfully completed. Commit complete. SCOTT@book> select count(*),avg(TIME_ELA),sum(TIME_ELA) from job_times ;   COUNT(*) AVG(TIME_ELA) SUM(TIME_ELA) ---------- ------------- -------------          4         440.5          1762 --//你可以发现这个就与没有做result cache的结果相近了. --//我重启数据库.通过result cache :RC latch记数也可以验证这个问题. SCOTT@book> column name format a30 SCOTT@book> select name, gets, misses, sleeps, wait_time from v$latch where name like 'Result Cache%'; NAME                                 GETS     MISSES     SLEEPS  WAIT_TIME ------------------------------ ---------- ---------- ---------- ---------- Result Cache: RC Latch                  0          0          0          0 Result Cache: SO Latch                  0          0          0          0 Result Cache: MB Latch                  0          0          0          0 SCOTT@book> @ aa.sql 1791 4 rows deleted. Commit complete. PL/SQL procedure successfully completed. Commit complete. SCOTT@book> select count(*),avg(TIME_ELA),sum(TIME_ELA) from job_times ;   COUNT(*) AVG(TIME_ELA) SUM(TIME_ELA) ---------- ------------- -------------          4           432          1728 SCOTT@book> select name, gets, misses, sleeps, wait_time from v$latch where name like 'Result Cache%'; NAME                                 GETS     MISSES     SLEEPS  WAIT_TIME ------------------------------ ---------- ---------- ---------- ---------- Result Cache: RC Latch             405177       3865         10        132 Result Cache: SO Latch                  8          0          0          0 Result Cache: MB Latch                  0          0          0          0 SCOTT@book> @ aa.sql 10000 4 rows deleted. Commit complete. PL/SQL procedure successfully completed. Commit complete. SCOTT@book> select count(*),avg(TIME_ELA),sum(TIME_ELA) from job_times ;   COUNT(*) AVG(TIME_ELA) SUM(TIME_ELA) ---------- ------------- -------------          4       3978.25         15913 SCOTT@book> select name, gets, misses, sleeps, wait_time from v$latch where name like 'Result Cache%'; NAME                                 GETS     MISSES     SLEEPS  WAIT_TIME ------------------------------ ---------- ---------- ---------- ---------- Result Cache: RC Latch            1787843     534395     683654   67269002 Result Cache: SO Latch                 16          0          0          0 Result Cache: MB Latch                  0          0          0          0