延迟块清除介绍

在Oracle中数据锁(这里主要指TX类型行锁)实际上是数据的属性,存储在块首部,称之为事务槽(ITL)。COMMIT操作的职责包括释放块上的锁,实际的释放方式即清除块上相应的事务槽,但这里存在一个性能的考量。设想一个UPDATE大量数据的操作,因为执行时间较长,一部分已修改的块已被缓冲池flush out写至磁盘,当UPDATE操作完成执行COMMIT操作时,则需要将那些已写至磁盘的数据块重新读入,这将消耗大量I/O,并使COMMIT操作十分缓慢;为了解决这一矛盾,Oracle使用了延迟块清除的方案,对待存在以下情况的块COMMIT操作不做块清除:

在更新过程中,被缓冲池flush out写至磁盘的块

若更新操作涉及的块超过了块缓冲区缓存的10%时,超出的部分块。

虽然COMMIT放弃对这些块的块清除(block cleanout)操作,但COMMIT操作仍会修改回滚段的段头,回滚段的段头包括了段中的事务的字典,COMMIT操作将本事务转化为非ACTIVE状态。

当下一次操作如SELECT,UPDATE,INSERT或DELETE访问到这些块时可能需要在读入后完成块清除,这样的操作称之为块延迟清除(deferred block cleanout);块延迟清除通过事务槽上的回滚段号,槽号等信息访问回滚段头的事务字典,若事务不再活跃或事务过期则完成清除块上的事务槽,事务槽清除后继续执行相应的操作。

块延迟清除的影响在SELECT操作过程中体现的最为明显。总结来说块延迟清除是COMMIT操作的一个延续,始终是一种十分轻微的操作,且该种操作是行级的,不会使段(Segment)的属性有所改变。

Comments

  1. admin says

    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

    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. Thank you !很有帮助!

diyindo进行回复 取消回复

*

沪ICP备14014813号-2

沪公网安备 31010802001379号