[20190214]11g Query Result Cache RC Latches.txt
[20190214]11g Query Result Cache RC Latches.txt
--//昨天我重複連結http://www.pythian.com/blog/oracle-11g-query-result-cache-rc-latches/的測試，
--//按照我的理解如果sql語句密集執行，使用Result Cache反而更加糟糕，這是我以前沒有注意到的。
--//聯想我們生產系統也存在類似的問題，我們有1個判斷連線的語句select count（*) from test_connect;
--//在業務高峰它執行可以達到1600次/秒。另外一個簡單的select sysdate from dual; 也達到800次/秒。
--//而實際上業務高峰sql語句執行率3000次/秒。這樣的2條語句就佔了2400次/秒。我以前一直以為將表設定
--//為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> show parameter job
NAME TYPE VALUE
------------------- ------- ------
job_queue_processes integer 200
SCOTT@book> select * from v$latchname where name like 'Result Cache%';
LATCH# NAME HASH
------ ----------------------- ----------
436 Result Cache: RC Latch 1054203712
437 Result Cache: SO Latch 986859868
438 Result Cache: MB Latch 995186388
--//我看到Result Cache名字與作者的不同，命名為Result Cache: RC Latch。
SCOTT@book> select name,gets from v$latch where lower(name) like '%result cache%';
NAME GETS
------------------------------ ----------
Result Cache: RC Latch 0
Result Cache: SO Latch 0
Result Cache: MB Latch 0
SCOTT@book> select count(*) from v$latch_children where lower(name) like '%result cache%';
COUNT(*)
----------
0
--//可以注意一個細節,Result Cache沒有children latch。也僅僅1個Result Cache: RC Latch 父latch。從這裡也可以看出如果
--//做了result cache的表，多個使用者併發執行，可能反而不能獲得好的效能,可能出現大量的Result Cache: RC Latch爭用的情況.
2.建立測試例子：
create table t as select rownum id from dual ;
create unique index pk_t on t(id);
--//分析略。
SCOTT@book> create table job_times ( sid number, time_ela number);
Table created.
--//按照源連結的例子修改如下：
create or replace procedure do_work(
p_iterations in number
) is
l_rowid rowid;
v_t 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
select count(*) into v_t from t;
end loop;
update job_times set
time_ela=dbms_utility.get_time-time_ela
where rowid=l_rowid;
commit;
end;
/
3.測試:
--//首先測試不做result cache的情況：
--//alter table t result_cache (mode default);
declare
l_job number;
begin
for i in 1 .. 50
loop
dbms_job.submit(
job => l_job,
what => 'do_work(1000000);'
);
end loop;
end;
/
SCOTT@book> commit ;
Commit complete.
--//注意一定要寫提交，不然dbms_job.submit要等很久才執行。
SCOTT@book> select count(*),avg(TIME_ELA),sum(TIME_ELA) from job_times ;
COUNT(*) AVG(TIME_ELA) SUM(TIME_ELA)
---------- ------------- -------------
50 9235.1 461755
4.測試:
--///測試做result cache的情況，為了測試的準確，我重啟資料庫。
SCOTT@book> delete from job_times;
50 rows deleted.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> alter table t result_cache (mode force);
Table altered.
--//重啟資料庫.
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
declare
l_job number;
begin
for i in 1 .. 50
loop
dbms_job.submit(
job => l_job,
what => 'do_work(100000);'
);
end loop;
end;
/
SCOTT@book> commit ;
Commit complete.
SCOTT@book> select count(*),avg(TIME_ELA),sum(TIME_ELA) from job_times ;
COUNT(*) AVG(TIME_ELA) SUM(TIME_ELA)
---------- ------------- -------------
50 7135.96 356798
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 54232541 3499238 0 0
Result Cache: SO Latch 202 0 0 0
Result Cache: MB Latch 0 0 0 0
--//很明顯,即使存在Result Cache: RC Latch的爭用,但是WAIT_TIME=0,不過我發現這樣測試的一個缺點,就是50個job並不是同時執行.
--//$ ps -ef | grep ora_[j]|wc ,看看數量是不斷增加的過程.
--//而且採用Result Cache後效果還是增強的.
5.換一個方式測試:
SCOTT@book> delete from job_times;
53 rows deleted.
SCOTT@book> commit ;
Commit complete.
--//設定result_cache=default
SCOTT@book> alter table t result_cache (mode default);
Table altered.
$ seq 50 | xargs -I{} echo 'sqlplus -s -l scott/book <<< "execute do_work(1000000)" & '| bash
--//等全部完成...
SCOTT@book> select count(*),avg(TIME_ELA),sum(TIME_ELA) from job_times ;
COUNT(*) AVG(TIME_ELA) SUM(TIME_ELA)
---------- ------------- -------------
50 10588.26 529413
SCOTT@book> delete from job_times;
50 rows deleted.
SCOTT@book> commit ;
Commit complete.
--//設定result_cache=force
SCOTT@book> alter table t result_cache (mode force);
Table altered.
$ seq 50 | xargs -I{} echo 'sqlplus -s -l scott/book <<< "execute do_work(1000000)" & '| bash
SCOTT@book> select count(*),avg(TIME_ELA),sum(TIME_ELA) from job_times ;
COUNT(*) AVG(TIME_ELA) SUM(TIME_ELA)
---------- ------------- -------------
50 8573.28 428664
--//可以看到即使這樣大併發,採用result cache還是要快許多,沒有遇到作者的情況.
--//可以11GR2做了一些改進,不會遇到這樣的情況.
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 103461569 7263987 0 0
Result Cache: SO Latch 302 0 0 0
Result Cache: MB Latch 0 0 0 0
6.不過當我拿作者的最後的例子做最後的測試發現,使用result cache慢很多.
SCOTT@book> create cluster hc ( n number(*,0)) single table hashkeys 15000 size 230;
Cluster created.
SCOTT@book> create table hc_t ( n number(*,0), v varchar2(200)) cluster hc (n);
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> commit;
Commit complete.
--//分析表略.
All we need now is two procedures, one with a regular select and another with a cached select:
create or replace procedure do_hc(
p_iterations 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, 10000));
for cur in (select * 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;
/
Procedure created.
create or replace procedure do_rc(
p_iterations 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, 10000));
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;
/
Procedure created.
The hash cluster will go first:
SCOTT@book> delete from job_times;
4 rows deleted.
SQL> commit;
Commit complete.
declare
l_job number;
begin
for i in 1 .. 4
loop
dbms_job.submit(
job => l_job,
what => 'do_hc(100000);'
);
end loop;
end;
/
PL/SQL procedure successfully completed.
SCOTT@book> commit ;
Commit complete.
--allow jobs to complete
SCOTT@book> select case grouping(sid) when 1 then 'Total:' else to_char(sid) end sid, sum(time_ela) ela from job_times group by rollup((sid, time_ela));
SID ELA
------- ----
41 446
54 437
80 438
94 437
Total: 1758
--//每個測試僅僅需要4秒.
Now let's see if Result Cache can beat those numbers:
SCOTT@book> delete from job_times;
4 rows deleted.
SCOTT@book> commit ;
Commit complete.
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 20385043 535762 5 94
Result Cache: SO Latch 9 0 0 0
Result Cache: MB Latch 0 0 0 0
declare
l_job number;
begin
for i in 1 .. 4
loop
dbms_job.submit(
job => l_job,
what => 'do_rc(100000);'
);
end loop;
end;
/
PL/SQL procedure successfully completed.
SCOTT@book> commit ;
Commit complete.
--allow jobs to complete
SCOTT@book> select case grouping(sid) when 1 then 'Total:' else to_char(sid) end sid, sum(time_ela) ela from job_times group by rollup((sid, time_ela));
SID ELA
------ ------
41 3850
54 3853
80 3860
94 3863
Total: 15426
--//我的測試使用Result Cache 更加糟糕!!每個測試需要38秒.而作者的測試兩者幾乎差不多.作者用 Nothing (almost) 來表達.
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 21768802 1045691 663187 64314325
Result Cache: SO Latch 17 0 0 0
Result Cache: MB Latch 0 0 0 0
--//我開始以為這裡有1個將結果集放入共享池的過程,每一次執行都需要放入共享池.再次呼叫應該會快一些.
create or replace procedure do_rc(
p_iterations 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, 10000));
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;
/
--//再次執行:
declare
l_job number;
begin
for i in 1 .. 4
loop
dbms_job.submit(
job => l_job,
what => 'do_rc(100000);'
);
end loop;
end;
/
PL/SQL procedure successfully completed.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> select case grouping(sid) when 1 then 'Total:' else to_char(sid) end sid, sum(time_ela) ela from job_times group by rollup((sid, time_ela));
SID ELA
----- -----
72 3980
81 3900
96 3936
108 3922
Total 15738
--//問題依舊.我估計不同查詢存在select /*+ result_cache */ * from hc_t where n=l_n的情況下,探查Result Cache: RC Latch持有
--//時間很長,導致使用result cache更慢,這樣看來result_cache更加適合統計類結果不變的語句.而且繫結變數不要變化很多的情況.
--//換成普通表測試看看:
SCOTT@book> rename hc_t to hc_tx;
Table renamed.
SCOTT@book> create table hc_t as select * from hc_tx ;
Table created.
SCOTT@book> create unique index i_hc_t on hc_t(n);
Index created.
--//分析表略.
--//呼叫do_hc的情況如下:
SCOTT@book> select count(*),avg(TIME_ELA),sum(TIME_ELA) from job_times ;
COUNT(*) AVG(TIME_ELA) SUM(TIME_ELA)
---------- ------------- -------------
4 431.5 1726
--//呼叫do_rc的情況如下:
SCOTT@book> select count(*),avg(TIME_ELA),sum(TIME_ELA) from job_times ;
COUNT(*) AVG(TIME_ELA) SUM(TIME_ELA)
---------- ------------- -------------
4 4027.75 16111
--//結果一樣.刪除索引在測試看看.
SCOTT@book> drop index i_hc_t ;
Index dropped.
--//呼叫do_hc的情況如下:
--//delete from job_times;
--//commit ;
SCOTT@book> select count(*),avg(TIME_ELA),sum(TIME_ELA) from job_times ;
COUNT(*) AVG(TIME_ELA) SUM(TIME_ELA)
---------- ------------- -------------
4 4160 16640
--//呼叫do_rc的情況如下:
--//delete from job_times;
--//commit ;
SCOTT@book> select count(*),avg(TIME_ELA),sum(TIME_ELA) from job_times ;
COUNT(*) AVG(TIME_ELA) SUM(TIME_ELA)
---------- ------------- -------------
4 3828 15312
--//這個時候result cache優勢才顯示出來.總之在生產系統使用要注意這個細節,一般result cahe僅僅只讀表(dml很少的靜態表)外.
--//如果經常使用不同變數查詢表,能使用索引的情況,使用result cache毫無優勢可言.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2632907/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20190214]11g Query Result Cache RC Latches補充.txt
- 淺談Oracle Result CacheOracle
- 關於mysql的query_cacheMySql
- [20241108]跟蹤library cache lock library cache pin使用gdb(11g)4.txt
- [20241108]跟蹤library cache lock library cache pin使用gdb(11g)3.txt
- [20241105]跟蹤library cache lock library cache pin使用gdb(11g)2.txt
- JDBC/MYSQL問題 : Unknown system variable 'query_cache_size'JDBCMySql
- 【11gR2新特性】DBMS_RESULT_CACHE管理結果快取的包快取
- MySQL query_cache_type的DEMAND引數介紹和使用舉例MySql
- Oracle 11g 密碼延遲認證與 library cache lock 等待Oracle密碼
- [20210520]11g shared pool latch與library cache mutex的簡單探究.txtMutex
- GaussDB(for MySQL) ：Partial Result Cache，通過快取中間結果對運算元進行加速MySql快取
- Numercial result of ADFP
- [20210521]11g shared pool latch與library cache mutex的簡單探究4.txtMutex
- [20210520]11g shared pool latch與library cache mutex的簡單探究3.txtMutex
- FileReader result 屬性
- Numercial result of irRMILrp-CGP
- 【Lintcode】1615. The Result of Investment
- attempt to index local ‘result‘ (a nil value)Index
- Query DSL
- reg query /?
- ES 筆記十：Query String & Simple Query String筆記
- RC4加密加密
- Kotlin 1.4.0-RC！Kotlin
- coca SLOW QUERY
- expdp query用法
- Flashback Query(轉)
- Elasticsearch——query stringElasticsearch
- ORA-01489: result of string concatenation is too long
- struts 2 result的type值解釋
- [譯] 如何在 Swift 5 中使用 ResultSwift
- library cache pin和library cache lock(一)
- library cache pin和library cache lock (zt)
- library cache pin和library cache lock(二)
- Spring CacheSpring
- Guava CacheGuava
- Rc+RefCell解釋
- 官宣 .NET RC 2