1 log 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;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> desc X$KCRFSTRAND
名称
--------------------------------------
ADDR
INDX
INST_ID
PNEXT_BUF_KCRFA_CLN
NEXT_BUF_NUM_KCRFA_CLN
BYTES_IN_BUF_KCRFA_CLN
AVAILABLE_BUFS_KCRFA_CLN
MEM_BUFS_AVAILABLE_KCRFA_CLN
STRAND_HEADER_BNO_KCRFA_CLN
FIRST_REDO_KCRFA_CLN
STRAND_HDR_SCN_KCRFA_CLN
LWN_BUF_NUM_KCRFA_CLN
HIGHEST_SCN_KCRFA_CLN
HIGHEST_SUBSCN_KCRFA_CLN
PVT_STRAND_STATE_KCRFA_CLN
STRAND_NUM_ORDINAL_KCRFA_CLN
PTR_KCRF_PVT_STRAND
INDEX_KCRF_PVT_STRAND
SPACE_KCRF_PVT_STRAND
TXN_KCRF_PVT_STRAND
ACTION_KCRFA
LOG_RESIDUE_KCRFA
LAST_BUF_WRITTEN_KCRFA
LAST_BUF_GATHERED_KCRFA
CONS_LOW_BUFNO_KCRFA
FIRST_BUF_KCRFA
LAST_BUF_KCRFA
TOTAL_BUFS_KCRFA
STRAND_SIZE_KCRFA
LOWBUF_KCRFA
HIBUF_KCRFA
WRAPHIBUF_KCRFA
WRAPLOWBUF_KCRFA
LOG_SEQ_KCRFA
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$sgastat
2 where name like '%private%';
NAME POOL
-------------------------- ------------
private strands shared pool
KCB private handles shared pool
SQL> select latch#,child#,name from V$latch_children
2 where name like '%redo%' order by 1,2
3 ;
LATCH# CHILD# NAME
---------- ---------- ----------------------------------------
147 1 redo copy
147 2 redo copy
147 3 redo copy
147 4 redo copy
147 5 redo copy
147 6 redo copy
147 7 redo copy
147 8 redo copy
148 1 redo allocation
148 2 redo allocation
148 3 redo allocation
148 4 redo allocation
148 5 redo allocation
148 6 redo allocation
148 7 redo allocation
148 8 redo allocation
148 9 redo allocation
148 10 redo allocation
148 11 redo allocation
148 12 redo allocation
148 13 redo allocation
148 14 redo allocation
148 15 redo allocation
148 16 redo allocation
148 17 redo allocation
148 18 redo allocation
148 19 redo allocation
148 20 redo allocation
3 IMU Latch：
SQL> select name from V$latch_children where name like '%undo%';
NAME
--------------------------------------------------
undo global data
undo global data
undo global data
undo global data
undo global data
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
In memory undo latch
已选择23行。
SQL> select name, gets, misses, immediate_gets IM, sleeps
2 from v$latch_children where name like 'In%undo%';
NAME GETS MISSES IM SLEEPS
-------------------------------------------------- ---------- ---------- ---------- ----------
In memory undo latch 2563 0 423 0
In memory undo latch 22 0 11 0
In memory undo latch 326 0 77 0
In memory undo latch 1836 0 918 0
In memory undo latch 2 0 1 0
In memory undo latch 0 0 0 0
In memory undo latch 0 0 0 0
In memory undo latch 0 0 0 0
In memory undo latch 0 0 0 0
In memory undo latch 0 0 0 0
In memory undo latch 0 0 0 0
In memory undo latch 0 0 0 0
In memory undo latch 0 0 0 0
In memory undo latch 0 0 0 0
In memory undo latch 0 0 0 0
In memory undo latch 0 0 0 0
In memory undo latch 0 0 0 0
In memory undo latch 0 0 0 0
已选择18行。
SQL> select KSPFTCTXDVL,kspftctxdf,KSPFTCTXVL,KSPFTCTXIS,ksppinm
2 from x$ksppcv2 a,x$ksppi b
3 where a.indx=b.indx and b.ksppinm like '%undo%'
4 ;
KSPFTCTXDVL KSPFTCTXDF KSPFTCTXVL KSPFTCTXIS KSPPINM
-------------------------------------------------------------------------------- ---------- -------------------------------------------------------------------------------- ---------- --------------------------------------------------------------------------------
TRUE FALSE _gc_undo_affinity
TRUE TRUE TRUE FALSE _kcl_undo_locks
TRUE TRUE TRUE FALSE _kcl_undo_grouping
AUTO TRUE AUTO FALSE _gc_undo_affinity_locks
TRUE TRUE TRUE FALSE undo_management
100 TRUE 100 FALSE undo_tablespace
0 TRUE 0 FALSE _collect_undo_stats
TRUE FALSE _undo_debug_mode
0 TRUE 0 FALSE _verify_undo_quota
FALSE TRUE FALSE FALSE _in_memory_undo
3 TRUE 3 FALSE _undo_autotune
TRUE TRUE TRUE FALSE undo_retention
0 TRUE 0 FALSE _undo_debug_usage
0 TRUE 0 FALSE _optimizer_undo_cost_change
0 TRUE 0 FALSE _optimizer_undo_changes
15 rows selected
SQL>
SQL> select pool,name from V$sgastat where pool='shared pool' and lower(name) like '%undo%';
POOL NAME
------------ --------------------------
shared pool UNDO INFO SEGMENTED ARRAY
shared pool KCB undo bitvec
shared pool Undo Meta. Data
shared pool Auto tune undo info
shared pool UNDO INFO
shared pool KTI-UNDO
shared pool UNDO INFO HASH
shared pool UNDO STAT INFO
8 rows selected
SQL> select name from V$latch where name like '%undo%';
NAME
--------------------------------------------------
undo global data
In memory undo latch
4 IMU Lock：
SQL> select * from v$lock_type where type='IM';
TYPE NAME ID1_TAG ID2_TAG IS_ DESCRIPTION
------------------------------ ------------------------------ -------------------- -------------------- --- ------------------------------
IM Kti blr lock pool # 0 NO Serializes block recovery for
an IMU txn
SQL> select name from v$statname where name like 'IMU%';
NAME
------------------------------
IMU commits
IMU Flushes
IMU contention
IMU recursive-transaction flus
h
IMU undo retention flush
IMU ktichg flush
IMU bind flushes
IMU mbu flush
IMU pool not allocated
IMU CR rollbacks
IMU undo allocation size
IMU Redo allocation size
IMU- failed to get a private s
trand
已选择13行。
5 IMU Commits：
SQL> select * from v$sysstat where name like '%commit%';
STATISTIC# NAME CLASS VALUE STAT_ID
---------- ------------------------------ ---------- ---------- ----------
4 user commits 1 395 582481098
98 commit cleanout failures: writ 8 0 2063165199
e disabled
99 commit cleanout failures: bloc 8 0 781179189
k lost
100 commit cleanout failures: cann 8 0 176423721
ot pin
101 commit cleanout failures: hot 8 0 2500023621
backup in progress
102 commit cleanout failures: buff 8 0 1626141486
er being written
103 commit cleanout failures: call 8 5 3086716748
back failure
104 commit cleanouts 8 1972 2934869719
105 commit cleanouts successfully 8 1967 3554314084
completed
191 commit txn count during cleano 128 21 1405359431
ut
206 commit batch/immediate request 1 0 640321576
ed
207 commit batch requested 1 0 2338940602
208 commit immediate requested 1 0 796663742
209 commit batch/immediate perform 1 0 3711642721
ed
210 commit batch performed 1 0 1391516579
211 commit immediate performed 1 0 3494177237
212 commit wait/nowait requested 1 0 3800399491
213 commit nowait requested 1 0 3697788123
214 commit wait requested 1 0 2843300285
215 commit wait/nowait performed 1 0 1541239326
216 commit nowait performed 1 0 485928497
217 commit wait performed 1 0 8047752
230 IMU commits 128 384 1914489094
344 OTC commit optimization attemp 128 0 2480822770
ts
345 OTC commit optimization hits 128 0 4064864054
346 OTC commit optimization failur 128 0 3633344886
e - setup
已选择26行。
6 Question：
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：
1 http://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
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13750068/viewspace-719816/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/13750068/viewspace-719816/
675
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
