Finding out how space is occupied at Oracle's block level

Products

• Data Loss Prevention Enforce

Issue/Introduction

It has been an ongoing complaint that the amount of space taken up within a database isn't always reported appropriately and that even though records are deleted, that freed space isn't realized in expected proportions; if 2k worth of data is deleted, there should be a 2k worth of space available.

 

Space management in an Oracle database is complicated by Oracle's usage of blocks and tracking through a "freelist".

 

Resolution

In Oracle's scheme of things, data is stored at the atomic-level within blocks. While Oracle now allows database to be created with mixed sizes of block, typically blocks are the same size and by default that is 8 kilobytes. 

Oracle manages block usage through a "freelist", conceptually a file that has a list of all blocks and if they are available for use (they have space) or not. 

Oracle further manages blocks through the use of two thresholds: an upper threshold and a low threshold.  As Oracle needs to store date, it checks the freelist, identifies the next available block with space, determines if it has enough space, and then uses it.  Once that block "fills" past, for example, 90%, and upper threshold, Oracle updates the freelist to show that block as being filled and doesn't consider it for use until it is emptied.  If a record is deleted and it has been stored in a block that contains other records, the block may remain unavailable according to the freelist until the block is, for example, less than 20% full, a lower threshold. 

While this makes storage management nice and zippy for Oracle, it causes customers to tear out their hair.  Add to this the fact that Oracle uses the freelist to calculate free space.  Iimagine 10 blocks all full, so off the freelist, then half the records emptied from each block; you may think there is 40k free, but Oracle, according to it's free list sees 80k full. 

 

No wonder it's so confusing to explain where a customer's space went.

 

While the following scripts aren't a panacea, they do provide evidence of how Oracle manages storage and gives people a way to understand what's happening at the block level.

All scripts need to be run from SQLPlus logged in as sys as sysdba.

The first script generates a list of "segments" or objects with in a tablespace.  These are necessary for the subsequent scripts.  The script example is set to gather information on the LOB_TABLESPACE owned by user PROTECT:

 

select segment_name,
       segment_type,
       bytes
  from dba_segments
 where tablespace_name='LOB_TABLESPACE'
   and owner ='PROTECT';

The output looks like this:

SEGMENT_NAME
---------------------------------------------------------------------------------
SEGMENT_TYPE            BYTES
------------------ ----------
SYS_IL0000046323C00003$$
LOBINDEX                65536

SYS_IL0000046265C00003$$
LOBINDEX                65536

SYS_IL0000046279C00004$$
LOBINDEX                65536

SYS_LOB0000046323C00003$$
LOBSEGMENT         3154116608

SYS_LOB0000046265C00003$$
LOBSEGMENT              65536

SYS_LOB0000046279C00004$$
LOBSEGMENT           17825792

 

The next script will display the block counts according to amount of freespace.  This script focuses on lobsegment 'SYS_LOB0000046323C00003$$':

 

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  ('PROTECT', 'SYS_LOB0000046323C00003$$', 'LOB', 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('Blocks with 00-25% free space =  '||v_fs1_blocks);
dbms_output.put_line('Blocks with 26-50% free space = '||v_fs2_blocks);
dbms_output.put_line('Blocks with 51-75% free space = '||v_fs3_blocks);
dbms_output.put_line('Blocks with 76-100% free space = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
/

the output from this is:

Unformatted  Blocks = 6493
Blocks with 00-25% free space =  0
Blocks with 26-50% free space = 0
Blocks with 51-75% free space = 0
Blocks with 76-100% free space = 0
Full Blocks = 377663

PL/SQL procedure successfully completed.

 

From a table perspective, the following script can be used.  This script focuses on MESSAGE table:

 

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 (
'PROTECT', 'MESSAGE', '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('Blocks with 00-25% free space = '||v_fs1_blocks);
dbms_output.put_line('Blocks with 26-50% free space = '||v_fs2_blocks);
dbms_output.put_line('Blocks with 51-75% free space = '||v_fs3_blocks);
dbms_output.put_line('Blocks with 76-100% free space = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
/

Unformatted Blocks = 48
Blocks with 00-25% free space = 0
Blocks with 26-50% free space = 7
Blocks with 51-75% free space = 0
Blocks with 76-100% free space = 64
Full Blocks = 6173

PL/SQL procedure successfully completed.

 

 

CONFUSION ALERT!  Take a moment to reflect on the two examples above. In the output from the first example, where "SYS_LOB0000046323C00003$$", a lob segment, was investigated showed:

Blocks with 00-25% free space =  0
Blocks with 26-50% free space = 0
Blocks with 51-75% free space = 0
Blocks with 76-100% free space = 0
Full Blocks = 377663

while the output for the MESSAGE table showed:

Blocks with 00-25% free space = 0
Blocks with 26-50% free space = 7
Blocks with 51-75% free space = 0
Blocks with 76-100% free space = 64
Full Blocks = 6173

Either the LOB segments are so well organized as to fit squarely in each block or there really aren't any fractionally filled blocks.  And the answer to both is, strangely, "Yes".  LOB segments group blocks together into "chunks".  The blocks inside the chunks appear to be either used or empty.  While the output is confusing, it's also accurate.  In regular segment blocks all the information is drawn from the block header.  In a LOB segment "chunk" this information is drawn from a chunk header. LOB blocks don't appear as fractionally filled, either their blocks are fully occupied or they aren't.

Hopefully, this has provided another manner in which to explain to a customer how their data is being stored.