/ Check-in [cd997770]
Login

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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: cd997770013e923ac3fa34b1546b97681923c8b1
User & Date: drh 2015-08-04 14:18:10
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: e3c6d4b6 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: cd997770 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: 70b57daf user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to tool/spaceanal.tcl.

138
139
140
141
142
143
144

145
146
147
148
149
150
151
...
160
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
200
201
202


203
204
205
206
207
208
209
...
231
232
233
234
235
236
237

238
239
240
241
242
243
244
...
340
341
342
343
344
345
346
347


348
349
350
351
352
353
354
...
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
...
395
396
397
398
399
400
401

402
403
404
405
406
407
408
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
................................................................................
# 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}

proc isleaf {pagetype is_index} {
  return [expr {$pagetype == "leaf" || ($pagetype == "internal" && $is_index)}]
}
proc isoverflow {pagetype is_index} {
  return [expr {$pagetype == "overflow"}]
}
proc isinternal {pagetype is_index} {
  return [expr {$pagetype == "internal" && $is_index==0}]
}

db func isleaf isleaf
db func isinternal isinternal
db func isoverflow isoverflow

set isCompressed 0
set compressOverhead 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(isleaf(pagetype, $idx_btree) * ncell) AS leaf_entries,
      sum(payload) AS payload,
      sum(isoverflow(pagetype, $idx_btree) * payload) AS ovfl_payload,
      sum(path LIKE '%+000000') AS ovfl_cnt,
      max(mx_payload) AS mx_payload,
      sum(isinternal(pagetype, $idx_btree)) AS int_pages,
      sum(isleaf(pagetype, $idx_btree)) AS leaf_pages,
      sum(isoverflow(pagetype, $idx_btree)) AS ovfl_pages,
      sum(isinternal(pagetype, $idx_btree) * unused) AS int_unused,
      sum(isleaf(pagetype, $idx_btree) * unused) AS leaf_unused,
      sum(isoverflow(pagetype, $idx_btree) * unused) AS ovfl_unused,
      sum(pgsize) AS compressed_size


    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
................................................................................
  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, 
................................................................................
      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


    FROM space_used WHERE $where" {} {}

  # Output the sub-report title, nicely decorated with * characters.
  #
  puts ""
  titleline $title
  puts ""
................................................................................
    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 AND is_index = 0
    "]
    set avg_fanout [format %.2f $avg_fanout]
  }
  set ovfl_cnt_percent [percent $ovfl_cnt $nleaf {of all entries}]

  # Print out the sub-report statistics.
  #
................................................................................
  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

  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}]]







>







 







<
<
<
<
<
<
<
<
<
<
<
<
<
<


>








|

|


|
|
|
|
|
|
|
>
>







 







>







 







|
>
>







 







|







 







>







138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
...
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
...
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
...
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
...
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
...
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
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
................................................................................
# 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
................................................................................
  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, 
................................................................................
      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 ""
................................................................................
    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.
  #
................................................................................
  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}]]