Skip to Main Content
  • Questions
  • Analyzing LOB segments in an Auto Segment Space Managed Tablespace

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Robyn.

Asked: December 14, 2003 - 7:23 pm UTC

Last updated: June 08, 2006 - 9:13 am UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

Hi Tom,

Can you tell me if there is any way of analyzing space usage for LOB segments which are stored in an ASSM tablespace ?

I can't use dbms_space.free_blocks or dbms_space.unused_space on segments in a tablespace with auto segment space management.

I can't use dbms_space.space_usage on a lobsegment. (only works for tables, indexes and clusters)

There must be a way to do it - can you shed any light ?



and Tom said...

You can use dbms_lob.space_usage?

ops$tkyte@ORA920> variable l_unformatted_blocks number
ops$tkyte@ORA920> variable l_unformatted_bytes number
ops$tkyte@ORA920> variable l_fs1_blocks number
ops$tkyte@ORA920> variable l_fs1_bytes number
ops$tkyte@ORA920> variable l_fs2_blocks number
ops$tkyte@ORA920> variable l_fs2_bytes number
ops$tkyte@ORA920> variable l_fs3_blocks number
ops$tkyte@ORA920> variable l_fs3_bytes number
ops$tkyte@ORA920> variable l_fs4_blocks number
ops$tkyte@ORA920> variable l_fs4_bytes number
ops$tkyte@ORA920> variable l_full_blocks number
ops$tkyte@ORA920> variable l_full_bytes number
ops$tkyte@ORA920>
ops$tkyte@ORA920> begin
2 dbms_space.space_usage
3 ( USER, 'SYS_LOB0000059125C00002$$',
4 'LOB',
5 :l_unformatted_blocks,
6 :l_unformatted_bytes, :l_fs1_blocks, :l_fs1_bytes,
7 :l_fs2_blocks, :l_fs2_bytes, :l_fs3_blocks, :l_fs3_bytes,
8 :l_fs4_blocks, :l_fs4_bytes, :l_full_blocks, :l_full_bytes );
9 end;
10 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> select
2 :l_unformatted_blocks,
3 :l_unformatted_bytes, :l_fs1_blocks, :l_fs1_bytes,
4 :l_fs2_blocks, :l_fs2_bytes, :l_fs3_blocks, :l_fs3_bytes,
5 :l_fs4_blocks, :l_fs4_bytes, :l_full_blocks, :l_full_bytes
6 from dual;

:L_UNFORMATTED_BLOCKS :L_UNFORMATTED_BYTES :L_FS1_BLOCKS :L_FS1_BYTES :L_FS2_BLOCKS :L_FS2_BYTES :L_FS3_BLOCKS :L_FS3_BYTES
--------------------- -------------------- ------------- ------------ ------------- ------------ ------------- ------------
:L_FS4_BLOCKS :L_FS4_BYTES :L_FULL_BLOCKS :L_FULL_BYTES
------------- ------------ -------------- -------------
2 16384 0 0 0 0 0 0
0 0 3 24576




but you might be interested in my script show_space -- this is from my book Effective Oracle By Design and works in 9i with ASSM (search on this site for 8.0/8i versions)

ops$tkyte@ORA920> exec show_space( 'SYS_LOB0000059125C00002$$', user, 'LOB' )
Unformatted Blocks ..................... 2
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 3
Total Blocks............................ 8
Total Bytes............................. 65,536
Total MBytes............................ 0
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 9
Last Used Ext BlockId................... 360
Last Used Block......................... 8

PL/SQL procedure successfully completed.


create or replace procedure show_space
( p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default 'TABLE',
p_partition in varchar2 default NULL )
-- this procedure uses authid current user so it can query DBA_*
-- views using privileges from a ROLE and so it can be installed
-- once per database, instead of once per user that wanted to use it
authid current_user
as
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
l_segment_space_mgmt varchar2(255);
l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number; l_fs1_bytes number;
l_fs2_blocks number; l_fs2_bytes number;
l_fs3_blocks number; l_fs3_bytes number;
l_fs4_blocks number; l_fs4_bytes number;
l_full_blocks number; l_full_bytes number;

-- inline procedure to print out numbers nicely formatted
-- with a simple label
procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,'.') ||
to_char(p_num,'999,999,999,999') );
end;
begin
-- this query is executed dynamically in order to allow this procedure
-- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
-- via a role as is customary.
-- NOTE: at runtime, the invoker MUST have access to these two
-- views!
-- this query determines if the object is a ASSM object or not
begin
execute immediate
'select ts.segment_space_management
from dba_segments seg, dba_tablespaces ts
where seg.segment_name = :p_segname
and (:p_partition is null or
seg.partition_name = :p_partition)
and seg.owner = :p_owner
and seg.tablespace_name = ts.tablespace_name'
into l_segment_space_mgmt
using p_segname, p_partition, p_partition, p_owner;
exception
when too_many_rows then
dbms_output.put_line
( 'This must be a partitioned table, use p_partition => ');
return;
end;


-- if the object is in an ASSM tablespace, we must use this API
-- call to get space information, else we use the FREE_BLOCKS
-- API for the user managed segments
if l_segment_space_mgmt = 'AUTO'
then
dbms_space.space_usage
( p_owner, p_segname, p_type, l_unformatted_blocks,
l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);

