Forum Stats

  • 3,837,191 Users
  • 2,262,234 Discussions
  • 7,900,218 Comments

Discussions

Why is there such a drastic difference between space allocated and space that should be allocated?

I must be completely missing something or misunderstanding something. I've had an SR opened on this issue and gone round and round with the engineer. The output of these queries is drastically different and I don't understand why.

I've put various queries and their output in the attachment which is probably easier to read.


This issue came to light by executing a form of this query to identify tables that needed reorged:

select owner,table_name,round((blocks*16),2)||'kb' "Fragmented size",

round((num_rows*avg_row_len/1024),2)||'kb' "Actual size", round((blocks*16),2)-

round((num_rows*avg_row_len/1024),2)||'kb' "Free space",

round(((round((blocks*16),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*16),2))*100 -10,2) "reclaimable

space % " from dba_tables where table_name ='INTERR_SKUPLANNPARAM' AND OWNER = 'IGPMGR';

Fragmented size is ~48Gb

Actual size is ~18Gb

Free space is ~30Gb

Reclaimable: 51%


I reorged the table with several different methods, but no matter what I do, ~40 - 48Gb of space gets allocated for this table.


The engineer assigned to my SR asked me to run this query from Doc ID 2132004.1 :


set serveroutput on

declare

v_unformatted_blocks number;

v_unformatted_bytes number;

v_fs1_blocks number;

v_fs1_bytes number;

v_fs2_blocks number;

v_fs2_bytes number;

v_fs3_blocks number;

v_fs3_bytes number;

v_fs4_blocks number;

v_fs4_bytes number;

v_full_blocks number;

v_full_bytes number;

begin

dbms_space.space_usage ('IGPMGR', 'INTERR_SKUPLANNPARAM', 'TABLE', v_unformatted_blocks,

v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,

v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);

dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);

dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);

dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);

dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);

dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);

dbms_output.put_line('Full Blocks = '||v_full_blocks);

end;

/


Here is the output:

Unformatted Blocks = 1056

FS1 Blocks = 0

FS2 Blocks = 1

FS3 Blocks = 0

FS4 Blocks = 336

Full Blocks = 3060023


How can nearly all of the blocks be full if there are  305561679 rows with AVG_ROW_LEN = 63? Calculates to 19250385777 bytes or ~17.92Gb

Best Answers

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,995 Blue Diamond
    edited May 20, 2022 5:16PM Answer ✓


    Partial answer to the row-length oddity.

    If the avg_col_len for a column is zero Oracle's calculation of the avg_row_len doesn't allow for the length byte that has to be there to say that the column length is zero. This suggests that quite a lot of the columns in your table are null for most of their rows. As a quick check:

    select  column_name, num_nulls
    from    user_tab_cols
    where   table_name = 'INTERR_SKUPLANNPARAM'
    /
    

    You might want to modify this to use dba_tab_cols, of course.

    For every column BEFORE the not null column that reports zero, add 1 to the reported avg_row_len.

    Regards

    Jonathan Lewis

    User_GEUPH
  • User_GEUPH
    User_GEUPH Member Posts: 5 Blue Ribbon
    Answer ✓

    Re Jonathons response about the length byte, ... I ran the query:

    select  column_name, num_nulls
    from    dba_tab_cols
    where   table_name = 'INTERR_SKUPLANNPARAM'
    /
    

    68 of the 76 columns have over 300 million nulls. So, by that logic, avg_row_length would be at least 63 (calculated average) + 68, or 131 bytes. This would account for more than half of the space that I thought was over allocated.

    Still in the process of reviewing the other comments and suggestions, but that one was real easy to check and seemed like it might explain a very significant amount of the over allocation, which it did. Thank you, Jonathan!

