Oracle latch: row cache objects系统整体慢

       GD系统很慢，数据库负载是平常6倍，排行第一的等待事件是latch: row cache objects

Event	Waits	Total Wait Time (sec)	Wait Avg(ms)	% DB time	Wait Class
latch: row cache objects	14,465,199	509.1K	35	42.5	Concurrency
DB CPU		158.2K		13.2
db file sequential read	6,253,819	57.4K	9	4.8	User I/O
SQL*Net message from dblink	20,642	26.7K	1292	2.2	Network

      由于不能连现场环境的，从数据库报告里面可以看到很多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

Cache	Get Requests	Pct Miss	Scan Reqs	Pct Miss	Mod Reqs	Final Usage
dc_awr_control	25	8.00	0		0	1
dc_global_oids	44	0.00	0		0	310
dc_histogram_data	103,004	0.72	0		34	166,645
dc_histogram_defs	36,631	3.36	0		22	84,754
dc_objects	50,867	0.27	0		9	18,270
dc_partition_scns	19	0.00	0		0	60
dc_profiles	652	0.00	0		0	1
dc_rollback_segments	85,110	0.00	0		0	4,118
dc_segments	11,520	0.97	0		3	13,314
dc_sequences	529	34.03	0		529	47
dc_table_scns	19	0.00	0		0	6
dc_tablespaces	2,076,498	0.00	0		0	20
dc_users	2,092,954	0.00	0		0	108
global database name	2,015	0.00	0		0	2
outstanding_alerts	30	100.00	0		0	0
sch_lj_oids	2	0.00	0		0	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,
	decode(dc.kqrsttyp, 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
/