[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

(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