[20211026]關於18c row cache mutex.txt
[20211026]關於18c row cache mutex.txt
--//晚上,看了許多row cache mutex相關連結,發現如下:
ksun-oracle.blogspot.com/2020/08/row-cache-object-and-row-cache-mutex.html
ksun-oracle.blogspot.com/2018/07/row-cache-mutex-in-oracle-122010_28.html
ksun-oracle.blogspot.com/2017/07/nlsdatabaseparameters-dcprops-latch-row.html
--//ksun-oracle.blogspot.com 站點的測試比較詳細.從上面介紹可以看出.12.1.0.2.0 (12cR1) 還再使用"latch: row cache
--//objects",從Oracle 12.2.0.1.0 (12cR2)開始才使用 "row cache mutex" 替換"latch: row cache objects".
In Oracle 12.2.0.1.0 (12cR2), "row cache mutex" replaced 12.1.0.2.0 (12cR1) "latch: row cache objects", similar to
"latch: library cache" substitution by "library cache: mutex X" in the previous release.
--//看了anbob網站,感覺row cache mutex相關的bug非常多.也許oracle每一項新特性都伴隨大量的bug,穩定性還不足.
--//我們生產系統使用的ODA 的oracle版本竟然是19.0.0.0.0,上線之前都沒有打任何補丁,無語...
--//ksun-oracle.blogspot.com 還拿nls_database_parameters檢視做了許多相關測試.昨天上午上班無法....,回家仔細看了一下.
--//手頭已經沒有12.1.X版本,無法在生產系統19c上做這樣的測試，找了一臺虛擬機器上執行18c的測試看看。
1.環境：
SYS@192.168.a.b:1521/orcl> @ ver
SYS@192.168.a.b:1521/orcl> @ prxx
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 18.0.0.0.0
BANNER : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
BANNER_FULL : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
BANNER_LEGACY : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.測試環境建立：
create table job_times (sid number, time_ela number,method varchar2(20));
$ cat m14.txt
set verify off
host sleep $(echo &&3/50 | bc -l )
variable vmethod varchar2(20);
exec :vmethod := '&&2';
insert into job_times values ( sys_context ('userenv', 'sid') ,dbms_utility.get_time ,:vmethod) ;
commit ;
declare
v_id number;
v_d date;
v_val VARCHAR2(1000);
l_count PLS_INTEGER;
begin
for i in 1 .. &&1 loop
--//select /*+ &&3 */ value into v_val from v$nls_parameters where parameter = 'NLS_CHARACTERSET';
select /*+ &&3 */ value into v_val from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
--//select sql_text into v_val from v$sql where rownum=1;
--//SELECT /*+ &&3 */ seq1.NEXTVAL into v_id FROM DUAL;
end loop;
end ;
/
update job_times set time_ela = dbms_utility.get_time - time_ela where sid=sys_context ('userenv', 'sid') and method= (standard_in) 1: syntax errorvmethod;
commit;
quit
3.測試：
--//沒想到很慢，我只能修改迴圈次數以及併發數量。
$ zzdate;seq 10 | xargs -I{} -P 10 sqlplus -s -l "sys/xxxxxxx@orcl as sysdba" @m14.txt 1e5 P=10 {} 1 >/dev/null;zzdate
trunc(sysdate)+09/24+16/1440+31/86400 == 2021/10/26 09:16:31
trunc(sysdate)+09/24+21/1440+13/86400 == 2021/10/26 09:21:13
SYS@192.168.a.b:1521/orclcdb> @tpt/ashtop event,p1,p2,p3 "module like 'sqlplus%' and username='SYS' and event like 'row cache%'" trunc(sysdate)+09/24+16/1440+31/86400 trunc(sysdate)+09/24+21/1440+13/86400
Total
Seconds AAS %This EVENT P1 P2 P3 FIRST_SEEN LAST_SEEN
--------- ------- ------- ---------------- --- ---- --- ------------------- -------------------
1663 5.9 92% | row cache mutex 15 19 0 2021-10-26 09:16:33 2021-10-26 09:21:11
144 .5 8% | row cache mutex 60 19 0 2021-10-26 09:16:37 2021-10-26 09:21:12
8 .0 0% | row cache mutex 15 10 0 2021-10-26 09:16:46 2021-10-26 09:21:09
2 .0 0% | row cache mutex 60 10 0 2021-10-26 09:17:32 2021-10-26 09:17:32
--//可以發現如果應用存在這樣類似語句大量併發執行，會拖累整個系統效能。
SYS@orcl> Select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from job_times group by method order by 3 ;
METHOD COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA)
-------------------- ---------- ---------------------- -------------
P=10 10 27925 279253
--//大約平均280秒上下。
SYS@192.168.a.b:1521/orclcdb> @ ev_name 'row cache mutex'
SYS@192.168.a.b:1521/orclcdb> @ prxx
==============================
EVENT# : 344
EVENT_ID : 306610566
NAME : row cache mutex
PARAMETER1 : cache id
PARAMETER2 : where requested
PARAMETER3 :
WAIT_CLASS_ID : 3875070507
WAIT_CLASS# : 4
WAIT_CLASS : Concurrency
DISPLAY_NAME : row cache mutex
CON_ID : 0
PL/SQL procedure successfully completed.
SYS@192.168.a.b:1521/orclcdb> column usage format 9999999
SYS@192.168.a.b:1521/orclcdb> select * from v$rowcache where cache# in (15,60);
CACHE# TYPE SUBORDINATE# PARAMETER COUNT USAGE FIXED GETS FASTGETS GETMISSES SCANS SCANMISSES SCANCOMPLETES MODIFICATIONS FLUSHES DLM_REQUESTS DLM_CONFLICTS DLM_RELEASES CON_ID
------ ------ ------------ ----------- ----- ----- ----- --------- -------- --------- ----- ---------- ------------- ------------- ------- ------------ ------------- ------------ ------
15 PARENT dc_props 89 89 0 257978364 0 8146 4 0 4 4 4 0 0 0 0
60 PARENT dc_cdbprops 7 7 0 19065107 0 581 0 0 0 6 6 0 0 0 0
--//CACHE#=15 對應dc_props，CACHE#=60對應dc_cdbprops。
--//順便看以上相關語句的執行計劃，可以發現訪問的是X$PROPS。
Plan hash value: 2762963881
------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)|
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
|* 1 | FIXED TABLE FULL| X$PROPS | 1 | 31 | 0 (0)|
------------------------------------------------------------------
--//檢視檢視的定義如下：
SYS@192.168.a.b:1521/orclcdb> @ ddl sys.nls_database_parameters
C300
------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."NLS_DATABASE_PARAMETERS" ("PARAMETER", "VALUE") AS
select name,
substr(value$, 1, 64)
from x$props
where name like 'NLS%';
--//如果對比11g的定義：
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> @ ddl sys.nls_database_parameters
C300
---------------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SYS"."NLS_DATABASE_PARAMETERS" ("PARAMETER", "VALUE") AS
select name,
substr(value$, 1, 40)
from props$
where name like 'NLS%';
--//可以發現兩者訪問的物件不同。前者是x$props，後者是props$。
SYS@orcl> select cache_name, existent, count(*) cnt from v$rowcache_parent where cache_name = 'dc_props' group by cache_name, existent;
CACHE_NA E CNT
-------- - ----------
dc_props N 24
dc_props Y 36
SYS@192.168.a.b:1521/orclcdb> select cache_name, existent, count(*) cnt from v$rowcache_parent where cache_name = 'dc_cdbprops' group by cache_name, existent;
CACHE_NAME EX CNT
---------------------- -- ------------
dc_cdbprops N 11
dc_cdbprops Y 1
SYS@192.168.a.b:1521/orclcdb> select * from x$mutex_sleep where mutex_type='Row Cache' order by sleeps desc;
ADDR INDX INST_ID CON_ID MUTEX_TYPE MUTEX_TYPE_ID LOCATION_ID LOCATION SLEEPS WAIT_TIME
---------------- ------------ ------------ ------------ ------------ ------------- ------------ ------------------------------- ------- ------------
00007F646C176500 4 1 0 Row Cache 4 19 [19] kqrpre 9924170 50159384303
00007F646C176620 6 1 0 Row Cache 4 10 [10] kqreqd 117833 98550710
00007F646C1763E0 2 1 0 Row Cache 4 32 [32] kqrsfd 1996 1329094
00007F646C176590 5 1 0 Row Cache 4 17 [17] kqrCreateUsingSecondaryKey 187 280745
00007F646C1766B0 7 1 0 Row Cache 4 8 [08] kqrget 42 18877
00007F646C176350 1 1 0 Row Cache 4 33 [33] kqrsrd 36 50532
00007F646C176740 8 1 0 Row Cache 4 3 [03] kqrUpdateHashTable 33 9042
00007F646C1762C0 0 1 0 Row Cache 4 34 [34] kqrssc 7 40739
00007F646C176470 3 1 0 Row Cache 4 31 [31] kqrcmt 1 8
9 rows selected.
--//locate_id=19,10 與等待事件P2對應。
SELECT *
FROM ( SELECT mutex_addr, COUNT (*)
FROM x$mutex_sleep_history
WHERE mutex_type = 'Row Cache'
GROUP BY mutex_addr
ORDER BY 2 DESC)
WHERE ROWNUM <= 5;
MUTEX_ADDR COUNT(*)
---------------- ----------
0000000089F20D10 33
0000000089F20AB8 32
0000000089F20D70 30
0000000089F20D58 30
0000000089F209E0 19
SYS@192.168.a.b:1521/orclcdb> @ tpt/fcha 0000000089F20D10
Find in which heap (UGA, PGA or Shared Pool) the memory address 0000000089F20D10 resides...
WARNING!!! This script will query X$KSMSP, which will cause heavy shared pool latch contention
in systems under load and with large shared pool. This may even completely hang
your instance until the query has finished! You probably do not want to run this in production!
Press ENTER to continue, CTRL+C to cancel...
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
------ ---------------- ------------ ------------ -------------------------------- ------------ ---------------- ------------ ----------------
SGA 00000000890CD000 1 1 permanent memor 15579088 perm 0 00
SYS@192.168.a.b:1521/orclcdb> @ tpt/fcha 0000000089F20AB8
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
------ ---------------- ------------ ------------ -------------------------------- ------------ ---------------- ------------ ----------------
SGA 00000000890CD000 1 1 permanent memor 15579088 perm 0 00
--//可以發現都是位於0x00000000890CD000 ，0x00000000890CD000+15579088(十進位制)區域。
4.繼續測試：
--//如果換成select /*+ &&3 */ value into v_val from v$nls_parameters where parameter = 'NLS_CHARACTERSET';語句，不需要訪
--//問dc_props,dc_cdbprops等資料字典。執行情況呢？
$ cat m14.txt
set verify off
host sleep $(echo &&3/50 | bc -l )
variable vmethod varchar2(20);
exec :vmethod := '&&2';
insert into job_times values ( sys_context ('userenv', 'sid') ,dbms_utility.get_time ,:vmethod) ;
commit ;
declare
v_id number;
v_d date;
v_val VARCHAR2(1000);
l_count PLS_INTEGER;
begin
for i in 1 .. &&1 loop
select /*+ &&3 */ value into v_val from v$nls_parameters where parameter = 'NLS_CHARACTERSET';
--//select /*+ &&3 */ value into v_val from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
--//select sql_text into v_val from v$sql where rownum=1;
--//SELECT /*+ &&3 */ seq1.NEXTVAL into v_id FROM DUAL;
end loop;
end ;
/
update job_times set time_ela = dbms_utility.get_time - time_ela where sid=sys_context ('userenv', 'sid') and method= (standard_in) 1: syntax errorvmethod;
commit;
quit
$ zzdate;seq 10 | xargs -I{} -P 10 sqlplus -s -l "sys/xxxxxxx@orcl as sysdba" @m14.txt 1e5 P=10x {} 1 >/dev/null;zzdate
trunc(sysdate)+10/24+02/1440+31/86400 == 2021/10/26 10:02:31
trunc(sysdate)+10/24+02/1440+39/86400 == 2021/10/26 10:02:39
--//僅僅需要8秒執行完成。
SYS@orcl> Select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from job_times group by method order by 3 ;
METHOD COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA)
-------------------- ---------- ---------------------- -------------
P=10x 10 630 6303
P=10 10 27925 279253
SYS@192.168.a.b:1521/orclcdb> @tpt/ashtop event,p1,p2,p3 "module like 'sqlplus%' and username='SYS' and event like 'row cache%'" trunc(sysdate)+10/24+02/1440+31/86400 trunc(sysdate)+10/24+02/1440+39/86400
no rows selected
--//沒有任何row cache相關等待事件。
SYS@192.168.a.b:1521/orclcdb> @tpt/ashtop event,p1,p2,p3 "module like 'sqlplus%' and username='SYS' " trunc(sysdate)+10/24+02/1440+31/86400 trunc(sysdate)+10/24+02/1440+39/86400
Total
Seconds AAS %This EVENT P1 P2 P3 FIRST_SEEN LAST_SEEN
--------- ------- ------- ------------------- ---------- ------------- -------------------- ------------------- -------------------
39 4.9 59% | 65536 1 0 2021-10-26 10:02:33 2021-10-26 10:02:38
10 1.3 15% | CRS call completion 14 6 0 2021-10-26 10:02:32 2021-10-26 10:02:32
6 .8 9% | 3390613612 721554505728 18446744069421400154 2021-10-26 10:02:33 2021-10-26 10:02:38
6 .8 9% | 3390613612 1228360646656 18446744069421400068 2021-10-26 10:02:33 2021-10-26 10:02:38
5 .6 8% | 1613826344 592 0 2021-10-26 10:02:34 2021-10-26 10:02:38
--// CRS call completion 也沒有遇到，有機會研究。
--//執行計劃如下：
select /*+ 1 */ value from v$nls_parameters where parameter =
'NLS_CHARACTERSET'
Plan hash value: 1805486652
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)|
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
|* 1 | FIXED TABLE FULL| X$NLS_PARAMETERS | 1 | 32 | 0 (0)|
---------------------------------------------------------------------------
--//同樣是X表,所以特別提醒，一些監視或者應用遞迴語句以及一些系統檢視儘量規避資料欄位相關資訊的訪問。
--//另外這臺資料庫已經執行很久了，看看row cache objects latch的情況。
SYS@orcl> select name, latch#,gets from v$latch where name in ('row cache objects');
NAME LATCH# GETS
-------------------- ---------- ----------
row cache objects 544 13295
SYS@orcl> select name, latch#,gets from v$latch where name in ('row cache objects');
NAME LATCH# GETS
-------------------- ---------- ----------
row cache objects 544 13296
--//執行1次增加1，說明12R2以後我估計row cahce已經使用mutex代替latch。
--//19c也類似：
> select name, latch#,gets from v$latch where name in ('row cache objects');
NAME LATCH# GETS
---------------------------------------- ---------- ----------
row cache objects 569 1890
> select name, latch#,gets from v$latch where name in ('row cache objects');
NAME LATCH# GETS
---------------------------------------- ---------- ----------
row cache objects 569 1891
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2839264/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20211031]18c row cache mutext等待事件探究.txtMutex事件
- [20190402]Library Cache mutex.txtMutex
- [20220301]oracle如何定位使用library cache mutex.txtOracleMutex
- 關於hibernate的 No row with the given identifier existsIDE
- 【ASK_ORACLE】Row Cache Enqueue鎖之概念篇OracleENQ
- MySQL 關於Table cache設定MySql
- 關於mysql的query_cacheMySql
- 關於 PHP artisan config:cache 引發的 bugPHP
- 關於Oracle 18c將採用新的命名版本號的方式Oracle
- 當刪除oracle資料庫user時發生row cache lock 等待事件Oracle資料庫事件
- [20211026]奇怪註解不起作用.txt
- 用於排查cache buffers chainsAI
- 有關mysql中ROW_COUNT()的小例子MySql
- 關於 http cache 的一個小問題以及引發的思考HTTP
- MySQL裡的found_row()與row_count()MySql
- A. Arrow a Row
- ORACLE ROW MOVEMENTOracle
- Oracle 18cOracle
- 用於快取的新HTTP標準：Cache-Status和Target-Cache-Control快取HTTP
- MySQL Binlogging Fails With Writing One Row To The Row-based Binary Log FailedMySqlAI
- Buffer Cache以及buffer busy waits/gc相關事件AIGC事件
- Nginx下關於快取控制欄位cache-control的配置說明 - 運維小結Nginx快取運維
- 關於 Senparc.Weixin.Cache.Redis 引用的 StackExchange.Redis 版本不匹配的反饋測試Redis
- #1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs
- enq: TX - row lock contentionENQ
- 關於IT，關於技術
- Flutter 之 Row、Column詳解Flutter
- flutter佈局-2-rowFlutter
- 623-Add One Row to Tree
- [20191125]18c oraversion.txt
- library cache pin和library cache lock(一)
- library cache pin和library cache lock (zt)
- library cache pin和library cache lock(二)
- Oracle 18c新特性詳解 - 表和表空間相關的新特性Oracle
- Spring CacheSpring
- Guava CacheGuava
- PostgreSQL的shared_buffers和系統OS cache的關係SQL
- Service Worker Cache 和 HTTP Cache 的區別HTTP