1 2#pragma ident "%Z%%M% %I% %E% SMI" 3 4# Run this TCL script using "testfixture" in order get a report that shows 5# how much disk space is used by a particular data to actually store data 6# versus how much space is unused. 7# 8 9# Get the name of the database to analyze 10# 11if {[llength $argv]!=1} { 12 puts stderr "Usage: $argv0 database-name" 13 exit 1 14} 15set file_to_analyze [lindex $argv 0] 16 17# Open the database 18# 19sqlite db [lindex $argv 0] 20set DB [btree_open [lindex $argv 0]] 21 22# Output the schema for the generated report 23# 24puts \ 25{BEGIN; 26CREATE TABLE space_used( 27 name clob, -- Name of a table or index in the database file 28 is_index boolean, -- TRUE if it is an index, false for a table 29 payload int, -- Total amount of data stored in this table or index 30 pri_pages int, -- Number of primary pages used 31 ovfl_pages int, -- Number of overflow pages used 32 pri_unused int, -- Number of unused bytes on primary pages 33 ovfl_unused int -- Number of unused bytes on overflow pages 34);} 35 36# This query will be used to find the root page number for every index and 37# table in the database. 38# 39set sql { 40 SELECT name, type, rootpage FROM sqlite_master 41 UNION ALL 42 SELECT 'sqlite_master', 'table', 2 43 ORDER BY 1 44} 45 46# Initialize variables used for summary statistics. 47# 48set total_size 0 49set total_primary 0 50set total_overflow 0 51set total_unused_primary 0 52set total_unused_ovfl 0 53 54# Analyze every table in the database, one at a time. 55# 56foreach {name type rootpage} [db eval $sql] { 57 set cursor [btree_cursor $DB $rootpage 0] 58 set go [btree_first $cursor] 59 set size 0 60 catch {unset pg_used} 61 set unused_ovfl 0 62 set n_overflow 0 63 while {$go==0} { 64 set payload [btree_payload_size $cursor] 65 incr size $payload 66 set stat [btree_cursor_dump $cursor] 67 set pgno [lindex $stat 0] 68 set freebytes [lindex $stat 4] 69 set pg_used($pgno) $freebytes 70 if {$payload>238} { 71 set n [expr {($payload-238+1019)/1020}] 72 incr n_overflow $n 73 incr unused_ovfl [expr {$n*1020+238-$payload}] 74 } 75 set go [btree_next $cursor] 76 } 77 btree_close_cursor $cursor 78 set n_primary [llength [array names pg_used]] 79 set unused_primary 0 80 foreach x [array names pg_used] {incr unused_primary $pg_used($x)} 81 regsub -all ' $name '' name 82 puts -nonewline "INSERT INTO space_used VALUES('$name'" 83 puts -nonewline ",[expr {$type=="index"}]" 84 puts ",$size,$n_primary,$n_overflow,$unused_primary,$unused_ovfl);" 85 incr total_size $size 86 incr total_primary $n_primary 87 incr total_overflow $n_overflow 88 incr total_unused_primary $unused_primary 89 incr total_unused_ovfl $unused_ovfl 90} 91 92# Output summary statistics: 93# 94puts "-- Total payload size: $total_size" 95puts "-- Total pages used: $total_primary primary and $total_overflow overflow" 96set file_pgcnt [expr {[file size [lindex $argv 0]]/1024}] 97puts -nonewline "-- Total unused bytes on primary pages: $total_unused_primary" 98if {$total_primary>0} { 99 set upp [expr {$total_unused_primary/$total_primary}] 100 puts " (avg $upp bytes/page)" 101} else { 102 puts "" 103} 104puts -nonewline "-- Total unused bytes on overflow pages: $total_unused_ovfl" 105if {$total_overflow>0} { 106 set upp [expr {$total_unused_ovfl/$total_overflow}] 107 puts " (avg $upp bytes/page)" 108} else { 109 puts "" 110} 111set n_free [expr {$file_pgcnt-$total_primary-$total_overflow}] 112if {$n_free>0} {incr n_free -1} 113puts "-- Total pages on freelist: $n_free" 114puts "COMMIT;" 115