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.
Oracle community attachment.docx (16.15 KB)
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