Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | In the sqlite3_analyzer.exe utility, show the depth of each btree and report the average fanout of indexes and WITHOUT ROWID tables. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
cd997770013e923ac3fa34b1546b9768 |
User & Date: | drh 2015-08-04 14:18:10.060 |
Context
2015-08-04
| ||
15:29 | Update the [showdb] program so that the database file is read directly (bypassing the SQLite VFS) only if the --raw option is specified. Otherwise, it is read using the default VFS. Also, the URI syntax may be used on the command line to specify the name of the database file to examine, so an alternative VFS may be requested using a URI parameter. (check-in: e3c6d4b6e7 user: dan tags: trunk) | |
14:18 | In the sqlite3_analyzer.exe utility, show the depth of each btree and report the average fanout of indexes and WITHOUT ROWID tables. (check-in: cd99777001 user: drh tags: trunk) | |
2015-08-03
| ||
13:44 | Allow a VIEW to reference undefined tables and functions when initially created. The error report is deferred until the VIEW is used. This allows views to be created before subviews and tables that the view references. (check-in: 70b57dafb3 user: drh tags: trunk) | |
Changes
Changes to tool/spaceanal.tcl.
︙ | ︙ | |||
138 139 140 141 142 143 144 145 146 147 148 149 150 151 | sqlite3 mem :memory: set tabledef {CREATE TABLE space_used( name clob, -- Name of a table or index in the database file tblname clob, -- Name of associated table is_index boolean, -- TRUE if it is an index, false for a table nentry int, -- Number of entries in the BTree leaf_entries int, -- Number of leaf entries payload int, -- Total amount of data stored in this table or index ovfl_payload int, -- Total amount of data stored on overflow pages ovfl_cnt int, -- Number of entries that use overflow mx_payload int, -- Maximum payload size int_pages int, -- Number of interior pages used leaf_pages int, -- Number of leaf pages used ovfl_pages int, -- Number of overflow pages used | > | 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 | sqlite3 mem :memory: set tabledef {CREATE TABLE space_used( name clob, -- Name of a table or index in the database file tblname clob, -- Name of associated table is_index boolean, -- TRUE if it is an index, false for a table nentry int, -- Number of entries in the BTree leaf_entries int, -- Number of leaf entries depth int, -- Depth of the b-tree payload int, -- Total amount of data stored in this table or index ovfl_payload int, -- Total amount of data stored on overflow pages ovfl_cnt int, -- Number of entries that use overflow mx_payload int, -- Maximum payload size int_pages int, -- Number of interior pages used leaf_pages int, -- Number of leaf pages used ovfl_pages int, -- Number of overflow pages used |
︙ | ︙ | |||
160 161 162 163 164 165 166 | # Create a temporary "dbstat" virtual table. # db eval {CREATE VIRTUAL TABLE temp.stat USING dbstat} db eval {CREATE TEMP TABLE dbstat AS SELECT * FROM temp.stat ORDER BY name, path} db eval {DROP TABLE temp.stat} | < < < < < < < < < < < < < < > | | | | | | | | | > > | 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 | # Create a temporary "dbstat" virtual table. # db eval {CREATE VIRTUAL TABLE temp.stat USING dbstat} db eval {CREATE TEMP TABLE dbstat AS SELECT * FROM temp.stat ORDER BY name, path} db eval {DROP TABLE temp.stat} set isCompressed 0 set compressOverhead 0 set depth 0 set sql { SELECT name, tbl_name FROM sqlite_master WHERE rootpage>0 } foreach {name tblname} [concat sqlite_master sqlite_master [db eval $sql]] { set is_index [expr {$name!=$tblname}] set idx_btree [expr {$is_index || [is_without_rowid $name]}] db eval { SELECT sum(ncell) AS nentry, sum((pagetype=='leaf')*ncell) AS leaf_entries, sum(payload) AS payload, sum((pagetype=='overflow') * payload) AS ovfl_payload, sum(path LIKE '%+000000') AS ovfl_cnt, max(mx_payload) AS mx_payload, sum(pagetype=='internal') AS int_pages, sum(pagetype=='leaf') AS leaf_pages, sum(pagetype=='overflow') AS ovfl_pages, sum((pagetype=='internal') * unused) AS int_unused, sum((pagetype=='leaf') * unused) AS leaf_unused, sum((pagetype=='overflow') * unused) AS ovfl_unused, sum(pgsize) AS compressed_size, max((length(CASE WHEN path LIKE '%+%' THEN '' ELSE path END)+3)/4) AS depth FROM temp.dbstat WHERE name = $name } break set total_pages [expr {$leaf_pages+$int_pages+$ovfl_pages}] set storage [expr {$total_pages*$pageSize}] if {!$isCompressed && $storage>$compressed_size} { set isCompressed 1 |
︙ | ︙ | |||
231 232 233 234 235 236 237 238 239 240 241 242 243 244 | mem eval { INSERT INTO space_used VALUES( $name, $tblname, $is_index, $nentry, $leaf_entries, $payload, $ovfl_payload, $ovfl_cnt, $mx_payload, $int_pages, $leaf_pages, $ovfl_pages, | > | 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 | mem eval { INSERT INTO space_used VALUES( $name, $tblname, $is_index, $nentry, $leaf_entries, $depth, $payload, $ovfl_payload, $ovfl_cnt, $mx_payload, $int_pages, $leaf_pages, $ovfl_pages, |
︙ | ︙ | |||
340 341 342 343 344 345 346 | int(sum(leaf_pages)) AS leaf_pages, int(sum(int_pages)) AS int_pages, int(sum(ovfl_pages)) AS ovfl_pages, int(sum(leaf_unused)) AS leaf_unused, int(sum(int_unused)) AS int_unused, int(sum(ovfl_unused)) AS ovfl_unused, int(sum(gap_cnt)) AS gap_cnt, | | > > | 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 | int(sum(leaf_pages)) AS leaf_pages, int(sum(int_pages)) AS int_pages, int(sum(ovfl_pages)) AS ovfl_pages, int(sum(leaf_unused)) AS leaf_unused, int(sum(int_unused)) AS int_unused, int(sum(ovfl_unused)) AS ovfl_unused, int(sum(gap_cnt)) AS gap_cnt, int(sum(compressed_size)) AS compressed_size, int(max(depth)) AS depth, count(*) AS cnt FROM space_used WHERE $where" {} {} # Output the sub-report title, nicely decorated with * characters. # puts "" titleline $title puts "" |
︙ | ︙ | |||
377 378 379 380 381 382 383 | set nTab [mem eval " SELECT count(*) FROM ( SELECT DISTINCT tblname FROM space_used WHERE $where AND is_index=0 ) "] set avg_fanout [mem eval " SELECT (sum(leaf_pages+int_pages)-$nTab)/sum(int_pages) FROM space_used | | > | 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 | set nTab [mem eval " SELECT count(*) FROM ( SELECT DISTINCT tblname FROM space_used WHERE $where AND is_index=0 ) "] set avg_fanout [mem eval " SELECT (sum(leaf_pages+int_pages)-$nTab)/sum(int_pages) FROM space_used WHERE $where "] set avg_fanout [format %.2f $avg_fanout] } set ovfl_cnt_percent [percent $ovfl_cnt $nleaf {of all entries}] # Print out the sub-report statistics. # statline {Percentage of total database} $total_pages_percent statline {Number of entries} $nleaf statline {Bytes of storage consumed} $storage if {$compressed_size!=$storage} { set compressed_size [expr {$compressed_size+$compressOverhead*$total_pages}] set pct [expr {$compressed_size*100.0/$storage}] set pct [format {%5.1f%%} $pct] statline {Bytes used after compression} $compressed_size $pct } statline {Bytes of payload} $payload $payload_percent if {$cnt==1} {statline {B-tree depth} $depth} statline {Average payload per entry} $avg_payload statline {Average unused bytes per entry} $avg_unused if {[info exists avg_fanout]} { statline {Average fanout} $avg_fanout } if {$showFrag && $total_pages>1} { set fragmentation [percent $gap_cnt [expr {$total_pages-1}]] |
︙ | ︙ |