oracle實驗記錄 (buffer_cache分析（2）cbc latch)
關於cache buffers chain latch (cbc latch)
block讀入sga buffer cache中時，對應的buffer header掛在hash bucket上的hash chain上，cache buffer chains latch 可以控制多個hash bucket，確保hash chain的完整性
，當在hash cahins中 新增刪除讀取資料時 程式需要獲得cbc latch（9I開始 讀的話cbc latch可以共享）
datablock放入哪個bucket 演算法為 mod(dba,_db_block_hash_buckets)
SQL> select count(*) from v$latch_children where name='cache buffers chains';
COUNT(*)
----------
1024
SQL> select count(distinct hladdr ) from x$bh; （hladdr= hash latch address）
COUNT(DISTINCTHLADDR)
---------------------
1024
補充下邏輯讀
process先在運用hash演算法 得到塊所在的hash bucket 然後該bucket中找塊所在的cache buffer chain.找到後 在這個 chain上加一個cache buffer chains latch,latch加上之後在這個chain中需要 所要塊的buffer header,通過buffer header中block資訊 找到該block PIN住，釋放cache buffer chains latch,然後可以訪問該塊,server process不會一次將所有行提取出來,是按照命令抓取,每次讀一定數量的行(比如sqlplus 中set arraysize X),這些行取出之後,會經由PGA傳給user.行一旦從buffer cache中取出,釋放塊上pin後 邏輯讀結束
每個邏輯讀需要一個latch get 操作 和一個cpu 操作,latch get的目標獲得latch，任意一個時刻 只有一個process可以 擁有cache buffer chain latch，
process每申請一次Cache buffer chains閂，就是一次邏輯讀
SQL> create table t1 (a int);
表已建立。
SQL> insert into t1 values(1);
已建立 1 行。
SQL> insert into t1 values(2);
已建立 1 行。
SQL> insert into t1 values(3);
已建立 1 行。
SQL> insert into t1 values(4);
已建立 1 行。
SQL> commit;
SQL> set autotrace trace stat
SQL> select * from t1;
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
463 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
SQL> set arraysize 2
SQL> select * from t1;
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets~~~~~~~~~~~~~~~~~~~~~多了一次邏輯讀 一共4行 sqlplus限制每次輸出2行 多獲取一次 cache buffers chain latch 多了一次邏輯讀
0 physical reads
0 redo size
593 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
備註：consisten gets 演算法公式
consistent reads計算=ceil(獲取行數(card)/arraysize)+used blocks(FTS的話就是HWM下BLOCK)+1
分析:ceil(num_rows/arraysize) 例如取100行 每次顯示到 螢幕10行 需要取10次,oracle 訪問buffer cache 中相應的 hash chain 搜尋需要的buffer時需要 持有 cache buffers chains latch取完資料後釋放,再取時再獲取,這樣需要獲取10次才夠顯示完100行, cache buffers chains latch每獲取一次就是一次邏輯讀 (對於select來說就是).
+1 是多加一次segment header block scan
cache buffer chains latch主要爭用原因
1.低效sql，併發會話執行語句 設法獲得相同資料集，少的邏輯讀 意味少的latch get操作
2.hot block
多個會話重複訪問一個或多個子cache buffer chain latch時熱塊就產生了
通過等待時間檢查是否有熱塊
SQL> create table t1 (a int, b int);
表已建立。
SQL> ed
已寫入 file afiedt.buf
1 declare
2 begin
3 for i in 1..10000 loop
4 insert into t1 values(i,i+1);
5 end loop;
6 commit;
7* end;
8 /
PL/SQL 過程已成功完成。
SQL> select distinct file#,block# from (select dbms_rowid.rowid_relative_fno(ro
wid) file#,dbms_rowid.rowid_block_number(rowid) block# from t1);
FILE# BLOCK#
---------- ----------
1 61635
1 61645
1 61646
1 61647
1 61634
1 61649
1 61638
1 61648
1 61640
1 61651
1 61643
FILE# BLOCK#
---------- ----------
1 61650
1 61637
1 61639
1 61641
1 61642
1 61636
1 61644
已選擇18行。
SQL> select object_id,data_object_id from user_objects where object_name='T1';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
51706 51706
已寫入 file afiedt.buf
1 declare
2 begin
3 for i in 1..100000 loop
4 update t1 set b=i+2 where a=i;
5 commit;
6 end loop;
7* end;
SQL> /
PL/SQL 過程已成功完成。
SQL> declare
2 begin
3 for i in 1..100000 loop
4 update t1 set b=i+2 where a=i;
5 commit;
6 end loop;
7 end;
8 /
PL/SQL 過程已成功完成。
～～～～～～～～～～～～～～～～～～～多個會話併發訪問修改
SQL> select s.event,sid ,s.p1raw,s.p2,s.p3 ,s.seconds_in_wait,s.wait_time,s.stat
e from v$session_wait s where s.event='latch free';
EVENT SID
---------------------------------------------------------------- ----------
P1RAW P2 P3 SECONDS_IN_WAIT WAIT_TIME STATE
-------- ---------- ---------- --------------- ---------- -------------------
latch free 142
03C49AA0 177 0 0 -1 WAITED SHORT TIME
latch free是一個彙總
製造了latch free 事件 由於實驗環境 製造的事件持有latch時間太短 沒有抓到物件
而且 試驗中 latch address不是 cache buffer chains latch
SQL> select name from v$latch where addr='03C49AA0';
NAME
--------------------------------------------------
dml lock allocation
1 SELECT a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name
2 FROM x$bh a, dba_objects b
3 WHERE (a.obj = b.object_id OR a.obj = b.data_object_id)
4* AND a.hladdr = '03C49AA0'
SQL> /
未選定行
SQL> col event format a30
SQL> select event,p1,p1text from v$session_wait_history where event like '%cache
buffer%';
EVENT P1 P1TEXT
------------------------------ ---------- ---------------
latch: cache buffers chains 538535040 address
latch: cache buffers chains 539117440 address~~~~~~~~~~~~~~~~~~這個address型別是number v$latch中衛raw
latch: cache buffers chains 538807680 address
latch: cache buffers chains 538999040 address
latch: cache buffers chains 539117440 address
latch: cache buffers chains 538807680 address
latch: cache buffers chains 538712320 address
latch: cache buffers chains 538498560 address
latch: cache buffers chains 538962560 address
已選擇9行。
SQL> select event,to_char(p1,'xxxxxxxxxxx'),p1text from v$session_wait_history w
here event like '%cache buffer%';
EVENT TO_CHAR(P1,' P1TEXT
------------------------------ ------------ ---------------
latch: cache buffers chains 20196480 address～～～～～～～～～～～～～～～轉換下 型別
latch: cache buffers chains 20224780 address
latch: cache buffers chains 201d8d80 address
latch: cache buffers chains 20207900 address
latch: cache buffers chains 201c1900 address
1 SELECT a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name
2 FROM x$bh a, dba_objects b
3 WHERE (a.obj = b.object_id OR a.obj = b.data_object_id)
4* AND a.hladdr = '03C49AA0'
SQL> /
HLADDR FILE# DBABLK TCH OBJ OBJECT_NAM
-------- ---------- ---------- ---------- ---------- ----------
.............................................................
-------- ---------- ---------- ---------- ---------- ----------
$
20196480 1 53192 1 181 ATTRIBUTE$
20196480 1 53192 1 181 METHOD$
20196480 1 53192 1 181 PARAMETER$
20196480 1 53192 1 181 RESULT$
已選擇35行。 ~~~~~~~~~~~~~~~~~~~通過歷史資訊 找到了一個還在佔有該latch的 修改的物件（可以看到 是一些系統表）
*************************************************
關於cache buffer chains latchs 爭用 與熱塊 (cache buffer chains latchs 是造成latch free 事件的 原因之一)
第1種方式 通過等待事件 (latch free 是個彙總事件)來檢視是否是熱塊 具體過程是
1.
SQL> select s.event,sid ,s.p1raw,s.p2,s.p3 ,s.seconds_in_wait,s.wait_time,s.stat
e from v$session_wait s where s.event='latch free';
找到等待事件，p1raw代表 latch地址（v$latch_children中address），p2 latch#,p3嘗試次數
如果有大量的latch free 且 p1raw相等（表示大家在爭用這個 latch），那麼可能是熱塊（可以通過v$latch_children 結合P1raw看 是不是cache buffer chains latch select name from v$latch_children where addr='XXXXXXX'）
2.用p1raw連線 x$bh中hladdr 連線dba_objectS找到具體物件，TCH 高一般就是hot block,block在lru端從冷端到熱端時候tch重置為0所以tch 0不代表一定是冷塊
1 SELECT a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name
2 FROM x$bh a, dba_objects b
3 WHERE (a.obj = b.object_id OR a.obj = b.data_object_id)
4* AND a.hladdr = 'XXXXX' (hladdr=hash latch address)
解決：讓塊中放的行數少些，通常是修改應用
第2種方式 通過top 方式 來查詢爭用等待最多的cache buffers chains latch 和top 熱塊（按tch =touch方式）
1.從v$latch_children中找 top10(order by sleeps 相當於按等待次數排序)的 子latch資訊 top 10,但這top 10爭用的cache buffers chains latch並不一定是熱塊
SQL> select * from (select addr,child#,gets,misses,sleeps ,immediate_gets,immedi
ate_misses ,spin_gets from v$latch_children where name='cache buffers chains' or
der by sleeps desc ) where rownum<11;
ADDR CHILD# GETS MISSES SLEEPS IMMEDIATE_GETS
-------- ---------- ---------- ---------- ---------- --------------
IMMEDIATE_MISSES SPIN_GETS
---------------- ----------
20210780 863 14406851 27589 136 37
0 27456
201D3600 472 4830021 6128 124 211
7 6009
201BC180 323 4830264 4440 120 313
4 4326
ADDR CHILD# GETS MISSES SLEEPS IMMEDIATE_GETS
-------- ---------- ---------- ---------- ---------- --------------
IMMEDIATE_MISSES SPIN_GETS
---------------- ----------
20216180 899 4829460 3730 111 375
6 3624
20207900 806 4834203 5280 101 365
0 5184
2021F000 956 4830676 4656 100 349
2 4558
ADDR CHILD# GETS MISSES SLEEPS IMMEDIATE_GETS
-------- ---------- ---------- ---------- ---------- --------------
IMMEDIATE_MISSES SPIN_GETS
---------------- ----------
20196480 81 4829726 5823 98 203
2 5728
2019BC00 116 4830893 3463 95 322
4 3371
201CA780 415 4832435 4373 94 378
2 4281
ADDR CHILD# GETS MISSES SLEEPS IMMEDIATE_GETS
-------- ---------- ---------- ---------- ---------- --------------
IMMEDIATE_MISSES SPIN_GETS
---------------- ----------
201D8D80 507 4830339 4335 91 372
1 4247
已選擇10行。
SQL> select addr,ts#,file#,dbarfil,dbablk,tch ,hladdr from x$bh where hladdr='2
210780';
ADDR TS# FILE# DBARFIL DBABLK TCH HLADDR
-------- ---------- ---------- ---------- ---------- ---------- --------
089AC730 2 3 3 6315 1 20210780
089AC674 2 3 3 6315 1 20210780
089AC5B8 2 3 3 6315 1 20210780
089AC4FC 2 3 3 6315 1 20210780
089AC730 0 1 1 56596 8 20210780
089AC730 0 1 1 17321 1 20210780
089AC674 0 1 1 17321 1 20210780
089AC5B8 0 1 1 17321 1 20210780
089AC4FC 0 1 1 17321 1 20210780
089AC730 2 3 3 19795 1 20210780
089AC674 2 3 3 19795 1 20210780
ADDR TS# FILE# DBARFIL DBABLK TCH HLADDR
-------- ---------- ---------- ---------- ---------- ---------- --------
089AC5B8 2 3 3 19795 1 20210780
089AC4FC 2 3 3 19795 1 20210780
089AC730 0 1 1 52491 1 20210780
089AC730 2 3 3 2443 1 20210780
089AC674 2 3 3 2443 1 20210780
089AC5B8 2 3 3 2443 1 20210780
089AC4FC 2 3 3 2443 1 20210780
089AC730 2 3 3 33275 0 20210780
089AC674 0 1 1 9111 0 20210780
089AC730 0 1 1 61633 1 20210780
已選擇21行。
可以看到 top 10 中 sleeps第一的cache buffer chain latch 管理的block中（cbc latch對應多個bucket所以管理多個塊是正常的） 並沒有熱塊tch都很低(但 block在lru端從冷端到熱端時候tch重置為0所以tch 0不代表一定是冷塊)，造成cache buffers chains latche爭用2個主要原因就是 熱塊和chains太長
2.檢視系統中10大熱塊(從熱塊角度出發)
SQL> select * from (select addr,ts#,file#,dbarfil,dbablk,tch ,hladdr from x$bh o
rder by tch desc) where rownum<11;
ADDR TS# FILE# DBARFIL DBABLK TCH HLADDR
-------- ---------- ---------- ---------- ---------- ---------- --------
089AC5A0 0 1 1 1658 274 201F7F00
089AC5A0 0 1 1 1657 273 20195080
089AC394 2 3 3 2775 269 2018E000
089AC5A0 2 3 3 2772 269 201A5700
089AC394 2 3 3 2776 269 201F0E80
089AC394 2 3 3 2774 269 201CB180
089AC5A0 2 3 3 32272 269 20223880
089AC394 2 3 3 2773 269 20208580
089AC5A0 2 3 3 32271 269 201C0A00
089AC5A0 0 1 1 1674 265 201E6200
已選擇10行。
3. 連線x$bh與v$latch_chindren資訊
select b.addr,a.ts#,a.dbarfil,a.dbablk,a.tch ,b.gets,b.misses,b.sleeps fro
m (select * from (select addr,ts#,file#,dbarfil,dbablk,tch,hladdr from x$bh orde
r by tch desc ) where rownum<11) a,(select addr,gets,misses,sleeps from v$latch_
children where name ='cache buffers chains') b where a.hladdr=b.addr
SQL> /
ADDR TS# DBARFIL DBABLK TCH GETS MISSES
-------- ---------- ---------- ---------- ---------- ---------- ----------
SLEEPS
----------
20223880 2 3 32272 2505 25908 0
0
20208580 2 3 2773 2502 23968 0
0
201F7F00 0 1 1658 2554 15562 0
0
ADDR TS# DBARFIL DBABLK TCH GETS MISSES
-------- ---------- ---------- ---------- ---------- ---------- ----------
SLEEPS
----------
201F0E80 2 3 2776 2502 20830 0
0
201E6200 0 1 1674 2484 14678 0
0
201CB180 2 3 2774 2502 20818 0
0
ADDR TS# DBARFIL DBABLK TCH GETS MISSES
-------- ---------- ---------- ---------- ---------- ---------- ----------
SLEEPS
----------
201C0A00 2 3 32271 2502 29558 0
0
201A5700 2 3 2772 2505 15735 0
0
20195080 0 1 1657 2521 18961 0
0
ADDR TS# DBARFIL DBABLK TCH GETS MISSES
-------- ---------- ---------- ---------- ---------- ---------- ----------
SLEEPS
----------
2018E000 2 3 2775 2502 20768 0
0
已選擇10行。
分析將 系統中top 前10的熱塊與 cache buffer chains latch 聯絡起來
通過這些塊 找到 物件資訊 連結dba_extents
SQL> col segment_name format a30
1* select distinct e.owner,e.segment_name,e.segment_type from dba_extents e,(s
elect b.addr,a.ts#,a.dbarfil,a.dbablk,a.tch ,b.gets,b.misses,b.sleeps from (sel
ect * from (select addr,ts#,file#,dbarfil,dbablk,tch,hladdr from x$bh order by t
ch desc ) where rownum<11) a,(select addr,gets,misses,sleeps from v$latch_childr
en where name ='cache buffers chains') b where a.hladdr=b.addr) f where e.relat
ive_fno=f.dbarfil and e.block_id<=f.dbablk and e.block_id +e.blocks>f.dbablk
SQL> /
OWNER SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------------------ -----------------
SYS JOB$ TABLE
SYS I_JOB_NEXT INDEX
SYS SYS_IOT_TOP_8802 INDEX
與 直接連線top 10 tch 的結果一樣
1 SELECT distinct e.owner, e.segment_name, e.segment_type
2 FROM dba_extents e,
3 (SELECT *
4 FROM (SELECT addr, ts#, file#, dbarfil, dbablk, tch
5 FROM x$bh
6 ORDER BY tch DESC)
7 WHERE ROWNUM < 11) b
8 WHERE e.relative_fno = b.dbarfil
9 AND e.block_id <= b.dbablk
10* AND e.block_id + e.blocks > b.dbablk
SQL> /
OWNER SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------------------ ------------------
SYS JOB$ TABLE
SYS I_JOB_NEXT INDEX
SYS SYS_IOT_TOP_8802 INDEX
最後抓SQL 加rule 這個hint的原因 ：下面連線表比較多 避免 cbo下oracle自己嘗試分析 要採用那種連線（產生執行計劃 解析時間將很長）按rbo順序模式連線
1* select /*+ rule*/ hash_value,sql_text from v$sqLAREA where (hash_value,addr
ess)in (select g.hash_value,g.address from v$sqlarea g ,(select distinct e.owner
,e.segment_name,e.segment_type from dba_extents e,(select b.addr,a.ts#,a.dbarfil
,a.dbablk,a.tch ,b.gets,b.misses,b.sleeps from (select * from (select addr,ts#,
file#,dbarfil,dbablk,tch,hladdr from x$bh order by tch desc ) where rownum<11) a
,(select addr,gets,misses,sleeps from v$latch_children where name ='cache buffer
s chains') b where a.hladdr=b.addr) f where e.relative_fno=f.dbarfil and e.bloc
k_id<=f.dbablk and e.block_id+e.blocks>f.dbablk)j where upper(g.sql_text) like '
%'||j.segment_name||'%' and j.segment_type='TABLE') ORDER BY HASH_VALUE,ADDRESS
SQL> /
HASH_VALUE
----------
SQL_TEXT
--------------------------------------------------------------------------------
85383888
select t.inst_id,t.addr,t.kslltnum,t.kslltcnm,n.kslldlvl, n.kslldnam,n.ks
lldhsh, t.kslltwgt,t.kslltwff,t.kslltwsl,t.kslltngt,t.kslltnfa, t.
kslltwkc,t.kslltwth,t.ksllthst0,t.ksllthst1, t.ksllthst2,t.ksllthst3,t.ks
llthst4,t.ksllthst5, t.ksllthst6,t.ksllthst7,t.ksllthst8, t.ksllth
st9,t.ksllthst10, t.ksllthst11, t.kslltwtt from x$ksllt t, x$kslld n wh
ere t.kslltcnm > 0 and t.kslltnum = n.indx
138190469
HASH_VALUE
----------
SQL_TEXT
--------------------------------------------------------------------------------
SELECT snap_id , SQL_ID FROM (SELECT /*+ ordered use_nl(t2) index(t2) */ t2.sn
ap_id , t1.SQLID_KEWRSIE SQL_ID FROM X$KEWRSQLIDTAB t1, WRH$_SQLTEXT t2 WH
ERE t2.dbid(+) = :dbid AND t2.SQL_ID(+) = t1.SQLID_KEWRSIE) WHERE nvl(snap_id,
0) < :snap_id
375665851
SELECT startsn.stat_id, x.keh_id, GREATEST( 0, (endsn.value - startsn.val
ue) ) as value_diff FROM (SELECT t1.* FROM WRH$_SYS_TIME_MODEL t1, WRM$_SNAPSHO
T s1 WHERE t1.dbid = s1.dbid AND t1.instance_number = s1.instance_number
HASH_VALUE
----------
SQL_TEXT
--------------------------------------------------------------------------------
AND t1.snap_id = s1.snap_id AND s1.bl_moved = 0 UNION ALL SELECT t2.* FROM
WRH$_SYS_TIME_MODEL_BL t2, WRM$_SNAPSHOT s2 WHERE t2.dbid = s2.dbid AND t2.in
stance_number = s2.instance_number AND t2.snap_id = s2.snap_id AND s2.bl_mo
ved <> 0) startsn , (SELECT t1.* FROM WRH$_SYS_TIME_MODEL t1, WRM$_SNAPSHOT s1
WHERE t1.dbid = s1.dbid AND t1.instance_number = s1.instance_number AND
t1.snap_id = s1.snap_id AND s1.bl_moved = 0 UNION ALL SELECT t2.* FROM WRH$_
SYS_TIME_MODEL_BL t2, WRM$_SNAPSHOT s2 WHERE t2.dbid = s2.dbid AND t2.instanc
e_number = s2.instance_number AND t2.snap_id = s2.snap_id AND s2.bl_moved <
> 0) endsn , X$KEHTIMMAP x WHERE startsn.snap_id = :begin_snap_id an
HASH_VALUE
----------
SQL_TEXT
--------------------------------------------------------------------------------
d endsn.snap_id = :end_snap_i
652170634
SELECT KEY_PART1, KEY_PART2, KEY_PART3, DECODE ( :B4 , KEY_PART1, 1, KEY_PART2,
2, KEY_PART3, 3, 0 ), DECODE ( :B3 , KEY_PART1, 1, KEY_PART2, 2, KEY_PART3, 3, 0
) FROM MGMT_BCN_AVAIL_DEF WHERE TARGET_GUID = :B2 AND METRIC_GUID = :B1
819358145
SELECT snap_id , SQL_ID FROM (SELECT /*+ ordered use_nl(t2) index(t2) */ t2.sn
HASH_VALUE
----------
SQL_TEXT
--------------------------------------------------------------------------------
ap_id , t1.SQLID_KEWRSIE SQL_ID FROM X$KEWRSQLIDTAB t1, WRH$_SQL_BIND_METADATA
t2 WHERE t2.dbid(+) = :dbid AND t2.SQL_ID(+) = t1.SQLID_KEWRSIE AND t2.PO
SITION(+) = 1) WHERE nvl(snap_id, 0) < :snap_id
979299602
select t1.inst_id,t1.ksllasnam, t2.ksllwnam, t1.kslnowtf, t1.kslsleep, t1.kslwsc
wsl, t1.kslwsclthg, t2.ksllwnam from x$ksllw t2, x$kslwsc t1 wh
ere t2.indx = t1.indx
HASH_VALUE
----------
SQL_TEXT
--------------------------------------------------------------------------------
1488414063
insert into wrh$_service_name (snap_id, dbid, service_name_hash, service_name)
select :lah_snap_id, :dbid, t2.name_hash, t2.name from x$kewrattrnew t1, v$
services t2 where t1.num1_kewrattr = t2.name_hash
1494863970
SELECT T2.TARGET_GUID FROM MGMT_TARGETS T1, MGMT_TARGETS T2 WHERE T1.TARGET_GUID
=:B2 AND T1.EMD_URL=T2.EMD_URL AND T2.TARGET_TYPE=:B1
HASH_VALUE
----------
SQL_TEXT
--------------------------------------------------------------------------------
2146304988
SELECT snap_id , SERVICE_NAME_HASH FROM (SELECT /*+ ordered use_nl(t2) index(t
2) */ t2.snap_id , t1.NAME_HASH SERVICE_NAME_HASH FROM V$SERVICES t1, WRH$_SERV
ICE_NAME t2 WHERE t2.dbid(+) = :dbid AND t2.SERVICE_NAME_HASH(+) = t1.NAM
E_HASH) WHERE nvl(snap_id, 0) < :snap_id
2298265060
declare begin for i in 1..100000 loop update t1 set b=i+3 where a=i; commit; end
loop; end;
HASH_VALUE
----------
SQL_TEXT
--------------------------------------------------------------------------------
3327029265
SELECT snap_id , SQL_ID, PLAN_HASH_VALUE FROM (SELECT /*+ ordered use_nl(t2) i
ndex(t2) */ t2.snap_id , t1.SQLID_KEWRSPE SQL_ID, t1.PLANHASH_KEWRSPE PLAN_HAS
H_VALUE FROM X$KEWRTSQLPLAN t1, WRH$_SQL_PLAN t2 WHERE t2.dbid(+) = :dbid
AND t2.SQL_ID(+) = t1.SQLID_KEWRSPE AND t2.PLAN_HASH_VALUE(+) = t1.PLANHASH_KEW
RSPE AND t2.ID(+) = 0) WHERE nvl(snap_id, 0) < :snap_id
3407318689
HASH_VALUE
----------
SQL_TEXT
--------------------------------------------------------------------------------
SELECT snap_id , OPTIMIZER_ENV_HASH_VALUE FROM (SELECT /*+ ordered use_nl(t2)
index(t2) */ t2.snap_id , t1.OPTENVHV_KEWROEE OPTIMIZER_ENV_HASH_VALUE FROM X$K
EWRTOPTENV t1, WRH$_OPTIMIZER_ENV t2 WHERE t2.dbid(+) = :dbid AND t2.OPTI
MIZER_ENV_HASH_VALUE(+) = t1.OPTENVHV_KEWROEE) WHERE nvl(snap_id, 0) < :snap_id
3554734773
select inst_id, kglnaobj, kglfnobj, kglobt03, kglobhs0+kglobhs1+kglobhs2+kglobhs
3+kglobhs4+kglobhs5+kglobhs6, kglobt08+kglobt11, kglobt10, kglobt01, kglobccc, k
globclc, kglhdlmd, kglhdlkc, kglobt04, kglobt05, kglobt48, kglobt35, kglobpc6, k
HASH_VALUE
----------
SQL_TEXT
--------------------------------------------------------------------------------
glhdldc, substr(to_char(kglnatim,'YYYY-MM-DD/HH24:MI:SS'),1,19), kglhdivc, kglob
t12, kglobt13, kglobwdw, kglobt14, kglobwap, kglobwcc, kglobwcl, kglobwui, kglob
t42, kglobt43, kglobt15, kglobt02, decode(kglobt32, 0, 'NONE',
1, 'ALL_ROWS', 2, 'FIRST_ROWS', 3, 'RULE',
4, 'CHOOSE', 'UNKNOWN'), kglobtn0, kglobcce, kglobcceh, kglobt17,
kglobt18, kglobts4, kglhdkmk, kglhdpar, kglnahsh, kglobt46, kglobt30, kglobts0,
kglobt19, kglobts1, kglobt20, kglobt21, kglobts2, kglobt06, kglobt07, decode(kgl
obt28, 0, NULL, kglobt28), kglhdadr, decode(bitand(kglobt00,64),64, 'Y', 'N'), d
ecode(kglobsta, 1, 'VALID', 2, 'VALID_AUTH_ERROR', 3, 'VALI
HASH_VALUE
----------
SQL_TEXT
--------------------------------------------------------------------------------
D_COMPILE_ERROR', 4, 'VALID_UNAUT
4058483781
select d.inst_id,d.kslldadr,la.latch#,d.kslldlvl,d.kslldnam,d.kslldhsh, l
a.gets,la.misses, la.sleeps,la.immediate_gets,la.immediate_misses,la.wait
ers_woken, la.waits_holding_latch,la.spin_gets,la.sleep1,la.sleep2,
la.sleep3,la.sleep4,la.sleep5,la.sleep6,la.sleep7,la.sleep8,la.sleep9,
la.sleep10, la.sleep11, la.wait_time from x$kslld d, (select kslltnum latch#
, sum(kslltwgt) gets,sum(kslltwff) misses,sum(kslltwsl) sleeps, su
HASH_VALUE
----------
SQL_TEXT
--------------------------------------------------------------------------------
m(kslltngt) immediate_gets,sum(kslltnfa) immediate_misses, sum(kslltwkc)
waiters_woken,sum(kslltwth) waits_holding_latch, sum(ksllthst0) spin_gets
,sum(ksllthst1) sleep1,sum(ksllthst2) sleep2, sum(ksllthst3) sleep3,sum(k
sllthst4) sleep4,sum(ksllthst5) sleep5, sum(ksllthst6) sleep6,sum(ksllths
t7) sleep7,sum(ksllthst8) sleep8, sum(ksllthst9) sleep9,sum(ksllthst10) s
leep10,sum(ksllthst11) sleep11, sum(kslltwtt) wait_time from x$ksllt g
roup by kslltnum) la where la.latch# =
已選擇14行。
抓到sql後 就是優化了
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-620668/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle實驗記錄(buffer_cache分析(3)cbc lru chain latch)OracleAI
- oracle實驗記錄 (buffer_cache分析（1）)Oracle
- oracle實驗記錄(buffer_cache分析(4)dbwr,lgwr,ckpt)Oracle
- oracle實驗記錄 （oracle 分析shared pool(2)）Oracle
- oracle實驗記錄 (oracle 詳細分析redo（2）)Oracle
- oracle實驗記錄 (分析oracle硬解析&軟解析&fast soft parse(2))OracleAST
- oracle實驗記錄 （dump undo2）Oracle
- oracle實驗記錄Rman duplicate database 2OracleDatabase
- oracle實驗記錄 (oracle 分析shared pool(1))Oracle
- oracle實驗記錄 (oracle 詳細分析redo（1）)Oracle
- oracle實驗記錄 (oracle 詳細分析redo（3）)Oracle
- oracle實驗記錄 (oracle 詳細分析redo（4）)Oracle
- oracle實驗記錄 (oracle 詳細分析redo（5）)Oracle
- cbc latch或cache buffer chains latch系列一AI
- oracle實驗記錄 (oracle 10G 詳細分析undo)Oracle
- oracle實驗記錄 （flashback）Oracle
- oracle實驗記錄 （OMF）Oracle
- oracle實驗記錄 （NET）Oracle
- oracle實驗記錄 （audit）Oracle
- oracle實驗記錄 （cursor_sharing(2)SIMILAR）OracleMILA
- oracle實驗記錄（logfile基礎操作2）Oracle
- oracle實驗記錄 （storage儲存引數(2)）Oracle
- oracle實驗記錄（手動dupliacate database(2)）OracleDatabase
- oracle實驗記錄 （恢復-rman維護(2)）Oracle
- oracle實驗記錄 (oracle reset parameter)Oracle
- oracle實驗記錄 （分析oracle硬解析&軟解析&fast soft parse）OracleAST
- oracle實驗記錄 （恢復read only tablespace(2)）Oracle
- oracle實驗記錄 (oracle 10G dataguard(2)引數部分)Oracle
- Oracle Data Redaction實驗記錄Oracle
- oracle實驗記錄 （block cleanout）OracleBloC
- oracle實驗記錄 （dump undo）Oracle
- oracle實驗記錄 （inlist card）Oracle
- oracle實驗記錄 (oracle 資料字典)Oracle
- oracle實驗記錄 （恢復-關於控制檔案(2)）Oracle
- Oracle檔案改名實驗記錄Oracle
- oracle實驗記錄 （選擇率）Oracle
- oracle實驗記錄 （dump logfile）Oracle
- oracle實驗記錄 （事務控制）Oracle