Documentation Source Text

<title>The DBSTAT Virtual Table</title>
<tcl>hd_keywords dbstat {dbstat virtual table}</tcl>

<h2>The DBSTAT Virtual Table</h2>














<p>
^The <b>dbstat</b> virtual table is available on all 
[database connections] when SQLite is built using the
[SQLITE_ENABLE_DBSTAT_VTAB] compile-time option.
^The dbstat virtual table provides low-level information 
about btree and overflow pages in a database file.
................................................................................

<p>
There is a single row of the dbstat table for each page in the
database file.  Freelist pages, the lock page, and
pointer-map pages of the database file do not appear in the
dbstat virtual table.

<h3>The "path" column of the dbstat virtual table</h3>

<p>
The "path" column describes the path taken from the 
root node of the btree structure to each page.  The
"path" of the root node itself is '/'.

The "path" for the left-most child page of the root of
................................................................................

<blockquote><pre>
'/1c2/000+000000'         // First page in overflow chain
'/1c2/000+000001'         // Second page in overflow chain
'/1c2/000+000002'         // Third page in overflow chain
</pre></blockquote>


If the paths are sorted using the BINARY collation sequence, then
the overflow pages associated with a cell will appear earlier in the
sort-order than its child page:

<blockquote><pre>
'/1c2/000/'               // Left-most child of 451st child of root
</pre></blockquote>

<h3>Example uses of the dbstat virtual table</h3>

<p>
To find the total number of pages used to store table "xyz" in schema "aux1",
use:

<blockquote><pre>
SELECT count(*) FROM dbstat('aux1') WHERE name='xyz';

<title>The DBSTAT Virtual Table</title>
<tcl>hd_keywords dbstat {dbstat virtual table}</tcl>
<table_of_contents>
<h1>The DBSTAT Virtual Table</h1>

