Oracle latch: row cache objects系统整体慢

latch: row cache objects14,465,199509.1K3542.5ConcurrencyDB CPU
158.2K
13.2
db file sequential read6,253,81957.4K94.8User I/OSQL*Net message from dblink20,64226.7K12922.2Network 由于不能连现场环境的，从数据库报告里面可以看到很多SQL在等待，有一条执行非常频繁的SQL等待非常多（4小时执行500万次），那怎么确认呢？

with t as( select s.sql_id,count(1) cc
from dba_hist_active_sess_history s
where (s.event = 'latch: row cache objects')
and s.snap_id in(
select snap_id
from (select snap_id from dba_hist_snapshot s order by s.snap_id desc)
where rownum <= 36 --这个数据库是1小时产生一次快照，36的意思是36小时以前
)
group by sql_id)
select s.SQL_ID,s.sql_text,cc
from dba_hist_sqltext s,t
where s.sql_id = t.sql_id
order by cc desc

根据上面的SQL，确认了执行频繁的这条SQL问题最大。

先说一下latch: row cache objects：This latch comes into play when user processes are attempting to access or update the cached data dictionary values.说的这个latch是对象的数据字典。

Dictionary Cache Stats

• "Pct Misses" should be very low (< 2% in most cases)
• "Final Usage" is the number of cache entries being used

dc_awr_control 25 8.00 0 1 dc_global_oids 44 0.00 0 310 dc_histogram_data 103,004 0.72 34 166,645 dc_histogram_defs 36,631 3.36 22 84,754 dc_objects 50,867 0.27 9 18,270 dc_partition_scns 19 60 dc_profiles 652 0.00 dc_rollback_segments 85,110 4,118 dc_segments 11,520 0.97 3 13,314 dc_sequences 529 34.03 47 dc_table_scns 19 6 dc_tablespaces 2,076,498 20 dc_users 2,092,954 108 global database name 2 outstanding_alerts 30 100.00 0 sch_lj_oids 2 10

对比相同时间段的数据库报告，发现dc_users和dc_tablespaces比系统正常时要高。

DC_SEQUENCES
Caused by using sequences in simultaneous insert operations. =>
Consider caching sequences using the cache option. Especially important on RAC instances!
Bug 6027068 – Contention on ORA_TQ_BASE sequence -fixed in 10.2.0.5 and 11.2.0.1

DC_OBJECTS
Look for any object compilation activity which might require an exclusive lock,blocking other activities. If object compiles are occurring this can require an exclusive lock which will block other activity. Tune by examining invalid objects and dependencies with following SQL:
select * from dba_objects order by last_ddl_time desc;
select * from dba_objects where status = 'INVALID';
Can be a bug like the following ones: Bug 11070004 – High row cache objects latch contention w/ oracle text queries Bug 11693365 – Concurrent Drop table and Select on Reference constraint table hangs(deadlock) – fixed in 12.1 DC_SEGMENTS This is most likely due to segment allocation. Identify what the session holding the enqueue is doing and use errorstacks to diagnose.

DC_USERS
– This may occur if a session issues a GRANT to a user,and that user is in the process of logging on to the database.
– Excessive calls to dc_users can be a symptom of “set role XXXX”
– You can check the presents of massive login attempts,even the failed ones by analyzing listener.log (use OEM 12c-> All Metrics or by checking database AUDIT if available or using own tools).
– Bug 7715339 – Logon failures causes “row cache lock” waits – Allow disable of logon delay

DC_TABLESPACES
Probably the most likely cause is the allocation of new extents. If extent sizes are set low then the application may constantly be requesting new extents and causing contention. Do you have objects with small extent sizes that are rapidly growing? (You may be able to spot these by looking for objects with large numbers of extents). Check the trace for insert/update activity,check the objects inserted into for number of extents.

DC_USED_EXTENTS and DC_FREE_EXTENTS
This row cache lock wait may occur similar during space management operations where tablespaces are fragmented or have inadequate extent sizes. Tune by checking whether tablespaces are fragmented,extent sizes are too small,or tablespaces are managed manually.

DC_ROLLBACK_SEGMENTS
– This is due to rollback segment allocation. Just like dc_segments,identify what is holding the enqueue and also generate errorstacks.
Possible Bugs:
– Bug 7313166 Startup hang with self deadlock on dc_rollback_segments (Versions BELOW 11.2)
– Bug 7291739 Contention Under Auto-Tuned Undo Retention (Doc ID 742035.1)

DC_TABLE_SCNS
Bug 5756769 – Deadlock between Create MVIEW and DML – fixed in 10.2.0.5,11.1.07 and 11.2.0.1

DC_AWR_CONTROL
This enqueue is related to control of the Automatic Workload Repository. As such any operation manipulating the repository may hold this so look for processes blocking these.

解决的方法就是改写SQL，倒不是把SQL优化到多少秒，三表关联改为两表关联，减少了对数据字典的访问。