Answers

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,995 Blue Diamond

    You didn't say which version of Oracle, and that's an important detail.

    One thing to note is that your table has 76 columns and column 74 is declared NOT NULL, which means there must be at least 73 "length bytes" preceding the NOT NULL column, even if every single column is NULL. So the avg_row_len is wrong, and the first question that we have to answer is why is that the case.

    Are the statistics reasonably up to date and how did you collect them, and what did you do to get the avg_row_len - the document you supplied has an error and doesn't show the query you ran. If you're using ANALYZE instead of dbms_stats.gather_table_stats(), and if you summed avg_col_len instead of reported avg_row_len that might explain the oddity.

    Another possibility is simply that it's a side effect of how you use the table and, particularly, the varchar2(2000) column near the end of the table. If you insert a large number of rows and then update them with large strings for this column you may end up with a lot of migrated rows in the table, and there are plenty of odd bugs associated with row migration in ASSM tablespaces.

    Regards

    Jonathan Lewis

  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy

    blocks*16

    Check the block size.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,995 Blue Diamond
    edited May 20, 2022 5:16PM Answer ✓


    Partial answer to the row-length oddity.

    If the avg_col_len for a column is zero Oracle's calculation of the avg_row_len doesn't allow for the length byte that has to be there to say that the column length is zero. This suggests that quite a lot of the columns in your table are null for most of their rows. As a quick check:

    select  column_name, num_nulls
    from    user_tab_cols
    where   table_name = 'INTERR_SKUPLANNPARAM'
    /
    

    You might want to modify this to use dba_tab_cols, of course.

    For every column BEFORE the not null column that reports zero, add 1 to the reported avg_row_len.

    Regards

    Jonathan Lewis

    User_GEUPH
  • User_GEUPH
    User_GEUPH Member Posts: 5 Blue Ribbon
    Answer ✓

    Re Jonathons response about the length byte, ... I ran the query:

    select  column_name, num_nulls
    from    dba_tab_cols
    where   table_name = 'INTERR_SKUPLANNPARAM'
    /
    

    68 of the 76 columns have over 300 million nulls. So, by that logic, avg_row_length would be at least 63 (calculated average) + 68, or 131 bytes. This would account for more than half of the space that I thought was over allocated.

    Still in the process of reviewing the other comments and suggestions, but that one was real easy to check and seemed like it might explain a very significant amount of the over allocation, which it did. Thank you, Jonathan!

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,995 Blue Diamond


    I should have included the avg_col_len in the query against user_tab_cols so that you could see the explicit zeros.

    A well-known "packing" strategy for tables of this type is to redesign them so that columns that are almost always null are put at the end of the table - although that does mean you have to mess about rebuilding the table if you subsequently add a non-null column to the row definition.

    Any columns after the last populate column in a row don't use a length byte, so in your case re-ordering the columns could save a lot of space.


    Another thought is that you have 16KB blocks and your arithmetic is based on 16KB, but with pctfree 10 you may be leaving 1639 bytes unused in each block. Also, a block is flagged as full if inserting a row would take is over the "10% free" limit. I think there are some variations with version about exactly how that works, but it could mean that (on average) with your potential for a 2000+ byte row you could end up with blocks marked full when you had more like 2,650 bytes free space. (pctfree 10 plus half a "worst case" row).

    One thing that might shed some light on further space oddities is a little script I wrote some time ago to count the number of rows per block. For your very large table you probably don't want to run the whole thing but a hinting a full tablescan with a rownum limit of a couple of thousand rows might be instructive. The full script is at this URL, but I'd starting by running just:

    select 
            dbms_rowid.rowid_relative_fno(rowid)    rel_file_no, 
            dbms_rowid.rowid_block_number(rowid)    block_no,
            count(*)                                rows_starting_in_block
    from 
            INTERR_SKUPLANNPARAM
    where
            rownum <= 1000
    group by 
            dbms_rowid.rowid_relative_fno(rowid), 
            dbms_rowid.rowid_block_number(rowid) 
    order by 
            dbms_rowid.rowid_relative_fno(rowid), 
            dbms_rowid.rowid_block_number(rowid)
    ;
    

    Regards

    Jonathan Lewis

  • User_GEUPH
    User_GEUPH Member Posts: 5 Blue Ribbon

    Addressing some of the earlier comments:

    Oracle Database 11g Release 11.2.0.3.0 - 64bit Production - and yes, I know this is WAYYY out of support

    I did use: dbms_stats.gather_table_stats() and did select avg_row_len from dba_tables.

    Re: "Another possibility is simply that it's a side effect of how you use the table and, particularly, the varchar2(2000) column near the end of the table. If you insert a large number of rows and then update them with large strings for this column you may end up with a lot of migrated rows in the table, and there are plenty of odd bugs associated with row migration in ASSM tablespaces." The table is part of a packaged application so I don't know much about how the table is used. The VARCHAR2(2000 CHAR) (ERROR_STR) field is one of the fields that contains over 300 million nulls though. Looking at the table name, fields, etc. it looks like this is nothing more than a table to log error messages so I'm guessing that ERROR_STR doesn't change much.

    Yes, blocksize really is 16K.

    So, I did respond just a tad quick now that I've reviewed the comments more carefully:

    67 of the 76 columns that have nulls are before the "NOT NULL" column, so I guess "true" avg_row_len would be 130 instead of 131 bytes.

    In any event, Jonathan was able explain a very significant part of the space allocation. I realize there's still a significant amount of space allocated that we still haven't explained, but I'm considering this "good enough" at this point. Thank you to everyone that commented!

  • User_GEUPH
    User_GEUPH Member Posts: 5 Blue Ribbon

    I did run that query, but adjusted it up to 10000 rows.

    The query returned 102 rows. 99 of the blocks had 99 rows in them, 1 had 1, 1 had 10, 1 had 20.

    99*130 = 12870 : about 79% full by my calculations so there must be some other overhead there that hasn't been accounted for

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,995 Blue Diamond

    The combination of (very old) version number and the rowid_count code are suggestive.

    How have you got 3 blocks near the "start" of the table that are in the 0 - 25% utilisation range when the dbms_space_usage code says that all but one of your blocks are at least 75% full?

    I'd rerun that script with rowum <= 100000 to see if there were a lot more blocks that were less than 25% full, and re-run the dbms_space_usage code to see if it still said that virtually every block was full or near full. If the two results are significantly out synch with each other it would indicate that you've done some very large deletes in the past and Oracle failed to update the relevant space management bits properly. (It's a difficult problem - whatever algorithm you choose there will be some people who complain, but there were some changes in (I think) 12c, maybe 11.2.0.4, to limit the threat.

    Ultimately you might want to run the script at the URL I supplied across the whole table, maybe just using the second statement to summarise the number of blocks that hold N rows where N will probably range between 1 and 99, and certainly shouldn't go above 206.


    One other little detail: each row in a block uses 3 bytes for metadata and 2 bytes for an entry in a "row directory", so you need to add 5 to the row-length that's based on the column estimates. That caters for another 4% of your space.


    Regards

    Jonathan Lewis

    User_GEUPH
  • User_GEUPH
    User_GEUPH Member Posts: 5 Blue Ribbon

    Hi Jonathan,

    Before opening this discussion, and even before opening the SR, I'd asked an ex-colleague of mine if she could explain the issue I was seeing, but she couldn't. Since you gave a good explanation for the majority of the space being allocated, I forwarded the link to this discussion to her. Her exact response: "... Jonathan Lewis is a Legend in Oracle. Google him. He's written books. Pretty Oracle famous : ) Cool that your question interested him enough that he answered! I think I owned one of his books back in the day. :)". So, thank you very much for taking time and interest in this and for sharing your knowledge.

    Back to you're latest inquiry, "How have you got 3 blocks near the "start" of the table that are in the 0 - 25% utilization range when the dbms_space_usage code says that all but one of your blocks are at least 75% full?" That's a very good question. I assumed those blocks contained rows where a lot of the columns were actually populated instead of containing nulls. However, today I tried re-running the query and only one block had less than 99 rows which is what I should have seen last week.

    Rethinking and reanalyzing what I did, there was a short between the keyboard and chair last week. 🙄 There are 10000 rows with a rownum <=10000

    I said last week: "I did run that query, but adjusted it up to 10000 rows. The query returned 102 rows. 99 of the blocks had 99 rows in them, 1 had 1, 1 had 10, 1 had 20." This doesn't even add up to 10000 rows, it's only 9832. Thinking back through what I did, I ran the query 3 times:

    for 1000 rows: 10 blocks with 99 rows and 1 with 10


    for 2000 rows: 20 blocks with 99 rows and 1 with 20


    for 10000 rows: 101 blocks with 99 rows and 1 with 1


    All of the output went to the same window though, so when I scrolled back through after executing the last query, I must have scrolled too far and seen the earlier output with 10 and 20 row blocks. Sorry for the wild goose chase.

    I did increase the number of rows analyzed to a million, and do see a few blocks with slightly less than 99 rows, but nothing with only 10 or 20 rows / block.

    I did re-run the dbms_space_usage code and here is today's output:


    I also ran the second statement from your script:


    Best regards,

    Dave