7 Replies Latest reply on Dec 27, 2008 11:31 PM by Jonathan Lewis

    Performance Impact of Intra-block chaining

    Vivek Sharma-Mumbai-Oracle
      Hi All,

      A table with more that 255 columns, rows that have the data after the 255th column are like to be chained within the same block. This is intra-block chaining. These two pieces are chained together using the rowid's. Can anyone explain what is the performance impact of intra-block chaining ?

      Regards
      Vivek
        • 1. Re: Performance Impact of Intra-block chaining
          Aman....
          Vivek Sharma wrote:
          Hi All,

          A table with more that 255 columns, rows that have the data after the 255th column are like to be chained within the same block. This is intra-block chaining. These two pieces are chained together using the rowid's. Can anyone explain what is the performance impact of intra-block chaining ?
          Vivek,
          Read this thread where Ying explains the same.
          Re: Re-sorting of table columns
          Also TOP has some talk about it,
          http://books.google.com/books?id=w8qzDTUVHSQC&pg=PA537&lpg=PA537&dq=impact+of+intra+block+chaining&source=web&ots=_epu7p3Z2H&sig=Qm8QgYhyLqQi6LNVZB-nCkNgqEE&hl=en&sa=X&oi=book_result&resnum=1&ct=result#PPA537,M1
          The impact is , IMO would be read penality only. I am not sure that there will be any other impacts.
          Vivek, check this link where this exact question was asked and the answer is no issue. I am not sure that its correct or not but thinking about it , makes some what sense. I haven't edited my last reply. Hope someone else would share his/her ideas as well.
          HTH
          Aman....

          Edited by: Aman.... on Dec 26, 2008 9:39 PM added one more link.,
          • 2. Re: Performance Impact of Intra-block chaining
            181444
            The performance impact of having a table with more than 255 columns is that it effecitvely takes more than one logical IO to fetch a row. Note that if you fetch a column from the first row piece that I believe that the newer versions of Oracle do not have to access the additional row pieces meaning that there is no impact in this case. I have not tested this statement so if you have a high column count table you might want to try.

            HTH -- Mark D Powell --
            • 3. Re: Performance Impact of Intra-block chaining
              Vivek Sharma-Mumbai-Oracle
              Hi Mark,

              I have checked for the performance (logical i/o's) and it seems there are no difference between the I/O's done by a query on less than 255 column and more than 255 columns tables. I created a table with 300 columns and one with 10 columns. Both of these had a primary key index and querying 1st and last column on primary key got the result set in 3 i/o's. Therefore, I am curious to know if there are any performance impacts.

              Regards
              Vivek
              • 4. Re: Performance Impact of Intra-block chaining
                Pavan Kumar
                SQL> declare
                  2  v_sql varchar2(32767);
                  3  begin
                  4  v_sql := 'Create table test256(';
                  5  for i in 1..256 loop
                  6  v_sql := v_sql ||'C'||i ||' NUMBER,';
                  7  end loop;
                  8  v_sql := RTRIM(V_SQL,',')||')';
                  9  EXECUTE IMMEDIATE V_SQL;
                 10  END;
                 11  /
                
                PL/SQL procedure successfully completed.
                
                SQL> select count(*) from user_tab_columns where table_name='TEST256';
                
                  COUNT(*)
                ----------
                       256
                
                
                -- Insert into Table test256
                SQL> BEGIN
                  2   EXECUTE IMMEDIATE 'INSERT INTO test256 VALUES('||LPAD('1,',510,'1,')||'1)'
                 ;
                  3   commit;
                  4  end;
                  5  /
                
                PL/SQL procedure successfully completed.
                
                SQL> select count(*) from test256;
                
                  COUNT(*)
                ----------
                         1
                
                SQL> SELECT DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID),DBMS_ROWID.ROWID_BLOCK_NUMBER(R
                OWID) from test256;
                
                DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
                ------------------------------------ ------------------------------------
                                                   4                                   84
                
                datafile = 4
                Block number = 84
                
                
                Let us dump the block and see the information
                F:\APP\PAVAN\ORADATA\ORCL1\USERS01.DBF
                
                Check the dump
                
                Start dump data blocks tsn: 4 file#:4 minblk 84 maxblk 84
                Block dump from cache:
                Dump of buffer cache at level 4 for tsn=4, rdba=16777300
                BH (0x20BFB01C) file#: 4 rdba: 0x01000054 (4/84) class: 1 ba: 0x20B8A000
                  set: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 103 lid: 0x00000000,0x00000000
                  dbwrid: 0 obj: 70065 objn: 70065 tsn: 4 afn: 4
                  hash: [0x2EA8E160,0x2EA8E160] lru: [0x17FEF66C,0x20BFB14C]
                  obj-flags: object_ckpt_list
                  ckptq: [0x20BFB120,0x17FF5550] fileq: [0x20BFB128,0x17FF5558] objq: [0x17FF55DC,0x20BFB1AC]
                  st: XCURRENT md: NULL tch: 3
                  flags: buffer_dirty redo_since_read gotten_in_current_mode
                  LRBA: [0xf.bece.0] LSCN: [0x0.13a24e] HSCN: [0x0.13a24e] HSUB: [1]
                  cr pin refcnt: 0 sh pin refcnt: 0
                  buffer tsn: 4 rdba: 0x01000054 (4/84)
                  scn: 0x0000.0013a24e seq: 0x04 flg: 0x02 tail: 0xa24e0604
                  frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
                Hex dump of block: st=0, typ_found=1
                Dump of memory from 0x20B8A000 to 0x20B8C000
                20B8A000 0000A206 01000054 0013A24E 02040000  [....T...N.......]
                20B8A010 00000000 00000001 000111B1 0013A163  [............c...]
                20B8A020 00000000 00320003 01000051 001A0005  [......2.Q.......]
                20B8A030 000002C5 00C009CA 00190180 00002002  [............. ..]
                20B8A040 0013A24E 00000000 00000000 00000000  [N...............]
                20B8A050 00000000 00000000 00000000 00000000  [................]
                20B8A060 00000000 00000000 00000000 00008000  [................]
                20B8A070 00000000 00000000 00000000 00020100  [................]
                20B8A080 0016FFFF 1C5E1C74 00001C5E 1C800002  [....t.^.^.......]
                20B8A090 00001C74 00000000 00000000 00000000  [t...............]
                20B8A0A0 00000000 00000000 00000000 00000000  [................]
                        Repeat 248 times
                20B8B030 00000000 00000002 00001FE8 00000001  [................]
                20B8B040 00000000 00000002 00000003 00000000  [................]
                20B8B050 00000000 00000000 00000000 00000000  [................]
                        Repeat 201 times
                20B8BCF0 01010128 00540000 02C10200 02FF0104  [(.....T.........]
                20B8BD00 C10202C1 02C10202 0202C102 C10202C1  [................]
                20B8BD10 02C10202 0202C102 C10202C1 02C10202  [................]
                20B8BD20 0202C102 C10202C1 02C10202 0202C102  [................]
                20B8BD30 C10202C1 02C10202 0202C102 C10202C1  [................]
                20B8BD40 02C10202 0202C102 C10202C1 02C10202  [................]
                20B8BD50 0202C102 C10202C1 02C10202 0202C102  [................]
                20B8BD60 C10202C1 02C10202 0202C102 C10202C1  [................]
                20B8BD70 02C10202 0202C102 C10202C1 02C10202  [................]
                20B8BD80 0202C102 C10202C1 02C10202 0202C102  [................]
                20B8BD90 C10202C1 02C10202 0202C102 C10202C1  [................]
                20B8BDA0 02C10202 0202C102 C10202C1 02C10202  [................]
                20B8BDB0 0202C102 C10202C1 02C10202 0202C102  [................]
                20B8BDC0 C10202C1 02C10202 0202C102 C10202C1  [................]
                20B8BDD0 02C10202 0202C102 C10202C1 02C10202  [................]
                20B8BDE0 0202C102 C10202C1 02C10202 0202C102  [................]
                20B8BDF0 C10202C1 02C10202 0202C102 C10202C1  [................]
                20B8BE00 02C10202 0202C102 C10202C1 02C10202  [................]
                20B8BE10 0202C102 C10202C1 02C10202 0202C102  [................]
                20B8BE20 C10202C1 02C10202 0202C102 C10202C1  [................]
                20B8BE30 02C10202 0202C102 C10202C1 02C10202  [................]
                20B8BE40 0202C102 C10202C1 02C10202 0202C102  [................]
                20B8BE50 C10202C1 02C10202 0202C102 C10202C1  [................]
                20B8BE60 02C10202 0202C102 C10202C1 02C10202  [................]
                20B8BE70 0202C102 C10202C1 02C10202 0202C102  [................]
                20B8BE80 C10202C1 02C10202 0202C102 C10202C1  [................]
                20B8BE90 02C10202 0202C102 C10202C1 02C10202  [................]
                20B8BEA0 0202C102 C10202C1 02C10202 0202C102  [................]
                20B8BEB0 C10202C1 02C10202 0202C102 C10202C1  [................]
                20B8BEC0 02C10202 0202C102 C10202C1 02C10202  [................]
                20B8BED0 0202C102 C10202C1 02C10202 0202C102  [................]
                20B8BEE0 C10202C1 02C10202 0202C102 C10202C1  [................]
                20B8BEF0 02C10202 0202C102 C10202C1 02C10202  [................]
                20B8BF00 0202C102 C10202C1 02C10202 0202C102  [................]
                20B8BF10 C10202C1 02C10202 0202C102 C10202C1  [................]
                20B8BF20 02C10202 0202C102 C10202C1 02C10202  [................]
                20B8BF30 0202C102 C10202C1 02C10202 0202C102  [................]
                20B8BF40 C10202C1 02C10202 0202C102 C10202C1  [................]
                20B8BF50 02C10202 0202C102 C10202C1 02C10202  [................]
                20B8BF60 0202C102 C10202C1 02C10202 0202C102  [................]
                20B8BF70 C10202C1 02C10202 0202C102 C10202C1  [................]
                20B8BF80 02C10202 0202C102 C10202C1 02C10202  [................]
                20B8BF90 0202C102 C10202C1 02C10202 0202C102  [................]
                20B8BFA0 C10202C1 02C10202 0202C102 C10202C1  [................]
                20B8BFB0 02C10202 0202C102 C10202C1 02C10202  [................]
                20B8BFC0 0202C102 C10202C1 02C10202 0202C102  [................]
                20B8BFD0 C10202C1 02C10202 0202C102 C10202C1  [................]
                20B8BFE0 02C10202 0202C102 C10202C1 02C10202  [................]
                20B8BFF0 0202C102 C10202C1 02C10202 A24E0604  [..............N.]
                Block header dump:  0x01000054
                 Object id on Block? Y
                 seg/obj: 0x111b1  csc: 0x00.13a163  itc: 3  flg: E  typ: 1 - DATA
                     brn: 0  bdba: 0x1000051 ver: 0x01 opc: 0
                     inc: 0  exflg: 0
                 
                 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
                0x01   0x0005.01a.000002c5  0x00c009ca.0180.19  --U-    2  fsc 0x0000.0013a24e
                0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
                0x03   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
                bdba: 0x01000054
                data_block_dump,data header at 0x20b8a07c
                ===============
                tsiz: 0x1f80
                hsiz: 0x16
                pbl: 0x20b8a07c
                     76543210
                flag=--------
                ntab=1
                nrow=2
                frre=-1
                fsbo=0x16
                fseo=0x1c74
                avsp=0x1c5e
                tosp=0x1c5e
                0xe:pti[0]     nrow=2     offs=0
                0x12:pri[0]     offs=0x1c80
                0x14:pri[1]     offs=0x1c74
                block_row_dump:
                tab 0, row 0, @0x1c80
                tl: 768 fb: -----L-- lb: 0x1  cc: 255
                col  0: [ 2]  c1 02
                col  1: [ 2]  c1 02
                col  2: [ 2]  c1 02
                col  3: [ 2]  c1 02
                col  4: [ 2]  c1 02
                col  5: [ 2]  c1 02
                col  6: [ 2]  c1 02
                col  7: [ 2]  c1 02
                col  8: [ 2]  c1 02
                col  9: [ 2]  c1 02
                ...
                ...
                col 241: [ 2]  c1 02
                col 242: [ 2]  c1 02
                col 243: [ 2]  c1 02
                col 244: [ 2]  c1 02
                col 245: [ 2]  c1 02
                col 246: [ 2]  c1 02
                col 247: [ 2]  c1 02
                col 248: [ 2]  c1 02
                col 249: [ 2]  c1 02
                col 250: [ 2]  c1 02
                col 251: [ 2]  c1 02
                col 252: [ 2]  c1 02
                col 253: [ 2]  c1 02
                col 254: [ 2]  c1 02
                tab 0, row 1, @0x1c74
                tl: 12 fb: --H-F--- lb: 0x1  cc: 1
                nrid:  0x01000054.0
                col  0: [ 2]  c1 02
                end_of_block_dump
                Block dump from disk:
                buffer tsn: 4 rdba: 0x01000054 (4/84)
                scn: 0x0000.00000000 seq: 0x01 flg: 0x04 tail: 0x00003a01
                frmt: 0x02 chkval: 0x9d6e type: 0x3a=unknown
                Hex dump of block: st=0, typ_found=0
                Dump of memory from 0x0DA82200 to 0x0DA84200
                DA82200 0000A23A 01000054 00000000 04010000  [:...T...........]
                DA82210 00009D6E 00000000 00000000 00000000  [n...............]
                DA82220 00000000 00000000 00000000 00000000  [................]
                        Repeat 508 times
                DA841F0 00000000 00000000 00000000 00003A01  [.............:..]
                Dump of memory from 0x0DA82214 to 0x0DA841FC
                DA82210          00000000 00000000 00000000      [............]
                DA82220 00000000 00000000 00000000 00000000  [................]
                        Repeat 508 times
                DA841F0 00000000 00000000 00000000           [............]    
                End dump data blocks tsn: 4 file#: 4 minblk 84 maxblk 84
                
                
                Here in the Dump 
                "tl: 768 fb: -----L-- lb: 0x1  cc: 255"
                
                Let us try to Update the column 256 and try for another dump and see it
                
                
                SQL> UPDATE test256
                  2  SET C1 = 0,  C256 = 257;
                
                1 row updated.
                
                SQL> commit;
                
                Commit complete.
                
                
                Start dump data block from file F:\APP\PAVAN\ORADATA\ORCL1\USERS01.DBF minblk 84 maxblk 84
                 V10 STYLE FILE HEADER:
                     Compatibility Vsn = 185597952=0xb100000
                     Db ID=1057206558=0x3f03b11e, Db Name='ORCL1'
                     Activation ID=0=0x0
                     Control Seq=2327=0x917, File size=640=0x280
                     File Number=4, Blksiz=8192, File Type=3 DATA
                Dump all the blocks in range:
                buffer tsn: 4 rdba: 0x01000054 (4/84)
                scn: 0x0000.0013a24e seq: 0x04 flg: 0x06 tail: 0xa24e0604
                frmt: 0x02 chkval: 0x2732 type: 0x06=trans data
                Hex dump of block: st=0, typ_found=1
                Dump of memory from 0x0DA82200 to 0x0DA84200
                DA82200 0000A206 01000054 0013A24E 06040000  [....T...N.......]
                DA82210 00002732 00000001 000111B1 0013A163  [2'..........c...]
                DA82220 00000000 00320003 01000051 001A0005  [......2.Q.......]
                DA82230 000002C5 00C009CA 00190180 00002002  [............. ..]
                DA82240 0013A24E 00000000 00000000 00000000  [N...............]
                DA82250 00000000 00000000 00000000 00000000  [................]
                DA82260 00000000 00000000 00000000 00008000  [................]
                DA82270 00000000 00000000 00000000 00020100  [................]
                DA82280 0016FFFF 1C5E1C74 00001C5E 1C800002  [....t.^.^.......]
                DA82290 00001C74 00000000 00000000 00000000  [t...............]
                DA822A0 00000000 00000000 00000000 00000000  [................]
                        Repeat 248 times
                DA83230 00000000 00000002 00001FE8 00000001  [................]
                DA83240 00000000 00000002 00000003 00000000  [................]
                DA83250 00000000 00000000 00000000 00000000  [................]
                        Repeat 201 times
                DA83EF0 01010128 00540000 02C10200 02FF0104  [(.....T.........]
                DA83F00 C10202C1 02C10202 0202C102 C10202C1  [................]
                DA83F10 02C10202 0202C102 C10202C1 02C10202  [................]
                DA83F20 0202C102 C10202C1 02C10202 0202C102  [................]
                DA83F30 C10202C1 02C10202 0202C102 C10202C1  [................]
                DA83F40 02C10202 0202C102 C10202C1 02C10202  [................]
                DA83F50 0202C102 C10202C1 02C10202 0202C102  [................]
                DA83F60 C10202C1 02C10202 0202C102 C10202C1  [................]
                DA83F70 02C10202 0202C102 C10202C1 02C10202  [................]
                DA83F80 0202C102 C10202C1 02C10202 0202C102  [................]
                DA83F90 C10202C1 02C10202 0202C102 C10202C1  [................]
                DA83FA0 02C10202 0202C102 C10202C1 02C10202  [................]
                DA83FB0 0202C102 C10202C1 02C10202 0202C102  [................]
                DA83FC0 C10202C1 02C10202 0202C102 C10202C1  [................]
                DA83FD0 02C10202 0202C102 C10202C1 02C10202  [................]
                DA83FE0 0202C102 C10202C1 02C10202 0202C102  [................]
                DA83FF0 C10202C1 02C10202 0202C102 C10202C1  [................]
                DA84000 02C10202 0202C102 C10202C1 02C10202  [................]
                DA84010 0202C102 C10202C1 02C10202 0202C102  [................]
                DA84020 C10202C1 02C10202 0202C102 C10202C1  [................]
                DA84030 02C10202 0202C102 C10202C1 02C10202  [................]
                DA84040 0202C102 C10202C1 02C10202 0202C102  [................]
                DA84050 C10202C1 02C10202 0202C102 C10202C1  [................]
                DA84060 02C10202 0202C102 C10202C1 02C10202  [................]
                DA84070 0202C102 C10202C1 02C10202 0202C102  [................]
                DA84080 C10202C1 02C10202 0202C102 C10202C1  [................]
                DA84090 02C10202 0202C102 C10202C1 02C10202  [................]
                DA840A0 0202C102 C10202C1 02C10202 0202C102  [................]
                DA840B0 C10202C1 02C10202 0202C102 C10202C1  [................]
                DA840C0 02C10202 0202C102 C10202C1 02C10202  [................]
                DA840D0 0202C102 C10202C1 02C10202 0202C102  [................]
                DA840E0 C10202C1 02C10202 0202C102 C10202C1  [................]
                DA840F0 02C10202 0202C102 C10202C1 02C10202  [................]
                DA84100 0202C102 C10202C1 02C10202 0202C102  [................]
                DA84110 C10202C1 02C10202 0202C102 C10202C1  [................]
                DA84120 02C10202 0202C102 C10202C1 02C10202  [................]
                DA84130 0202C102 C10202C1 02C10202 0202C102  [................]
                DA84140 C10202C1 02C10202 0202C102 C10202C1  [................]
                DA84150 02C10202 0202C102 C10202C1 02C10202  [................]
                DA84160 0202C102 C10202C1 02C10202 0202C102  [................]
                DA84170 C10202C1 02C10202 0202C102 C10202C1  [................]
                DA84180 02C10202 0202C102 C10202C1 02C10202  [................]
                DA84190 0202C102 C10202C1 02C10202 0202C102  [................]
                DA841A0 C10202C1 02C10202 0202C102 C10202C1  [................]
                DA841B0 02C10202 0202C102 C10202C1 02C10202  [................]
                DA841C0 0202C102 C10202C1 02C10202 0202C102  [................]
                DA841D0 C10202C1 02C10202 0202C102 C10202C1  [................]
                DA841E0 02C10202 0202C102 C10202C1 02C10202  [................]
                DA841F0 0202C102 C10202C1 02C10202 A24E0604  [..............N.]
                Block header dump:  0x01000054
                 Object id on Block? Y
                 seg/obj: 0x111b1  csc: 0x00.13a163  itc: 3  flg: E  typ: 1 - DATA
                     brn: 0  bdba: 0x1000051 ver: 0x01 opc: 0
                     inc: 0  exflg: 0
                 
                 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
                0x01   0x0005.01a.000002c5  0x00c009ca.0180.19  --U-    2  fsc 0x0000.0013a24e
                0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
                0x03   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
                bdba: 0x01000054
                data_block_dump,data header at 0xda8227c
                ===============
                tsiz: 0x1f80
                hsiz: 0x16
                pbl: 0x0da8227c
                     76543210
                flag=--------
                ntab=1
                nrow=2
                frre=-1
                fsbo=0x16
                fseo=0x1c74
                avsp=0x1c5e
                tosp=0x1c5e
                0xe:pti[0]     nrow=2     offs=0
                0x12:pri[0]     offs=0x1c80
                0x14:pri[1]     offs=0x1c74
                block_row_dump:
                tab 0, row 0, @0x1c80
                tl: 768 fb: -----L-- lb: 0x1  cc: 255
                col  0: [ 2]  c1 02
                col  1: [ 2]  c1 02
                col  2: [ 2]  c1 02
                col  3: [ 2]  c1 02
                col  4: [ 2]  c1 02
                col  5: [ 2]  c1 02
                col  6: [ 2]  c1 02
                col  7: [ 2]  c1 02
                ..
                ..
                col 239: [ 2]  c1 02
                col 240: [ 2]  c1 02
                col 241: [ 2]  c1 02
                col 242: [ 2]  c1 02
                col 243: [ 2]  c1 02
                col 244: [ 2]  c1 02
                col 245: [ 2]  c1 02
                col 246: [ 2]  c1 02
                col 247: [ 2]  c1 02
                col 248: [ 2]  c1 02
                col 249: [ 2]  c1 02
                col 250: [ 2]  c1 02
                col 251: [ 2]  c1 02
                col 252: [ 2]  c1 02
                col 253: [ 2]  c1 02
                col 254: [ 2]  c1 02
                tab 0, row 1, @0x1c74
                tl: 12 fb: --H-F--- lb: 0x1  cc: 1
                nrid:  0x01000054.0
                col  0: [ 2]  c1 02
                end_of_block_dump
                End dump data block from file F:\APP\PAVAN\ORADATA\ORCL1\USERS01.DBF minblk 84 maxblk 84
                • 5. Re: Performance Impact of Intra-block chaining
                  Pavan Kumar
                  Aman could you post some comments on the demo ..
                  I am sure I am missing and confused a bit here

                  - Pavan Kumar N
                  • 6. Re: Performance Impact of Intra-block chaining
                    Vivek Sharma-Mumbai-Oracle
                    Pavan,

                    That block dump demo needs an explanation. I could not figure out any difference. May be I am confused too :;.

                    Regards
                    Vivek
                    • 7. Re: Performance Impact of Intra-block chaining
                      Jonathan Lewis
                      Vivek Sharma wrote:

                      A table with more that 255 columns, rows that have the data after the 255th column are like to be chained within the same block. This is intra-block chaining. These two pieces are chained together using the rowid's. Can anyone explain what is the performance impact of intra-block chaining ?
                      There is a trade-off to consider: If you chain (within the same block), you have to search the block by (secondary) rowid if you want to pick upi columns after 255 - and again for columns after 511 and 766. But the alternative is to run through a code-path that counts its way over the first 255 columns if you want the 256th column - so I don't supposed there's much difference in CPU cost between intra-block chaining and having very large column counts.

                      The big cost, and the bit where you spend your time, is deciding whether you really ought to have a table with so many columns at all. Then you worry about the best order for the columns to minimise walking along rows. Then you worry about the possible overheads of chaining.

                      Regards
                      Jonathan Lewis
                      http://jonathanlewis.wordpress.com
                      http://www.jlcomp.demon.co.uk


                      "Science is more than a body of knowledge; it is a way of thinking" Carl Sagan