SQL> select p.*
from GG_def_process p
where exists (select 1
from (select a.process_id,max(a.version) as version1
from GG_DEF_PROCESS a,GG_def_deploye b
where a.deploye_id = b.deploye_id
and b.state = 1
group by a.process_id) d
where p.version = d.version1
and p.process_id = d.process_id
and p.process_id = 'GG_RESULTS_REGISTER');
已用时间: 00: 00: 00.29
执行计划
----------------------------------------------------------
Plan hash value: 401082799
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 135 | 11780 (10)| 00:02:22 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | GG_DEF_PROCESS | 2151 | 283K| 13 (0)| 00:00:01 |
|* 3 | FILTER | | | | | |
| 4 | HASH GROUP BY | | 1 | 102 | 11 (10)| 00:00:01 |
|* 5 | FILTER | | | | | |
| 6 | NESTED LOOPS | | | | | |
| 7 | NESTED LOOPS | | 4 | 408 | 10 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| GG_DEF_PROCESS | 4 | 248 | 6 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | BIN$C/6DhukgMGjgUzgPCgqOZw==$0 | 4 | | 2 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | BIN$C/6DhujTMGjgUzgPCgqOZw==$0 | 1 | | 0 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID | GG_DEF_DEPLOYE | 1 | 40 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "LCAM_SYS"."GG_DEF_DEPLOYE" "B","LCAM_SYS"."GG_DEF_PROCESS" "A"
WHERE :B1='GG_RESULTS_REGISTER' AND "A"."PROCESS_ID"=:B2 AND "A"."DEPLOYE_ID"="B"."DEPLOYE_ID" AND
"B"."STATE"=1 GROUP BY "A"."PROCESS_ID",:B3 HAVING MAX("A"."VERSION")=:B4))
3 - filter(MAX("A"."VERSION")=:B1)
5 - filter(:B1='GG_RESULTS_REGISTER')
9 - access("A"."PROCESS_ID"=:B1)
10 - access("A"."DEPLOYE_ID"="B"."DEPLOYE_ID")
11 - filter("B"."STATE"=1)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
104 consistent gets
15 physical reads
0 redo size
1012 bytes sent via SQL*Net to client
472 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


SQL> select * from (
select p.*,row_number() over(partition by process_id order by p.version desc) rn
from GG_def_process p,GG_def_deploye b
where p.deploye_id = b.deploye_id
and b.state = 1
and p.process_id = 'GG_RESULTS_REGISTER') a where rn=1;


已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 1874179583
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 618 | 9 (12)| 00:00:01 |
|* 1 | VIEW | | 3 | 618 | 9 (12)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK | | 3 | 525 | 9 (12)| 00:00:01 |
| 3 | NESTED LOOPS | | | | | |
| 4 | NESTED LOOPS | | 3 | 525 | 8 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| GG_DEF_PROCESS | 3 | 405 | 5 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | BIN$C/6DhukgMGjgUzgPCgqOZw==$0 | 3 | | 2 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | BIN$C/6DhujTMGjgUzgPCgqOZw==$0 | 1 | | 0 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID | GG_DEF_DEPLOYE | 1 | 40 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"=1)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "PROCESS_ID" ORDER BY INTERNAL_FUNCTION("P"."VERSION")DESC )<=1)
6 - access("P"."PROCESS_ID"='GG_RESULTS_REGISTER')
7 - access("P"."DEPLOYE_ID"="B"."DEPLOYE_ID")
8 - filter("B"."STATE"=1)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
1067 bytes sent via SQL*Net to client
472 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

如果出问题的时候能连数据库，且有sys的权限，是可以看到到底是哪个数据字典争用严重。

col cache# head "Cache|no" form 999 col parameter head "Parameter" form a25 col type head "Type" form a12 col subordinate# head "Sub|ordi|nate" form 9999 col rcgets head "Cache|Gets" form 999999999999 col rcmisses head "Cache|Misses" form 999999999999 col rcmodifications head "Cache|Modifica|tions" form 999999999999 col rcflushes head "Cache|Flushes" form 999999999999 col kqrstcln head "Child#" form 999 col lagets head "Latch|Gets" form 999999999999 col lamisses head "Latch|Misses" form 999999999999 col laimge head "Latch|Immediate|gets" form 999999999999 select dc.kqrstcid CACHE#,dc.kqrsttxt PARAMETER,decode(dc.kqrsttyp,1,'PARENT','SUBORDINATE') type,2,kqrstsno,null) subordinate#,dc.kqrstgrq rcgets,dc.kqrstgmi rcmisses,dc.kqrstmrq rcmodifications,dc.kqrstmfl rcflushes,dc.kqrstcln,la.gets lagets,la.misses lamisses,la.immediate_gets laimge from x$kqrst dc,v$latch_children la where dc.inst_id = userenv('instance') and la.child# = dc.kqrstcln and la.name = 'row cache objects' order by rcgets desc / 

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

GD系统很慢，数据库负载是平常6倍，排行第一的等待事件是latch: row cache objects
Event	Waits	Total Wait Time (sec)	Wait Avg(ms)	% DB time	Wait Class
Cache	Get Requests	Pct Miss	Scan Reqs	Mod Reqs	Final Usage