row cache lock 事后分析处理

现场同事告知oracle 19C 下生产大量trc文件，把oracle目录撑爆

查看trc文件如下

kqrpre: key mismatch po=0x132745948 hash=27d744ca
----------------------------------------
SO: 0x12a9d2098, type: row cache enqueues (111), map: 0x17537fa88
    state: LIVE (0x4532), flags: 0x0
    owner: 0x128885560, proc: 0xf7f07950
    link: 0x12a9d20b8[0x12e5e9c30, 0x1288855d0]
    conid: 3, conuid: 565920805, SGA version=(1,0), pg: 0
SOC: 0x17537fa88, type: row cache enqueues (111), map: 0x12a9d2098
     state: LIVE (0x99fc), flags: INIT (0x1)
row cache enqueue: count=1 session=0x104cbbe48 object=0x132745948, mode=S
flag=09 WAI/-/-/LOD/-/-/-/- savepoint=0x11b
row cache parent object: addr=0x132745948 cid=8(dc_objects) conid=3 conuid=565920805
hash=27d744ca typ=21 transaction=(nil) flags=00008000 inc=0, pdbinc=1
objectno=254513 ownerid=105 nsp=1
name=YTS_TRANS_20240331
own=0x132745a18[0x17537fb08,0x17537fb08] wat=0x132745a28[0x139e16960,0x16eb4b120] mode=S req=S
status=-/-/-/-/-/-/-/-/LOADING  KGH pinned
set=0, complete=FALSE

　通过trc文件来看，对表YTS_TRANS_20240331中缓存做dump生成大量trc文件

收集处理问题时间段awr报告，发现大量row cache lock等待事件

查看数据字典缓存

主要申请miss在dc_segments 段对象上，通过addm 可以定位到具体sql语句

 查看表YTS_TRANS_20240331 ddl语句，发现创建表时缺少存储相关参数

解决：

1、调整open_cursors
alter system set open_cursors=2000 scope=spfile;
alter system set session_cached_cursor=500 scope=spfile;
2、调整shared pool
alter system set shared_pool_size=5g scope=spfile;
3、调整initial next值
alter table yts_trans_20240331 move storage( initial 10m next 10M);

一劳永逸的解决这个问题关闭oracle 19c的延迟段

alter system set deferred_segment_creation=false;

参考mos

Resolving Issues Where 'Row Cache Lock' Waits are Occurring (Doc ID 1476670.1)
Troubleshooting: "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! " (Doc ID 278316.1)
select p1text,p1,p2text,p2,p3text,p3 from v$session where event='row cache lock';
select parameter,count,gets,getmisses,modifications from v$rowcache where cache#=8;