2008년 02월 06일
[펌] Tablespace block map
From : http://toolkit.rdbms-insight.com/dbtools.php
Note: Proofread any scripts before using. Always try scripts on a test instance first. I'm not responsible for any damage, even if you somehow manage to make my scripts corrupt every last byte of your data, set your server on fire and serve you personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...
Get a nice ascii-art block map of a given datafile. (Accepts tablespacename as an argument and defaults to the first datafile in thetablespace.)
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 printedout 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:
- Package owner must be explicitly granted select on sys.dba_extents and sys.dba_free_space
- Package owner must own the dbtools_blk_map table specified in the code comments
- Code assumes a consistent db_block_size of 4K. If that's not the case in your database, change the db_block_size constant or, if using multiple block sizes, change the code to allow a block size to be passed in.
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;
Note: Proofread any scripts before using. Always try scripts on a test instance first. I'm not responsible for any damage, even if you somehow manage to make my scripts corrupt every last byte of your data, set your server on fire and serve you personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...
이 글과 관련있는 글을 자동검색한 결과입니다 [?]
- tablespace sql문 by ides
- 데이터베이스 테이블 구조 변경 관련 by _NiX
- [ITWILL / 아이티윌 / 오라클 / OCP / OCA] 3장 Table 생성 및 변경 by laputa
- 테이블 생성 예제 (데이터 타입, constraint) by ides
- 테이블 생성 by 바보나무
# by | 2008/02/06 18:11 | Oracle | 트랙백 | 덧글(1)
Commented by intern at 2008/04/23 09:35 
※ 이 포스트는 더 이상 덧글을 남길 수 없습니다.
