1、業務現象：業務高峰期時，資料庫處理能力陡降，逾時嚴重。截圖如下：
查詢active_session_history 發現均是平時的業務語句占用：
select t.sql_id,s.SQL_TEXT, count(*)
from v$active_session_history t,v$sqlarea s
where t.SAMPLE_TIME >
to_timestamp('20190306 15:00:00', 'YYYYMMDD hh24:mi:ss')
and
t.SAMPLE_TIME <
to_timestamp('20190306 15:01:00', 'YYYYMMDD hh24:mi:ss')
-- and t.SESSION_TYPE='FOREGROUND'
and t.SQL_ID=s.SQL_ID
group by t.SQL_ID,s.SQL_TEXT
order by count(*) desc
2、檢視等待時間及sql 如下：
select t.EVENT,t.WAIT_CLASS,t.SQL_ID,t.p1, count(*)
from v$active_session_history t
where t.SAMPLE_TIME >
to_timestamp('20190306 15:00:00', 'YYYYMMDD hh24:mi:ss')
and
t.SAMPLE_TIME <
to_timestamp('20190306 15:01:00', 'YYYYMMDD hh24:mi:ss')
group by t.EVENT,t.WAIT_CLASS,t.SQL_ID,t.p1
order by count(*) desc
發現有很多latch，且latch 号為33857648584 轉換為16進制為7E212B7C8：，檢視latch種類：
select * from v$latch_children where addr like '%7E212B7C8';
select distinct s.kqrstcln latch#,r.cache#,r.parameter name,r.type,r.subordinate# from v$rowcache r,x$kqrst s where r.cache#=s.kqrstcid order by 1,4,5
發現latch child号為8的全是dc_users相關，
檢視sql 的執行計劃 select * from table(dbms_xplan.display_cursor('9cm09btnkgfvc',null,'advanced'));
或
select t.EVENT,t.WAIT_CLASS,t.SQL_ID,t.SQL_PLAN_OPERATION,count(*)
from v$active_session_history t
where t.SAMPLE_TIME >
to_timestamp('20190306 15:00:00', 'YYYYMMDD hh24:mi:ss')
and
t.SAMPLE_TIME <
to_timestamp('20190306 15:01:00', 'YYYYMMDD hh24:mi:ss')
group by t.EVENT,t.WAIT_CLASS,t.SQL_ID,t.SQL_PLAN_OPERATION
order by count(*) desc
查詢dc_user, concurrency,hash_join, latch row cache object 等關鍵詞，發現bug如下，比較符合：
Bug 13902396 – Hash joins cause “row cache objects” latch gets and “shared pool” latch gets (disabled fix) (文檔 ID 13902396.8)
Slow Performance with High Waits for ‘row cache lock’ With Possible Database Hang (文檔 ID 2189126.1)
解決辦法：
在11.2.0.4的版本上，打更新檔13902396（這個更新檔沒包括在任何PSU内），然後設定如下Event：
event=’45053 trace name context forever, level 127′