Get a nice ascii-art block map of a given datafile. (Accepts tablespace name as an argument and defaults to the first datafile in the tablespace.)
This can be a big help when trying to figure out what tablespaces need defragmenting, why a 300 M datafile only holds 100 M of data yet cannot be shrunk, etc.
The program divides the datafile into N chunks (you may specify N or the chunk size) and collects fragmentation and freespace data on each chunk. The chunk data is stored in a table you must create, dbtools_blk_map. The data is then printed out in a familiar tablespace-block-map format. Columns and rows are numbered to allow for easy identification of particular chunks which you can then zoom in on by querying dbtools_blk_map directly, or by running the procedure zoom( chunk_id ); .
Requirements:
Sample output:
SQL> set serverout on SQL> begin dbtools.ts_block_map(tablespace_name=>'METADATA',num_chunks_in=>400); end; SQL> / Tablespace size: 1200 MB Chunk size: 3072 KB Blocks per chunk: 768 *** Tablespace map for METADATA *** | |0 1 2 3 4 5 6 7 8 9 | | |0 2 4 6 8 0 2 4 6 8 0 2 4 6 8 0 2 4 6 8 0 2 4 6 8 0 2 4 6 8 0 2 4 6 8 0 2 4 6 8 0 2 4 6 8 0 2 4 6 8 | +------+----------------------------------------------------------------------------------------------------+ |0 |####*##### # #### #### #- # ## ## #*=#*###**#*#*-### ##1###| |100 |##*-########- ==- = . * *##=*#####*###*. #### ###=## # ## #* =###-#####*#*###=##.| |200 | =-#####=#* ###=####** ##.## ###############################- *##### ###= | |300 | * = = *##* | PL/SQL procedure successfully completed. SQL> exec dbtools.zoom(212); Chunk label: 212 Blocks in chunk: 1280 Blocks: 271360 - 272639 # blocks used: 717 # pieces used: 2 Average used piece size: 358.5 blocks Percent unused blocks: 44% Summary of extents in this chunk: OWNER SEGMENT_NAME NUM EXTENTS NUM BLOCKS AVG BLKS/EXTNT SMALLEST EXTNT ------------------------------------------------------------------------------------------------------- HR_DATA EXTRA_COMP 1 65 65 65 HR_DATA PS_DIRECTORY_V 1 652 652 652 PL/SQL procedure successfully completed.
Package code:
create or replace
package dbtools
is
procedure ts_block_map (tablespace_name in varchar2,
file_id in number default null,
num_chunks_in number default null,
chunk_bytes_in in number default null);
procedure print_map (chunk_list in varchar2, num_chunks number);
procedure print_map_from_table ;
procedure zoom ( chunk_label in number ) ;
end ;
/
create or replace
package body dbtools is
/*
create table dbtools_blk_map
(file_id number,
block_id number,
chunk_size_in_blocks number,
sum_used_blocks number,
used_pieces number,
avg_used_piece_size number,
sum_unused_blocks number,
percent_unused number,
chunk_label number,
chunk_symbol char(1)
);
*/
procedure ts_block_map (tablespace_name in varchar2,
file_id in number default null,
num_chunks_in number default null,
chunk_bytes_in in number default null)
is
num_chunks number;
chunk_size number; --in blocks
chunk_bytes number; --in bytes
ts_bytes number;
file# number;
db_block_size number;
this_chunk_symbol char(1);
chunk_list varchar2(32767);
this_chunk_start_block number;
this_chunk_end_block number;
this_chunk_usedblks number;
this_chunk_pieces number;
this_chunk_chunksize number;
this_chunk_freeblks number;
this_chunk_pctfree number;
piece_threshold_1 number;
piece_threshold_2 number;
this_chunkID number;
begin
dbms_output.enable(2000000);
delete from dbtools_blk_map;
commit;
db_block_size := 4096;
piece_threshold_1 := 21;
piece_threshold_2 := 31;
if num_chunks_in is not null then
num_chunks := num_chunks_in;
else
num_chunks := 200;
end if;
if ts_block_map.file_id is null then
select min(file_id) into file#
from sys.dba_data_files df
where df.tablespace_name=upper(ts_block_map.tablespace_name);
else
file# := ts_block_map.file_id;
end if;
select df.bytes
into ts_bytes
from sys.dba_data_files df where df.file_id=file#;
if ( chunk_bytes_in is not null and num_chunks_in is null) then
num_chunks := ceil(ts_bytes/chunk_bytes_in);
end if;
chunk_size := ceil(ts_bytes/(num_chunks*db_block_size)); --round up
chunk_bytes := chunk_size * db_block_size ;
dbms_output.put_line ( 'Tablespace size: ' || ts_bytes/(1024*1024) || ' MB' );
dbms_output.put_line ( 'Chunk size: ' || chunk_bytes/(1024) || ' KB');
dbms_output.put_line ( 'Blocks per chunk: ' || chunk_size );
this_chunk_start_block := 0;
this_chunkid := 0;
while this_chunk_start_block < ts_bytes/db_block_size
loop
begin <>
select sum(blk_in_chunk), count(blk_in_chunk), avg(blk_in_chunk),
chunk_size-sum(blk_in_chunk),
((chunk_size-sum(blk_in_chunk))/chunk_size)*100
into this_chunk_usedblks, this_chunk_pieces, this_chunk_chunksize,
this_chunk_freeblks, this_chunk_pctfree
from
(
select segment_name, block_id, blocks, bytes,
case
when block_id < this_chunk_start_block then
(blocks+block_id-this_chunk_start_block)
when (block_id+blocks) > this_chunk_start_block+chunk_size-1
then (this_chunk_start_block+chunk_size-1-block_id)
else blocks
end blk_in_chunk --blocks of this extent in the chunk
from sys.dba_extents e where e.file_id=file#
and (block_id between this_chunk_start_block and
(this_chunk_start_block+chunk_size-1)
or
(block_id+blocks-1) between this_chunk_start_block and
(this_chunk_start_block+chunk_size-1) )
);
exception
when no_data_found then
this_chunk_pctfree := 100;
this_chunk_usedblks:=0;
this_chunk_pieces:=0;
this_chunk_chunksize:=0;
this_chunk_freeblks:=chunk_size;
when others then
raise;
end selblock;
if ( this_chunk_pctfree=100 or this_chunk_pctfree is null) then
this_chunk_symbol := ' ' ;
elsif this_chunk_pctfree >= 90 then
this_chunk_symbol := '.' ;
elsif ( this_chunk_pctfree between 70 and 90 and this_chunk_pieces <
piece_threshold_1) then
this_chunk_symbol := '-' ;
elsif ( this_chunk_pctfree between 50 and 70 and this_chunk_pieces <
piece_threshold_1) then
this_chunk_symbol := '=' ;
elsif ( this_chunk_pctfree between 30 and 50 and this_chunk_pieces <
piece_threshold_1) then
this_chunk_symbol := '*';
elsif ( this_chunk_pctfree between 10 and 30 and this_chunk_pieces <
piece_threshold_1) then
this_chunk_symbol := '@';
elsif ( this_chunk_pctfree between 0 and 10 and this_chunk_pieces <
piece_threshold_1) then
this_chunk_symbol := '#';
elsif ( this_chunk_pieces > piece_threshold_1 ) then
this_chunk_symbol := '1';
elsif (this_chunk_pieces > piece_threshold_2) then
this_chunk_symbol := '2';
else
this_chunk_symbol := '!'; --should not reach this case
end if;
insert into system.dbtools_blk_map (file_id, block_id,
chunk_size_in_blocks,
sum_used_blocks, used_pieces, avg_used_piece_size,
sum_unused_blocks, percent_unused, chunk_label, chunk_symbol)
values (file#, this_chunk_start_block, chunk_size,
this_chunk_usedblks, this_chunk_pieces, this_chunk_chunksize,
this_chunk_freeblks, this_chunk_pctfree, this_chunkID, this_chunk_symbol)
;
commit;
chunk_list := chunk_list || this_chunk_symbol ;
this_chunk_start_block := this_chunk_start_block + chunk_size ;
this_chunkID := this_chunkID + 1;
end loop;
dbms_output.put_line ( ' ');
dbms_output.put_line ( '*** Tablespace map for ' ||
upper(ts_block_map.tablespace_name) || ' ***');
print_map ( chunk_list, num_chunks );
end ts_block_map;
procedure print_map (chunk_list in varchar2, num_chunks number)
is
--print nicely in rows of 100 symbols per row
--set pagesize to at least 110
to_print varchar2(32767);
i number;
j number;
begin
dbms_output.put_line ( '| |0 1 2 3 4
5 6 7 8 9 |');
dbms_output.put_line ( '| |0 2 4 6 8 0 2 4 6 8 0 2 4 6 8 0 2 4 6 8 0 2 4 6
8 0 2 4 6 8 0 2 4 6 8 0 2 4 6 8 0 2 4 6 8 0 2 4 6 8 |');
dbms_output.put_line (
'+------+----------------------------------------------------------------------------------------------------+');
j := ceil(num_chunks/100)-1 ;
i := 0;
to_print := chunk_list || '[END]' ;
for i in 0..j
loop
dbms_output.put_line ('|' || rpad(i*100,6) || '|' ||
rpad(substr(to_print,1,100),100) || '|');
to_print := substr(to_print,101,length(to_print));
end loop;
end print_map;
procedure print_map_from_table
is
chunk_list varchar2(32767);
cursor mycur is
select db.chunk_symbol
from system.dbtools_blk_map db
order by chunk_label ;
num_chunks number;
begin
for d in mycur loop
chunk_list := chunk_list || d.chunk_symbol;
end loop;
select count(*) into num_chunks
from system.dbtools_blk_map db
;
print_map(chunk_list, num_chunks);
end;
procedure zoom ( chunk_label in number ) is
--Zoom in on a chunk in dbtools_blk_map
trow dbtools_blk_map%rowtype ;
firstblk number;
lastblk number;
cursor zoomcur (first_block number, last_block number, file number)
is
select owner, segment_name, count(*) num_extents, sum(blk_in_chunk)
num_blocks, avg(blk_in_chunk) avg_extent_blocks,
min(blk_in_chunk) min_extent_blocks
from
(select owner,
segment_name,
case
when block_id < zoomcur.first_block then
(blocks+block_id-zoomcur.first_block)
when (block_id+blocks) > zoomcur.last_block then
(zoomcur.last_block-block_id)
else blocks
end blk_in_chunk
from
dba_extents
where ( block_id between zoomcur.first_block and zoomcur.last_block
OR
(block_id+blocks-1) between zoomcur.first_block and
zoomcur.last_block
)
and file_id=zoomcur.file
)
group by owner, segment_name
;
begin
dbms_output.put_line ('Chunk label: ' || chunk_label);
select * into trow from dbtools_blk_map db where db.chunk_label =
zoom.chunk_label;
dbms_output.put_line (' ');
dbms_output.put_line ('Blocks in chunk: ' || trow.chunk_size_in_blocks );
firstblk := trow.block_id;
lastblk := trow.block_id + trow.chunk_size_in_blocks - 1 ;
dbms_output.put_line ('Blocks: ' || firstblk || ' - ' || lastblk );
dbms_output.put_line ('# blocks used: ' || trow.sum_used_blocks );
dbms_output.put_line ('# pieces used: ' || trow.used_pieces );
dbms_output.put_line ('Average used piece size: ' ||
round(trow.avg_used_piece_size,1) || ' blocks');
dbms_output.put_line ('Percent unused blocks: ' || round(trow.percent_unused,1)
|| '%' );
dbms_output.put_line (' ');
dbms_output.put_line ('Summary of extents in this chunk:');
dbms_output.put_line (rpad('OWNER',30) || rpad('SEGMENT_NAME',30) || rpad ('NUM
EXTENTS',12)
|| rpad('NUM BLOCKS',11) || rpad('AVG BLKS/EXTNT',15) || rpad('SMALLEST
EXTNT',15) ) ;
dbms_output.put_line
('-------------------------------------------------------------------------------------------------------');
for z in zoomcur (firstblk, lastblk, trow.file_id)
loop
dbms_output.put_line (rpad(z.owner,30) || rpad(z.segment_name,30) ||
rpad(z.num_extents,12)
|| rpad (z.num_blocks,11) || rpad(round(z.avg_extent_blocks),15)
|| rpad (z.min_extent_blocks,15));
end loop;
dbms_output.put_line (' ');
end zoom;
end dbtools;