7 Replies Latest reply: Oct 4, 2009 5:39 PM by NeedHelpDBA RSS

    Lob Chunk size defaulting to 8192

    NeedHelpDBA
      10.2.0.3 Enterprise on Linux x86-64

      DB is a warehouse and we truncate and insert and or update 90% of all records daily.

      I have a table with almost 8mill records and I ran the below to get max lenght for the lob.

      select max(dbms_lob.getlength(C500170300)) as T356_C500170300 from T356 where C500170300 is not null;

      The max for the lob was 404 bytes and the chunk size is 8192 and I was thinking that is casuing the lob to have around 50GB of wasted space that is being read during the updates. I tried to creating a duplicate table called T356TMP and lob with 512 and tried 1024 chunk size, both times it changes back to 8192.

      I thought it had to be a size that could be multipule or division of the tablespace block size.

      Based on what is happening, the smallest chunk size I can make is the tablespace block size, is this a true statement? or am I doing something wrong.

      Thanks
        • 1. Re: Lob Chunk size defaulting to 8192
          Centinul
          You may want to check out the documentation on LOBs again.

          [LOBs in tables (CHUNK):|http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14249/adlob_tables.htm#sthref202]
          *A chunk is one or more Oracle blocks.* You can specify the chunk size for the LOB when creating the table that contains the LOB. This corresponds to the data size used by Oracle Database when accessing or modifying the LOB value. Part of the chunk is used to store system-related information and the rest stores the LOB value.
          If the tablespace block size is the same as the database block size, then CHUNK is also a multiple of the database block size. The default CHUNK size is equal to the size of one tablespace block, and the maximum value is 32K.>

          HTH!
          • 2. Re: Lob Chunk size defaulting to 8192
            Jonathan Lewis
            SaveMeorYouDBA wrote:

            The max for the lob was 404 bytes and the chunk size is 8192 and I was thinking that is casuing the lob to have around 50GB of wasted space that is being read during the updates.

            Based on what is happening, the smallest chunk size I can make is the tablespace block size, is this a true statement? or am I doing something wrong.
            The smallest chunk size is the block size of the tablespace you put the LOB into.

            However, if your largest LOB is 404 bytes and you haven't declared the LOB with "disable storage in row", then the LOB will be stored in-line in the row. Have you checked the actual size of the LOB segment - you could look at view dba_lobs to get the table_name, column_name, and segment_name, then cross-check to dba_segments.

            If you feel that somewhere you are wasting space and things are slower than they ought to be, it's possible that you're running into problems of row migration - especially if you create the row without the LOB data and then update it with the LOB data, or allow the LOB data to start small and grow. Have you done anything to set a suitable value for PCTFREE on the table to allow free space in the block to allow rows to grow - the default setting is 10%, you may need something larger.

            (There is an entry on migrated rows in my [+*Oracle Glossary*+ |http://jonathanlewis.wordpress.com/2009/06/23/glossary/] if you want a longer descripion row migration)

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

            To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
            {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
            fixed format
            .
            
            "Science is more than a body of knowledge; it is a way of thinking" 
            Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
            • 3. Re: Lob Chunk size defaulting to 8192
              NeedHelpDBA
              Thanks for the replies. I should have inluded that I have looked at the size of the lob.

              BYTES     MAX-Data_Length     Chunk Size     Cache     STORAGE IN ROW
              65,336,770,560 404     8192     NO     DISABLED

              Record count 8253158

              Per the tool you help design. Statspack Analyzer
              You have 594,741 table fetch continued row actions during this period. Migrated/chained rows always cause double the I/O for a row fetch and "table fetch continued row" (chained row fetch) happens when we fetch BLOB/CLOB columns (if the avg_row_len > db_block_size), when we have tables with > 255 columns, and when PCTFREE is too small. You may need to reorganize the affected tables with the dbms_redefintion utility and re-set your PCTFREE parameters to prevent future row chaining.

              Elapsed Time was 2 hrs.

              I have not messed with PCTFREE yet, I assumed I had a bigger issue with how often the CLOB is accessed and that it is causing extra IO to read 7.5K of wasted space for each logical block read.

              About 85% of the Row length for this table are 384bytes, so I don't think Enable storage in row be best fit eaither,
              Reason:
              1.Block size is 8k, it would cause fletching since 4k and 384 would mean that only 1 complete row work each block, the 2nd row would be about 90 in the block and the rest in another block.
              2. since lob is only accessed during the main update/insert and only about 15% during read sql

              Would it make better since to create a tablespace for this smaller lob with a block size of 1024?
              • 4. Re: Lob Chunk size defaulting to 8192
                Jonathan Lewis
                SaveMeorYouDBA wrote:
                Thanks for the replies. I should have inluded that I have looked at the size of the lob.
                BYTES                  MAX-Data_Length     Chunk Size     Cache     STORAGE IN ROW
                65,336,770,560      404                     8192     NO     DISABLED
                Record count 8253158

                Per the tool you help design. Statspack Analyzer
                You have 594,741 table fetch continued row actions during this period. Migrated/chained rows always cause double the I/O for a row fetch and "table fetch continued row" (chained row fetch) happens when we fetch BLOB/CLOB columns (if the avg_row_len > db_block_size), when we have tables with > 255 columns, and when PCTFREE is too small. You may need to reorganize the affected tables with the dbms_redefintion utility and re-set your PCTFREE parameters to prevent future row chaining.
                I didn't help to design Statspack Analyzer - if I had, it wouldn't churn out so much rubbish. Have you seen anyone claim that I help in the design ? If so, can you let me know where that claim was published so I can have it deleted.

                Your LOBs are defined as nocache, disable storage in row.
                This means that
                a) each LOB will take at least one block in the lob segment, irrespective of how small the actual data might be
                b) the redo generated by writing one lob will be roughly the same as a full block, even if the data is a single byte.

                In passing - if you fetch an "out of row" LOB, the statistics do NOT report a "table fetch continued row". (Update: so if your continued fetches amount to a significant fraction of your accesses by rowid then, based on your other comments, you may have a pctfree issue to think about).


                >
                I have not messed with PCTFREE yet, I assumed I had a bigger issue with how often the CLOB is accessed and that it is causing extra IO to read 7.5K of wasted space for each logical block read.
                It's always a little tricky trying to decide what resources are being "wasted" and how to interpret "waste" - but it's certainly a good idea not to mess about with configuration details until you have identifed exactly where the problem is.

                About 85% of the Row length for this table are 384bytes, so I don't think Enable storage in row be best fit eaither,
                Reason:
                1.Block size is 8k, it would cause fletching since 4k and 384 would mean that only 1 complete row work each block, the 2nd row would be about 90 in the block and the rest in another block.
                2. since lob is only accessed during the main update/insert and only about 15% during read sql
                Where did the 4k come from ? Is this the avg_row_len when the LOB is stored out of line ? If that's the case, then you're really quite unlucky, it's one of those awkward sizes that makes it harder to pick a best block size - if you got it from somewhere else, then what it the avg_row_len currently ?

                When you query the data, how many rows do you expect to return from a single query ? How many of those rows do you expect to find packed close together ? I won't go into the arithmetic just yet, but if your row length is about 4KB, then you may be better off storing the LOBs in row anyway.
                Would it make better since to create a tablespace for this smaller lob with a block size of 1024?
                If it really makes sense to store your LOBs out of row, then you want to use the smallest possible block size (which is 2KB) - which means setting up a db_2k_cache_size and creating a tablespace of 2KB blocks. I would also suggest that you create the LOBs as CACHE rather then NOCACHE to reduce the I/O costs - particularly the redo costs.

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

                To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
                {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
                fixed format
                .
                
                "Science is more than a body of knowledge; it is a way of thinking" 
                Carl Sagan
                
                Edited by: Jonathan Lewis on Oct 4, 2009 7:27 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                • 5. Re: Lob Chunk size defaulting to 8192
                  NeedHelpDBA
                  let me see if I can re located that article for you.

                  For the 4k, I thought when you did in row enabled, it took 4k min even if it is 404bytes in size.

                  The avg row length is 384 bytes. Which I assumed I couldn't put both records in one block. Which would create more fetching in the long run.

                  If I do in row enable, will it only take the 404byest, just like any other column? or the 4k? or was 4k threshold just where it gets put in log_segif its larger then 4k?

                  We don't have many full table scans, which is why it is not read that much, but it does seem to impact our main import jobs on inserting and updates to the lob, with such a large amount of wasted space.

                  Looking for best design for this table.

                  Thanks again.

                  Edited by: SaveMeorYouDBA on Oct 4, 2009 3:16 PM
                  • 6. Re: Lob Chunk size defaulting to 8192
                    Jonathan Lewis
                    SaveMeorYouDBA wrote:

                    For the 4k, I thought when you did in row enabled, it took 4k min even if it is 404bytes and the rest of the data is 384 bytes. I couldn't put both records in one block.
                    The "4K" figure is the limit for in-row LOBs - if you insert a LOB greater than 4K (actually 3096 bytes) it is forced out of row.
                    If I do in row enable, will it only take the 404byest, just like any other column?
                    That's correct.
                    It sounds like you probably need to let the LOB be stored in row - it's going to save you a lot of space without costing you too much in extra I/Os for queries.


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

                    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
                    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
                    fixed format
                    .
                    
                    "Science is more than a body of knowledge; it is a way of thinking" 
                    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                    • 7. Re: Lob Chunk size defaulting to 8192
                      NeedHelpDBA
                      Thanks again it is much apperciated.

                      I'm testing it also, recreating table with in row enabled and will test both the read and insert jobs and compare them to the before test.