This discussion is archived
4 Replies Latest reply: Aug 25, 2009 6:03 PM by 601585 RSS

SELECT FOR UPDATE - does it write to REDO?

166493 Newbie
Currently Being Moderated
Greetings folks,

we have some strange performance issues with a recent application update. This is custom in-house application. Every morning since the deploy we get a spike in load, especially to log sync / redo archive. We capture activity surrounding the load, and found many "select for update" statements.

Im digging through metalink and docs looking to confirm "write or no write" to the redo log files. comments appreciated.
  • 1. Re: SELECT FOR UPDATE - does it write to REDO?
    sb92075 Guru
    Currently Being Moderated
    SELECT does not generate REDO
    DML generates REDO.
  • 2. Re: SELECT FOR UPDATE - does it write to REDO?
    mbobak Oracle ACE
    Currently Being Moderated
    And select for update is DML, so generates redo.
    SQL> create table test_redo as select rownum a from dba_tables where rownum < 1001
      2  /
    
    Table created.
    
    SQL>
    SQL>
    SQL>
    SQL> set autot traceonly
    SQL> select rownum from test_Redo for update
      2  /
    
    1000 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2431619654
    
    -----------------------------------------
    | Id  | Operation           | Name      |
    -----------------------------------------
    |   0 | SELECT STATEMENT    |           |
    |   1 |  FOR UPDATE         |           |
    |   2 |   COUNT             |           |
    |   3 |    TABLE ACCESS FULL| TEST_REDO |
    -----------------------------------------
    
    Note
    -----
       - rule based optimizer used (consider using cbo)
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
           1020  db block gets
             75  consistent gets
              0  physical reads
         206436  redo size
          77341  bytes sent via SQL*Net to client
           1218  bytes received via SQL*Net from client
             68  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
           1000  rows processed
    -Mark
  • 3. Re: SELECT FOR UPDATE - does it write to REDO?
    CharlesHooper Expert
    Currently Being Moderated
    sb92075 wrote:
    SELECT does not generate REDO
    DML generates REDO.
    It is possible for a SELECT to generate redo due to the effects of delayed block cleanout. A small test case:
    Session 1:
    CREATE TABLE T10 AS
    SELECT
      ROWNUM RN
    FROM
      DUAL
    CONNECT BY
      LEVEL<=1000;
    
    UPDATE
      T10
    SET
      RN=RN;
    
    UPDATE
      T10
    SET
      RN=RN;
    
    UPDATE
      T10
    SET
      RN=RN;
    
    
    Session 2:
    ALTER SYSTEM FLUSH BUFFER_CACHE;
    ALTER SYSTEM FLUSH BUFFER_CACHE;
    
    
    Session 1:
    COMMIT;
    
    
    Session 2:
    SET AUTOTRACE ON STATISTICS
    
    SELECT
      *
    FROM
      T10;
    
    1000 rows selected.
    
    
    Statistics
    ---------------------------------------------------
              0  recursive calls
              0  db block gets
             73  consistent gets
              3  physical reads
            188  redo size
          11104  bytes sent via SQL*Net to client
           1060  bytes received via SQL*Net from client
             68  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
           1000  rows processed
    Note that the above may be performed in a single session rather than using two sessions. Essentially, the flush of the buffer cache causes the dirty blocks (containing uncommitted changes) to be written to disk, and the blocks are "cleaned up" during the next SELECT which accesses the blocks.

    A much better explanation:
    http://jonathanlewis.wordpress.com/2009/06/16/clean-it-up/

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 4. Re: SELECT FOR UPDATE - does it write to REDO?
    601585 Oracle ACE
    Currently Being Moderated
    As mentioned above, select for update generates redo just like DML statements.

    It does not modify row itself, but needs to set lock byte and allocate ITL entry which would generate redo.


    ================================
    Dion Cho - Oracle Performance Storyteller

    http://dioncho.wordpress.com (english)
    http://ukja.tistory.com (korean)
    http://dioncho.blogspot.com (japanese)
    http://ask.ex-em.com (q&a)
    ================================

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points