Re: LOB space, with numbers

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Sun, 13 May 2012 07:37:52 +0000 (UTC)
Message-ID: <pan.2012.05.13.07.37.51_at_gmail.com>

On Sun, 13 May 2012 01:58:57 +0000, Mladen Gogala wrote:

> The segment grew for exactly 128MB

This is because a new extent was allocated and it was 128MB in size. The real problem is revealed by investigating with dbms_space. Even after deletion, DBMS_SPACE did not show any partially filled or empty blocks, only full blocks and unformatted blocks. That means that delete doesn't free blocks. Here is the script which invokes DBMS_SPACE that I used:

set serveroutput on
declare

   l_fs1_bytes number;
   l_fs2_bytes number;
   l_fs3_bytes number;
   l_fs4_bytes number;
   l_fs1_blocks number;
   l_fs2_blocks number;
   l_fs3_blocks number;
   l_fs4_blocks number;
   l_full_bytes number;
   l_full_blocks number;
   l_unformatted_bytes number;
   l_unformatted_blocks number;

begin

   dbms_output.enable;
   dbms_space.space_usage(

      segment_owner      => upper('&owner'),
      segment_name       => upper('&segment_name'),
      segment_type       => upper('&segment_type'),
      fs1_bytes          => l_fs1_bytes,
      fs1_blocks         => l_fs1_blocks,
      fs2_bytes          => l_fs2_bytes,
      fs2_blocks         => l_fs2_blocks,
      fs3_bytes          => l_fs3_bytes,
      fs3_blocks         => l_fs3_blocks,
      fs4_bytes          => l_fs4_bytes,
      fs4_blocks         => l_fs4_blocks,
      full_bytes         => l_full_bytes,
      full_blocks        => l_full_blocks,
      unformatted_blocks => l_unformatted_blocks,
      unformatted_bytes  => l_unformatted_bytes

   );

   dbms_output.put_line(' FS1 Blocks = '||l_fs1_blocks);
   dbms_output.put_line(' FS2 Blocks = '||l_fs2_blocks);
   dbms_output.put_line(' FS3 Blocks = '||l_fs3_blocks);
   dbms_output.put_line(' FS4 Blocks = '||l_fs4_blocks);
   dbms_output.put_line('Full Blocks = '||l_full_blocks);
   dbms_output.put_line('Unformatted Blocks = '||l_unformatted_blocks);

end;
/

The test performed looks like this:

QL> _at_dbms_space
Enter value for owner: scott

old  17:       segment_owner	  => upper('&owner'),
new  17:       segment_owner	  => upper('scott'),
Enter value for segment_name: music
old  18:       segment_name	  => upper('&segment_name'),
new  18:       segment_name	  => upper('music'),
Enter value for segment_type: lob
old  19:       segment_type	  => upper('&segment_type'),
new  19:       segment_type	  => upper('lob'),
FS1 Blocks = 0
FS2 Blocks = 0

FS3 Blocks = 0
FS4 Blocks = 0
Full Blocks = 73562
Unformatted Blocks = 9160

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> delete from scott.bin_files
  2 where filename like '/home/mgogala/mp3/misc/The%';

47 rows deleted.

Elapsed: 00:00:00.05
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> _at_dbms_space
Enter value for owner: scott

old  17:       segment_owner	  => upper('&owner'),
new  17:       segment_owner	  => upper('scott'),
Enter value for segment_name: music
old  18:       segment_name	  => upper('&segment_name'),
new  18:       segment_name	  => upper('music'),
Enter value for segment_type: lob
old  19:       segment_type	  => upper('&segment_type'),
new  19:       segment_type	  => upper('lob'),
FS1 Blocks = 0
FS2 Blocks = 0

FS3 Blocks = 0
FS4 Blocks = 0
Full Blocks = 73562
Unformatted Blocks = 9160

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

The block numbers before and after deletion are exactly the same. Either DBMS_SPACE is buggy or space management within LOB segment is buggy. Judging by the huge disk space consumption, the latter is the case.

-- 
http://mgogala.byethost5.com

Received on Sun May 13 2012 - 02:37:52 CDT