1 2 Previous Next 19 Replies Latest reply: May 3, 2013 4:39 PM by jgarry RSS

    tables occupies (too) many blocks

    UserMB
      hello
      i have 2 tables, which even though are similar in number of rows occupy very difference space
      table A with around 3 milion records, and 10 columns occupies ~ 28 000 blocks and is ~ 222 MB size
      table B with around 4,2 milion records and 24 columns occupies ~ 84 0000 blocks and is ~ 654MB size
      why there is such huge difference in them?
      i was expecting the increase of ~ 40%, not 200%;
      i would appreciate on some tips on what i should check and if there is anything i can do about this;
      thanks
      rgds
        • 1. Re: tables occupies (too) many blocks
          sb92075
          UserMB wrote:
          hello
          i have 2 tables, which even though are similar in number of rows occupy very difference space
          table A with around 3 milion records, and 10 columns occupies ~ 28 000 blocks and is ~ 222 MB size
          table B with around 4,2 milion records and 24 columns occupies ~ 84 0000 blocks and is ~ 654MB size
          why there is such huge difference in them?
          i was expecting the increase of ~ 40%, not 200%;
          i would appreciate on some tips on what i should check and if there is anything i can do about this;
          thanks
          rgds
          I suspect that many rows were DELETED from table B in the past.
          • 2. Re: tables occupies (too) many blocks
            sybrand_b
            10 columns as opposed to 24 columns.
            What is the average length of each row?
            Likely you are comparing apples with pears.
            Apart from that, disk is cheap, and 654 Mb is currently not that big,
            if you start micromanaging the database by -God forbid- listening to myths and fairytales to 'defragment' the database, whatever that may mean,
            this will be much more expensive for your employer as compared to buying another disk.

            Use the statistics in the dictionary and/or use dbms_space to see the actual space occupied.

            As you don't provide any details on your database, and the answer is version and database organisation dependent, I can't provide a complete answer.

            -------------
            Sybrand Bakker
            Senior Oracle DBA
            • 3. Re: tables occupies (too) many blocks
              UserMB
              hello
              in the past the table B was getting truncated every day;
              every day a new set of 4.2 milion records was inserted;
              now (dont ask why....) we need to update/insert the table, instead of truncate/insert, which was very fast;
              with the update its taking ages of course i.e. ~ 30 minutes, so i was looking at how is this table different from table A, which also went transition from truncate/insert to update/insert, but for table A it takes up to 10 minutes, which is acceptable (30 minutes not);
              rgds
              • 4. Re: tables occupies (too) many blocks
                sb92075
                UserMB wrote:
                hello
                in the past the table B was getting truncated every day;
                every day a new set of 4.2 milion records was inserted;
                now (dont ask why....) we need to update/insert the table, instead of truncate/insert, which was very fast;
                with the update its taking ages of course i.e. ~ 30 minutes, so i was looking at how is this table different from table A, which also went transition from truncate/insert to update/insert, but for table A it takes up to 10 minutes, which is acceptable (30 minutes not);
                rgds
                HOW To Make TUNING request
                SQL and PL/SQL FAQ
                • 5. Re: tables occupies (too) many blocks
                  1002948
                  Try this ' Alter table tablename shrink space' wil bring down hwm... and there might be hevy dml on table so that could be the reason

                  Edited by: Varun Rajesh on May 3, 2013 7:18 AM
                  • 6. Re: tables occupies (too) many blocks
                    sb92075
                    Varun Rajesh wrote:
                    Try this ' Alter table tablename shrink space' wil reduce num of blocks consumed it there is any fragmentation
                    pure unadulterated NONSENSE!

                    Blocks consumed remains UNCHANGED!
                    • 7. Re: tables occupies (too) many blocks
                      UserMB
                      hello
                      here some more details
                      TABLE_NAME     PCT_FREE     PCT_USED     INITIAL_EXTENT     NEXT_EXTENT     PCT_INCREASE     NUM_ROWS     BLOCKS     EMPTY_BLOCKS     AVG_ROW_LEN
                      tableA                    10                         65536          1048576                   3017815               28680     0                     70
                      tableB                    10                         65536          1048576                   4187659               83218     0                     137
                      db standard edition one, 10.2.0.5
                      rgds
                      • 8. Re: tables occupies (too) many blocks
                        UserMB
                        hello
                        to my knowledeg i have not requested the tuning of the insert statemtent
                        nevettheless than you for you valuable contribution
                        rgds
                        • 9. Re: tables occupies (too) many blocks
                          sb92075
                          UserMB wrote:
                          hello
                          here some more details
                          TABLE_NAME     PCT_FREE     PCT_USED     INITIAL_EXTENT     NEXT_EXTENT     PCT_INCREASE     NUM_ROWS     BLOCKS     EMPTY_BLOCKS     AVG_ROW_LEN
                          tableA                    10                         65536          1048576                   3017815               28680     0                     70
                          tableB                    10                         65536          1048576                   4187659               83218     0                     137
                          db standard edition one, 10.2.0.5
                          rgds
                          tableB has 1/3 more rows which are about TWICE as long as tableA; so you should NOT be surprised that tableB is larger than tableA
                          • 10. Re: tables occupies (too) many blocks
                            UserMB
                            I am not surprised it is;
                            i am surprised its bigger by 200% , thus my question from the orignal post;
                            rgds
                            • 11. Re: tables occupies (too) many blocks
                              sb92075
                              UserMB wrote:
                              I am not surprised it is;
                              i am surprised its bigger by 200% , thus my question from the orignal post;
                              rgds
                                1* select (28680      + (28680        )/3)*2 from dual
                              SQL> /
                              
                              (28680+(28680)/3)*2
                              -------------------
                                            76480
                              • 12. Re: tables occupies (too) many blocks
                                Justin_Mungal
                                sb92075 wrote:
                                Varun Rajesh wrote:
                                Try this ' Alter table tablename shrink space' wil reduce num of blocks consumed it there is any fragmentation
                                pure unadulterated NONSENSE!

                                Blocks consumed remains UNCHANGED!
                                Can you tell us why it's pure, unadulterated nonsense? I've tested shrink space before and I've seen it reduce the block count. Here is a test case I created a while back:
                                set linesize 150
                                
                                col avg_row_len format 999,999,999,999
                                col num_rows format 999,999,999,999
                                col blocks format 999,999,999,999
                                col block_bytes format 999,999,999,999
                                col est_row_bytes format 999,999,999,999
                                
                                drop table justin.test;
                                
                                create table justin.test as (select * from dba_objects);
                                
                                exec dbms_stats.gather_table_stats('JUSTIN', 'TEST');
                                
                                select tb.AVG_ROW_LEN, tb.NUM_ROWS, tb.BLOCKS,
                                 tb.blocks * ts.block_size block_bytes,
                                 tb.AVG_ROW_LEN * tb.NUM_ROWS est_row_bytes,
                                 100*(1-(tb.AVG_ROW_LEN * tb.NUM_ROWS)/(tb.blocks * ts.block_size)) est_free_space_pct
                                from dba_tables tb, dba_tablespaces ts
                                where tb.tablespace_name = ts.tablespace_name
                                 and owner='JUSTIN'
                                 and table_name='TEST'
                                /
                                
                                delete from justin.test where owner='SYS';
                                
                                exec dbms_stats.gather_table_stats('JUSTIN', 'TEST');
                                
                                select tb.AVG_ROW_LEN, tb.NUM_ROWS, tb.BLOCKS,
                                 tb.blocks * ts.block_size block_bytes,
                                 tb.AVG_ROW_LEN * tb.NUM_ROWS est_row_bytes,
                                 100*(1-(tb.AVG_ROW_LEN * tb.NUM_ROWS)/(tb.blocks * ts.block_size)) est_free_space_pct
                                from dba_tables tb, dba_tablespaces ts
                                where tb.tablespace_name = ts.tablespace_name
                                 and owner='JUSTIN'
                                 and table_name='TEST'
                                /
                                
                                alter table justin.test enable row movement;
                                
                                alter table justin.test shrink space;
                                
                                exec dbms_stats.gather_table_stats('JUSTIN', 'TEST');
                                
                                select tb.AVG_ROW_LEN, tb.NUM_ROWS, tb.BLOCKS,
                                 tb.blocks * ts.block_size block_bytes,
                                 tb.AVG_ROW_LEN * tb.NUM_ROWS est_row_bytes,
                                 100*(1-(tb.AVG_ROW_LEN * tb.NUM_ROWS)/(tb.blocks * ts.block_size)) est_free_space_pct
                                from dba_tables tb, dba_tablespaces ts
                                where tb.tablespace_name = ts.tablespace_name
                                 and owner='JUSTIN'
                                 and table_name='TEST'
                                /
                                
                                Table dropped.
                                
                                SQL> SQL>
                                Table created.
                                
                                SQL> SQL>
                                PL/SQL procedure successfully completed.
                                
                                SQL> SQL>   2    3    4    5    6    7    8    9
                                     AVG_ROW_LEN         NUM_ROWS           BLOCKS      BLOCK_BYTES    EST_ROW_BYTES EST_FREE_SPACE_PCT
                                ---------------- ---------------- ---------------- ---------------- ---------------- ------------------
                                              97           75,279            1,100        9,011,200        7,302,063          18.966808
                                
                                SQL> SQL>
                                31963 rows deleted.
                                
                                SQL> SQL>
                                PL/SQL procedure successfully completed.
                                
                                SQL> SQL>   2    3    4    5    6    7    8    9
                                     AVG_ROW_LEN         NUM_ROWS           BLOCKS      BLOCK_BYTES    EST_ROW_BYTES EST_FREE_SPACE_PCT
                                ---------------- ---------------- ---------------- ---------------- ---------------- ------------------
                                              98           43,316            1,100        9,011,200        4,244,968         52.8923118
                                
                                SQL> SQL>
                                Table altered.
                                
                                SQL> SQL>
                                Table altered.
                                
                                SQL> SQL>
                                PL/SQL procedure successfully completed.
                                
                                SQL> SQL>   2    3    4    5    6    7    8    9
                                     AVG_ROW_LEN         NUM_ROWS           BLOCKS      BLOCK_BYTES    EST_ROW_BYTES EST_FREE_SPACE_PCT
                                ---------------- ---------------- ---------------- ---------------- ---------------- ------------------
                                              98           43,316              621        5,087,232        4,244,968         16.5564299
                                The table went from consuming 1100 blocks to 621 blocks.

                                Edited by: Justin Mungal on May 3, 2013 9:31 AM - The arithmetic is based on an article by Jonathan Lewis: http://jonathanlewis.wordpress.com/2007/11/23/table-rebuilds/.

                                Edited by: Justin Mungal on May 3, 2013 10:02 AM - Re-ran test case and corrected formatting. Executed on 11.2.0.3.
                                • 13. Re: tables occupies (too) many blocks
                                  Martin Preiss
                                  your tableB has 4187659 rows with an average length of 137 bytes. Multiplying the values (and assuming that there is no compression) I get ~ 547 MB and that's not to far from your gross size of 654MB so I would not worry.

                                  Edited by: Martin Preiss on May 3, 2013 4:32 PM
                                  • 14. Re: tables occupies (too) many blocks
                                    Justin_Mungal
                                    sybrand_b wrote:
                                    10 columns as opposed to 24 columns.
                                    What is the average length of each row?
                                    Likely you are comparing apples with pears.
                                    Apart from that, disk is cheap, and 654 Mb is currently not that big,
                                    if you start micromanaging the database by -God forbid- listening to myths and fairytales to 'defragment' the database, whatever that may mean,
                                    this will be much more expensive for your employer as compared to buying another disk.

                                    Use the statistics in the dictionary and/or use dbms_space to see the actual space occupied.

                                    As you don't provide any details on your database, and the answer is version and database organisation dependent, I can't provide a complete answer.

                                    -------------
                                    Sybrand Bakker
                                    Senior Oracle DBA
                                    Can you tell me why people around here freak out whenever they hear the word fragmentation? It seems like it all depends on your definition of fragmentation.

                                    If you delete half of the rows in a table, and it still occupies the same amount of space, couldn't one consider that fragmented? A real-world implication of this is that the HWM is in the same place, and so full table scans pre and post delete will take the same amount of time.

                                    I'm not saying that it even matters; it purely depends on your situation and data access patterns. I'm just trying to understand why this is such a source of controversy.
                                    1 2 Previous Next