|
|
1log file switch (private strand flush incomplete)
User sessions trying to generate redo, wait on this event when LGWR waits for DBWR to complete flushing redo from IMU buffers into the log buffer; when DBWR is complete LGWR can then finish writing the current log, and then switch log files.-------------from oracle document。
SQL> select * from v$version;
BANNERITPUB个人空间Ta_l.b;T }v
----------------------------------------------------------------ITPUB个人空间F~w;x[SQ`
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProdITPUB个人空间;w p"BG;g%C
PL/SQL Release 10.2.0.1.0 - ProductionITPUB个人空间O7J ZV8OA'o
CORE 10.2.0.1.0 ProductionITPUB个人空间 [4U#T wg
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
'qJ&M"r[#V13750068NLSRTL Version 10.2.0.1.0 - Production
SQL> desc X$KCRFSTRAND
EM!z*Q/S13750068 名称
] q ]?0M8w;G13750068 --------------------------------------
/u [`m \(|5q's+Y-e13750068 ADDRITPUB个人空间~*G"v P@!w^
INDX
*i1[/IwF ?I Y13750068 INST_IDITPUB个人空间Y'p0T8z)?#m(g0]h
PNEXT_BUF_KCRFA_CLNITPUB个人空间F)}4Rs x e$X S
NEXT_BUF_NUM_KCRFA_CLN
,\ a6Eb*_E)e13750068 BYTES_IN_BUF_KCRFA_CLNITPUB个人空间a^ {A K cjI
AVAILABLE_BUFS_KCRFA_CLN
p |Z5Glk9]*i/[13750068 MEM_BUFS_AVAILABLE_KCRFA_CLNITPUB个人空间h l,E'd|6v(l*e
STRAND_HEADER_BNO_KCRFA_CLN
2h^ sO/f.n:Ba.^13750068 FIRST_REDO_KCRFA_CLN
9A9w P2sa13750068 STRAND_HDR_SCN_KCRFA_CLNITPUB个人空间? ] m.d;khY
LWN_BUF_NUM_KCRFA_CLN
kO+ii Z13750068 HIGHEST_SCN_KCRFA_CLN
{&k7E7Z{n#e:z13750068 HIGHEST_SUBSCN_KCRFA_CLNITPUB个人空间8j(N/s-?$a
PVT_STRAND_STATE_KCRFA_CLN
8?%@6]8y g(b13750068 STRAND_NUM_ORDINAL_KCRFA_CLN
q/o%X5m:A"h9x13750068 PTR_KCRF_PVT_STRANDITPUB个人空间 bw KZ/ez&b%TM Y
INDEX_KCRF_PVT_STRAND
pp[)JC C13750068 SPACE_KCRF_PVT_STRAND
v-V I$Pj1Zo9q7jS:n13750068 TXN_KCRF_PVT_STRANDITPUB个人空间'B9wYCBc.u2?
ACTION_KCRFAITPUB个人空间e D;x:w,SY
LOG_RESIDUE_KCRFA
gl e4N$U g13750068 LAST_BUF_WRITTEN_KCRFA
C z(v{$zj&aJ13750068 LAST_BUF_GATHERED_KCRFAITPUB个人空间@e3P U h([
CONS_LOW_BUFNO_KCRFAITPUB个人空间3Al1YK^,M6h-E
FIRST_BUF_KCRFAITPUB个人空间 }_Yi.U
LAST_BUF_KCRFA
8H?/no[q0a;jN13750068 TOTAL_BUFS_KCRFAITPUB个人空间&@7i?"kl
STRAND_SIZE_KCRFAITPUB个人空间?w K'y B M
LOWBUF_KCRFAITPUB个人空间i'Oz!P$s{.r
HIBUF_KCRFAITPUB个人空间8d'X,@&\,|6@
WRAPHIBUF_KCRFAITPUB个人空间xk](m7Zx{$w%q
WRAPLOWBUF_KCRFAITPUB个人空间1x |/BM%y&xM#J R m
LOG_SEQ_KCRFA
-i!S$Z,G2K9f13750068 LASTCHANGE_KCRFA
2 From 10.1 onwards, the log buffer is broken in to smaller sub-buffers called Private Redo Strands(shown in the x$kcrfstrand). These are also allocated in the shared pool. These all are written by redo allocation latches. There are allocation latches now for each private strand. The data when changed now, doesn’t contend to go into the log buffer as like before but goes into the private redo strands and from there, it goes into the log buffer when the buffer pools become full or when the transaction is committed. We can see the private strands shown in the shared pool from the V$sgastat（Aman Sharma）
SQL> select name,pool from V$sgastatITPUB个人空间 q)Z#iw:k xG
2 where name like '%private%';
NAME POOLITPUB个人空间-qdp+]2I$O6pX5@ N|
-------------------------- ------------ITPUB个人空间 ~7w!b3v;B|*vzx*}
private strands shared poolITPUB个人空间%|J Z @"R2?
KCB private handles shared pool
SQL> select latch#,child#,name from V$latch_childrenITPUB个人空间 v m5j$@ _8H;m]j
2 where name like '%redo%' order by 1,2
hvH$y P%f13750068 3 ;
LATCH# CHILD# NAMEITPUB个人空间 u.o%J8Q U"I Wx9Xs
---------- ---------- ----------------------------------------
j t3?:K o2\?e$V13750068 147 1 redo copyITPUB个人空间T5O nY!t B
147 2 redo copyITPUB个人空间,R[3q$Isi#FO Q
147 3 redo copyITPUB个人空间(_A$J#f2N!e7\uo
147 4 redo copyITPUB个人空间 D7c e5\[L5c(~ Y
147 5 redo copy
,nn4Y"P(Dh13750068 147 6 redo copy
I*Q P,x9Sl@$[13750068 147 7 redo copy
`B!G xH] \ng13750068 147 8 redo copyITPUB个人空间5N0fc})}$l
148 1 redo allocation
)B8c6NB2s6}+H13750068 148 2 redo allocationITPUB个人空间upZ7]-_W
148 3 redo allocationITPUB个人空间NlZ(f*mX9Z8q%P
148 4 redo allocation
$c w,bp1N/k Jy G13750068 148 5 redo allocation
LL&U^ Kr*jNJVD13750068 148 6 redo allocation
1h Kg-W _ X ^T\13750068 148 7 redo allocation
n!_/B"| |1I!k,s!B0?(F13750068 148 8 redo allocation
9~ KZ[ iYJ x13750068 148 9 redo allocationITPUB个人空间5wJFDR Z b
148 10 redo allocation
l*AK{v f Z0w%`13750068 148 11 redo allocation
-@ D [t g4@13750068 148 12 redo allocation
%| ['y2|2a&\13750068 148 13 redo allocationITPUB个人空间!y1W@2d+I
148 14 redo allocationITPUB个人空间$H g(Z,p8l/~t5A x
148 15 redo allocation
BBx'w5V&|3D3u8p*F,U:R13750068 148 16 redo allocation
"Lz9E+J IQ&b#u13750068 148 17 redo allocation
|1Q-em$H!G13750068 148 18 redo allocationITPUB个人空间 Ks u$G-V)E
148 19 redo allocation
;J R'G0Ep)w~ [13750068 148 20 redo allocation
3 IMU Latch：
SQL> select name from V$latch_children where name like '%undo%';
NAMEITPUB个人空间/jQ:T3{8fT$Z
--------------------------------------------------ITPUB个人空间 p p%v'N8^ T6n0d r m
undo global data
I d9H+x&p+rN\2e;L!P13750068undo global dataITPUB个人空间c1BB-B&sz3@e6V
undo global dataITPUB个人空间W7U]3N UA"~xn
undo global dataITPUB个人空间w`%j!S&W7b;~6{]u
undo global dataITPUB个人空间6FOd&|{F~ gP
In memory undo latchITPUB个人空间%O Xp1V K&Y {3J*\'b4Lo
In memory undo latch
v be)S8J f)W%[u13750068In memory undo latchITPUB个人空间+zA2J!fD#zS
In memory undo latchITPUB个人空间[+P4P)cU g8Ht
In memory undo latchITPUB个人空间g8PI vC] W*dT d
In memory undo latchITPUB个人空间!E"`6Pp{;};Q
In memory undo latchITPUB个人空间)MV`6Z$e DX
In memory undo latch
&o}5h0U/fN0F`J13750068In memory undo latch
w8Cf-S s0v13750068In memory undo latchITPUB个人空间(mU(f V+@C
In memory undo latch
a&Od+lJ)i/D13750068In memory undo latchITPUB个人空间1w D;Z;W X X P
In memory undo latch
,o"iy1oS13750068In memory undo latchITPUB个人空间#X%I6GH:t M
In memory undo latchITPUB个人空间%n'e7U/ql+I v'e!F~
In memory undo latchITPUB个人空间,fQ1b3HGH-W
In memory undo latchITPUB个人空间.NNEr:[
In memory undo latch
已选择23行。
SQL> select name, gets, misses, immediate_gets IM, sleepsITPUB个人空间x.s ]!qS-m9IV,O Z3U0G
2 from v$latch_children where name like 'In%undo%';
NAME GETS MISSES IM SLEEPS
Mc1oy'R @/J13750068-------------------------------------------------- ---------- ---------- ---------- ----------
_UMI z13750068In memory undo latch 2563 0 423 0ITPUB个人空间T3lP B.I I8g
In memory undo latch 22 0 11 0
&u:e I1A!N%Y&[$P13750068In memory undo latch 326 0 77 0
'I$SK#C$w)_%V/D13750068In memory undo latch 1836 0 918 0
6Z6LguZ d13750068In memory undo latch 2 0 1 0ITPUB个人空间mT!`e/j'| D7S
In memory undo latch 0 0 0 0ITPUB个人空间T;T^P;G2w(Un8s
In memory undo latch 0 0 0 0
oY8?Vp e(g6` }13750068In memory undo latch 0 0 0 0
:L6d ` rQn X13750068In memory undo latch 0 0 0 0ITPUB个人空间0j.B-| RoU L
In memory undo latch 0 0 0 0
nB M&z+x)a13750068In memory undo latch 0 0 0 0
k&A?n Xi)Z0U2F13750068In memory undo latch 0 0 0 0ITPUB个人空间j:u7h8w7d Z} @
In memory undo latch 0 0 0 0ITPUB个人空间DL;[2dZmR(~{8K
In memory undo latch 0 0 0 0
~ dS NJp6}*Y13750068In memory undo latch 0 0 0 0
IJ7] MU+Ga13750068In memory undo latch 0 0 0 0
~(~}T X,t13750068In memory undo latch 0 0 0 0ITPUB个人空间 g7w&k.[ fZDkz
In memory undo latch 0 0 0 0
已选择18行。
SQL> select KSPFTCTXDVL,kspftctxdf,KSPFTCTXVL,KSPFTCTXIS,ksppinmITPUB个人空间m)q.N t q d0z
2 from x$ksppcv2 a,x$ksppi bITPUB个人空间1Z.Z Q`'^X Sb
3 where a.indx=b.indx and b.ksppinm like '%undo%'ITPUB个人空间Q LCQQ|4Ny` M"C4R
4 ;
9q/k'Cga ^&x\4iX13750068
&T.Q T/G };~]13750068KSPFTCTXDVL KSPFTCTXDF KSPFTCTXVL KSPFTCTXIS KSPPINMITPUB个人空间 DmF0m*`-?n3[
-------------------------------------------------------------------------------- ---------- -------------------------------------------------------------------------------- ---------- --------------------------------------------------------------------------------
E4z s|:XO&l D{13750068 TRUE FALSE _gc_undo_affinity
V0a6j g)T@W#y@a13750068TRUE TRUE TRUE FALSE _kcl_undo_locksITPUB个人空间mph\| FR
TRUE TRUE TRUE FALSE _kcl_undo_grouping
+Blj+{Z0} K)i2H2r2P13750068AUTO TRUE AUTO FALSE _gc_undo_affinity_locks
bF|1e)M13750068TRUE TRUE TRUE FALSE undo_managementITPUB个人空间7@'Q r[sQ
100 TRUE 100 FALSE undo_tablespaceITPUB个人空间g LYx0eT\!@
0 TRUE 0 FALSE _collect_undo_stats
*U8TE"A#s3~ zD ^ i*N13750068 TRUE FALSE _undo_debug_modeITPUB个人空间_ fE"M7zl
0 TRUE 0 FALSE _verify_undo_quotaITPUB个人空间9X/Ik:a!i
FALSE TRUE FALSE FALSE _in_memory_undo
x M+X1{Y137500683 TRUE 3 FALSE _undo_autotune
EJ~ L#Ge hZ13750068TRUE TRUE TRUE FALSE undo_retention
*`{M tVqg-k137500680 TRUE 0 FALSE _undo_debug_usageITPUB个人空间.S-FHb.S4Y.v_
0 TRUE 0 FALSE _optimizer_undo_cost_change
)?*[(y2f~ ^dW/Et v137500680 TRUE 0 FALSE _optimizer_undo_changesITPUB个人空间 L.MhX#_$H
UF`U(V@ rO'Yl$t1375006815 rows selected
x RAN5M&b/A1_M6I2C13750068 ITPUB个人空间 If`Sx:}?m
SQL>
SQL> select pool,name from V$sgastat where pool='shared pool' and lower(name) like '%undo%';
H#n.A#Y&|)B M v13750068
B A3e YNIkGk.r13750068POOL NAME
.i y W l8?!j13750068------------ --------------------------ITPUB个人空间'Y[^6qa@]
shared pool UNDO INFO SEGMENTED ARRAY
[q-g;I i"\13750068shared pool KCB undo bitvec
bm0WdB'E13750068shared pool Undo Meta. Data
$zS"wX$s { K13750068shared pool Auto tune undo infoITPUB个人空间9hsf }:{9Pnu
shared pool UNDO INFOITPUB个人空间I8A H%IpPDy
shared pool KTI-UNDO
a-y9Llh n v3[j13750068shared pool UNDO INFO HASHITPUB个人空间d"Fj!lh3k'Zt%C,z"{'i
shared pool UNDO STAT INFO
H f}$Y2w13750068
b%w&kO.mN x137500688 rows selected
SQL> select name from V$latch where name like '%undo%';ITPUB个人空间 Z Kym2o}$ak l Rf
ITPUB个人空间$l9t;y:Q8oQ ^&wP/N
NAMEITPUB个人空间v.GCy/Cq;t
--------------------------------------------------
4z1k{3U X1X*bX P13750068undo global data
@ ^c-~'] bvXx13750068In memory undo latch
K$j9i G*A sl;S/n13750068
4IMU Lock：
ITPUB个人空间9W+UD] v }5w
SQL> select * from v$lock_type where type='IM';
TYPE NAME ID1_TAG ID2_TAG IS_ DESCRIPTION
t l/e6e}5Vd13750068------------------------------ ------------------------------ -------------------- -------------------- --- ------------------------------
2o-wmOJcSB_K13750068IM Kti blr lock pool # 0 NO Serializes block recovery forITPUB个人空间S^ Rt0J1DL} k&L(s
an IMU txn
SQL> select name from v$statname where name like 'IMU%';
NAMEITPUB个人空间 lt.o&aKYy
------------------------------
fF9gWk*d] vB/Y13750068IMU commits
:wz*r u-l1\H13750068IMU FlushesITPUB个人空间 z}7u`3?8P+I*G HMOj
IMU contention
ie+s'bW N13750068IMU recursive-transaction flus
tpP.N B\-z13750068h
IMU undo retention flushITPUB个人空间l,l5x,\!kh"K9f
IMU ktichg flush
2M1Op@8g]+q13750068IMU bind flushes
`/ed$q ~-xI13750068IMU mbu flushITPUB个人空间@ A2P7JW5X
IMU pool not allocatedITPUB个人空间!t \+ez }
IMU CR rollbacks
[8VS7FS[y)h/K13750068IMU undo allocation size
N` hI&l,p+|13750068IMU Redo allocation sizeITPUB个人空间;[2F'H y u4l ZoF&h,W
IMU- failed to get a private sITPUB个人空间I@4^o-BH
trand
ITPUB个人空间`%uZ.b{M
已选择13行。
5IMU Commits：
SQL> select * from v$sysstat where name like '%commit%';
STATISTIC# NAME CLASS VALUE STAT_IDITPUB个人空间'wb)u7O}4z D`Cr(z
---------- ------------------------------ ---------- ---------- ----------
? mX9C#@[ T"eP"y13750068 4 user commits 1 395 582481098
P?1?P1~/n S;[/}}6a#E13750068 98 commit cleanout failures: writ 8 0 2063165199ITPUB个人空间p q'R a l5gW mX
e disabled
99 commit cleanout failures: bloc 8 0 781179189
;C$o l&^:I4V13750068 k lost
100 commit cleanout failures: cann 8 0 176423721
5FN7`)P+d"h y13750068 ot pin
101 commit cleanout failures: hot 8 0 2500023621
*o8E}Zz S6Ua13750068 backup in progress
102 commit cleanout failures: buff 8 0 1626141486
G(j&gF MnA%oG7y13750068 er being written
103 commit cleanout failures: call 8 5 3086716748ITPUB个人空间(_5D g7e:|*v5H5y0{_
back failure
104 commit cleanouts 8 1972 2934869719
.? S1IG%P/~"CgB H\13750068 105 commit cleanouts successfully 8 1967 3554314084ITPUB个人空间!g%v8c4L.k+V w@ L U
completed
191 commit txn count during cleano 128 21 1405359431
4q9V^ ?@;`2a13750068 ut
206 commit batch/immediate request 1 0 640321576ITPUB个人空间 E?+i5y Z|
ed
207 commit batch requested 1 0 2338940602ITPUB个人空间? ?)g g/X;o ~0~
208 commit immediate requested 1 0 796663742
f)Fn C/r*cz+n%e13750068 209 commit batch/immediate perform 1 0 3711642721
)o'a+S0A6R.ea13750068 ed
210 commit batch performed 1 0 1391516579ITPUB个人空间Xm9u*OIux
211 commit immediate performed 1 0 3494177237
``*if6KO9[#v#@0Oy13750068 212 commit wait/nowait requested 1 0 3800399491
.P'I7NTT13750068 213 commit nowait requested 1 0 3697788123ITPUB个人空间k%XI4UW:AE @[
214 commit wait requested 1 0 2843300285ITPUB个人空间'g kc0c@ Pn
215 commit wait/nowait performed 1 0 1541239326
4Jk ne-d`$^6oL13750068 216 commit nowait performed 1 0 485928497ITPUB个人空间G$WuYC1jp Z
217 commit wait performed 1 0 8047752
?*O_.J k13750068 230 IMU commits 128 384 1914489094ITPUB个人空间 jy2A2oR;X+]2RY
344 OTC commit optimization attemp 128 0 2480822770
Ix"s5`$E7PL6Cr Yl7I13750068 ts
345 OTC commit optimization hits 128 0 4064864054
i~_"Q&t1z:l13750068 346 OTC commit optimization failur 128 0 3633344886
6K&WdJ)G D W'auL,]13750068 e - setup
ITPUB个人空间y+l~fPx d)[
已选择26行。
c{/B7|f&D13750068
6Question：
Now，I have some question：
As far as I know，Private Redo Strands is maintained by an redo allocation latch,but according to Aman Sharma：The IMU pools are allocated in the shared pool. The data that we change gets copied to these pools from a latch called In memory Undo latch。
which latch maintain Private Redo Strands？IMUlatch or Redo Allocation Latch?
Reference：
1http://blog.aristadba.com/?p=17
The IMU pools are allocated in the shared pool. The data that we change gets copied to these pools from a latch called In memory Undo latch. We can see it here from V$latch,
When we modify the data, oracle doesn’t apply the data to the data blocks immediately but It allocates the data in the IMU pools in the shared pool with the help of this IMU latch. This mechanism avoids the pinning of the Undo segment header block and also to the undo data blocks which are otherwise used immediately when the transaction starts. Now they are only used when the transaction commits. The data is in the pool and from there only it gets copied to the buffer cache. As each pool is assigned to one transaction and is maintained by an individual latch, the mechanism works well to reduce the contention. I am still not clear that with a very very heavy OLTP environment, what would happen? Are the pools increased at that time or not, or some thing else happens, I am not sure.
--------by Aman Sharma
|
|
楼主: vage
IP卡
狗仔卡
发表于 2012-3-19 16:11
显身卡
楼主
