Documentation Source Text

Check-in [13de82bb23]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Improvements to the DBSTAT and sqlite3_analyzer.exe documentation pages.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 13de82bb23cffbb07f5386c175494f87b537b498
User & Date: drh 2016-06-23 17:44:21
Context
2016-06-23
19:04
Added preliminary documentation for the new CSV virtual table. check-in: b0191810a4 user: drh tags: trunk
17:44
Improvements to the DBSTAT and sqlite3_analyzer.exe documentation pages. check-in: 13de82bb23 user: drh tags: trunk
16:59
Enhancements to the WITHOUT ROWID virtual table documentation. check-in: ad981b591f user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/dbstat.in.

1
2

3













4
5
6
7
8
9
10
..
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
..
91
92
93
94
95
96
97

98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
<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';


>
|
>
>
>
>
>
>
>
>
>
>
>
>
>







 







|







 







>








|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
..
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
...
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
<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';

Changes to pages/sqlanalyze.in.

1
2

3
4
5
6
7
8
9
10
..
25
26
27
28
29
30
31









32
33
34
35
36
37
38
39
40
41
42
43
44
45
....
1373
1374
1375
1376
1377
1378
1379
1380
<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>
|

>
|







 







>
>
>
>
>
>
>
>
>






|







 







|
1
2
3
4
5
6
7
8
9
10
11
..
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
....
1383
1384
1385
1386
1387
1388
1389
1390
<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>