<p>
The DBSTAT virtual tables is a read-only [eponymous virtual table] that returns
information about which pages of the database files are used by which
tables and indexes in the schema.
The the DBSTAT virtual table is used to implement [sqlite3_analyzer.exe]
utility program, and to help compute the 
[https://www.sqlite.org/src/repo-tabsize|table size pie-chart] in
the [https://www.fossil-scm.org/|Fossil-implemented] version control system
for SQLite.
</p>

<h2>Overview</h2>

<p>
^The <b>dbstat</b> virtual table is available on all 
[database connections] when SQLite is built using the
[SQLITE_ENABLE_DBSTAT_VTAB] compile-time option.
^The dbstat virtual table provides low-level information 
about btree and overflow pages in a database file.
................................................................................

<p>
There is a single row of the dbstat table for each page in the
database file.  Freelist pages, the lock page, and
pointer-map pages of the database file do not appear in the
dbstat virtual table.

<h2>The "path" column of the dbstat virtual table</h2>

<p>
The "path" column describes the path taken from the 
root node of the btree structure to each page.  The
"path" of the root node itself is '/'.

The "path" for the left-most child page of the root of
................................................................................

<blockquote><pre>
'/1c2/000+000000'         // First page in overflow chain
'/1c2/000+000001'         // Second page in overflow chain
'/1c2/000+000002'         // Third page in overflow chain
</pre></blockquote>

<p>
If the paths are sorted using the BINARY collation sequence, then
the overflow pages associated with a cell will appear earlier in the
sort-order than its child page:

<blockquote><pre>
'/1c2/000/'               // Left-most child of 451st child of root
</pre></blockquote>

<h2>Example uses of the dbstat virtual table</h2>

<p>
To find the total number of pages used to store table "xyz" in schema "aux1",
use:

<blockquote><pre>
SELECT count(*) FROM dbstat('aux1') WHERE name='xyz';

<title>sqlite3_analyzer.exe: Database Size Measurement Utility</title>
<tcl>hd_keywords sqlite3_analyzer sqlite3_analyzer.exe</tcl>

<h1>The sqllite3_analyzer.exe Utility Program</h1>

<p>
The <tt>sqlite3_analyzer.exe</tt> binary is a command-line utility program
that measures and displays how much and how efficiently space is used by 
individual tables and indexes with an SQLite database file.
Example usage:

................................................................................
a database at the
<a href="#sqlx">end of the report</a>.  The constructed database contains
the raw data from which the report was extracted.  Hence the original
report can be read into an instance of the [command-line shell] and then
the raw data can be queried to dig deeper into the space utilization of
a particular database file.











<h2>Example Output</h2>

<p>The following is sqlite3_analyzer output for an example
places.sqlite database used by Firefox.

<pre>
/** Disk-Space Utilization Report For &#9608;&#9608;&#9608;&#9608;&#9608;&#9608;&#9608;&#9608;&#9608;&#9608;&#9608;&#9608;&#9608;&#9608;&#9608;&#9608;&#9608;&#9608;/places.sqlite

Page size in bytes................................ 32768     
Pages in the whole file (measured)................ 221       
Pages in the whole file (calculated).............. 221       
Pages that store data............................. 221        100.0% 
Pages on the freelist (per header)................ 0            0.0% 
................................................................................
INSERT INTO space_used VALUES('moz_places_url_uniqueindex','moz_places',1,10894,10859,2,1010666,0,0,1823,1,36,0,24134,143261,0,16,1212416);
INSERT INTO space_used VALUES('moz_places_guid_uniqueindex','moz_places',1,10894,10887,2,196000,0,0,18,1,8,0,32581,33545,0,5,294912);
INSERT INTO space_used VALUES('moz_bookmarks_guid_uniqueindex','moz_bookmarks',1,313,313,1,5207,0,0,17,0,1,0,0,26614,0,0,32768);
INSERT INTO space_used VALUES('moz_annos_placeattributeindex','moz_annos',1,195,195,1,1871,0,0,10,0,1,0,0,30304,0,0,32768);
INSERT INTO space_used VALUES('moz_items_annos_itemattributeindex','moz_items_annos',1,79,79,1,562,0,0,9,0,1,0,0,31961,0,0,32768);
INSERT INTO space_used VALUES('moz_keywords_placepostdata_uniqueindex','moz_keywords',1,0,0,1,0,0,0,0,0,1,0,0,32760,0,0,32768);
COMMIT;
</pre>

<title>The sqlite3_analyzer.exe Utility Program</title>
<tcl>hd_keywords sqlite3_analyzer sqlite3_analyzer.exe</tcl>
<table_of_contents>
<h1>The sqlite3_analyzer.exe Utility Program</h1>

<p>
The <tt>sqlite3_analyzer.exe</tt> binary is a command-line utility program
that measures and displays how much and how efficiently space is used by 
individual tables and indexes with an SQLite database file.
Example usage:

................................................................................
a database at the
<a href="#sqlx">end of the report</a>.  The constructed database contains
the raw data from which the report was extracted.  Hence the original
report can be read into an instance of the [command-line shell] and then
the raw data can be queried to dig deeper into the space utilization of
a particular database file.

<h2>Implementation</h2>

<p>
The <tt>sqlite3_analyzer.exe</tt> program is a
[http://www.tcl.tk/|TCL] program that uses the [dbstat virtual table]
to gather information about the database file and then format that
information neatly.
</p>


<h2>Example Output</h2>

<p>The following is sqlite3_analyzer output for an example
places.sqlite database used by Firefox.

<blockquote><pre>
/** Disk-Space Utilization Report For &#9608;&#9608;&#9608;&#9608;&#9608;&#9608;&#9608;&#9608;&#9608;&#9608;&#9608;&#9608;&#9608;&#9608;&#9608;&#9608;&#9608;&#9608;/places.sqlite

Page size in bytes................................ 32768     
Pages in the whole file (measured)................ 221       
Pages in the whole file (calculated).............. 221       
Pages that store data............................. 221        100.0% 
Pages on the freelist (per header)................ 0            0.0% 
................................................................................
INSERT INTO space_used VALUES('moz_places_url_uniqueindex','moz_places',1,10894,10859,2,1010666,0,0,1823,1,36,0,24134,143261,0,16,1212416);
INSERT INTO space_used VALUES('moz_places_guid_uniqueindex','moz_places',1,10894,10887,2,196000,0,0,18,1,8,0,32581,33545,0,5,294912);
INSERT INTO space_used VALUES('moz_bookmarks_guid_uniqueindex','moz_bookmarks',1,313,313,1,5207,0,0,17,0,1,0,0,26614,0,0,32768);
INSERT INTO space_used VALUES('moz_annos_placeattributeindex','moz_annos',1,195,195,1,1871,0,0,10,0,1,0,0,30304,0,0,32768);
INSERT INTO space_used VALUES('moz_items_annos_itemattributeindex','moz_items_annos',1,79,79,1,562,0,0,9,0,1,0,0,31961,0,0,32768);
INSERT INTO space_used VALUES('moz_keywords_placepostdata_uniqueindex','moz_keywords',1,0,0,1,0,0,0,0,0,1,0,0,32760,0,0,32768);
COMMIT;
</pre></blockquote>