6 Replies Latest reply: Jan 31, 2013 8:33 AM by Jonathan Lewis RSS

    USED_UREC in v$transaction

    Kunwar
      Hi friends,
      One question regarding USED_UREC v$transaction view in the my 11.2.0.3.0 version database.
      16:04:05 aim_dba@GLBABP1>  create table test_CHAR as select object_name as UN_INDEX, object_name as INDEX_  from dba_objects;
      Elapsed: 00:00:13.07
      
      16:07:31 aim_dba@GLBABP1>  exec dbms_stats.gather_table_stats(ownname=>'AIM_DBA', tabname=>'TEST_CHAR', granularity=>'ALL', cascade=>true, degree=>4, method_opt =>'FOR ALL INDEXED COLUMNS', estimate_percent =>100);
      Elapsed: 00:00:14.96
      
      16:07:56 aim_dba@GLBABP1> update TEST_CHAR set UN_INDEX='TEST';
      
      880883 rows updated.
      
      Elapsed: 00:00:32.10
      
      16:08:45 aim_dba@GLBABP1> @cn TEST_CHAR;
      
        COUNT(1)
      ----------
          880883
      
      1 row selected.
      
      
      ---from other sessions while checking the UNDO stats i get the below
      15:56:49 aim_dba@GLBABP1> /
      
         SID SERIAL# USERNAME   SCHEMANAME      PROCESS         MACHINE    SQL_ID        PREV_SQL_ID   STATUS    USED_UBLK  USED_UREC START_TIME
      ------ ------- ---------- --------------- --------------- ---------- ------------- ------------- -------- ---------- ---------- --------------------
         149   17379 AIM_DBA    AIM_DBA         22195           indlin314  16axuazrykqb0 16axuazrykqb0 INACTIVE      19468    1761688 01/30/13 16:08:12
      
         
      16:08:47 aim_dba@GLBABP1> l
        1  select s.sid,s.serial#,
        2  s.USERNAME,
        3  s.SCHEMANAME,
        4  --s.OSUSER,
        5  s.PROCESS,
        6  s.MACHINE,
        7  s.SQL_ID,
        8  s.PREV_SQL_ID,
        9  --s.EVENT,
       10  s.STATUS,
       11  USED_UBLK,USED_UREC,START_TIME
       12*  from v$session s, v$transaction t where s.taddr=t.addr
      
       
       
      I would like to know from where the number 1761688 comes from.
      880883 is the number of records in the table.
      I googled/searched on Metalink but no still no clue. The table is not INDEXED.

      Regds,
      Kunwar
        • 1. Re: USED_UREC in v$transaction
          Aman....
          It's double of the number of the rows in the table, why I don't know. I got the same result with your script except that in my table, I got 145225 for 72617 rows in the table.

          But for a smaller table, the number is fine. If you test for the EMP table, the number is matching with the 14 rows of it.

          Aman....
          • 2. Re: USED_UREC in v$transaction
            Jonathan Lewis
            Aman.... wrote:
            It's double of the number of the rows in the table, why I don't know. I got the same result with your script except that in my table, I got 145225 for 72617 rows in the table.

            But for a smaller table, the number is fine. If you test for the EMP table, the number is matching with the 14 rows of it.
            I think I've written something about this somewhere before, but I can't find the notes.
            For some reason, in versions of Oracle from 10g onwards, a large update can decide that it needs to go into "write consistency" mode, so it rolls back and restarts. On the restart, however, it updates row by row (rather than using an internal array method) and locks each row before updating it - this is why you end up with used_urec close to 2 * rows updated. I don't know why this happens. It's possible that it's only relevant for a large update after a direct load, or CTAS, or insert as select.

            Sample of undo block dump showing the effect:
            *-----------------------------
            * Rec #0x7  slt: 0x00  objn: 79294(0x000135be)  objd: 79295  tblspc: 5(0x00000005)
            *       Layer:  11 (Row)   opc: 1   rci 0x06   
            Undo type:  Regular undo   Last buffer split:  No 
            Temp Object:  No 
            Tablespace Undo:  No 
            rdba: 0x00000000
            *-----------------------------
            KDO undo record:
            KTB Redo 
            op: 0x02  ver: 0x01  
            compat bit: 4 (post-11) padding: 0
            op: C  uba: 0x00c007bc.0234.06
            KDO Op code: LKR row dependencies Disabled
              xtype: XA flags: 0x00000000  bdba: 0x0140050c  hdba: 0x01400480
            itli: 2  ispac: 0  maxfr: 4863
            tabn: 1 slot: 183 to: 0
             
            *-----------------------------
            * Rec #0x8  slt: 0x00  objn: 79294(0x000135be)  objd: 79295  tblspc: 5(0x00000005)
            *       Layer:  11 (Row)   opc: 1   rci 0x07   
            Undo type:  Regular undo   Last buffer split:  No 
            Temp Object:  No 
            Tablespace Undo:  No 
            rdba: 0x00000000
            *-----------------------------
            KDO undo record:
            KTB Redo 
            op: 0x02  ver: 0x01  
            compat bit: 4 (post-11) padding: 0
            op: C  uba: 0x00c007bc.0234.07
            KDO Op code: ORP row dependencies Disabled
              xtype: XA flags: 0x00000008  bdba: 0x0140050c  hdba: 0x01400480
            itli: 2  ispac: 0  maxfr: 4863
            tabn: 1 slot: 183(0xb7) size/delt: 11
            fb: --H-FL-- lb: 0x2  cc: 3
            null: ---
            kdrhccnt=3,full row: 2C 02 03 0D 01 6B CC C3 05 01 4B
             
            Undo record 7 is the undo for "lock row 183"
            Undo record 8 is the undo for "update row 183"

            Regards
            Jonathan Lewis
            • 3. Re: USED_UREC in v$transaction
              Aman....
              >
              I think I've written something about this somewhere before, but I can't find the notes.
              For some reason, in versions of Oracle from 10g onwards, a large update can decide that it needs to go into "write consistency" mode, so it rolls back and restarts. On the restart, however, it updates row by row (rather than using an internal array method) and locks each row before updating it - this is why you end up with used_urec close to 2 * rows updated. I don't know why this happens. It's possible that it's only relevant for a large update after a direct load, or CTAS, or insert as select.
              Wow, write consistency for Undo , absolutely had no idea about it sir! Yes , I believe for some large chunk of data only as I tested with a small table and the count was same as the number of the rows of the table. I thought it had to do something with the average row length of the row but your explanation ruled it out. Thanks so much , learned again something new today :- ) . And I tried to search but I couldn't find anything written by you about it.
              Sample of undo block dump showing the effect:
              *-----------------------------
              * Rec #0x7  slt: 0x00  objn: 79294(0x000135be)  objd: 79295  tblspc: 5(0x00000005)
              *       Layer:  11 (Row)   opc: 1   rci 0x06   
              Undo type:  Regular undo   Last buffer split:  No 
              Temp Object:  No 
              Tablespace Undo:  No 
              rdba: 0x00000000
              *-----------------------------
              KDO undo record:
              KTB Redo 
              op: 0x02  ver: 0x01  
              compat bit: 4 (post-11) padding: 0
              op: C  uba: 0x00c007bc.0234.06
              KDO Op code: LKR row dependencies Disabled
              xtype: XA flags: 0x00000000  bdba: 0x0140050c  hdba: 0x01400480
              itli: 2  ispac: 0  maxfr: 4863
              tabn: 1 slot: 183 to: 0
              
              *-----------------------------
              * Rec #0x8  slt: 0x00  objn: 79294(0x000135be)  objd: 79295  tblspc: 5(0x00000005)
              *       Layer:  11 (Row)   opc: 1   rci 0x07   
              Undo type:  Regular undo   Last buffer split:  No 
              Temp Object:  No 
              Tablespace Undo:  No 
              rdba: 0x00000000
              *-----------------------------
              KDO undo record:
              KTB Redo 
              op: 0x02  ver: 0x01  
              compat bit: 4 (post-11) padding: 0
              op: C  uba: 0x00c007bc.0234.07
              KDO Op code: ORP row dependencies Disabled
              xtype: XA flags: 0x00000008  bdba: 0x0140050c  hdba: 0x01400480
              itli: 2  ispac: 0  maxfr: 4863
              tabn: 1 slot: 183(0xb7) size/delt: 11
              fb: --H-FL-- lb: 0x2  cc: 3
              null: ---
              kdrhccnt=3,full row: 2C 02 03 0D 01 6B CC C3 05 01 4B
              Undo record 7 is the undo for "lock row 183"
              Undo record 8 is the undo for "update row 183"
              Right! But I am still not sure that what good is achieved by doing this? Isn't it an extra work ?

              Regards
              Aman....
              • 4. Re: USED_UREC in v$transaction
                Jonathan Lewis
                Aman.... wrote:
                >
                For some reason, ..
                Right! But I am still not sure that what good is achieved by doing this? Isn't it an extra work ?
                Yes, it is extra work, and I have no idea why it's done. I'm inclined to believe it's a side effect of some other clever trick that reduces the workload in other (probably "proper OLTP") circumstances, but I haven't come up with anything that sounds like a good justification for it.

                Possibly associated with this is a change in the handling of the "last change SCN" in a block. This is accompanied by a sequence number that allows many changes to be made to a block in the right order. In older versions of Oracle if you modified the block more than 254 (or maybe 255) times before a commit (from ANY session) then the SCN would increment and the counter would set itself back to its low value. In recent versions of Oracle I can't get the counter anywhere near the maximum, so the SCN changes much faster than in used to.


                Regards
                Jonathan Lewis

                Edited by: Jonathan Lewis on Jan 31, 2013 8:14 AM
                • 5. Re: USED_UREC in v$transaction
                  Kunwar
                  Hi Jonathan and Aman,
                  Thanks for the inputs. I read below in oneTom Kyte publication "Expert Oracle Database Architecture"
                  (Chapter 7:CONCURRENCY AND MULTI-VERSIONING)

                  "
                  We have been
                  using a single-row example, but what happens if you start a large batch update and it is restarted after
                  processing the first 100,000 records? It will roll back the 100,000 row changes, restart in SELECT FOR
                  UPDATE mode, and do the 100,000 row changes again after that.
                  "
                  Is it the same with what you think as the reason behind such results? i searched but didnt find more on it in any documentation or any white paper as such.

                  I ran a test and got the below results.
                  ---Tables created
                  create table test_100 as select object_name as UN_INDEX, object_name as INDEX_  from dba_objects where rownum  <101;
                  create table test_1000 as select object_name as UN_INDEX, object_name as INDEX_  from dba_objects where rownum  <1001;
                  create table test_10000 as select object_name as UN_INDEX, object_name as INDEX_  from dba_objects where rownum  <10001;
                  create table test_100000 as select object_name as UN_INDEX, object_name as INDEX_  from dba_objects where rownum  <100001;
                  create table test_1000000 as select object_name as UN_INDEX, object_name as INDEX_  from dba_objects where rownum  <1000001;
                  
                  ---Stats also collected
                  
                  update test_100 set UN_INDEX='TEST';
                  update test_1000 set UN_INDEX='TEST';
                  update test_10000 set UN_INDEX='TEST';
                  update test_100000 set UN_INDEX='TEST';
                  update test_1000000 set UN_INDEX='TEST';
                   
                   
                  ---running the scripts (with stats from  another session)
                  
                  test_100)
                  
                  
                  12:22:02 aim_dba@TESTDB> update test_100 set UN_INDEX='TEST';
                  
                  100 rows updated.
                  
                  USED_UBLK  USED_UREC 
                  --------- ---------- 
                          3         99 
                  
                  
                  test_1000)
                   12:23:12 aim_dba@TESTDB> update test_1000 set UN_INDEX='TEST';
                  
                  1000 rows updated.
                  
                  USED_UBLK  USED_UREC  
                  -------- ----------   
                        15        998 
                         
                  test_10000)
                  
                   12:23:36 aim_dba@TESTDB> update test_10000 set UN_INDEX='TEST';
                  
                  10000 rows updated.
                  
                  
                  USED_UBLK  USED_UREC       
                  --------- ----------
                        135       9995
                         
                  test_100000)
                  
                  12:24:57 aim_dba@TESTDB> update test_100000 set UN_INDEX='TEST';
                  
                  100000 rows updated.
                  
                  USED_UBLK  USED_UREC       
                  --------- ----------
                       2261     199990
                        
                  test_1000000)
                  
                  
                  12:26:00 aim_dba@TESTDB> update test_1000000 set UN_INDEX='TEST';
                  886251 rows updated.
                  
                  USED_UBLK  USED_UREC      
                  --------- ----------
                      19589    1772424
                  • 6. Re: USED_UREC in v$transaction
                    Jonathan Lewis
                    Kunwar wrote:
                    Thanks for the inputs. I read below in oneTom Kyte publication "Expert Oracle Database Architecture"
                    (Chapter 7:CONCURRENCY AND MULTI-VERSIONING)

                    "
                    We have been
                    using a single-row example, but what happens if you start a large batch update and it is restarted after
                    processing the first 100,000 records? It will roll back the 100,000 row changes, restart in SELECT FOR
                    UPDATE mode, and do the 100,000 row changes again after that.
                    "
                    Is it the same with what you think as the reason behind such results? i searched but didnt find more on it in any documentation or any white paper as such.
                    It's the same effect - though neither of us can think of a good reason why it should happen in a single user system with nothing else going on.

                    Regards
                    Jonathan Lewis