1 # Run this TCL script using "testfixture" in order get a report that shows
2 # how much disk space is used by a particular data to actually store data
3 # versus how much space is unused.
6 # Get the name of the database to analyze
9 if {[llength $argv]!=1} {
10 puts stderr
"Usage: $argv0 database-name"
13 set file_to_analyze
[lindex $argv 0]
14 if {![file exists
$file_to_analyze]} {
15 puts stderr
"No such file: $file_to_analyze"
18 if {![file readable
$file_to_analyze]} {
19 puts stderr
"File is not readable: $file_to_analyze"
22 if {[file size
$file_to_analyze]<512} {
23 puts stderr
"Empty or malformed database: $file_to_analyze"
29 sqlite3 db
[lindex $argv 0]
30 set DB
[btree_open
[lindex $argv 0] 1000 0]
32 # In-memory database for collecting statistics. This script loops through
33 # the tables and indices in the database being analyzed, adding a row for each
34 # to an in-memory database (for which the schema is shown below). It then
35 # queries the in-memory db to produce the space-analysis report.
39 {CREATE TABLE space_used
(
40 name clob
, -- Name of a table or index in the database
file
41 tblname clob
, -- Name of associated table
42 is_index boolean
, -- TRUE
if it is an index
, false
for a table
43 nentry int
, -- Number of entries in the BTree
44 leaf_entries int
, -- Number of leaf entries
45 payload int
, -- Total amount of data stored in this table or index
46 ovfl_payload int
, -- Total amount of data stored on overflow pages
47 ovfl_cnt int
, -- Number of entries that use overflow
48 mx_payload int
, -- Maximum payload size
49 int_pages int
, -- Number of interior pages used
50 leaf_pages int
, -- Number of leaf pages used
51 ovfl_pages int
, -- Number of overflow pages used
52 int_unused int
, -- Number of unused bytes on interior pages
53 leaf_unused int
, -- Number of unused bytes on primary pages
54 ovfl_unused int
-- Number of unused bytes on overflow pages
58 proc integerify
{real
} {
59 return [expr int
($real)]
61 mem function int integerify
63 # Quote a string for use in an SQL query. Examples:
65 # [quote {hello world}] == {'hello world'}
66 # [quote {hello world's}] == {'hello world''s'}
69 regsub -all '
$txt '' q
73 # This proc is a wrapper around the btree_cursor_info command. The
74 # second argument is an open btree cursor returned by [btree_cursor].
75 # The first argument is the name of an array variable that exists in
76 # the scope of the caller. If the third argument is non-zero, then
77 # info is returned for the page that lies $up entries upwards in the
78 # tree-structure. (i.e. $up==1 returns the parent page, $up==2 the
81 # The following entries in that array are filled in with information retrieved
82 # using [btree_cursor_info]:
84 # $arrayvar(page_no) = The page number
85 # $arrayvar(entry_no) = The entry number
86 # $arrayvar(page_entries) = Total number of entries on this page
87 # $arrayvar(cell_size) = Cell size (local payload + header)
88 # $arrayvar(page_freebytes) = Number of free bytes on this page
89 # $arrayvar(page_freeblocks) = Number of free blocks on the page
90 # $arrayvar(payload_bytes) = Total payload size (local + overflow)
91 # $arrayvar(header_bytes) = Header size in bytes
92 # $arrayvar(local_payload_bytes) = Local payload size
93 # $arrayvar(parent) = Parent page number
95 proc cursor_info
{arrayvar csr
{up
0}} {
97 foreach [list a
(page_no
) \
105 a
(local_payload_bytes
) \
106 a
(parent
) ] [btree_cursor_info
$csr $up] {}
109 # Determine the page-size of the database. This global variable is used
110 # throughout the script.
112 set pageSize
[db
eval {PRAGMA page_size
}]
114 # Analyze every table in the database, one at a time.
116 # The following query returns the name and root-page of each table in the
117 # database, including the sqlite_master table.
120 SELECT name
, rootpage FROM sqlite_master WHERE type
='table'
122 SELECT 'sqlite_master'
, 1
125 foreach {name rootpage
} [db
eval $sql] {
126 puts stderr
"Analyzing table $name..."
128 # Code below traverses the table being analyzed (table name $name), using the
129 # btree cursor $cursor. Statistics related to table $name are accumulated in
130 # the following variables:
132 set total_payload
0 ;# Payload space used by all entries
133 set total_ovfl
0 ;# Payload space on overflow pages
134 set unused_int
0 ;# Unused space on interior nodes
135 set unused_leaf
0 ;# Unused space on leaf nodes
136 set unused_ovfl
0 ;# Unused space on overflow pages
137 set cnt_ovfl
0 ;# Number of entries that use overflows
138 set cnt_leaf_entry
0 ;# Number of leaf entries
139 set cnt_int_entry
0 ;# Number of interor entries
140 set mx_payload
0 ;# Maximum payload size
141 set ovfl_pages
0 ;# Number of overflow pages used
142 set leaf_pages
0 ;# Number of leaf pages
143 set int_pages
0 ;# Number of interior pages
145 # As the btree is traversed, the array variable $seen($pgno) is set to 1
146 # the first time page $pgno is encountered.
150 # The following loop runs once for each entry in table $name. The table
151 # is traversed using the btree cursor stored in variable $csr
153 set csr
[btree_cursor
$DB $rootpage 0]
154 for {btree_first
$csr} {![btree_eof
$csr]} {btree_next
$csr} {
157 # Retrieve information about the entry the btree-cursor points to into
158 # the array variable $ci (cursor info).
162 # Check if the payload of this entry is greater than the current
163 # $mx_payload statistic for the table. Also increase the $total_payload
166 if {$ci(payload_bytes
)>$mx_payload} {set mx_payload
$ci(payload_bytes
)}
167 incr total_payload
$ci(payload_bytes
)
169 # If this entry uses overflow pages, then update the $cnt_ovfl,
170 # $total_ovfl, $ovfl_pages and $unused_ovfl statistics.
172 set ovfl
[expr {$ci(payload_bytes
)-$ci(local_payload_bytes
)}]
175 incr total_ovfl
$ovfl
176 set n
[expr {int
(ceil
($ovfl/($pageSize-4.0)))}]
178 incr unused_ovfl
[expr {$n*($pageSize-4) - $ovfl}]
181 # If this is the first table entry analyzed for the page, then update
182 # the page-related statistics $leaf_pages and $unused_leaf. Also, if
183 # this page has a parent page that has not been analyzed, retrieve
184 # info for the parent and update statistics for it too.
186 if {![info exists seen
($ci(page_no
))]} {
187 set seen
($ci(page_no
)) 1
189 incr unused_leaf
$ci(page_freebytes
)
191 # Now check if the page has a parent that has not been analyzed. If
192 # so, update the $int_pages, $cnt_int_entry and $unused_int statistics
193 # accordingly. Then check if the parent page has a parent that has
194 # not yet been analyzed etc.
196 # set parent $ci(parent_page_no)
198 {$ci(parent
)!=0 && ![info exists seen
($ci(parent
))]} {incr up
} \
200 # Mark the parent as seen.
202 set seen
($ci(parent
)) 1
204 # Retrieve info for the parent and update statistics.
205 cursor_info ci
$csr $up
207 incr cnt_int_entry
$ci(page_entries
)
208 incr unused_int
$ci(page_freebytes
)
212 btree_close_cursor
$csr
214 # Handle the special case where a table contains no data. In this case
215 # all statistics are zero, except for the number of leaf pages (1) and
216 # the unused bytes on leaf pages ($pageSize - 8).
218 # An exception to the above is the sqlite_master table. If it is empty
219 # then all statistics are zero except for the number of leaf pages (1),
220 # and the number of unused bytes on leaf pages ($pageSize - 112).
222 if {[llength [array names seen
]]==0} {
225 set unused_leaf
[expr {$pageSize-112}]
227 set unused_leaf
[expr {$pageSize-8}]
231 # Insert the statistics for the table analyzed into the in-memory database.
233 set sql
"INSERT INTO space_used VALUES("
234 append sql
[quote
$name]
235 append sql
",[quote $name]"
237 append sql
",[expr {$cnt_leaf_entry+$cnt_int_entry}]"
238 append sql
",$cnt_leaf_entry"
239 append sql
",$total_payload"
240 append sql
",$total_ovfl"
241 append sql
",$cnt_ovfl"
242 append sql
",$mx_payload"
243 append sql
",$int_pages"
244 append sql
",$leaf_pages"
245 append sql
",$ovfl_pages"
246 append sql
",$unused_int"
247 append sql
",$unused_leaf"
248 append sql
",$unused_ovfl"
253 # Analyze every index in the database, one at a time.
255 # The query below returns the name, associated table and root-page number
256 # for every index in the database.
259 SELECT name
, tbl_name
, rootpage FROM sqlite_master WHERE type
='index'
262 foreach {name tbl_name rootpage
} [db
eval $sql] {
263 puts stderr
"Analyzing index $name of table $tbl_name..."
265 # Code below traverses the index being analyzed (index name $name), using the
266 # btree cursor $cursor. Statistics related to index $name are accumulated in
267 # the following variables:
269 set total_payload
0 ;# Payload space used by all entries
270 set total_ovfl
0 ;# Payload space on overflow pages
271 set unused_leaf
0 ;# Unused space on leaf nodes
272 set unused_ovfl
0 ;# Unused space on overflow pages
273 set cnt_ovfl
0 ;# Number of entries that use overflows
274 set cnt_leaf_entry
0 ;# Number of leaf entries
275 set mx_payload
0 ;# Maximum payload size
276 set ovfl_pages
0 ;# Number of overflow pages used
277 set leaf_pages
0 ;# Number of leaf pages
279 # As the btree is traversed, the array variable $seen($pgno) is set to 1
280 # the first time page $pgno is encountered.
284 # The following loop runs once for each entry in index $name. The index
285 # is traversed using the btree cursor stored in variable $csr
287 set csr
[btree_cursor
$DB $rootpage 0]
288 for {btree_first
$csr} {![btree_eof
$csr]} {btree_next
$csr} {
291 # Retrieve information about the entry the btree-cursor points to into
292 # the array variable $ci (cursor info).
296 # Check if the payload of this entry is greater than the current
297 # $mx_payload statistic for the table. Also increase the $total_payload
300 set payload
[btree_keysize
$csr]
301 if {$payload>$mx_payload} {set mx_payload
$payload}
302 incr total_payload
$payload
304 # If this entry uses overflow pages, then update the $cnt_ovfl,
305 # $total_ovfl, $ovfl_pages and $unused_ovfl statistics.
307 set ovfl
[expr {$payload-$ci(local_payload_bytes
)}]
310 incr total_ovfl
$ovfl
311 set n
[expr {int
(ceil
($ovfl/($pageSize-4.0)))}]
313 incr unused_ovfl
[expr {$n*($pageSize-4) - $ovfl}]
316 # If this is the first table entry analyzed for the page, then update
317 # the page-related statistics $leaf_pages and $unused_leaf.
319 if {![info exists seen
($ci(page_no
))]} {
320 set seen
($ci(page_no
)) 1
322 incr unused_leaf
$ci(page_freebytes
)
325 btree_close_cursor
$csr
327 # Handle the special case where a index contains no data. In this case
328 # all statistics are zero, except for the number of leaf pages (1) and
329 # the unused bytes on leaf pages ($pageSize - 8).
331 if {[llength [array names seen
]]==0} {
333 set unused_leaf
[expr {$pageSize-8}]
336 # Insert the statistics for the index analyzed into the in-memory database.
338 set sql
"INSERT INTO space_used VALUES("
339 append sql
[quote
$name]
340 append sql
",[quote $tbl_name]"
342 append sql
",$cnt_leaf_entry"
343 append sql
",$cnt_leaf_entry"
344 append sql
",$total_payload"
345 append sql
",$total_ovfl"
346 append sql
",$cnt_ovfl"
347 append sql
",$mx_payload"
349 append sql
",$leaf_pages"
350 append sql
",$ovfl_pages"
352 append sql
",$unused_leaf"
353 append sql
",$unused_ovfl"
358 # Generate a single line of output in the statistics section of the
361 proc statline
{title value
{extra
{}}} {
362 set len
[string length
$title]
363 set dots
[string range
{......................................
} $len end
]
364 set len
[string length
$value]
365 set sp2
[string range
{ } $len end
]
369 puts "$title$dots $value$sp2$extra"
372 # Generate a formatted percentage value for $num/$denom
374 proc percent
{num denom
{of
{}}} {
375 if {$denom==0.0} {return ""}
376 set v
[expr {$num*100.0/$denom}]
378 if {$v==100.0 ||
$v<0.001 ||
($v>1.0 && $v<99.0)} {
379 return [format {%5.1f
%% %s
} $v $of]
380 } elseif
{$v<0.1 ||
$v>99.9} {
381 return [format {%7.3f
%% %s
} $v $of]
383 return [format {%6.2f
%% %s
} $v $of]
387 proc divide
{num denom
} {
388 if {$denom==0} {return 0.0}
389 return [format %.2f
[expr double
($num)/double
($denom)]]
392 # Generate a subreport that covers some subset of the database.
393 # the $where clause determines which subset to analyze.
395 proc subreport
{title where
} {
396 global pageSize file_pgcnt
398 # Query the in-memory database for the sum of various statistics
399 # for the subset of tables/indices identified by the WHERE clause in
400 # $where. Note that even if the WHERE clause matches no rows, the
401 # following query returns exactly one row (because it is an aggregate).
403 # The results of the query are stored directly by SQLite into local
404 # variables (i.e. $nentry, $nleaf etc.).
408 int(sum(nentry)) AS nentry,
409 int(sum(leaf_entries)) AS nleaf,
410 int(sum(payload)) AS payload,
411 int(sum(ovfl_payload)) AS ovfl_payload,
412 max(mx_payload) AS mx_payload,
413 int(sum(ovfl_cnt)) as ovfl_cnt,
414 int(sum(leaf_pages)) AS leaf_pages,
415 int(sum(int_pages)) AS int_pages,
416 int(sum(ovfl_pages)) AS ovfl_pages,
417 int(sum(leaf_unused)) AS leaf_unused,
418 int(sum(int_unused)) AS int_unused,
419 int(sum(ovfl_unused)) AS ovfl_unused
420 FROM space_used WHERE $where" {} {}
422 # Output the sub-report title, nicely decorated with * characters.
425 set len
[string length
$title]
426 set stars
[string repeat
* [expr 65-$len]]
427 puts "*** $title $stars"
430 # Calculate statistics and store the results in TCL variables, as follows:
432 # total_pages: Database pages consumed.
433 # total_pages_percent: Pages consumed as a percentage of the file.
434 # storage: Bytes consumed.
435 # payload_percent: Payload bytes used as a percentage of $storage.
436 # total_unused: Unused bytes on pages.
437 # avg_payload: Average payload per btree entry.
438 # avg_fanout: Average fanout for internal pages.
439 # avg_unused: Average unused bytes per btree entry.
440 # ovfl_cnt_percent: Percentage of btree entries that use overflow pages.
442 set total_pages
[expr {$leaf_pages+$int_pages+$ovfl_pages}]
443 set total_pages_percent
[percent
$total_pages $file_pgcnt]
444 set storage
[expr {$total_pages*$pageSize}]
445 set payload_percent
[percent
$payload $storage {of storage consumed
}]
446 set total_unused
[expr {$ovfl_unused+$int_unused+$leaf_unused}]
447 set avg_payload
[divide
$payload $nleaf]
448 set avg_unused
[divide
$total_unused $nleaf]
450 # TODO: Is this formula correct?
452 SELECT count(*) FROM (
453 SELECT DISTINCT tblname FROM space_used WHERE $where AND is_index=0
456 set avg_fanout
[mem
eval "
457 SELECT (sum(leaf_pages+int_pages)-$nTab)/sum(int_pages) FROM space_used
458 WHERE $where AND is_index = 0
460 set avg_fanout
[format %.2f
$avg_fanout]
462 set ovfl_cnt_percent
[percent
$ovfl_cnt $nleaf {of all entries
}]
464 # Print out the sub-report statistics.
466 statline
{Percentage of total database
} $total_pages_percent
467 statline
{Number of entries
} $nleaf
468 statline
{Bytes of storage consumed
} $storage
469 statline
{Bytes of payload
} $payload $payload_percent
470 statline
{Average payload per
entry} $avg_payload
471 statline
{Average unused bytes per
entry} $avg_unused
472 if {[info exists avg_fanout
]} {
473 statline
{Average fanout
} $avg_fanout
475 statline
{Maximum payload per
entry} $mx_payload
476 statline
{Entries that use overflow
} $ovfl_cnt $ovfl_cnt_percent
478 statline
{Index pages used
} $int_pages
480 statline
{Primary pages used
} $leaf_pages
481 statline
{Overflow pages used
} $ovfl_pages
482 statline
{Total pages used
} $total_pages
484 set int_unused_percent
\
485 [percent
$int_unused [expr {$int_pages*$pageSize}] {of index space
}]
486 statline
"Unused bytes on index pages" $int_unused $int_unused_percent
488 statline
"Unused bytes on primary pages" $leaf_unused \
489 [percent
$leaf_unused [expr {$leaf_pages*$pageSize}] {of primary space
}]
490 statline
"Unused bytes on overflow pages" $ovfl_unused \
491 [percent
$ovfl_unused [expr {$ovfl_pages*$pageSize}] {of overflow space
}]
492 statline
"Unused bytes on all pages" $total_unused \
493 [percent
$total_unused $storage {of all space
}]
497 # Calculate the overhead in pages caused by auto-vacuum.
499 # This procedure calculates and returns the number of pages used by the
500 # auto-vacuum 'pointer-map'. If the database does not support auto-vacuum,
501 # then 0 is returned. The two arguments are the size of the database file in
502 # pages and the page size used by the database (in bytes).
503 proc autovacuum_overhead
{filePages pageSize
} {
505 # Read the value of meta 4. If non-zero, then the database supports
506 # auto-vacuum. It would be possible to use "PRAGMA auto_vacuum" instead,
507 # but that would not work if the SQLITE_OMIT_PRAGMA macro was defined
508 # when the library was built.
509 set meta4
[lindex [btree_get_meta
$::DB] 4]
511 # If the database is not an auto-vacuum database or the file consists
512 # of one page only then there is no overhead for auto-vacuum. Return zero.
513 if {0==$meta4 ||
$filePages==1} {
517 # The number of entries on each pointer map page. The layout of the
518 # database file is one pointer-map page, followed by $ptrsPerPage other
519 # pages, followed by a pointer-map page etc. The first pointer-map page
520 # is the second page of the file overall.
521 set ptrsPerPage
[expr double
($pageSize/5)]
523 # Return the number of pointer map pages in the database.
524 return [expr int
(ceil
( ($filePages-1.0)/($ptrsPerPage+1.0) ))]
528 # Calculate the summary statistics for the database and store the results
529 # in TCL variables. They are output below. Variables are as follows:
531 # pageSize: Size of each page in bytes.
532 # file_bytes: File size in bytes.
533 # file_pgcnt: Number of pages in the file.
534 # file_pgcnt2: Number of pages in the file (calculated).
535 # av_pgcnt: Pages consumed by the auto-vacuum pointer-map.
536 # av_percent: Percentage of the file consumed by auto-vacuum pointer-map.
537 # inuse_pgcnt: Data pages in the file.
538 # inuse_percent: Percentage of pages used to store data.
539 # free_pgcnt: Free pages calculated as (<total pages> - <in-use pages>)
540 # free_pgcnt2: Free pages in the file according to the file header.
541 # free_percent: Percentage of file consumed by free pages (calculated).
542 # free_percent2: Percentage of file consumed by free pages (header).
543 # ntable: Number of tables in the db.
544 # nindex: Number of indices in the db.
545 # nautoindex: Number of indices created automatically.
546 # nmanindex: Number of indices created manually.
547 # user_payload: Number of bytes of payload in table btrees
548 # (not including sqlite_master)
549 # user_percent: $user_payload as a percentage of total file size.
551 set file_bytes
[file size
$file_to_analyze]
552 set file_pgcnt
[expr {$file_bytes/$pageSize}]
554 set av_pgcnt
[autovacuum_overhead
$file_pgcnt $pageSize]
555 set av_percent
[percent
$av_pgcnt $file_pgcnt]
557 set sql
{SELECT sum
(leaf_pages
+int_pages
+ovfl_pages
) FROM space_used
}
558 set inuse_pgcnt
[expr int
([mem
eval $sql])]
559 set inuse_percent
[percent
$inuse_pgcnt $file_pgcnt]
561 set free_pgcnt
[expr $file_pgcnt-$inuse_pgcnt-$av_pgcnt]
562 set free_percent
[percent
$free_pgcnt $file_pgcnt]
563 set free_pgcnt2
[lindex [btree_get_meta
$DB] 0]
564 set free_percent2
[percent
$free_pgcnt2 $file_pgcnt]
566 set file_pgcnt2
[expr {$inuse_pgcnt+$free_pgcnt2+$av_pgcnt}]
568 set ntable
[db
eval {SELECT count
(*)+1 FROM sqlite_master WHERE type
='table'
}]
569 set nindex
[db
eval {SELECT count
(*) FROM sqlite_master WHERE type
='index'
}]
570 set sql
{SELECT count
(*) FROM sqlite_master WHERE name LIKE 'sqlite_autoindex
%'
}
571 set nautoindex
[db
eval $sql]
572 set nmanindex
[expr {$nindex-$nautoindex}]
574 # set total_payload [mem eval "SELECT sum(payload) FROM space_used"]
575 set user_payload
[mem one
{SELECT int
(sum
(payload
)) FROM space_used
576 WHERE NOT is_index AND name NOT LIKE 'sqlite_master'
}]
577 set user_percent
[percent
$user_payload $file_bytes]
579 # Output the summary statistics calculated above.
581 puts "/** Disk-Space Utilization Report For $file_to_analyze"
582 puts "*** As of [clock format [clock seconds] -format {%Y-%b-%d %H:%M:%S}]"
584 statline
{Page size in bytes
} $pageSize
585 statline
{Pages in the whole
file (measured
)} $file_pgcnt
586 statline
{Pages in the whole
file (calculated
)} $file_pgcnt2
587 statline
{Pages that store data
} $inuse_pgcnt $inuse_percent
588 statline
{Pages on the freelist
(per header
)} $free_pgcnt2 $free_percent2
589 statline
{Pages on the freelist
(calculated
)} $free_pgcnt $free_percent
590 statline
{Pages of auto-vacuum overhead
} $av_pgcnt $av_percent
591 statline
{Number of tables in the database
} $ntable
592 statline
{Number of indices
} $nindex
593 statline
{Number of named indices
} $nmanindex
594 statline
{Automatically generated indices
} $nautoindex
595 statline
{Size of the
file in bytes
} $file_bytes
596 statline
{Bytes of user payload stored
} $user_payload $user_percent
598 # Output table rankings
601 puts "*** Page counts for all tables with their indices ********************"
603 mem
eval {SELECT tblname
, count
(*) AS cnt
,
604 int
(sum
(int_pages
+leaf_pages
+ovfl_pages
)) AS size
605 FROM space_used GROUP BY tblname ORDER BY size
+0 DESC
, tblname
} {} {
606 statline
[string toupper
$tblname] $size [percent
$size $file_pgcnt]
612 subreport
{All tables and indices
} 1
614 subreport
{All tables
} {NOT is_index
}
616 subreport
{All indices
} {is_index
}
618 foreach tbl
[mem
eval {SELECT name FROM space_used WHERE NOT is_index
621 set name
[string toupper
$tbl]
622 set n
[mem
eval "SELECT count(*) FROM space_used WHERE tblname='$qn'"]
624 subreport
"Table $name and all its indices" "tblname='$qn'"
625 subreport
"Table $name w/o any indices" "name='$qn'"
626 subreport
"Indices of table $name" "tblname='$qn' AND is_index"
628 subreport
"Table $name" "name='$qn'"
632 # Output instructions on what the numbers above mean.
635 *** Definitions
******************************************************
639 The number of bytes in a single page of the database
file.
642 Number of pages in the whole
file
645 " The number of $pageSize-byte pages that go into forming the complete
649 Pages that store data
651 The number of pages that store data
, either as primary B
*Tree pages or
652 as overflow pages. The number at the right is the data pages divided by
653 the total number of pages in the
file.
655 Pages on the freelist
657 The number of pages that are not currently in use but are reserved
for
658 future use. The percentage at the right is the number of freelist pages
659 divided by the total number of pages in the
file.
661 Pages of auto-vacuum overhead
663 The number of pages that store data used by the database to facilitate
664 auto-vacuum. This is zero
for databases that do not support auto-vacuum.
666 Number of tables in the database
668 The number of tables in the database
, including the SQLITE_MASTER table
669 used to store schema information.
673 The total number of indices in the database.
675 Number of named indices
677 The number of indices created using an explicit CREATE INDEX statement.
679 Automatically generated indices
681 The number of indices used to implement PRIMARY KEY or UNIQUE constraints
684 Size of the
file in bytes
686 The total amount of disk space used by the entire database files.
688 Bytes of user payload stored
690 The total number of bytes of user payload stored in the database. The
691 schema information in the SQLITE_MASTER table is not counted when
692 computing this number. The percentage at the right shows the payload
693 divided by the total
file size.
695 Percentage of total database
697 The amount of the complete database
file that is devoted to storing
698 information described by this category.
702 The total number of B-Tree key
/value pairs stored under this category.
704 Bytes of storage consumed
706 The total amount of disk space required to store all B-Tree entries
707 under this category. The is the total number of pages used times
712 The amount of payload stored under this category. Payload is the data
713 part of table entries and the key part of index entries. The percentage
714 at the right is the bytes of payload divided by the bytes of storage
717 Average payload per
entry
719 The average amount of payload on each
entry. This is just the bytes of
720 payload divided by the number of entries.
722 Average unused bytes per
entry
724 The average amount of free space remaining on all pages under this
725 category on a per-entry basis. This is the number of unused bytes on
726 all pages divided by the number of entries.
728 Maximum payload per
entry
730 The largest payload size of any
entry.
732 Entries that use overflow
734 The number of entries that user one or more overflow pages.
738 This is the number of pages used to hold all information in the current
739 category. This is the sum of index
, primary
, and overflow pages.
743 This is the number of pages in a table B-tree that hold only key
(rowid
)
744 information and no data.
748 This is the number of B-tree pages that hold both key and data.
752 The total number of overflow pages used
for this category.
754 Unused bytes on index pages
756 The total number of bytes of unused space on all index pages. The
757 percentage at the right is the number of unused bytes divided by the
758 total number of bytes on index pages.
760 Unused bytes on primary pages
762 The total number of bytes of unused space on all primary pages. The
763 percentage at the right is the number of unused bytes divided by the
764 total number of bytes on primary pages.
766 Unused bytes on overflow pages
768 The total number of bytes of unused space on all overflow pages. The
769 percentage at the right is the number of unused bytes divided by the
770 total number of bytes on overflow pages.
772 Unused bytes on all pages
774 The total number of bytes of unused space on all primary and overflow
775 pages. The percentage at the right is the number of unused bytes
776 divided by the total number of bytes.
779 # Output a dump of the in-memory database. This can be used for more
780 # complex offline analysis.
782 puts "**********************************************************************"
783 puts "The entire text of this report can be sourced into any SQL database"
784 puts "engine for further analysis. All of the text above is an SQL comment."
785 puts "The data used to generate this report follows:"
790 mem
eval {SELECT
* FROM space_used
} x
{
791 puts -nonewline "INSERT INTO space_used VALUES"
795 if {$v=="" ||
![string is double
$v]} {set v
[quote
$v]}
796 puts -nonewline $sep$v