1 2 Previous Next 16 Replies Latest reply: Dec 30, 2009 11:58 PM by Rajesh Menon RSS

    Space Management: How to calculate UNDO space need to shrink a segment ?

    Rajesh Menon
      Hello All,

      We used to shrink Tables using the commands -

      -- Enable row movement.
      ALTER TABLE <owner>.<table> ENABLE ROW MOVEMENT;

      -- Recover space for the object and all dependant objects.
      ALTER TABLE <owner>.<table> SHRINK SPACE CASCADE;

      We use to check the current UNDO tablespace usage, before these commands.

      But some times this will fail with the below error !


      ERROR at line 1:
      ORA-00604: error occurred at recursive SQL level 1
      ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO'

      I suspect there should be enough UNDO space for this shrinking (At lease the size of the table, i think).

      How can we calculate, the exact UNDO space required for a table to shrink..

      Regards,
      -Rajesh Menon
        • 1. Re: Space Management: How to calculate UNDO space need to shrink a segment ?
          Pavan Kumar
          hi,

          What is the size of undo

          SELECT dt.tablespace_name, dt.contents,
          ddf.file_name, ddf.bytes/1024/1024 size_MEG
          FROM dba_tablespaces dt,
          dba_data_files ddf
          WHERE dt.tablespace_name = ddf.tablespace_name
          AND dt.contents = 'UNDO';

          Are the indexes available on table, if yes then try to drop and recreate.

          - Pavan Kumar N
          Oracle 9i/10g - OCP
          http://oracleinternals.blogspot.com/
          • 2. Re: Space Management: How to calculate UNDO space need to shrink a segment ?
            Rajesh Menon
            I Hope UNDO Tablespace size does not matter here.
            UNDO tablespace will be used during compression of a segment.There should be some free space before during Shrinking.
            But at the same time iit will not use full UNDO Tablespace space for a single segment compression.

            What i need to know is , how can i calculate the UNDO space REQUIRED FOR A SINGLE SEGMENT (TABLE) COMPRESSION !


            Before compression, I used to check UNDO usage by using the following queries -

            -- Used percent in UNDO Tablespace
            SELECT used_percent
            FROM dba_tablespace_usage_metrics
            WHERE tablespace_name =
            (SELECT value
            FROM v$parameter
            WHERE name ='undo_tablespace');

            -- Free Space in UNDO Tablespace
            SELECT NVL(SUM(bytes)/1024/1024,0) mb
            FROM dba_free_space
            WHERE tablespace_name =
            (SELECT value
            FROM v$parameter
            WHERE name ='undo_tablespace');

            I Hope it is clear now !

            Edited by: Rajesh Menon on Dec 22, 2009 8:50 PM
            • 3. Re: Space Management: How to calculate UNDO space need to shrink a segment ?
              Pavan Kumar
              Hi,

              Refer to undo space calculation formula or equation

              http://oraclepoint.com/oralife/2008/10/06/how-to-calculate-size-of-undo-tablespace-and-undo-retention/
              http://www.akadia.com/services/ora_optimize_undo.html

              try to check PL/sql interface for undo advisor
              http://download.oracle.com/docs/cd/B12037_01/server.101/b10739/undo.htm#BABJCFFE

              - Pavan Kumar N
              Oracle 9i/10g - OCP
              http://oracleinternals.blogspot.com/

              Edited by: Pavan Kumar on Dec 23, 2009 10:31 AM
              • 4. Re: Space Management: How to calculate UNDO space need to shrink a segment ?
                sb92075
                ALTER TABLE <owner>.<table> SHRINK SPACE CASCADE;
                If you never issue command above, then ZERO undo is required.

                I seriously question the usefulness of this whole task.

                Post results of
                SELECT * from v$version
                • 5. Re: Space Management: How to calculate UNDO space need to shrink a segment ?
                  Rajesh Menon
                  Pavan ,

                  Still you didn't get my question, i think.

                  I don't need an optimal UNDO retention / resizing of UNDO Tablespace in database level.

                  My question is simple.

                  Suppose I need to compress one table say test.

                  SQL> create table test (id number,name varchar2(30)) tablespace test ;

                  Table created.

                  SQL> begin
                  2 for i in 1..100000 loop
                  3 insert into test values (i,i);
                  4 end loop;
                  5 end;
                  6 /

                  PL/SQL procedure successfully completed.

                  SQL> commit;

                  Commit complete.

                  SQL> select count(*) from test;

                  COUNT(*)
                  ----------
                  100000

                  SQL> select sum(bytes)/1024/1024 mb from dba_segments where segment_name='TEST';

                  MB
                  ----------
                  2.0625

                  Now suppose after some delete statements, i need to compress this table.
                  Here my current UNDO usage is -

                  TABLESPACE_NAME MB MAXMB USED_PERCENT
                  ------------------------------ ---------- ---------- ------------
                  UNDO 100 0 88.94

                  SQL> select nvl(sum(bytes)/1024/1024,0) mb from dba_free_space where tablespace_name =(select value from v$parameter where name ='undo_tablespace');

                  MB
                  ----------
                  3

                  So here i have 3 MB UNDO free in my UNDO tablespace. And the segment i need to compress is of size 2 MB.
                  Here how can i calculate, the size req in UNDO tablesapce for compressing this table of size 2 MB. Is there any calculations provided by Oracle ?
                  Or just we can simply take the size of segment it self ?

                  Anyways even if the Tablespace is totally free, it will not use 100 MB for shrinking a segment of size 2 GB.
                  And i don't want to resize my UNDO Tablespace size at all ( Say it is optimal to my applications)
                  • 6. Re: Space Management: How to calculate UNDO space need to shrink a segment ?
                    Rajesh Menon
                    SQL> select * from v$version;

                    BANNER
                    ----------------------------------------------------------------
                    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
                    PL/SQL Release 10.2.0.4.0 - Production
                    CORE 10.2.0.4.0 Production
                    TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
                    NLSRTL Version 10.2.0.4.0 - Production
                    • 7. Re: Space Management: How to calculate UNDO space need to shrink a segment ?
                      Pavan Kumar
                      Hi,
                      So here i have 3 MB UNDO free in my UNDO table space. And the segment i need to compress is of size 2 MB.
                      Here how can i calculate, the size required in UNDO tablespace for compressing this table of size 2 MB. Is there any calculations provided by Oracle ?
                      Or just we can simply take the size of segment it self ?
                      
                      Anyways even if the Table space is totally free, it will not use 100 MB for shrinking a segment of size 2 GB.
                      And i don't want to re size my UNDO Table space size at all ( Say it is optimal to my applications)
                      AFAIK, you might have enabled the undo_table space management to AUTO and persists some retention period.
                      See, based on your table size, in above stated example, if the required undo space is not there, then overwriting the things in table space can happening based on guaranteed period retention. You might come across "unable to extent" - based on Optimal sizing. I suggest in case any bulk deletes - go for Nologging. Estimating the size of compression - better go export test for that specific table and from that you can estimate upto some extent.

                      - Pavan Kumar N
                      Oracle 9i/10g - OCP
                      http://oracleinternals.blogspot.com/
                      - Pavan Kumar N
                      Oracle 9i/10g - OCP
                      http://oracleinternals.blogspot.com/
                      • 8. Re: Space Management: How to calculate UNDO space need to shrink a segment ?
                        Rajesh Menon
                        Pavan,

                        1) Some times we may not shrink the segment immediately after the deletion (Eg: Purging Perfstat tables using sppurge.sql)
                        Even if we give NOLOGGING option. Also in case of production database, there might be a chance that other users also using UNDO 24X7.
                        So if we can find out the UNDO space needed to shrink the table, we can do it when we get enough space in UNDO tablesapce.

                        2) Export is not a regular option for multiple Tables having millions of rows in a Production system.
                        Also can you please explain how can the Size Estimation of compression (during export) help us to calculate the target UNDO space for compression ?

                        Thanks in advance

                        Edited by: Rajesh Menon on Dec 22, 2009 10:22 PM

                        Edited by: Rajesh Menon on Dec 22, 2009 10:23 PM
                        • 9. Re: Space Management: How to calculate UNDO space need to shrink a segment ?
                          sb92075
                          If the tables grow, have rows deleted, are shrunk & only grow again.
                          What measurable benefit is gained by SHRINK?
                          Why not let the space be reused WITHOUT having to expand only to be manually SRHUNK & to grow again?
                          • 10. Re: Space Management: How to calculate UNDO space need to shrink a segment ?
                            Rajesh Menon
                            I am not getting your points..

                            anyways as per me, SHRINK will only reduce the steps in REORG..

                            like MOVING segments from One tablesapce to another and MOVe it back,
                            EXPORT-TRUNCATE-IMPORT or EXPORT-DROP-TRUNCATE

                            In case of SHRINK, Segments will be available to users all the time.
                            • 11. Re: Space Management: How to calculate UNDO space need to shrink a segment ?
                              sb92075
                              In case of SHRINK, Segments will be available to users all the time.
                              If you do not SHRINK, Segments will be available to users all the time.
                              So what is gained by SHRINK?
                              • 12. Re: Space Management: How to calculate UNDO space need to shrink a segment ?
                                Girish Sharma
                                Rajesh,

                                Alter table ... Shrink will generate significant REDO and UNDO because same has been said by Hemant sir at this link:
                                "Yes, the ALTER TABLE .. SHRINK does generate signficant Undo and Redo."
                                Is alter table shrink space a logged operation?

                                And
                                By Tom sir at this link also :
                                http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2860243091362

                                So, now there is no doubt about it; whatever other says...

                                Now question is we are shrinking the table but since UNDO size is not fitting. So simple and less time taking step is export and import; rather than digging into UNDO segment, extent and their sizing.

                                Regards
                                Girish Sharma
                                • 13. Re: Space Management: How to calculate UNDO space need to shrink a segment ?
                                  Jonathan Lewis
                                  As others have pointed out, the SHRINK command is not necessarily something you want to use - for various reasons.
                                  But if you want a very rough guide to UNDO generated, you just have to remember that the shrink command deletes rows from the end of the table and inserts them at the beginning, updating indexes as it goes so (VERY ROUGHLY):

                                  Delete row == 100 bytes plus row length
                                  Insert row == 100 bytes
                                  Delete index entry == 100 bytes plus key length
                                  Insert index entry == 100 bytes plus key length

                                  So total undo = 200 + row length + 200 * index_count + sum(all key lengths) ... for each row moved.
                                  VERY simple-minded approximation: rows moved = rows deleted since last shrink.


                                  Thinking about what this means: If you move a single row which has two indexes, the undo overhead is 600 bytes before you allow for the real data.
                                  If your rows are small, then the undo requirement is going to be very large compared to the table size.
                                  (And don't forget that all the undo gets logged as part of the redo)

                                  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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                  • 14. Re: Space Management: How to calculate UNDO space need to shrink a segment
                                    Rajesh Menon
                                    Thank you for your comments.

                                    My requirement is to purge snapshots in PERFSTAT schema and REORG the top 5 segments.
                                    We have a cronjob to purge the data. after purging, we use to REORG the PERFSTAT schema by using EXPORT-TRUNCATE-IMPORT.
                                    Now we have prepared a PL/SQL block for automating this. our script will shrink the Top N tables (size wise) in PERFSTAT schema, once the purging complete.
                                    But for giving proper exceptions for this, i need to know about the appoximate UNDO space used in UNDO tablespace, during this shrinking.

                                    Thats why i posted this question :)
                                    1 2 Previous Next