CACHE BUFFER CHAINS
出現CACHE BUFFER CHAIN 後首先檢視V$SESSION_WAIT 找到P1raw的值,此P1對應了v$latch_children addr和 x$bh 的HLADDR
然後檢視v$latch_children 找到GET最高的LATCH ADDR
select * from v$latch_children where name='cache buffers chains' order by gets desc;
最後和X$BH連線找到物件
檢視當前等待的,注意本語句只能查詢本例項,RAC需要每個例項都進行。
select hladdr,
obj,
b.sid,
b.sql_id,
(select object_name
from dba_objects
where (data_object_id is null and object_id = x.obj)
or data_object_id = x.obj
and rownum = 1) as object_name,
dbarfil,
dbablk,
tch,
TIM
from x$bh x,
(select P1RAW,sql_id,sid from v$session where wait_class<>'Idle' and event='latch: cache buffers chains') b
where HLADDR in (select P1RAW from v$session where wait_class<>'Idle' and event='latch: cache buffers chains')
and x.hladdr=b.P1RAW(+)
order by tch desc;
檢視系統中最為嚴重的
select hladdr,
obj,
(select object_name
from dba_objects
where (data_object_id is null and object_id = x.obj)
or data_object_id = x.obj
and rownum = 1) as object_name,
dbarfil,
dbablk,
tch,
TIM
from x$bh x
where HLADDR in (select addr
from (select *
from (select *
from v$latch_children
where name = 'cache buffers chains'
order by gets desc)
where rownum < 10))
order by tch desc;
測試:
1、建立測試表
create table cbc_test(id number,name char(100));
2、插入資料
insert into cbc_test(id,name)
select rownum,object_name from dba_objects;
insert into cbc_test
select * from cbc_test;
多執行幾次
SQL> select count(*) from cbc_test;
COUNT(*)
----------
401336
3、建立索引
create index cbc_test_idx on cbc_test(id);
4、建立儲存過程進行大量的掃描
create or replace procedure cbc_do_select is
begin
for x in (select /*+ INDEX(cbc_test cbc_test_idx) */ * from cbc_test where id >=0) loop
null;
end loop;
end;
5、模擬足夠多的JOB
var job_no number;
begin
sys.dbms_job.submit(job => :job,
what => 'cbc_do_select;',
next_date => to_date('13-06-2013 10:16:04', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'sysdate+1/2880');
commit;
end;
6、觀察
HLADDR OBJ SID SQL_ID OBJECT_NAME DBARFIL DBABLK TCH TIM
---------------- ---------- ---------- ------------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ----------
000007FF1C0FCBC0 53216 CBC_TEST 1 72322 534 1371092084
000007FF1C7D72E8 53216 CBC_TEST 1 71538 529 1371092084
000007FF1C784708 53216 138 3uakn4vk92jf8 CBC_TEST 1 75337 517 1371092080
000007FF1C0FCBC0 53216 CBC_TEST 1 72089 491 1371092080
000007FF1C7D72E8 53216 CBC_TEST 1 71305 487 1371092080
000007FF1C0FCBC0 53216 CBC_TEST 1 76660 469 1371092080
000007FF1C7D72E8 53216 CBC_TEST 1 75876 466 1371092080
000007FF1CBDB268 53216 CBC_TEST 1 74975 429 1371092082
000007FF1C784708 53216 138 3uakn4vk92jf8 CBC_TEST 1 70999 422 1371092082
000007FF1C784708 53216 138 3uakn4vk92jf8 CBC_TEST 1 75570 421 1371092082
000007FF1C784708 4294967295 138 3uakn4vk92jf8 2 193 26 1371091585
000007FF1C784708 9091 138 3uakn4vk92jf8 WRH$_SGA_TARGET_ADVICE 3 3599 6 1371088807
000007FF1CBDB268 8999 WRH$_SQL_SUMMARY 3 3004 4 1371045615
000007FF1C784708 4226 138 3uakn4vk92jf8 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST 3 34198 4 1371045618
000007FF1C7D72E8 8995 WRH$_SQLTEXT 3 34504 3 1371045618
000007FF1C7D72E8 181 C_TOID_VERSION# 1 58291 3 1371086393
000007FF1C784708 93 138 3uakn4vk92jf8 ACCESS$ 1 27386 3 1371014071
000007FF1C0FCBC0 181 C_TOID_VERSION# 1 50399 3 1371086393
000007FF1C784708 181 138 3uakn4vk92jf8 C_TOID_VERSION# 1 14372 3 1371086393
000007FF1CBDB268 101 I_SYN1 1 763 2 1371045607
HLADDR OBJ SID SQL_ID OBJECT_NAME DBARFIL DBABLK TCH TIM
---------------- ---------- ---------- ------------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ----------
000007FF1CBDB268 53100 WRH$_SERVICE_STAT_PK 3 37941 2 1371045619
000007FF1C784708 53090 138 3uakn4vk92jf8 WRH$_PARAMETER 3 38536 2 1371045620
000007FF1C7D72E8 4294967295 2 499 1 1371089114
000007FF1C0FCBC0 52856 WRH$_SQLSTAT_INDEX 3 35288 1 1371045613
000007FF1C0FCBC0 73 IDL_UB1$ 1 11124 1 1371014072
000007FF1C7D72E8 75 IDL_UB2$ 1 6002 1 1370999208
26 rows selected
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/7728585/viewspace-763765/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle Cache Buffer ChainsOracleAI
- Latch: cache buffer chains (%)AI
- buffer cache實驗6-latch:cache buffers lru chainsAI
- cbc latch或cache buffer chains latch系列一AI
- cache buffers chains and cache buffers lru chainsAI
- 客戶資料庫出現大量cache buffer chains latch資料庫AI
- 【原創】cache buffer chains的一次解決過程AI
- buffer busy waits, latch cache buffers chains, read by other session區別AISession
- latch: cache buffers chainsAI
- cache buffers chains vs cache buffers lru chainAI
- latch:cache buffers chains案例AI
- Buffer Cache 原理
- cache buffer chainAI
- 用於排查cache buffers chainsAI
- Cache Buffers chains,存在共享模式?AI模式
- IO之核心buffer----"buffer cache"
- Buffer Cache Hit Ratio
- Oracle Buffer Cache原理Oracle
- Oracle database buffer cacheOracleDatabase
- Database Buffer Cache (79)Database
- 深入理解latch: cache buffers chainsAI
- Trouble shooting latch: cache buffers chainsAI
- ORACLE等待事件latch: cache buffers chainsOracle事件AI
- 等待事件_cache_buffers_chains_latch事件AI
- page cache與buffer cache的關係
- 模擬cache buffers chains與library cache pin等待事件AI事件
- linux cache and buffer【轉】Linux
- BUFFER CACHE尋找流程
- buffer cache logical structure!Struct
- buffer cache部分原理(LRU)
- Organization of the Database Buffer Cache (80)Database
- Size of the Database Buffer Cache (82)Database
- latch:cache buffers chains解決步驟AI
- latch:cache buffers chains的優化思路AI優化
- oracle buffer cache管理機制_buffer cache dump與lru機制小記Oracle
- 【BUFFER】Oracle buffer cache之 latch 學習記錄Oracle
- buffer與cache的區別
- database buffer cache之我見Database