Re: Defrag the darn thing
Date: 25 Nov 92 00:27:37 GMT
Message-ID: <215_at_ecsa01.encon.pge.com>
jjolly_at_jjolly.b24a.ingr.com (Jack G. Jolly) writes:
: Had a (flawed) strategy to defrag a tablespace in the db.
:
: 1) Export db
: 2) Drop tablespace
: 3) Create tablespace
: 4) Import users
:
: The problem was in step (2) - dropping a 300MB tablespace (non-empty)
: generated so many transactions that I got the infamous "snapshot too
: old" error. I have 10 rollback seg's in their own (20MB) tablespace. The
: whole deal is on raw files.
:
: I also read here about another strategy - create a table whose "initial"
: parm is equal to the sum of all freespace extents in the db - the idea
: was that the db would "collect the smaller extents into one big one",
: and then you would drop the table. I tried this idea without success -
: came back with "unable to allocate extent of size XXX" where XXX was
: the sum of all freespace in the tablespace.
:
: Any ideas?? Should the second strategy work??
:
: JJ
:
: ------------------
:
: ^^^^^^^^^ Jack Jolly _at_ jjolly_at_jjolly.b24a.ingr.com
: (| 0 - |)
: | * | Intergraph Corporation, Huntsville AL
: \_______/ AEC Project Management Support
Our group has written a oraperl program to do deframentation among other things. This program uses the second strategy at defraging. This program does not completely defragment the database but just removes free space that is immediately adjacent to each other. This program also has some other features to keep track of database usage and growth. We use it daily to monitor our database activity. If you haven't used Perl or Oraperl (an extension of Perl for Oracle databases) I highly recommend it as an alternative to writing Pro*C programs. Hope this helps.
- CUT HERE --- #!/usr/local/bin/oraperl # # This oraperl program identifies database objects that will not be able # to find enough freespace on their next extension. It sends these reports: # - Tablespace Free Space Report # - Tablespace History Report # - Tablespace Examination Report # This program tracks tablespace history by inserting rows into its # TABLESPACE_HIST table. # This program will optionally coalesce honeycombed free space. # # _at_(#)ts_status.pl 3.9 11/12/92 # # 10/15/92 - bjbc - initial release # # Dependencies: # - The Oracle userid must have select access to the SYS.DBA% tables. # - The Oracle userid must own a table called TABLESPACE_HIST # CREATE TABLE TABLESPACE_HIST # (TABLESPACE_NAME CHAR(30), # PCT_AVAILABLE NUMBER, # CHECK_DATE DATE); # # # #
&INIT(); &GET_TABLESPACE_SIZES(); &INSERT_TABLESPACE_HISTORY(); &GET_TABLESPACE_HISTORY(); &EXAMINE_OBJECTS(); &CLEANUP(); exit;
#------------------------------------------------------------------------- # # subroutines
#
#-------------------------------------------------------------------------sub GET_TABLESPACE_SIZES
{
# This subroutine produces a report listing free space, by tablespace.
format TS_TOP =
ECMS Database Administration
Tablespace Free Space Report
_at_|||||||||||||||||||||||||||
$date
Tablespace Max Contiguous Total Free Tablespace Pct # Free Honey-
Free Space Bytes Size Free Extents combs
---------- ------------- ------------- ------------- ---- ------- ------
.
format TS =
_at_<<<<<<<<<<< @>>>>>>>>>>>> @>>>>>>>>>>>> @>>>>>>>>>>>> @## @>>>>>> @>>>>
$tablespace_name, $max_contiguous_spaceX, $total_free_bytesX, $tablespace_sizeX, $percent_available, $total_free_extentsX, $honeycomb_count_tsX
.
format TS_BOTTOM =
_at_>>>>
$honeycomb_countX
.
$- = 0; # force top-of-form
$^ = "TS_TOP";
$~ = "TS";
$honeycomb_count = 0;
$total_free_bytes = 0;
$total_free_extents = 0;
# this query will get size information about each tablespace.
$tablespace_query = <<EOFEOF;
select tablespace_name, sum(bytes)
from sys.dba_data_files
where status = 'AVAILABLE'
group by tablespace_name
order by tablespace_name
EOFEOF
# open the cursor
$tablespace_csr = &ora_open($lda, $tablespace_query) || die $ora_errstr;
# get all tablespaces
while (($tablespace_name, $tablespace_size) = &ora_fetch($tablespace_csr))
{
# Build associative array %ts_max with tablespace_name and max_free.
$max_contiguous_space = &COALESCE_FREE_SPACE($tablespace_name);
$ts_max{$tablespace_name} = $max_contiguous_space;
# Calculate free space information
&SUM_FREE_SPACE($tablespace_name);
$percent_available = 100 * ($total_free_bytes / $tablespace_size);
# Build associative array %ts_current with tablespace_name and
# pct_free. It is used for inserting history.
$ts_current{$tablespace_name} = $percent_available;
# Format the big numbers by adding commas.
$max_contiguous_spaceX = &commas($max_contiguous_space);
$total_free_bytesX = &commas($total_free_bytes);
$tablespace_sizeX = &commas($tablespace_size);
$total_free_extentsX = &commas($total_free_extents);
$honeycomb_count_tsX = &commas($honeycomb_count_ts);
write;
# -r "repair honeycombs" is a command line option.
# Only repair honeycombs when they exist and are requested.
&REPAIR_HONEYCOMBS() if ($opt_r && $honeycomb_count_ts > 0);
}
# close the cursor
&ora_close($tablespace_csr) || die $ora_errstr;
$honeycomb_countX = &commas($honeycomb_count);
# Write the final report total.
$~ = 'TS_BOTTOM';
write;
}
sub COALESCE_FREE_SPACE
{
# This subroutine calculates the size of the largest piece of
# contiguous free space in a tablespace.
# It requires tablespace_name as an input argument.
# It returns the size of the largest piece of contiguous free space.
local ($ts) = _at__;
$previous_end_block = 0;
$previous_start_block = 0;
$previous_end_id = 0;
$previous_start_id = 0;
$honeycomb_count_ts = 0;
undef %free_list;
undef _at_free_bytes;
# This query will get free space information about one tablespace.
$free_space_query = <<EOFEOF;
select space.file_id, block_id "start block",
block_id + space.blocks -1 "end block" from sys.dba_free_space space, sys.dba_data_files files
where space.tablespace_name = files.tablespace_name and space.file_id = files.file_id and files.tablespace_name = '$ts'
and status = 'AVAILABLE'
order by 1,2
EOFEOF # open the cursor
$free_space_csr = &ora_open($lda, $free_space_query) || die $ora_errstr;
# Get all free_space segments for one tablespace while (($file_id, $start_block, $end_block) = &ora_fetch($free_space_csr)) {
# Combine file id and block address. This will allow support
# for tablespaces with more than one datafile.
$start_id = "$file_id:$start_block";
$end_id = "$file_id:$end_block";
$start_id_minus_1 = "$file_id:" . ($start_block - 1);
# Build associative array %free_list of start_id and
# end_id file ids and block addresses.
if ($start_id_minus_1 eq $previous_end_id)
{
# This free space is adjacent to the previous free space.
# Oracle will coalesce two adjacent free spaces into a larger
# free space, if it cannot find a single free space of the
# needed size.
$free_list{$previous_start_id} = $end_id;
# uptdate saved key
# do not change $previous_start_id
$previous_end_id = $end_id;
$honeycomb_count++;
$honeycomb_count_ts++;
}
else
{
# This free space is not immediately preceeded by a free segment.
# Create a new entry in the free_list.
$free_list{$start_id} = $end_id;
# update saved keys
$previous_end_id = $end_id;
$previous_start_id = $start_id;
}
}
# close the cursor
&ora_close($free_space_csr) || die $ora_errstr;
# Look at each entry in the %free_list. # Identify the largest piece of contiguous free space.
$max_free_bytes = 0;
$start_biggest = 0;
$end_biggest = 0;
while (($start_id, $end_id) = each %free_list) {
($file_id, $start_block) = split (/:/, $start_id);
($file_id, $end_block) = split (/:/, $end_id);
$bytes = ($end_block - $start_block + 1) * $ORA_BLOCK_SIZE;
if ($max_free_bytes < $bytes)
{
$max_free_bytes = $bytes;
$start_biggest = $start_block;
$end_biggest = $end_block;
}
# Save the size of each free space.
push(_at_free_bytes, $bytes);
}
# Return the size the largest piece of contiguous free space.
return $max_free_bytes;
}
sub NUMERICALLY
{
# This subroutine is used to sort by number value, rather than string
# contents.
$a <=> $b;
}
sub REPAIR_HONEYCOMBS
{
# This subroutine fills up all free space with dummy tables, then
# drops the dummy tables. This will force the RDBMS to coalesce the
# honeycombed free space. Honeycombs are adjacent free spaces.
# Sort the sizes of the free spaces in descending order.
undef _at_sorted_free_bytes;
_at_sorted_free_bytes = reverse sort NUMERICALLY @free_bytes;
# Create the dummy tables from the largest size down to the smallest size.
$i = 0;
while (_at_sorted_free_bytes)
{
$dropme_size = shift _at_sorted_free_bytes;
last if $dropme_size <= $ORA_BLOCK_SIZE; # don't create one-block table
$i++;
$create_statement = "CREATE TABLE DROPME${i} (DUMMY NUMBER)
STORAGE(INITIAL $dropme_size) TABLESPACE $tablespace_name";
&ora_do($lda, $create_statement)
|| warn "$create_statement\n$ora_errstr";
}
# Drop the dummy tables.
while ($i)
{
$drop_statement = "DROP TABLE DROPME${i}";
&ora_do($lda, $drop_statement)
|| warn "$drop_statement\n$ora_errstr";
$i--;
}
}
sub SUM_FREE_SPACE
{
# This subroutine calculates the sum of free bytes and the count of
# free extents in a tablespace.
# It requires tablespace_name as an input argument.
# The calculated values are stored in global variables.
local ($ts) = _at__;
# This query will get free space information about one tablespace.
$sum_free_query = <<EOFEOF;
select sum(space.bytes), count('x')
from sys.dba_free_space space, sys.dba_data_files files
where space.tablespace_name = files.tablespace_name and space.file_id = files.file_id and files.tablespace_name = '$ts'
and status = 'AVAILABLE'
EOFEOF # open the cursor
$sum_free_csr = &ora_open($lda, $sum_free_query) || die $ora_errstr;
# Get summary free_space info for one tablespace. There should only
# be one row.
($total_free_bytes, $total_free_extents) = &ora_fetch($sum_free_csr);
warn $ora_errstr if ($ora_errno);
# close the cursor
&ora_close($sum_free_csr) || die $ora_errstr;
}
sub INSERT_TABLESPACE_HISTORY
{
# This subroutine inserts current statistics into TABLESPACE_HIST.
# get current date in Oracle default date format _at_MoY = ('JAN','FEB','MAR','APR','MAY','JUN',
'JUL','AUG','SEP','OCT','NOV','DEC'); ($sec, $min, $hour, $mday, $mon, $year) = localtime(time); $dd_mon_yy = sprintf("%02d-%s-%02d", $mday, $MoY[$mon], $year);
while (($tablespace_name, $percent_free) = each %ts_current) {
$delete_statement = <<EOFEOF;
delete from TABLESPACE_HIST
where tablespace_name = '$tablespace_name' and check_date = '$dd_mon_yy'
EOFEOF
$insert_statement = <<EOFEOF;
insert into TABLESPACE_HIST
values ('$tablespace_name', $percent_free, '$dd_mon_yy')
EOFEOF
&ora_do($lda, $delete_statement) || die "$delete_statement\n$ora_errstr";
&ora_do($lda, $insert_statement) || die "$insert_statement\n$ora_errstr";
}
}
sub GET_TABLESPACE_HISTORY
{
# This subroutine produces a report listing historical percent_available, # by tablespace.
format HIST_TOP =
ECMS Database Administration
Tablespace History Report
_at_|||||||||||||||||||||||||||
$date
- Percent Available ----- 1 Month 3 Months 6 Months Tablespace Current Yesterday Last Week Ago Ago Ago -------------- ------- --------- --------- ------- -------- --------
.
format HIST =
_at_<<<<<<<<<<<<< @>> @>> @>> @>> @>> @>>
$tablespace_name, $current, $yesterday, $last_week, $last_month, $three_months_ago, $six_months_ago
.
$- = 0; # force top-of-form $^ = "HIST_TOP"; $~ = "HIST";
# this query will get historical information about each tablespace.
$historical_query = <<EOFEOF;
select tablespace_name, max(decode(check_date,to_char(sysdate), round(pct_available),NULL)) "current", max(decode(check_date,to_char(sysdate-1), round(pct_available),NULL)) yesterday, max(decode(check_date,to_char(sysdate-7), round(pct_available),NULL)) last_week, max(decode(check_date,to_char(sysdate-30), round(pct_available),NULL)) last_month, max(decode(check_date,to_char(sysdate-90), round(pct_available),NULL)) three_months_ago, max(decode(check_date,to_char(sysdate-180), round(pct_available),NULL)) six_months_agofrom tablespace_hist
group by tablespace_name
order by tablespace_name
EOFEOF # open the cursor
$historical_csr = &ora_open($lda, $historical_query) || die $ora_errstr;
# get the history for each tablespace while (($tablespace_name, $current, $yesterday, $last_week, $last_month,
$three_months_ago, $six_months_ago) =
&ora_fetch($historical_csr))
{
# Print the report.
write;
}
# close the cursor
&ora_close($historical_csr) || die $ora_errstr;
print "\n\n\n";
}
sub EXAMINE_OBJECTS
{
# This subroutine examines the future space needs of each object.
# It produces a report listing each object that will not find enough space,
# the next time it tries to extend.
$problem = 0;
$no_problem = 0;
format PROBLEM_TOP =
ECMS Database Administration
Tablespace Examination Report
_at_|||||||||||||||||||||||||||
$date
Pct
Owner Object Name Tablespace Ext Next Ext Inc
------------ ------------------------------ ----------- --- ------------ ---
.
format PROBLEM =
_at_<<<<<<<<<<< @<<<<<<<<<<<<<<<<<<<<<<<<<<<<< @<<<<<<<<<< @## @>>>>>>>>>>> @##
$owner, $segment_name, $tablespace_name, $extents, $next_extentX, $pct_increase
_at_>>>>>>>>>>>> will be requested, but only @>>>>>>>>>>>> is available.
$calculated_next_extentX, $ts_maximumX
.
$- = 0; # force top-of-form $^ = "PROBLEM_TOP"; $~ = "PROBLEM";
format PROBLEM_BOTTOM =
_at_>>>>>> Objects will have a problem extending. $problemX
_at_>>>>>> Objects will not have a problem extending. $no_problemX
.
# this query will get size information about each object.
$object_query = <<EOFEOF;
select seg.tablespace_name, seg.owner, seg.segment_name, seg.extents,
nvl(tab.next_extent, ind.next_extent),
nvl(tab.pct_increase, ind.pct_increase)
from sys.dba_segments seg, sys.dba_tables tab, sys.dba_indexes ind
where seg.owner = tab.owner(+)
and seg.segment_name = tab.table_name(+)
and seg.owner = ind.owner(+)
and seg.segment_name = ind.index_name(+)
and (ind.index_name(+) is not null or tab.table_name(+) is not null)
order by 2,1
EOFEOF # open the cursor
$object_csr = &ora_open($lda, $object_query) || die $ora_errstr;
# get all objects
while (($tablespace_name, $owner, $segment_name, $extents, $next_extent,
$pct_increase) = &ora_fetch($object_csr)) {
# examine the object
# The calculation used for the size of the next extent is:
# NEXT * ((1 + PERCENT_INCREASE/100) to the power of (new_ext_num -2))
# where:
# NEXT is the NEXT parameter from the "create object" storage clause
$calculated_next_extent =
$next_extent * ( (1 + ($pct_increase/100)) ** (($extents + 1) - 2) );
# round up to an even multiple of ORA_BLOCK_SIZE.
$calculated_next_extent =
(int($calculated_next_extent/$ORA_BLOCK_SIZE) + 1) * $ORA_BLOCK_SIZE
unless ($calculated_next_extent % $ORA_BLOCK_SIZE == 0);
if ($calculated_next_extent <= $ts_max{$tablespace_name})
{
# this object will be able to extend
$no_problem++;
}
else
{
# This object will not be able to extend. Write the report.
$problem++;
# Format the big numbers, by adding commas.
$next_extentX = &commas($next_extent);
$calculated_next_extentX = &commas($calculated_next_extent);
$ts_maximumX = &commas($ts_max{$tablespace_name});
write;
}
}
# close the cursor
&ora_close($object_csr) || die $ora_errstr;
# Format the big numbers, by adding commas.
$problemX = &commas($problem);
$no_problemX = &commas($no_problem);
# Write the final report totals.
$~ = 'PROBLEM_BOTTOM';
write;
}
sub INIT
{
# This subroutine defines constants, processes command line arguments, # and connects to the database.
# Hide the command line passwords from anyone using the "ps" command.
# Users of the "ps" command will only see "my_prog", instead of
# "/usr/local/bin/my_prog my_passwd".
$ARGV[0] =~ s/\s+.*//; # get rid of arguments in ps listing
$0 =~ s=.*/==; # get rid of path to program name
$ORA_BLOCK_SIZE = 2048;
require "ctime.pl";
require "getopts.pl";
#get command line options
&Getopts('hr');
$sid = "$ARGV[0]";
$userid = "$ARGV[1]";
$password = "$ARGV[2]";
$userid =~ tr/a-z/A-Z/; # change $userid to upper case.
chop($date = &ctime(time));
$usage = <<EOFEOF;
Usage: $0 [-r] [-h] sid userid password
-r = repair honeycombed free space
-h = help
sid = $ORACLE_SID instance name
userid = oracle userid
password = oracle password
Description:
This oraperl program identifies database objects that will not be able to find enough freespace on their next extension. It creates these reports:
- Tablespace Free Space Report
- Tablespace History Report
- Tablespace Examination Report
- Report Generation Statistics
The Oracle userid must have select access to the SYS.DBA% tables.
EOFEOF
if ($opt_h)
{
print $usage; exit;
}
# argv[0] = sid, argv[1] = userid, argv[2] = password
# $#ARGV == 2 means 3 command line arguments were entered
if ($#ARGV != 2)
{
print "Error: Incorrect number of command line arguments\n"; print $usage; exit;
}
warn ("Userid is not SYSTEM. It is $userid\n")
unless ($userid =~ /SYSTEM/);
# connect to the database
$lda = &ora_login("$sid", "$userid", "$password")
|| die "$sid, $userid, $password $ora_errstr";
select(STDERR); $| = 1; # make unbuffered
select(STDOUT); $| = 1; # make unbuffered
}
sub CLEANUP
{
if ($opt_r)
{
print "\n\nThe repair of $honeycomb_count honeycombed free spaces is complete.\n"; }
format EOR =
End of Report
.
# Write the End of Report trailer.
$~ = 'EOR';
write;
# disconnect from the database
&ora_logoff($lda) || warn "REM $ora_errstr";
}
# from the Camel book, chapter 5
sub commas {
local($_) = _at__;
1 while s/(.*\d)(\d\d\d)/$1,$2/;
$_;
}
#------------------------------------------------------------------------- # # end of source
#
#---------------------------------------------------------------------------- CUT HERE --- Received on Wed Nov 25 1992 - 01:27:37 CET