关于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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/12020513/viewspace-620668/
扫一扫
639
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
