1*c5c4113dSnw141292# Run this TCL script using "testfixture" in order get a report that shows 2*c5c4113dSnw141292# how much disk space is used by a particular data to actually store data 3*c5c4113dSnw141292# versus how much space is unused. 4*c5c4113dSnw141292# 5*c5c4113dSnw141292 6*c5c4113dSnw141292# Get the name of the database to analyze 7*c5c4113dSnw141292# 8*c5c4113dSnw141292if {[llength $argv]!=1} { 9*c5c4113dSnw141292 puts stderr "Usage: $argv0 database-name" 10*c5c4113dSnw141292 exit 1 11*c5c4113dSnw141292} 12*c5c4113dSnw141292set file_to_analyze [lindex $argv 0] 13*c5c4113dSnw141292if {![file exists $file_to_analyze]} { 14*c5c4113dSnw141292 puts stderr "No such file: $file_to_analyze" 15*c5c4113dSnw141292 exit 1 16*c5c4113dSnw141292} 17*c5c4113dSnw141292if {![file readable $file_to_analyze]} { 18*c5c4113dSnw141292 puts stderr "File is not readable: $file_to_analyze" 19*c5c4113dSnw141292 exit 1 20*c5c4113dSnw141292} 21*c5c4113dSnw141292if {[file size $file_to_analyze]<2048} { 22*c5c4113dSnw141292 puts stderr "Empty or malformed database: $file_to_analyze" 23*c5c4113dSnw141292 exit 1 24*c5c4113dSnw141292} 25*c5c4113dSnw141292 26*c5c4113dSnw141292# Open the database 27*c5c4113dSnw141292# 28*c5c4113dSnw141292sqlite db [lindex $argv 0] 29*c5c4113dSnw141292set DB [btree_open [lindex $argv 0]] 30*c5c4113dSnw141292 31*c5c4113dSnw141292# In-memory database for collecting statistics 32*c5c4113dSnw141292# 33*c5c4113dSnw141292sqlite mem :memory: 34*c5c4113dSnw141292set tabledef\ 35*c5c4113dSnw141292{CREATE TABLE space_used( 36*c5c4113dSnw141292 name clob, -- Name of a table or index in the database file 37*c5c4113dSnw141292 tblname clob, -- Name of associated table 38*c5c4113dSnw141292 is_index boolean, -- TRUE if it is an index, false for a table 39*c5c4113dSnw141292 nentry int, -- Number of entries in the BTree 40*c5c4113dSnw141292 payload int, -- Total amount of data stored in this table or index 41*c5c4113dSnw141292 mx_payload int, -- Maximum payload size 42*c5c4113dSnw141292 n_ovfl int, -- Number of entries that overflow 43*c5c4113dSnw141292 pri_pages int, -- Number of primary pages used 44*c5c4113dSnw141292 ovfl_pages int, -- Number of overflow pages used 45*c5c4113dSnw141292 pri_unused int, -- Number of unused bytes on primary pages 46*c5c4113dSnw141292 ovfl_unused int -- Number of unused bytes on overflow pages 47*c5c4113dSnw141292);} 48*c5c4113dSnw141292mem eval $tabledef 49*c5c4113dSnw141292 50*c5c4113dSnw141292# This query will be used to find the root page number for every index and 51*c5c4113dSnw141292# table in the database. 52*c5c4113dSnw141292# 53*c5c4113dSnw141292set sql { 54*c5c4113dSnw141292 SELECT name, tbl_name, type, rootpage 55*c5c4113dSnw141292 FROM sqlite_master WHERE type IN ('table','index') 56*c5c4113dSnw141292 UNION ALL 57*c5c4113dSnw141292 SELECT 'sqlite_master', 'sqlite_master', 'table', 2 58*c5c4113dSnw141292 ORDER BY 1 59*c5c4113dSnw141292} 60*c5c4113dSnw141292 61*c5c4113dSnw141292# Analyze every table in the database, one at a time. 62*c5c4113dSnw141292# 63*c5c4113dSnw141292foreach {name tblname type rootpage} [db eval $sql] { 64*c5c4113dSnw141292 puts stderr "Analyzing $name..." 65*c5c4113dSnw141292 set cursor [btree_cursor $DB $rootpage 0] 66*c5c4113dSnw141292 set go [btree_first $cursor] 67*c5c4113dSnw141292 set size 0 68*c5c4113dSnw141292 catch {unset pg_used} 69*c5c4113dSnw141292 set unused_ovfl 0 70*c5c4113dSnw141292 set n_overflow 0 71*c5c4113dSnw141292 set cnt_ovfl 0 72*c5c4113dSnw141292 set n_entry 0 73*c5c4113dSnw141292 set mx_size 0 74*c5c4113dSnw141292 set pg_used($rootpage) 1016 75*c5c4113dSnw141292 while {$go==0} { 76*c5c4113dSnw141292 incr n_entry 77*c5c4113dSnw141292 set payload [btree_payload_size $cursor] 78*c5c4113dSnw141292 incr size $payload 79*c5c4113dSnw141292 set stat [btree_cursor_dump $cursor] 80*c5c4113dSnw141292 set pgno [lindex $stat 0] 81*c5c4113dSnw141292 set freebytes [lindex $stat 4] 82*c5c4113dSnw141292 set pg_used($pgno) $freebytes 83*c5c4113dSnw141292 if {$payload>236} { 84*c5c4113dSnw141292 # if {[lindex $stat 8]==0} {error "overflow is empty with $payload"} 85*c5c4113dSnw141292 set n [expr {($payload-236+1019)/1020}] 86*c5c4113dSnw141292 incr n_overflow $n 87*c5c4113dSnw141292 incr cnt_ovfl 88*c5c4113dSnw141292 incr unused_ovfl [expr {$n*1020+236-$payload}] 89*c5c4113dSnw141292 } else { 90*c5c4113dSnw141292 # if {[lindex $stat 8]!=0} {error "overflow not empty with $payload"} 91*c5c4113dSnw141292 } 92*c5c4113dSnw141292 if {$payload>$mx_size} {set mx_size $payload} 93*c5c4113dSnw141292 set go [btree_next $cursor] 94*c5c4113dSnw141292 } 95*c5c4113dSnw141292 btree_close_cursor $cursor 96*c5c4113dSnw141292 set n_primary [llength [array names pg_used]] 97*c5c4113dSnw141292 set unused_primary 0 98*c5c4113dSnw141292 foreach x [array names pg_used] {incr unused_primary $pg_used($x)} 99*c5c4113dSnw141292 regsub -all ' $name '' name 100*c5c4113dSnw141292 set sql "INSERT INTO space_used VALUES('$name'" 101*c5c4113dSnw141292 regsub -all ' $tblname '' tblname 102*c5c4113dSnw141292 append sql ",'$tblname',[expr {$type=="index"}],$n_entry" 103*c5c4113dSnw141292 append sql ",$size,$mx_size,$cnt_ovfl," 104*c5c4113dSnw141292 append sql "$n_primary,$n_overflow,$unused_primary,$unused_ovfl);" 105*c5c4113dSnw141292 mem eval $sql 106*c5c4113dSnw141292} 107*c5c4113dSnw141292 108*c5c4113dSnw141292# Generate a single line of output in the statistics section of the 109*c5c4113dSnw141292# report. 110*c5c4113dSnw141292# 111*c5c4113dSnw141292proc statline {title value {extra {}}} { 112*c5c4113dSnw141292 set len [string length $title] 113*c5c4113dSnw141292 set dots [string range {......................................} $len end] 114*c5c4113dSnw141292 set len [string length $value] 115*c5c4113dSnw141292 set sp2 [string range { } $len end] 116*c5c4113dSnw141292 if {$extra ne ""} { 117*c5c4113dSnw141292 set extra " $extra" 118*c5c4113dSnw141292 } 119*c5c4113dSnw141292 puts "$title$dots $value$sp2$extra" 120*c5c4113dSnw141292} 121*c5c4113dSnw141292 122*c5c4113dSnw141292# Generate a formatted percentage value for $num/$denom 123*c5c4113dSnw141292# 124*c5c4113dSnw141292proc percent {num denom} { 125*c5c4113dSnw141292 if {$denom==0.0} {return ""} 126*c5c4113dSnw141292 set v [expr {$num*100.0/$denom}] 127*c5c4113dSnw141292 if {$v>1.0 && $v<99.0} { 128*c5c4113dSnw141292 return [format %4.1f%% $v] 129*c5c4113dSnw141292 } elseif {$v<0.1 || $v>99.9} { 130*c5c4113dSnw141292 return [format %6.3f%% $v] 131*c5c4113dSnw141292 } else { 132*c5c4113dSnw141292 return [format %5.2f%% $v] 133*c5c4113dSnw141292 } 134*c5c4113dSnw141292} 135*c5c4113dSnw141292 136*c5c4113dSnw141292# Generate a subreport that covers some subset of the database. 137*c5c4113dSnw141292# the $where clause determines which subset to analyze. 138*c5c4113dSnw141292# 139*c5c4113dSnw141292proc subreport {title where} { 140*c5c4113dSnw141292 set hit 0 141*c5c4113dSnw141292 mem eval "SELECT sum(nentry) AS nentry, \ 142*c5c4113dSnw141292 sum(payload) AS payload, \ 143*c5c4113dSnw141292 sum(CASE is_index WHEN 1 THEN 0 ELSE payload-4*nentry END) \ 144*c5c4113dSnw141292 AS data, \ 145*c5c4113dSnw141292 max(mx_payload) AS mx_payload, \ 146*c5c4113dSnw141292 sum(n_ovfl) as n_ovfl, \ 147*c5c4113dSnw141292 sum(pri_pages) AS pri_pages, \ 148*c5c4113dSnw141292 sum(ovfl_pages) AS ovfl_pages, \ 149*c5c4113dSnw141292 sum(pri_unused) AS pri_unused, \ 150*c5c4113dSnw141292 sum(ovfl_unused) AS ovfl_unused \ 151*c5c4113dSnw141292 FROM space_used WHERE $where" {} {set hit 1} 152*c5c4113dSnw141292 if {!$hit} {return 0} 153*c5c4113dSnw141292 puts "" 154*c5c4113dSnw141292 set len [string length $title] 155*c5c4113dSnw141292 incr len 5 156*c5c4113dSnw141292 set stars "***********************************" 157*c5c4113dSnw141292 append stars $stars 158*c5c4113dSnw141292 set stars [string range $stars $len end] 159*c5c4113dSnw141292 puts "*** $title $stars" 160*c5c4113dSnw141292 puts "" 161*c5c4113dSnw141292 statline "Percentage of total database" \ 162*c5c4113dSnw141292 [percent [expr {$pri_pages+$ovfl_pages}] $::file_pgcnt] 163*c5c4113dSnw141292 statline "Number of entries" $nentry 164*c5c4113dSnw141292 set storage [expr {($pri_pages+$ovfl_pages)*1024}] 165*c5c4113dSnw141292 statline "Bytes of storage consumed" $storage 166*c5c4113dSnw141292 statline "Bytes of payload" $payload [percent $payload $storage] 167*c5c4113dSnw141292 statline "Bytes of data" $data [percent $data $storage] 168*c5c4113dSnw141292 set key [expr {$payload-$data}] 169*c5c4113dSnw141292 statline "Bytes of key" $key [percent $key $storage] 170*c5c4113dSnw141292 set avgpay [expr {$nentry>0?$payload/$nentry:0}] 171*c5c4113dSnw141292 statline "Average payload per entry" $avgpay 172*c5c4113dSnw141292 set avgunused [expr {$nentry>0?($pri_unused+$ovfl_unused)/$nentry:0}] 173*c5c4113dSnw141292 statline "Average unused bytes per entry" $avgunused 174*c5c4113dSnw141292 statline "Average fanout" \ 175*c5c4113dSnw141292 [format %.2f [expr {$pri_pages==0?0:($nentry+0.0)/$pri_pages}]] 176*c5c4113dSnw141292 statline "Maximum payload per entry" $mx_payload 177*c5c4113dSnw141292 statline "Entries that use overflow" $n_ovfl [percent $n_ovfl $nentry] 178*c5c4113dSnw141292 statline "Total pages used" [set allpgs [expr {$pri_pages+$ovfl_pages}]] 179*c5c4113dSnw141292 statline "Primary pages used" $pri_pages ;# [percent $pri_pages $allpgs] 180*c5c4113dSnw141292 statline "Overflow pages used" $ovfl_pages ;# [percent $ovfl_pages $allpgs] 181*c5c4113dSnw141292 statline "Unused bytes on primary pages" $pri_unused \ 182*c5c4113dSnw141292 [percent $pri_unused [expr {$pri_pages*1024}]] 183*c5c4113dSnw141292 statline "Unused bytes on overflow pages" $ovfl_unused \ 184*c5c4113dSnw141292 [percent $ovfl_unused [expr {$ovfl_pages*1024}]] 185*c5c4113dSnw141292 set allunused [expr {$ovfl_unused+$pri_unused}] 186*c5c4113dSnw141292 statline "Unused bytes on all pages" $allunused \ 187*c5c4113dSnw141292 [percent $allunused [expr {$allpgs*1024}]] 188*c5c4113dSnw141292 return 1 189*c5c4113dSnw141292} 190*c5c4113dSnw141292 191*c5c4113dSnw141292# Output summary statistics: 192*c5c4113dSnw141292# 193*c5c4113dSnw141292puts "/** Disk-Space Utilization Report For $file_to_analyze" 194*c5c4113dSnw141292puts "*** As of [clock format [clock seconds] -format {%Y-%b-%d %H:%M:%S}]" 195*c5c4113dSnw141292puts "" 196*c5c4113dSnw141292set fsize [file size [lindex $argv 0]] 197*c5c4113dSnw141292set file_pgcnt [expr {$fsize/1024}] 198*c5c4113dSnw141292set usedcnt [mem eval {SELECT sum(pri_pages+ovfl_pages) FROM space_used}] 199*c5c4113dSnw141292set freecnt [expr {$file_pgcnt-$usedcnt-1}] 200*c5c4113dSnw141292set freecnt2 [lindex [btree_get_meta $DB] 0] 201*c5c4113dSnw141292statline {Pages in the whole file (measured)} $file_pgcnt 202*c5c4113dSnw141292set file_pgcnt2 [expr {$usedcnt+$freecnt2+1}] 203*c5c4113dSnw141292statline {Pages in the whole file (calculated)} $file_pgcnt2 204*c5c4113dSnw141292statline {Pages that store data} $usedcnt [percent $usedcnt $file_pgcnt] 205*c5c4113dSnw141292statline {Pages on the freelist (per header)}\ 206*c5c4113dSnw141292 $freecnt2 [percent $freecnt2 $file_pgcnt] 207*c5c4113dSnw141292statline {Pages on the freelist (calculated)}\ 208*c5c4113dSnw141292 $freecnt [percent $freecnt $file_pgcnt] 209*c5c4113dSnw141292statline {Header pages} 1 [percent 1 $file_pgcnt] 210*c5c4113dSnw141292 211*c5c4113dSnw141292set ntable [db eval {SELECT count(*)+1 FROM sqlite_master WHERE type='table'}] 212*c5c4113dSnw141292statline {Number of tables in the database} $ntable 213*c5c4113dSnw141292set nindex [db eval {SELECT count(*) FROM sqlite_master WHERE type='index'}] 214*c5c4113dSnw141292set autoindex [db eval {SELECT count(*) FROM sqlite_master 215*c5c4113dSnw141292 WHERE type='index' AND name LIKE '(% autoindex %)'}] 216*c5c4113dSnw141292set manindex [expr {$nindex-$autoindex}] 217*c5c4113dSnw141292statline {Number of indices} $nindex 218*c5c4113dSnw141292statline {Number of named indices} $manindex [percent $manindex $nindex] 219*c5c4113dSnw141292statline {Automatically generated indices} $autoindex \ 220*c5c4113dSnw141292 [percent $autoindex $nindex] 221*c5c4113dSnw141292 222*c5c4113dSnw141292set bytes_data [mem eval "SELECT sum(payload-4*nentry) FROM space_used 223*c5c4113dSnw141292 WHERE NOT is_index AND name!='sqlite_master'"] 224*c5c4113dSnw141292set total_payload [mem eval "SELECT sum(payload) FROM space_used"] 225*c5c4113dSnw141292statline "Size of the file in bytes" $fsize 226*c5c4113dSnw141292statline "Bytes of payload stored" $total_payload \ 227*c5c4113dSnw141292 [percent $total_payload $fsize] 228*c5c4113dSnw141292statline "Bytes of user data stored" $bytes_data \ 229*c5c4113dSnw141292 [percent $bytes_data $fsize] 230*c5c4113dSnw141292 231*c5c4113dSnw141292# Output table rankings 232*c5c4113dSnw141292# 233*c5c4113dSnw141292puts "" 234*c5c4113dSnw141292puts "*** Page counts for all tables with their indices ********************" 235*c5c4113dSnw141292puts "" 236*c5c4113dSnw141292mem eval {SELECT tblname, count(*) AS cnt, sum(pri_pages+ovfl_pages) AS size 237*c5c4113dSnw141292 FROM space_used GROUP BY tblname ORDER BY size DESC, tblname} {} { 238*c5c4113dSnw141292 statline [string toupper $tblname] $size [percent $size $file_pgcnt] 239*c5c4113dSnw141292} 240*c5c4113dSnw141292 241*c5c4113dSnw141292# Output subreports 242*c5c4113dSnw141292# 243*c5c4113dSnw141292if {$nindex>0} { 244*c5c4113dSnw141292 subreport {All tables and indices} 1 245*c5c4113dSnw141292} 246*c5c4113dSnw141292subreport {All tables} {NOT is_index} 247*c5c4113dSnw141292if {$nindex>0} { 248*c5c4113dSnw141292 subreport {All indices} {is_index} 249*c5c4113dSnw141292} 250*c5c4113dSnw141292foreach tbl [mem eval {SELECT name FROM space_used WHERE NOT is_index 251*c5c4113dSnw141292 ORDER BY name}] { 252*c5c4113dSnw141292 regsub ' $tbl '' qn 253*c5c4113dSnw141292 set name [string toupper $tbl] 254*c5c4113dSnw141292 set n [mem eval "SELECT count(*) FROM space_used WHERE tblname='$qn'"] 255*c5c4113dSnw141292 if {$n>1} { 256*c5c4113dSnw141292 subreport "Table $name and all its indices" "tblname='$qn'" 257*c5c4113dSnw141292 subreport "Table $name w/o any indices" "name='$qn'" 258*c5c4113dSnw141292 subreport "Indices of table $name" "tblname='$qn' AND is_index" 259*c5c4113dSnw141292 } else { 260*c5c4113dSnw141292 subreport "Table $name" "name='$qn'" 261*c5c4113dSnw141292 } 262*c5c4113dSnw141292} 263*c5c4113dSnw141292 264*c5c4113dSnw141292# Output instructions on what the numbers above mean. 265*c5c4113dSnw141292# 266*c5c4113dSnw141292puts { 267*c5c4113dSnw141292*** Definitions ****************************************************** 268*c5c4113dSnw141292 269*c5c4113dSnw141292Number of pages in the whole file 270*c5c4113dSnw141292 271*c5c4113dSnw141292 The number of 1024-byte pages that go into forming the complete database 272*c5c4113dSnw141292 273*c5c4113dSnw141292Pages that store data 274*c5c4113dSnw141292 275*c5c4113dSnw141292 The number of pages that store data, either as primary B*Tree pages or 276*c5c4113dSnw141292 as overflow pages. The number at the right is the data pages divided by 277*c5c4113dSnw141292 the total number of pages in the file. 278*c5c4113dSnw141292 279*c5c4113dSnw141292Pages on the freelist 280*c5c4113dSnw141292 281*c5c4113dSnw141292 The number of pages that are not currently in use but are reserved for 282*c5c4113dSnw141292 future use. The percentage at the right is the number of freelist pages 283*c5c4113dSnw141292 divided by the total number of pages in the file. 284*c5c4113dSnw141292 285*c5c4113dSnw141292Header pages 286*c5c4113dSnw141292 287*c5c4113dSnw141292 The number of pages of header overhead in the database. This value is 288*c5c4113dSnw141292 always 1. The percentage at the right is the number of header pages 289*c5c4113dSnw141292 divided by the total number of pages in the file. 290*c5c4113dSnw141292 291*c5c4113dSnw141292Number of tables in the database 292*c5c4113dSnw141292 293*c5c4113dSnw141292 The number of tables in the database, including the SQLITE_MASTER table 294*c5c4113dSnw141292 used to store schema information. 295*c5c4113dSnw141292 296*c5c4113dSnw141292Number of indices 297*c5c4113dSnw141292 298*c5c4113dSnw141292 The total number of indices in the database. 299*c5c4113dSnw141292 300*c5c4113dSnw141292Number of named indices 301*c5c4113dSnw141292 302*c5c4113dSnw141292 The number of indices created using an explicit CREATE INDEX statement. 303*c5c4113dSnw141292 304*c5c4113dSnw141292Automatically generated indices 305*c5c4113dSnw141292 306*c5c4113dSnw141292 The number of indices used to implement PRIMARY KEY or UNIQUE constraints 307*c5c4113dSnw141292 on tables. 308*c5c4113dSnw141292 309*c5c4113dSnw141292Size of the file in bytes 310*c5c4113dSnw141292 311*c5c4113dSnw141292 The total amount of disk space used by the entire database files. 312*c5c4113dSnw141292 313*c5c4113dSnw141292Bytes of payload stored 314*c5c4113dSnw141292 315*c5c4113dSnw141292 The total number of bytes of payload stored in the database. Payload 316*c5c4113dSnw141292 includes both key and data. The content of the SQLITE_MASTER table is 317*c5c4113dSnw141292 counted when computing this number. The percentage at the right shows 318*c5c4113dSnw141292 the payload divided by the total file size. 319*c5c4113dSnw141292 320*c5c4113dSnw141292Bytes of user data stored 321*c5c4113dSnw141292 322*c5c4113dSnw141292 The total number of bytes of data stored in the database, not counting 323*c5c4113dSnw141292 the database schema information stored in the SQLITE_MASTER table. The 324*c5c4113dSnw141292 percentage at the right is the user data size divided by the total file 325*c5c4113dSnw141292 size. 326*c5c4113dSnw141292 327*c5c4113dSnw141292Percentage of total database 328*c5c4113dSnw141292 329*c5c4113dSnw141292 The amount of the complete database file that is devoted to storing 330*c5c4113dSnw141292 information described by this category. 331*c5c4113dSnw141292 332*c5c4113dSnw141292Number of entries 333*c5c4113dSnw141292 334*c5c4113dSnw141292 The total number of B*Tree key/value pairs stored under this category. 335*c5c4113dSnw141292 336*c5c4113dSnw141292Bytes of storage consumed 337*c5c4113dSnw141292 338*c5c4113dSnw141292 The total amount of disk space required to store all B*Tree entries 339*c5c4113dSnw141292 under this category. The is the total number of pages used times 340*c5c4113dSnw141292 the pages size (1024). 341*c5c4113dSnw141292 342*c5c4113dSnw141292Bytes of payload 343*c5c4113dSnw141292 344*c5c4113dSnw141292 The amount of payload stored under this category. Payload is the sum 345*c5c4113dSnw141292 of keys and data. Each table entry has 4 bytes of key and an arbitrary 346*c5c4113dSnw141292 amount of data. Each index entry has 4 or more bytes of key and no 347*c5c4113dSnw141292 data. The percentage at the right is the bytes of payload divided by 348*c5c4113dSnw141292 the bytes of storage consumed. 349*c5c4113dSnw141292 350*c5c4113dSnw141292Bytes of data 351*c5c4113dSnw141292 352*c5c4113dSnw141292 The amount of data stored under this category. The data space reported 353*c5c4113dSnw141292 includes formatting information such as nul-terminators and field-lengths 354*c5c4113dSnw141292 that are stored with the data. The percentage at the right is the bytes 355*c5c4113dSnw141292 of data divided by bytes of storage consumed. 356*c5c4113dSnw141292 357*c5c4113dSnw141292Bytes of key 358*c5c4113dSnw141292 359*c5c4113dSnw141292 The sum of the sizes of all keys under this category. The percentage at 360*c5c4113dSnw141292 the right is the bytes of key divided by the bytes of storage consumed. 361*c5c4113dSnw141292 362*c5c4113dSnw141292Average payload per entry 363*c5c4113dSnw141292 364*c5c4113dSnw141292 The average amount of payload on each entry. This is just the bytes of 365*c5c4113dSnw141292 payload divided by the number of entries. 366*c5c4113dSnw141292 367*c5c4113dSnw141292Average unused bytes per entry 368*c5c4113dSnw141292 369*c5c4113dSnw141292 The average amount of free space remaining on all pages under this 370*c5c4113dSnw141292 category on a per-entry basis. This is the number of unused bytes on 371*c5c4113dSnw141292 all pages divided by the number of entries. 372*c5c4113dSnw141292 373*c5c4113dSnw141292Maximum payload per entry 374*c5c4113dSnw141292 375*c5c4113dSnw141292 The largest payload size of any entry. 376*c5c4113dSnw141292 377*c5c4113dSnw141292Entries that use overflow 378*c5c4113dSnw141292 379*c5c4113dSnw141292 Up to 236 bytes of payload for each entry are stored directly in the 380*c5c4113dSnw141292 primary B*Tree page. Any additional payload is stored on a linked list 381*c5c4113dSnw141292 of overflow pages. This is the number of entries that exceed 236 bytes 382*c5c4113dSnw141292 in size. The value to the right is the number of entries that overflow 383*c5c4113dSnw141292 divided by the total number of entries. 384*c5c4113dSnw141292 385*c5c4113dSnw141292Total pages used 386*c5c4113dSnw141292 387*c5c4113dSnw141292 This is the number of 1024 byte pages used to hold all information in 388*c5c4113dSnw141292 the current category. This is the sum of primary and overflow pages. 389*c5c4113dSnw141292 390*c5c4113dSnw141292Primary pages used 391*c5c4113dSnw141292 392*c5c4113dSnw141292 This is the number of primary B*Tree pages used. 393*c5c4113dSnw141292 394*c5c4113dSnw141292Overflow pages used 395*c5c4113dSnw141292 396*c5c4113dSnw141292 The total number of overflow pages used for this category. 397*c5c4113dSnw141292 398*c5c4113dSnw141292Unused bytes on primary pages 399*c5c4113dSnw141292 400*c5c4113dSnw141292 The total number of bytes of unused space on all primary pages. The 401*c5c4113dSnw141292 percentage at the right is the number of unused bytes divided by the 402*c5c4113dSnw141292 total number of bytes on primary pages. 403*c5c4113dSnw141292 404*c5c4113dSnw141292Unused bytes on overflow pages 405*c5c4113dSnw141292 406*c5c4113dSnw141292 The total number of bytes of unused space on all overflow pages. The 407*c5c4113dSnw141292 percentage at the right is the number of unused bytes divided by the 408*c5c4113dSnw141292 total number of bytes on overflow pages. 409*c5c4113dSnw141292 410*c5c4113dSnw141292Unused bytes on all pages 411*c5c4113dSnw141292 412*c5c4113dSnw141292 The total number of bytes of unused space on all primary and overflow 413*c5c4113dSnw141292 pages. The percentage at the right is the number of unused bytes 414*c5c4113dSnw141292 divided by the total number of bytes. 415*c5c4113dSnw141292} 416*c5c4113dSnw141292 417*c5c4113dSnw141292# Output the database 418*c5c4113dSnw141292# 419*c5c4113dSnw141292puts "**********************************************************************" 420*c5c4113dSnw141292puts "The entire text of this report can be sourced into any SQL database" 421*c5c4113dSnw141292puts "engine for further analysis. All of the text above is an SQL comment." 422*c5c4113dSnw141292puts "The data used to generate this report follows:" 423*c5c4113dSnw141292puts "*/" 424*c5c4113dSnw141292puts "BEGIN;" 425*c5c4113dSnw141292puts $tabledef 426*c5c4113dSnw141292unset -nocomplain x 427*c5c4113dSnw141292mem eval {SELECT * FROM space_used} x { 428*c5c4113dSnw141292 puts -nonewline "INSERT INTO space_used VALUES(" 429*c5c4113dSnw141292 regsub ' $x(name) '' qn 430*c5c4113dSnw141292 regsub ' $x(tblname) '' qtn 431*c5c4113dSnw141292 puts -nonewline "'$qn','$qtn'," 432*c5c4113dSnw141292 puts -nonewline "$x(is_index),$x(nentry),$x(payload),$x(mx_payload)," 433*c5c4113dSnw141292 puts -nonewline "$x(n_ovfl),$x(pri_pages),$x(ovfl_pages),$x(pri_unused)," 434*c5c4113dSnw141292 puts "$x(ovfl_unused));" 435*c5c4113dSnw141292} 436*c5c4113dSnw141292puts "COMMIT;" 437