7 Replies Latest reply: Feb 11, 2013 10:37 AM by 926398 RSS

    question about lob partiton growth?

    926398
      We are running oracle 11.2.0.3 on linux and we have a very large table that uses range partitoning by day. We changed the structure of the table which used to be "enable storage in row"
      and now we are using STORE AS SECUREFILE (
      TABLESPACE SYSTEM_TRACE_LOB
      DISABLE STORAGE IN ROW

      Since making this change, now in the tablespace SYSTEM_TRACE_LOB each day we have a new lob partiton and each day it seems to be growing bigger each day?
      For instance, on day one after making the storage clause change the lob partiton was 4gb then the next day there is a new lob partiton and it is 19gb then the next day the new lob partiton was 55gb and each day it has grown an average of 2 to 5 gb each day. Todays lob partiton is now at 132gb?
      Our volume of rows/data inserted into the table has not grown it has remained the same so I do not understand why this lob partiton seems to be accumulating more and more data each day?
      Can someone please explain why this is happening? It is causing us much pain trying to keep up with the storage required to store this data.
        • 1. Re: question about lob partiton growth?
          rp0428
          >
          We are running oracle 11.2.0.3 on linux and we have a very large table that uses range partitoning by day. We changed the structure of the table which used to be "enable storage in row"
          and now we are using STORE AS SECUREFILE (
          TABLESPACE SYSTEM_TRACE_LOB
          DISABLE STORAGE IN ROW

          Since making this change, now in the tablespace SYSTEM_TRACE_LOB each day we have a new lob partiton and each day it seems to be growing bigger each day?
          For instance, on day one after making the storage clause change the lob partiton was 4gb then the next day there is a new lob partiton and it is 19gb then the next day the new lob partiton was 55gb and each day it has grown an average of 2 to 5 gb each day. Todays lob partiton is now at 132gb?
          Our volume of rows/data inserted into the table has not grown it has remained the same so I do not understand why this lob partiton seems to be accumulating more and more data each day?
          Can someone please explain why this is happening? It is causing us much pain trying to keep up with the storage required to store this data.
          >
          What is the growth pattern of that tablespace itself?

          Can you post the queries and results you are using to determine the size of the lob partitions? And post the query and results showing the tablespace size.
          • 2. Re: question about lob partiton growth?
            jgarry
            Here's an interesting thread about LOB size: http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/984efdfc25e406a2/
            Jonathan has some more clues on his blog http://jonathanlewis.wordpress.com/category/oracle/infrastructure/lobs/
            • 3. Re: question about lob partiton growth?
              Dafo
              Hi,
              you should check your "retention" policy for the lob segment. Oracle keeps undo data of lob segments in the same lobs with data.
              If you omit PCTVERSION parameter in lob definition (which limits the size of a lob space saved for undo data) your lob segments will inherit the database RETENTION parameter (in auto undo configuration).
              - check how often you update lob segments data
              - check PCTVERSION parameter
              - you can compare lob segment size to real data inside lob (dbms_lob.getlength()), rest is acutally free space or undo pages
              - you can shrink your overgrowth lob segment (due to retention/undo in-lob generation)
              • 4. Re: question about lob partiton growth?
                mtefft
                With LOB segments (including SECUREFILES), each LOB requires at least one CHUNKSIZE which must be a multiple of your blocksize.

                So if you have many small LOBs, that originally fit very well in the table segment, you will find that moving them out of the tables segment (i.e. SECUREFILES or DISABLE STORAGE IN ROW) will result in many, many blocks in your LOB segment with very little actual data in them.
                • 5. Re: question about lob partiton growth?
                  926398
                  I am just looking in toad using the tablespaces view and it lists out all of the objects in the tablespace I am not sure what query is being used to generate the list.
                  I see a lob partition called SYS_LOB0000225050C00016$$:SYS_LOB_P20062 that is taking up 132gb but I am not sure what query to run to find out what partiton it is related to?
                  • 6. Re: question about lob partiton growth?
                    rp0428
                    >
                    I am just looking in toad using the tablespaces view and it lists out all of the objects in the tablespace I am not sure what query is being used to generate the list.
                    I see a lob partition called SYS_LOB0000225050C00016$$:SYS_LOB_P20062 that is taking up 132gb but I am not sure what query to run to find out what partiton it is related to?
                    >
                    Look at the ALL_TAB_PARTITIONS system view.

                    See the Database reference to see the description of the columns available.
                    http://docs.oracle.com/cd/E14072_01/server.112/e10820/statviews_2108.htm

                    That view can provide table name/owner, partition name, num_rows, blocks, empty_blocks, last_analyzed etc.

                    Post the relevant information about the blocks and extents that have been allocated.

                    If necessary gather stats first to make sure the info is current.
                    • 7. Re: question about lob partiton growth?
                      926398
                      Thanks everyone for all the great replies.