[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