8 Replies Latest reply: Apr 29, 2014 3:54 AM by 새내기 RSS

    Why redo log generation on BLOB data with disable storage in row?

    새내기

      When Using CTAS(create table ..as select ) or Insert with append hint on  "enable storage in row with nologging" , amount of redo log is small.

      but, "disable storage in row with nologging", amout of redo log is very large.

      why?

       

      Enviorment:

      Version : 11.2.0.3.0

      Archive mode

       

      --create source table

      CREATE TABLE TEST_LOB

      (

        ID                      VARCHAR2(30 BYTE)     NOT NULL,

        CID                    VARCHAR2(32 BYTE)    NOT NULL,

        NAME                 VARCHAR2(4000),           

        PHOTO                   BLOB

        )

        LOB (PHOTO) STORE AS (

        TABLESPACE TS_TEST

        ENABLE       STORAGE IN ROW

        CHUNK       8192

        RETENTION

        NOCACHE

        NOLOGGING

        INDEX NOLOGGING

        )

        NOLOGGING;

       

      --insert on source table

      insert /*+  append */ into test_lob  ( id, cid, name, photo)

      select lpad(mod(id,100),30,'0') id

             , lpad(mod(id,10),32,'1') cid

             , lpad(id, dbms_random.value(10,20), 'n') name

             , decode(mod(nt,25),0,utl_raw.cast_to_raw( lpad(nt,dbms_random.value(1600,1638),'p') ),null)

        from ( select lpad(level,30,'0') id

                      , level nt

                  from dual

                  connect by level <= 1000000

                  );

       

      -- create target table with enable storage in row

      CREATE TABLE TEST_LOB_DUMP(   ID                      VARCHAR2(30 BYTE)     NOT NULL,

        CID                    VARCHAR2(32 BYTE)    NOT NULL,

        NAME                 VARCHAR2(4000),           

        PHOTO                   BLOB ) 

      LOB (PHOTO) STORE AS (

        TABLESPACE TS_TEST_LOB

        ENABLE       STORAGE IN ROW

        CHUNK       8192

        RETENTION

        NOCACHE

        NOLOGGING

      INDEX LOB_PHOTO_IDX  ( TABLESPACE TS_TEST_LOB )

         ) NOLOGGING

         TABLESPACE TS_TEST_LOB;

       

      -- insert with append hint on target table, amout of red log entry is little.

      insert /*+  append */  into test_lob_dump

      select * from test_lob;

       

      -- create target table with diable storage in row

      CREATE TABLE TEST_LOB_DUMP(   ID                      VARCHAR2(30 BYTE)     NOT NULL,

        CID                    VARCHAR2(32 BYTE)    NOT NULL,

        NAME                 VARCHAR2(4000),           

        PHOTO                   BLOB ) 

      LOB (PHOTO) STORE AS (

        TABLESPACE TS_TEST_LOB

        DISABLE STORAGE IN ROW

        CHUNK       8192

        RETENTION

        NOCACHE

        NOLOGGING

      INDEX LOB_PHOTO_IDX  ( TABLESPACE TS_TEST_LOB )

         ) NOLOGGING

         TABLESPACE TS_TEST_LOB;

       

      -- insert with append hint on target table, amout of red log entry is large.

      insert /*+  append */  into test_lob_dump

      select * from test_lob;

       

      can you tell me what make difference with redo entry generation of "enable, disable storage in row " option?

       

      thank you.

        • 1. Re: Why redo log generation on BLOB data with disable storage in row?
          rp0428

          What is the default log setting for the tablespace 'TABLESPACE TS_TEST_LOB'?


          • 2. Re: Why redo log generation on BLOB data with disable storage in row?
            새내기

            The default log option is NOLOGGING for TABLESPACE TS_TEST_LOB.

             

            Thank you~

            • 3. Re: Why redo log generation on BLOB data with disable storage in row?
              Hemant K Chitale

              What is the amount of redo generated in both cases ?  For how many rows and what total volume of LOBs size ?

               

               

              Hemant K Chitale

              • 4. Re: Why redo log generation on BLOB data with disable storage in row?
                새내기

                when inserting 1,000,000 rows with append hint

                 

                "enable storage in row "  : "redo block written" is average of 0.2 for just a few second.

                but,

                "disable storage in row " : "redo block written" is average of 938 for one minute.

                 

                thank you.

                • 5. Re: Why redo log generation on BLOB data with disable storage in row?
                  Jonathan Lewis

                  새내기 wrote:

                   

                  when inserting 1,000,000 rows with append hint

                   

                  "enable storage in row "  : "redo block written" is average of 0.2 for just a few second.

                  but,

                  "disable storage in row " : "redo block written" is average of 938 for one minute.

                   

                  thank you.

                   

                  Couldn't you just tell us the total volume written by the transaction - I'm guessing that's 928 blocks per second for about one minute, which looks about right if you're using the typical 512 byte redo block size.

                   

                  You've got 40,000 blobs, so for the "out of line" LOBs should see something a bit over 160,000 redo records with a redo size of about 35MB - which would be relatively small if you had "large" BLOBs.  When you go out of line you end up inserting one BLOB at a time - when you're in-line it's more like a simple table copy with array inserts.  Out of line with nologging requires 4 redo records


                  a) generate LOB id and insert lob index entry

                  b) set bit in LOB space management block

                  c) set invalidation marker for the block allocated in the LOB segment (then do nonlogged write)

                  d) update LOB index entry with block address information for the LOB it is pointing to.

                   

                  Ball-park figure - average size of redo record will be about 220 bytes (the invalidate one will be smaller, the lob index ones will be bigger).

                   

                  Regards

                  Jonathan Lewis

                  • 6. Re: Why redo log generation on BLOB data with disable storage in row?
                    새내기

                    redo block size : 512 byte

                     

                    When using LOGGING, "redo block written" is average of 3000 per second for about 2 minutes.

                    but,

                    When using NOLOGGING, "redo block written" is average of 900 per second for about 1 minutes.(redo size :  900 * 512 * 60 = 27648000 right?)

                     

                    I just realized that Out of line with nologging requires 4 redo records as you said

                    a) generate LOB id and insert lob index entry

                    b) set bit in LOB space management block

                    c) set invalidation marker for the block allocated in the LOB segment (then do nonlogged write)

                    d) update LOB index entry with block address information for the LOB it is pointing to.

                     

                    Ball-park figure - average size of redo record will be about 220 bytes (the invalidate one will be smaller, the lob index ones will be bigger).

                     

                    Now, i just wonder why 4 redo records was not required while using "enable storage in clause".

                    Thank you for your kind response.

                     

                    post :

                    "CREATE TABLE TEST_LOB_DUMP

                    ...

                    INDEX LOB_PHOTO_IDX  ( TABLESPACE TS_TEST_LOB )

                       ) NOLOGGING

                       TABLESPACE TS_TEST_LOB;"


                    must be modified by

                     

                    "... TABLESPACE TS_TEST_LOB NOLOGGING;"

                    • 7. Re: Why redo log generation on BLOB data with disable storage in row?
                      Jonathan Lewis

                      새내기 wrote:

                       

                      Now, i just wonder why 4 redo records was not required while using "enable storage in clause".

                       

                       

                      Your BLOBs are below the 3960 byte limit, so will all be stored inline - so there's not need to maintain an index (two records) or add space to the LOB segment (invalidation record and bitmap update records).  With inline the BLOB behaves just like any other column, so your table will simply grow (probably 16 blocks at a time) producing a couple of redo records (one bitmap update one range invalidation record) on each extension.

                       

                      Regards

                      Jonathan Lewis

                      • 8. Re: Why redo log generation on BLOB data with disable storage in row?
                        새내기

                        Your reply is great helpful to me.

                        Thank you.