p( 'Unformatted Blocks ', l_unformatted_blocks );
p( 'FS1 Blocks (0-25) ', l_fs1_blocks );
p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
p( 'FS4 Blocks (75-100)', l_fs4_blocks );
p( 'Full Blocks ', l_full_blocks );
else
dbms_space.free_blocks(
segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks);

p( 'Free Blocks', l_free_blks );
end if;

-- and then the unused space API call to get the rest of the
-- information
dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );

p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );
end;
/


Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, December 15, 2003 - 8:50 am UTC

Excellent !!

Thankyou

Robyn Gotterson, December 15, 2003 - 4:47 pm UTC

I must admit that I didn't try using 'LOB' as the segment_type in dbms_space.space_usage as the doco says:
segment_type - Type of the segment to be analyzed (TABLE, INDEX, or CLUSTER.
Thanks for your help.



Still have a probblem with show_space on lob segments

Juergen Braendle, June 02, 2006 - 8:22 am UTC

Hi Tom,

I've read a lot about your show_space procedure in this groups but still have a problem. (BTW I've read a lot about it !)
Oracle Version:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters and Data Mining options

I've a table with some lob columns which contain a lot of pictures.
In these lob segments a lot of deletes an inserts are done. (more than 1000 pictures a day will be deleted and about the same amount will be inserted)

From the beginning we had problems with the deleting stuff so this lob segment grew more than expected. Now we want to check if the delete job ist working. 

So I needed something to check the free space inside this lob segments.

So I gave show_space a try:

SQL> exec show_space(  'SYS_LOB0000047762C00053$$', user, 'LOB'  );
Unformatted Blocks .....................               3
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................      16,398,506
Total Blocks............................      16,416,768
Total Bytes............................. 268,972,326,912
Total MBytes............................         256,512
Unused Blocks...........................           2,048
Unused Bytes............................      33,554,432
Last Used Ext FileId....................              76
Last Used Ext BlockId...................         229,381
Last Used Block.........................           6,144

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

I'm a right, that this will tell me that there is NO free space avail?
FS1-4 Blocks return "0"
Or am I doing something wrong?
I think if there is really no free space the segment should grow because we put a lot of new stuff in the db. But no grow at the moment.

Do I have to do some extra analyze stuff before using show_space?

Any help welcome.
You can ask me for any addditonal information if you need!

BTW Sorry for my bad english --- comming from Germany :-)


Greeetings

JB
 

Tom Kyte
June 02, 2006 - 11:20 am UTC

lobs do not manage space that way at all.

A block is always 100% allocated to a chunk (or not)

There is not any such concept as a "block that is available 50%", the block is 100% allocated to a chunk, the chunk is 100% allocated to that lob instance.

Still no way to get the "real" usage of lob segments

Juergen Braendle, June 06, 2006 - 3:53 am UTC

You told me, that lob segments manage their space different.
Does this mean, that there is no way of getting information about how much space is really used in a log segment?
I need to know how much space is avail after deleting a lot of stuff from a lob segment. (The segment itself will not shink.)
Any help welcome

Tom Kyte
June 06, 2006 - 8:41 am UTC

that is not exposed, no. There are no freelists, lobs do their own versioning and what not in the log segment itself, it is managed very differently from other segment types. There is no undo (pctversion and such version the lob data in the lob segment itself) for example. So, even if you "delete" a lob, it might well not be deleted - as we version X% of the lob segment for read consistency.

Another way getting the level of a lob segment ?!

Juergen Braendle, June 07, 2006 - 9:15 am UTC

OK,

so if there is no build in funktion to get used or free space from a lob segment
what do you think about this solution:


set serveroutput on

declare
lobsize_hr number;
lobused_hr number;

begin

select round(b.bytes/1024/1024,0)
into lobsize_hr
from user_lobs s, dba_segments b
where s.segment_name = b.segment_name
and s.tablespace_name = '<TABLESPACENAME>'
and s.table_name='<TABLENAME>'
and s.column_name='<COLUMNNAME>';


select round( (sum(dbms_lob.getlength(<COLUMNNAME>)))/(1024*1024)/100*110,0)
into lobused_hr
from <TABLENAME>;


dbms_output.put_line('Total_MByte_HR: '||lobsize_hr);
dbms_output.put_line('Used_MByte_HR : '||lobused_hr);

end;
/

In the first step I calculate the total size of the lob segment
In the second step I sum up all the sizes of the entries in the lobsegment
and add 10 % for the "pctfree stuff"

Do you think this will be a passable way?

It's not my favorite solution because "sum(dbms_lob.getlength..." take some time on
large lobs but I have no other solution :-(

Do you?

Greetings

JB

Tom Kyte
June 07, 2006 - 2:53 pm UTC

it would not be accurate, it would be an estimate and I'm not sure how close to reality it would be


say AVG( dbms_lob.getlength ) = 5k
but chunksize = 16k

11k allocated but not currently in use and cannot be used by any other lob (for example)

Another way getting the level of a lob segment ?!

Juergen Braendle, June 08, 2006 - 4:18 am UTC

Well I think with an average of 916714 bytes
and a chunk size of 16 k
the the error is quite negligible.

So this seems to be a solution for me.

Thanks a lot.

Jürgen

Tom Kyte
June 08, 2006 - 9:13 am UTC

the only answer is "maybe yes, maybe no"

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here