Debian Codegraph @

www/dbstat.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>The DBSTAT Virtual Table</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118 <div class=fancy>
119 <div class=nosearch>
120 <div class="fancy_title">
121 The DBSTAT Virtual Table
122 </div>
123 </div>
124
125
126
127
128
129 <h1 id="overview"><span>1. </span>Overview</h1>
130
131 <p>
132 The DBSTAT virtual table is a read-only <a href="vtab.html#epovtab">eponymous virtual table</a> that returns
133 information about the amount of disk space used to store the content
134 of an SQLite database.
135 Example use cases for the
136 DBSTAT virtual table include the <a href="sqlanalyze.html">sqlite3_analyzer.exe</a>
137 utility program and the
138 <a href="https://sqlite.org/src/repo-tabsize">table size pie-chart</a> in
139 the <a href="https://www.fossil-scm.org/">Fossil-implemented</a> version control system
140 for SQLite.
141 </p>
142
143 <p>
144 The DBSTAT virtual table is available on all
145 <a href="c3ref/sqlite3.html">database connections</a> when SQLite is built using the
146 <a href="compile.html#enable_dbstat_vtab">SQLITE_ENABLE_DBSTAT_VTAB</a> compile-time option.
147
148 </p><p>
149 The DBSTAT virtual table is an <a href="vtab.html#epovtab">eponymous virtual table</a>, meaning
150 that is not necessary to run <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> to create an
151 instance of the dbstat virtual table before using it. The "dbstat"
152 module name can be used as if it were a table name to query the
153 dbstat virtual table directly. For example:
154
155 </p><div class="codeblock"><pre>SELECT * FROM dbstat;
156 </pre></div>
157
158 <p>
159 If a named virtual table that uses the dbstat module is desired,
160 then the recommended way to create an instance of the dbstat
161 virtual table is as follows:
162
163 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE temp.stat USING dbstat(main);
164 </pre></div>
165
166 <p>
167 Note the "temp." qualifier before the virtual table name ("stat"). This
168 qualifier causes the virtual table to be temporary - to only exist for
169 the duration of the current database connection. This is the
170 recommended approach.
171
172 </p><p>
173 The "main" argument to dbstat is default schema
174 for which information is to be provided. The default is "main", and
175 so the use of "main" in the example above is redundant. For any
176 particular query, the schema can be changed by specifying the
177 alternative schema as a function argument to the virtual table
178 name in the FROM clause of the query. (See further discussion of
179 <a href="lang_select.html#tabfunc1">table-valued functions in the FROM clause</a> for more details.)
180
181 </p><p>
182 The schema for the DBSTAT virtual table looks like this:
183 </p><div class="codeblock"><pre>CREATE TABLE dbstat(
184 name TEXT, -- Name of table or index
185 path TEXT, -- Path to page from root
186 pageno INTEGER, -- Page number, or page count
187 pagetype TEXT, -- 'internal', 'leaf', 'overflow', or NULL
188 ncell INTEGER, -- Cells on page (0 for overflow pages)
189 payload INTEGER, -- Bytes of payload on this page or btree
190 unused INTEGER, -- Bytes of unused space on this page or btree
191 mx_payload INTEGER, -- Largest payload size of all cells on this row
192 pgoffset INTEGER, -- Byte offset of the page in the database file
193 pgsize INTEGER, -- Size of the page, in bytes
194 schema TEXT HIDDEN, -- Database schema being analyzed
195 aggregate BOOL HIDDEN -- True to enable aggregate mode
196 );
197 </pre></div>
198
199 <p>
200 The DBSTAT table only reports on the content of btrees within the database file.
201 Freelist pages, pointer-map pages, and the lock page are omitted from
202 the analysis.
203
204 </p><p>
205 By default, there is a single row in the DBSTAT table for each
206 btree page the database file. Each row provides
207 information about the space utilization of that one page of the
208 database. However, if the hidden column "aggregate" is TRUE, then
209 results are aggregated and there is a single row in the DBSTAT table
210 for each btree in the database, providing information about space
211 utilization across the entire btree.
212
213 <a name="dbstatpath"></a>
214 </p><h1 id="the_path_column_of_the_dbstat_virtual_table"><span>2. </span>The "path" column of the dbstat virtual table</h1>
215
216 <p>
217 The "path" column describes the path taken from the
218 root node of the btree structure to each page. The
219 "path" of the root node itself is '/'.
220 The "path" is NULL when "aggregate" is TRUE.
221
222 The "path" for the left-most child page of the root of
223 a btree page is '/000/'. (Btrees store content ordered from left to right
224 so the pages to the left have smaller keys than the pages to the right.)
225 The next to left-most child of the root page is '/001', and so on,
226 each sibling page identified by a 3-digit hex value.
227 The children of the 451st left-most sibling have paths such
228 as '/1c2/000/, '/1c2/001/' etc.
229
230 Overflow pages are specified by appending a '+' character and a
231 six-digit hexadecimal value to the path to the cell they are linked
232 from. For example, the three overflow pages in a chain linked from
233 the left-most cell of the 450th child of the root page are identified
234 by the paths:
235
236 </p><div class="codeblock"><pre>'/1c2/000+000000' // First page in overflow chain
237 '/1c2/000+000001' // Second page in overflow chain
238 '/1c2/000+000002' // Third page in overflow chain
239 </pre></div>
240
241 <p>
242 If the paths are sorted using the BINARY collation sequence, then
243 the overflow pages associated with a cell will appear earlier in the
244 sort-order than its child page:
245
246 </p><div class="codeblock"><pre>'/1c2/000/' // Left-most child of 451st child of root
247 </pre></div>
248
249 <a name="dbstatagg"></a>
250
251 <h1 id="aggregated_data"><span>3. </span>Aggregated Data</h1>
252
253 <p>
254 Beginning with SQLite version 3.31.0 (2020-01-22), the DBSTAT table
255 has a new <a href="vtab.html#hiddencol">hidden column</a> named "aggregate", which if constrained to be
256 TRUE will cause DBSTAT to generate one row per btree in the database,
257 rather than one row per page. When running in aggregated mode, the
258 "path", "pagetype", and "pgoffset" columns are always NULL and the
259 "pageno" column holds the number of pages in the entire btree, rather
260 than the number of the page that corresponds to the row.
261
262 </p><p>
263 The following table shows the meanings of the (non-hidden) columns of
264 DBSTAT in both normal and aggregated mode:
265
266 </p><center><blockquote>
267 <table border="1" cellpadding="5" cellspacing="0">
268 <tr>
269 <th>Column
270 </th><th>Normal meaning
271 </th><th>Aggregate-mode meaning
272 </th></tr>
273 <tr>
274 <th>name
275 </th><td colspan="2">
276 The name of the table or index that is implemented by
277 the btree of the current row
278 </td></tr><tr>
279 <th>path
280 </th><td>See <a href="#dbstatpath">description above</a>
281 </td><td>Always NULL
282 </td></tr><tr>
283 <th>pageno
284 </th><td>The page number of the database page for the current row
285 </td><td>The total number of pages in the btree for the current row
286 </td></tr><tr>
287 <th>pagetype
288 </th><td>'leaf' or 'interior'
289 </td><td>Always NULL
290 </td></tr><tr>
291 <th>ncell
292 </th><td colspan="2">Number of cells on the current page or btree
293 </td></tr><tr>
294 <th>payload
295 </th><td colspan="2">Bytes of useful payload on the current page or btree
296 </td></tr><tr>
297 <th>unused
298 </th><td colspan="2">Unused bytes of on the current page or btree
299 </td></tr><tr>
300 <th>mx_payload
301 </th><td colspan="2">The largest payload found anywhere in the current page
302 or btree.
303 </td></tr><tr>
304 <th>pgoffset
305 </th><td>Byte offset to the start of the page
306 </td><td>Always NULL
307 </td></tr><tr>
308 <th>pgsize
309 </th><td colspan="2">Total storage space used by the current page or btree.
310 </td></tr></table>
311 </blockquote></center>
312
313 <h1 id="example_uses_of_the_dbstat_virtual_table"><span>4. </span>Example uses of the dbstat virtual table</h1>
314
315 <p>
316 To find the total number of pages used to store table "xyz" in schema "aux1",
317 use either of the following two queries (the first is the traditional way,
318 and the second shows the use of the aggregated feature):
319
320 </p><div class="codeblock"><pre>SELECT count(*) FROM dbstat('aux1') WHERE name='xyz';
321 SELECT pageno FROM dbstat('aux1',1) WHERE name='xyz';
322 </pre></div>
323
324 <p>
325 To see how efficiently the content of a table is stored on disk,
326 compute the amount of space used to hold actual content divided
327 by the total amount of disk space used. The closer this number
328 is to 100%, the more efficient the packing. (In this example, the
329 'xyz' table is assumed to be in the 'main' schema. Again, there
330 are two different versions that show the use of DBSTAT both without
331 and with the new aggregated feature, respectively.)
332
333 </p><div class="codeblock"><pre>SELECT sum(pgsize-unused)*100.0/sum(pgsize) FROM dbstat WHERE name='xyz';
334 SELECT (pgsize-unused)*100.0/pgsize FROM dbstat
335 WHERE name='xyz' AND aggregate=TRUE;
336 </pre></div>
337
338 <p>
339 To find the average fan-out for a table, run:
340
341 </p><div class="codeblock"><pre>SELECT avg(ncell) FROM dbstat WHERE name='xyz' AND pagetype='internal';
342 </pre></div>
343
344
345 <p>
346 Modern filesystems operate faster when disk accesses are sequential.
347 Hence, SQLite will run faster if the content of the database file
348 is on sequential pages. To find out what fraction of the pages in
349 a database are sequential (and thus obtain a measurement that might
350 be useful in determining when to <a href="lang_vacuum.html">VACUUM</a>), run a query like the following:
351
352 </p><div class="codeblock"><pre>CREATE TEMP TABLE s(rowid INTEGER PRIMARY KEY, pageno INT);
353 INSERT INTO s(pageno) SELECT pageno FROM dbstat ORDER BY path;
354 SELECT sum(s1.pageno+1==s2.pageno)*1.0/count(*)
355 FROM s AS s1, s AS s2
356 WHERE s1.rowid+1=s2.rowid;
357 DROP TABLE s;
358 </pre></div>
359 <p align="center"><small><i>This page was last updated on 2025-04-16 13:13:29Z </small></i></p>
360