Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Improvements to the DBSTAT and sqlite3_analyzer.exe documentation pages. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
13de82bb23cffbb07f5386c175494f87 |
User & Date: | drh 2016-06-23 17:44:21.497 |
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
Changes to pages/dbstat.in.
1 2 | <title>The DBSTAT Virtual Table</title> <tcl>hd_keywords dbstat {dbstat virtual table}</tcl> | > | > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | <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. |
︙ | ︙ | |||
64 65 66 67 68 69 70 | <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. | | | 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 | <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 |
︙ | ︙ | |||
91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 | <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> | > | | 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 | <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 11 | <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: |
︙ | ︙ | |||
25 26 27 28 29 30 31 32 33 34 35 36 37 | 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. | > > > > > > > > > | | 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 | 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 ██████████████████/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% |
︙ | ︙ | |||
1373 1374 1375 1376 1377 1378 1379 | 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; | | | 1383 1384 1385 1386 1387 1388 1389 1390 | 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> |