spaceanal.tcl revision 7c478bd95313f5f23a4c958a745db2134aa03244
#pragma ident "%Z%%M% %I% %E% SMI"
# Run this TCL script using "testfixture" in order get a report that shows
# how much disk space is used by a particular data to actually store data
# versus how much space is unused.
#
# Get the name of the database to analyze
#
puts stderr "Usage: $argv0 database-name"
exit 1
}
if {![file exists $file_to_analyze]} {
puts stderr "No such file: $file_to_analyze"
exit 1
}
if {![file readable $file_to_analyze]} {
puts stderr "File is not readable: $file_to_analyze"
exit 1
}
puts stderr "Empty or malformed database: $file_to_analyze"
exit 1
}
# Open the database
#
# In-memory database for collecting statistics
#
set tabledef\
);}
# This query will be used to find the root page number for every index and
# table in the database.
#
set sql {
}
# Analyze every table in the database, one at a time.
#
puts stderr "Analyzing $name..."
catch {unset pg_used}
set unused_ovfl 0
set n_overflow 0
while {$go==0} {
incr n_entry
# if {[lindex $stat 8]==0} {error "overflow is empty with $payload"}
incr n_overflow $n
incr cnt_ovfl
} else {
# if {[lindex $stat 8]!=0} {error "overflow not empty with $payload"}
}
}
set unused_primary 0
set sql "INSERT INTO space_used VALUES('$name'"
append sql ",$size,$mx_size,$cnt_ovfl,"
append sql "$n_primary,$n_overflow,$unused_primary,$unused_ovfl);"
}
# Generate a single line of output in the statistics section of the
# report.
#
set extra " $extra"
}
puts "$title$dots $value$sp2$extra"
}
# Generate a formatted percentage value for $num/$denom
#
} else {
}
}
# Generate a subreport that covers some subset of the database.
# the $where clause determines which subset to analyze.
#
mem eval "SELECT sum(nentry) AS nentry, \
sum(payload) AS payload, \
sum(CASE is_index WHEN 1 THEN 0 ELSE payload-4*nentry END) \
AS data, \
max(mx_payload) AS mx_payload, \
sum(n_ovfl) as n_ovfl, \
sum(pri_pages) AS pri_pages, \
sum(ovfl_pages) AS ovfl_pages, \
sum(pri_unused) AS pri_unused, \
sum(ovfl_unused) AS ovfl_unused \
puts ""
set stars "***********************************"
puts "*** $title $stars"
puts ""
statline "Percentage of total database" \
statline "Average fanout" \
return 1
}
# Output summary statistics:
#
puts "/** Disk-Space Utilization Report For $file_to_analyze"
puts "*** As of [clock format [clock seconds] -format {%Y-%b-%d %H:%M:%S}]"
puts ""
WHERE NOT is_index AND name!='sqlite_master'"]
# Output table rankings
#
puts ""
puts "*** Page counts for all tables with their indices ********************"
puts ""
}
# Output subreports
#
}
}
subreport "Table $name and all its indices" "tblname='$qn'"
subreport "Table $name w/o any indices" "name='$qn'"
subreport "Indices of table $name" "tblname='$qn' AND is_index"
} else {
subreport "Table $name" "name='$qn'"
}
}
# Output instructions on what the numbers above mean.
#
puts {
*** Definitions ******************************************************
size.
}
# Output the database
#
puts "**********************************************************************"
puts "The entire text of this report can be sourced into any SQL database"
puts "engine for further analysis. All of the text above is an SQL comment."
puts "The data used to generate this report follows:"
puts "*/"
puts "BEGIN;"
puts $tabledef
unset -nocomplain x
puts -nonewline "INSERT INTO space_used VALUES("
puts -nonewline "'$qn','$qtn',"
puts -nonewline "$x(is_index),$x(nentry),$x(payload),$x(mx_payload),"
puts -nonewline "$x(n_ovfl),$x(pri_pages),$x(ovfl_pages),$x(pri_unused),"
puts "$x(ovfl_unused));"
}
puts "COMMIT;"