1 2 Previous Next 20 Replies Latest reply: Oct 26, 2012 5:52 AM by viconstg Go to original post RSS
      • 15. Re: How to limit the table size?
        viconstg
        I've investigated the problem further, and I've noticed that the real portions of deleted rows include 5-50 K records, i.e. the upper limit of 100 K is not achieved in practice. The COMMIT issued immediately after DELETE in the cleanup procedure, and this deletion works 0.5-3 minutes. Many INSERTs performed within 1 minutes, but the growth of table has the correlation with the number of new rows but not with the number of deleted rows or the duration of deletion.
        Within last 24 hours, the table size increased by 1.5G, although the space usage shows a lot of free space in used blocks:
        Size of blocks with:
        0-25% free space: 5251072
        25-50% free space: 23658496
        50-75% free space: 36896768
        75-100% free space: 110398971904
        full blocks: 50359533568
        Thus, the problem still alive. Please advise me how to limit the table size. Why 100 Gb of blocks with 75-100% free space remain unused? Is it possible to force the usage of this space?
        • 16. Re: How to limit the table size?
          Jonathan Lewis
          viconstg wrote:
          Size of blocks with:
          0-25% free space: 4726784
          25-50% free space: 17301504
          50-75% free space: 24920064
          75-100% free space: 102418669568
          full blocks: 54761594880
          Based on the pattern (and knowledge) of free space, your comment about the table "being unavailable" for 53 minutes during a shrink, and your comments about the volume and batching of data, I'd be inclined to guess that you're inserting with the /*+ append */ hint - which puts the new rows above the segment high water mark, and doesn't let Oracle re-use the free space in the table.

          Regards
          Jonathan Lewis
          • 17. Re: How to limit the table size?
            viconstg
            Thank you very much for your reply sir. Is there any way to catch such insertions? like audit or so?... I've tried to query v$sql.sql_fulltext but I've found nothing, i.e. no insert queries which used /*+ APPEND */ hint.
            • 18. Re: How to limit the table size?
              viconstg
              Finally, the size of problem table is stabilized with the following space usage:
              Size of blocks with:
              0-25% free space: 761856
              25-50% free space: 3260416
              50-75% free space: 3661824
              75-100% free space: 118116098048
              full blocks: 45070581760
              There is no /*+ APPEND */ insertions into that table, therefore I consider the current space usage as "normal". If somebody knows the way to instruct Oracle to be more aggressive in filling partially free blocks then please let me know.
              Thanks to all.
              • 19. Re: How to limit the table size?
                Jonathan Lewis
                viconstg wrote:
                Finally, the size of problem table is stabilized with the following space usage:
                Size of blocks with:
                0-25% free space: 761856
                25-50% free space: 3260416
                50-75% free space: 3661824
                75-100% free space: 118116098048
                full blocks: 45070581760
                There is no /*+ APPEND */ insertions into that table, therefore I consider the current space usage as "normal". If somebody knows the way to instruct Oracle to be more aggressive in filling partially free blocks then please let me know.
                The best answer is probably to patch to 11.2.0.3 - but you could check MOS for any outstanding patches for 11.2.0.3 that relate to space usage under ASSM. There has always been the potential for problems with large inserts and deletes in ASSM (more so than with freelists) because of the difficulty of deciding when to update the bitmap to reflect the space usage - whatever strategy you use there's a way for someone to find a boundary condition that results in either a space or a performance anomaly. I think the Oracle developers have been working on the underlying issue for some time.

                You might look at the dbms_space_admin package (or dbms_space) - one of them has a procedure for fixing up bitmaps
                that don't correspond to the actual space usage in the segment. Depending on your pattern of use, you might consider including a call to the procedure after any VERY LARGE delete. I'd have a chat with Oracle support about that idea before implementing it, though, I've tested the package a couple of times in the past but never used it in production.

                Regards
                Jonathan Lewis
                • 20. Re: How to limit the table size?
                  viconstg
                  Thank you very much sir. I'll take a look at dbms_space_admin.
                  1 2 Previous Next