延迟块清除介绍

Comments

1. admin says

   2010/08/19 at 22:55

   Hdr: 5970348 9.2.0.8 RDBMS 9.2.0.8 TXN MGMT LOCAL PRODID-5 PORTID-212
   Abstract: ENHANCEMENT REQUEST TO USE THE TABLESPACE READ-ONLY SCN FOR BLOCKS’ COMMIT SCN

   PROBLEM:
   ——–
   The original issue in the previous TAR (17073506.6) was for the performance
   team the issue was diagnosed and the cause was found to be delayed block
   cleanout issue with readonly tablespaces causing lot’s of read on the UNDO TS
   ans sometimes even an ora-1555.

   The issue happens during query on some tables, which reside on a READ ONLY
   tablespace. When this tablespace is put into read only mode, it waits till
   all transactions in this tablespace finish, so against objects in that READ
   ONLY tablespace there cannot be any DML transaction activity.

   Session statistics show increasing value for statistic #161 traction table
   consistent reads.
   10046 trace shows physical reads, ts# and block# of these blocks points to
   undo tablespace.

   As there cannot be any DML activity, the only reason to go into undo blocks
   are delayed block cleanouts. We need to check undo segment header to check if
   transaction is open. For consistent reads we need to check if it was commited
   before SCN of current query or not. This can be a problem, if the undo
   segment header no longer contains information about a not cleaned transaction
   – we need to undo changes in undo segment header itself. If from the start of
   query there were many transactions in this undo segment header, we need to
   apply more undo records for a consistent image of undo segment header and
   time to prepare CR block increase. The longer the query runs, the worse the
   situation becomes.
   The issue with this scenario is that blocks will newer be cleaned out while
   the tablespace is read only – delayed block cleanout will occour again and
   again on the same block.

   The situation does not happen always – it depends on number of blocks which
   are not cleaned out prior putting TBS into read-only mode and from number of
   transactions from start of query.
   And it does not happen for plugged in read only TTS tablespaces.

   Fact that delayed block cleanout occours we can see in uploaded errorstack
   dump -> function ktbgcl1 on list –
   ———————————————–
   ktrget -> ktrgcm -> ktbgcl1 -> ktugct -> kturbt

   Function kturbk {ktur}
   kturbk – Kernel transaction Undo Roll BacK This is for CR only. Read t
   he undo record (CRX) pointed by the uba and call ktundo to actually b
   acks out the change.

   Function kturbt {ktu}
   kturbt – Kernel Transactrion Undo Roll Back To (time scn)
   */ Routine to roll a transaction back so that “time” is
   */ in range on the block.
   */

   Function ktugct {ktu}
   ktugct – Kernel Transaction Undo Get Commit Times
   */ This routine will return the commit times for transaction ids i
   n */ a data block`s itl. It uses ktbisr and ktbisn to scan t
   he block and */

   Function ktbgcl1 UNKNOWN-FUNCTION / MODULE {}
   ktbgcl1 – KTB General CLean 1
   DESCRIPTION:
   Performs ITL cleanout for read consistency and for current blocks.
   For read consistency (KTB_CR), always cleanout until the CR scan
   time
   For current (KTB_CUR),

   Function ktrgcm {ktr}
   ktrgcm – common CR read code

   Function ktrget {ktr}
   Ktrget – get a read consistent block

   1. Is there a way to workaround (run some fast operation that does the block
   cleanout in the small time window between loading the data in the TS and
   making it read-only)? (see bug 4578972 in this context)
   2. Is there a way to change the delayed block cleanout behaviour? (maybe
   check with development)

   DIAGNOSTIC ANALYSIS:
   ——————–
   Trace files

   TRACE FILE
   ——————-

   WAIT #125: nam=’db file sequential read’ ela= 263 p1=2248 p2=19010 p3=1
   WAIT #125: nam=’db file sequential read’ ela= 261 p1=2248 p2=19007 p3=1
   WAIT #125: nam=’db file sequential read’ ela= 272 p1=2248 p2=19005 p3=1
   WAIT #125: nam=’db file sequential read’ ela= 259 p1=2248 p2=19002 p3=1
   WAIT #125: nam=’db file sequential read’ ela= 262 p1=2248 p2=18999 p3=1
   WAIT #125: nam=’db file sequential read’ ela= 255 p1=2248 p2=18997 p3=1
   WAIT #125: nam=’db file sequential read’ ela= 252 p1=2248 p2=18994 p3=1
   WAIT #125: nam=’db file sequential read’ ela= 254 p1=2248 p2=18992 p3=1
   WAIT #125: nam=’db file sequential read’ ela= 266 p1=2248 p2=18989 p3=1
   WAIT #125: nam=’db file sequential read’ ela= 260 p1=2248 p2=18986 p3=1
   WAIT #125: nam=’db file sequential read’ ela= 259 p1=2248 p2=18984 p3=1
   WAIT #125: nam=’db file sequential read’ ela= 256 p1=2248 p2=18981 p3=1
   WAIT #125: nam=’db file sequential read’ ela= 255 p1=2248 p2=18978 p3=1
   WAIT #125: nam=’db file sequential read’ ela= 257 p1=2248 p2=18976 p3=1
   WAIT #125: nam=’db file sequential read’ ela= 267 p1=2248 p2=18973 p3=1

   SQL> select * from v$datafile where file# = 2248
   2 /

   FILE# CREATION_CHANGE# CREATION_TIME TS#
   RF
   ILE# STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOINT_TIME
   UNRECOVERABLE_CHA
   NGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE#
   ON
   LINE_CHANGE# ONLINE_TIME BYTES BLOCKS
   CREATE_BYTES
   BLOCK_SIZE NAME
   ————— —————- ——————- —————
   ———–
   —- ——- ———- —————— ——————-
   —————–
   —- ——————- ————— ——————- —————
   —
   ———— ——————- ————— —————
   —————
   ————— ————————————–
   2248 54405323738 08.01.2006 02:37:02 1

   202 ONLINE READ WRITE 113926534903 21.02.2007 14:10:39

   0 113815629751

   113815629752 21.02.2007 02:13:04 1049624576 128128
   1049624576 8192 /u025/oracle/CSPB/data/undo_054.odf

   SQL> select * from v$tablespace where ts# = 1;

   TS# NAME INC
   ————— —————————— —
   1 UNDO YES

   WORKAROUND:
   ———–
   N/A

   RELATED BUGS:
   ————-
   bug 4578972

   REPRODUCIBILITY:
   —————-

   TEST CASE:
   ———-

   STACK TRACE:
   ————
   TRACE FILE
   ——————-

   PARSING IN CURSOR #126 len=289 dep=1 uid=30 oct=2 lid=30 tim=3029452714886
   hv=
   1359805658 ad=’31856328′
   INSERT INTO ST_T_SPLATKY_PO_SPLATNOSTI (DEN_ZOSTATOK, LOAN_ID, NB_KLASIF,
   PRODUKT_ID, SEKTOR_ID, DATUM_SPL, UROKY_NZ, UROKY_PROD_NZ, UROKY_UND_NZ,
   SPOSOB_SPLACANIA, SUVAHA, ORG_UTVAR_ID, KOD_POBOCKY) VALUES (:B13 , :B12 ,
   :B11
   , :B10 , :B9 , :B8 , :B7 , :B6 , :B5 , :B4 , :B3 , :B2 , :B1 )
   END OF STMT
   EXEC #126:c=0,e=64612,p=11,cr=0,cu=11,mis=0,r=1,dep=1,og=3,tim=3029452714879
   WAIT #125: nam=’db file sequential read’ ela= 314 p1=2173 p2=57798 p3=1
   WAIT #125: nam=’db file sequential read’ ela= 5493 p1=2173 p2=96221 p3=1

   =====================

   PARSING IN CURSOR #125 len=919 dep=1 uid=30 oct=3 lid=30 tim=3029452721016
   hv=
   4005310229 ad=’2fe422e0′
   SELECT /*+ ORDERED USE_NL(J U) INDEX(I UV_I_UVINTDAVSPR_TYP) INDEX(J
   UV_I_UVLON_POBOCKA) INDEX(U UVUROK_UVLON_FK_I)*/ J.LOAN_ID, J.NB_KLASIF, J.
   PRODUKT_ID, J.SEKTOR_ID, U.DATUM_SPLATNOSTI, (U.SUMA_UROKU – U.SPLATENE) *
   DECODE(U.TYP_UROKU,’RU’,1,’RUPO’,1,0) UROKY_NZ, (U.SUMA_UROKU – U.SPLATENE) *

   DECODE(U.TYP_UROKU,’UOZ’,1,’UONZ’,1,’SUZ’,1,’SUNZ’,1,0) UROKY_PROD_NZ, (U.
   SUMA_UROKU – U.SPLATENE) * DECODE(U.TYP_UROKU,’UND’,1,0) UROKY_UND_NZ,
   DECODE(U.
   TYP_UROKU,’RU’,1,’RUPO’,1,’SUZ’,1,’SUNZ’,1,’UND’,1,NULL) SUVAHA, J.
   SPOSOB_SPLACANIA, J.CSOJ_ID, J.POBOCKA_ID FROM UV_T_INTERVALY_DAVK_SPRAC I,
   UV_UVER_JADRO J, UV_UVER_UROKY U WHERE U.TYP_UROKU IN
   (‘RU’,’RUPO’,’UOZ’,’UONZ’,
   ‘UND’,’SUZ’,’SUNZ’) AND U.SUMA_UROKU – U.SPLATENE > 0 AND U.PRIZ_SPLATENE = 0

   AND U.DATUM_SPLATNOSTI <= :B2 AND U.LOAN_ID = J.LOAN_ID AND J.PRIZNAK_ZIVY = 1 AND J.POBOCKA_ID = I.NUM_HODNOTA1 AND I.TYP_INTERVALU = 'POBO' AND I. ID_INTERVALU = :B1 END OF STMT FETCH #125:c=0,e=5957,p=2,cr=4,cu=0,mis=0,r=1,dep=1,og=3,tim=3029452721013 BINDS #126: SUPPORTING INFORMATION: ----------------------- 24 HOUR CONTACT INFORMATION FOR P1 BUGS: ---------------------------------------- DIAL-IN INFORMATION: -------------------- IMPACT DATE: ------------

   回复
2. admin says

   2010/08/19 at 22:57

   Select for Update Generates Redo
   Problem Description
   ——————-

   You have noticed that ‘Select for UPDATE’ generates redo information.

   Solution Description
   ——————–

   This is expected behaviour. When you issue a query with a FOR UPDATE, you are
   actually starting a transaction, requiring you to put a lock on every row that
   is returned by the query. In order to start a transaction and lock rows, you
   have to perform the following steps:

   1. Assign a transaction ID and record it in a rollback segment headerslot.
   2. Store the transaction ID in each block “touched” by the transaction.
   3. Set the lock byte on each row “touched” by the transaction (in this
   case, each row returned by the query).

   Each of these steps causes the contents of one or more blocks to change.
   These changes have to be logged. The changes to the data blocks, steps
   2 and 3, have to be recorded in rollback segments. These changes to the
   rollback blocks also have to be logged.

   Explanation
   ———–

   SELECT FOR UPDATE gets a lock on the rows. In order to acquire a lock, it has
   to update the block header and possibly perform block cleanout as well. If the
   row header indicates the row is locked by an transaction which has committed,
   block cleanout has to be performed before a new row lock can be taken on the
   row.

   In fact, redo can be generated on a SELECT (without the FOR UPDATE clause),
   especially if it is an active table. Even simple SELECT statements can
   generate redo because of delayed block cleanout.

   References
   ———-

   Oracle 7/8 Concepts Manual

   Additional Search Words
   ———————–

   redo lock select for update

   回复
3. diyindo says

   2014/01/14 at 15:59

   Thank you ！很有帮助！

   回复