Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Enhance the stat VFS to report out the total size of all pages used by a table, even if the ZIPVFS compression backend is in play. Update the sqlite3_analyzer logic to use these new outputs. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: | 19b8eaaf70db82d401d33beb7fd36045 |
| User & Date: | drh 2011-09-28 00:50:14 |
Context
|
2011-09-28
| ||
| 01:10 | In the shell, allow arbitrary table names on the ".import" command. Ticket [d1d84037b90a449]. check-in: f4dd32d3 user: drh tags: trunk | |
| 00:50 | Enhance the stat VFS to report out the total size of all pages used by a table, even if the ZIPVFS compression backend is in play. Update the sqlite3_analyzer logic to use these new outputs. check-in: 19b8eaaf user: drh tags: trunk | |
|
2011-09-27
| ||
| 13:40 | Changes to sqlite3_analyzer to try to avoid integer overflow problems when linking against older versions of TCL. check-in: 8c846311 user: drh tags: trunk | |
Changes
Changes to src/test_stat.c.
60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 ... 122 123 124 125 126 127 128 129 130 131 132 133 134 135 ... 279 280 281 282 283 284 285 286 287 288 289 290 291 292 ... 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 ... 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 ... 527 528 529 530 531 532 533 534 535 536 537 538 539 540 ... 603 604 605 606 607 608 609 610 |
" name STRING, /* Name of table or index */" \ " path INTEGER, /* Path to page from root */" \ " pageno INTEGER, /* Page number */" \ " pagetype STRING, /* 'internal', 'leaf' or 'overflow' */" \ " ncell INTEGER, /* Cells on page (0 for overflow) */" \ " payload INTEGER, /* Bytes of payload on this page */" \ " unused INTEGER, /* Bytes of unused space on this page */" \ " mx_payload INTEGER /* Largest payload size of all cells */" \ ");" #if 0 #define VTAB_SCHEMA2 \ "CREATE TABLE yy( " \ " pageno INTEGER, /* B-tree page number */" \ " cellno INTEGER, /* Cell number within page */" \ " local INTEGER, /* Bytes of content stored locally */" \ " payload INTEGER, /* Total cell payload size */" \ " novfl INTEGER /* Number of overflow pages */" \ ");" #endif typedef struct StatTable StatTable; typedef struct StatCursor StatCursor; typedef struct StatPage StatPage; typedef struct StatCell StatCell; struct StatCell { ................................................................................ char *zPath; /* Value of 'path' column */ u32 iPageno; /* Value of 'pageno' column */ char *zPagetype; /* Value of 'pagetype' column */ int nCell; /* Value of 'ncell' column */ int nPayload; /* Value of 'payload' column */ int nUnused; /* Value of 'unused' column */ int nMxPayload; /* Value of 'mx_payload' column */ }; struct StatTable { sqlite3_vtab base; sqlite3 *db; }; ................................................................................ } static int statDecodePage(Btree *pBt, StatPage *p){ int nUnused; int iOff; int nHdr; int isLeaf; u8 *aData = sqlite3PagerGetData(p->pPg); u8 *aHdr = &aData[p->iPgno==1 ? 100 : 0]; p->flags = aHdr[0]; p->nCell = get2byte(&aHdr[3]); p->nMxPayload = 0; ................................................................................ iOff = get2byte(&aHdr[1]); while( iOff ){ nUnused += get2byte(&aData[iOff+2]); iOff = get2byte(&aData[iOff]); } p->nUnused = nUnused; p->iRightChildPg = isLeaf ? 0 : sqlite3Get4byte(&aHdr[8]); if( p->nCell ){ int i; /* Used to iterate through cells */ int nUsable = sqlite3BtreeGetPageSize(pBt) - sqlite3BtreeGetReserve(pBt); p->aCell = sqlite3_malloc((p->nCell+1) * sizeof(StatCell)); memset(p->aCell, 0, (p->nCell+1) * sizeof(StatCell)); for(i=0; i<p->nCell; i++){ StatCell *pCell = &p->aCell[i]; ................................................................................ /* Populate the StatCursor fields with the values to be returned ** by the xColumn() and xRowid() methods. */ if( rc==SQLITE_OK ){ int i; StatPage *p = &pCsr->aPage[pCsr->iPage]; pCsr->zName = (char *)sqlite3_column_text(pCsr->pStmt, 0); pCsr->iPageno = p->iPgno; statDecodePage(pBt, p); switch( p->flags ){ case 0x05: /* table internal */ case 0x02: /* index internal */ pCsr->zPagetype = "internal"; break; case 0x0D: /* table leaf */ ................................................................................ break; case 6: /* unused */ sqlite3_result_int(ctx, pCsr->nUnused); break; case 7: /* mx_payload */ sqlite3_result_int(ctx, pCsr->nMxPayload); break; } return SQLITE_OK; } static int statRowid(sqlite3_vtab_cursor *pCursor, sqlite_int64 *pRowid){ StatCursor *pCsr = (StatCursor *)pCursor; *pRowid = pCsr->iPageno; ................................................................................ } int SqlitetestStat_Init(Tcl_Interp *interp){ Tcl_CreateObjCommand(interp, "register_dbstat_vtab", test_dbstat, 0, 0); return TCL_OK; } #endif /* if defined(SQLITE_TEST) || TCLSH==2 */ |
| | < < < < < < < < < > < > > > > | > > > > > > > > > > > > > > > > > > > > > > < |
60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 ... 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 ... 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 ... 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 ... 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 ... 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 ... 620 621 622 623 624 625 626 |
" name STRING, /* Name of table or index */" \ " path INTEGER, /* Path to page from root */" \ " pageno INTEGER, /* Page number */" \ " pagetype STRING, /* 'internal', 'leaf' or 'overflow' */" \ " ncell INTEGER, /* Cells on page (0 for overflow) */" \ " payload INTEGER, /* Bytes of payload on this page */" \ " unused INTEGER, /* Bytes of unused space on this page */" \ " mx_payload INTEGER, /* Largest payload size of all cells */" \ " pgoffset INTEGER, /* Offset of page in file */" \ " pgsize INTEGER /* Size of the page */" \ ");" typedef struct StatTable StatTable; typedef struct StatCursor StatCursor; typedef struct StatPage StatPage; typedef struct StatCell StatCell; struct StatCell { ................................................................................ char *zPath; /* Value of 'path' column */ u32 iPageno; /* Value of 'pageno' column */ char *zPagetype; /* Value of 'pagetype' column */ int nCell; /* Value of 'ncell' column */ int nPayload; /* Value of 'payload' column */ int nUnused; /* Value of 'unused' column */ int nMxPayload; /* Value of 'mx_payload' column */ i64 iOffset; /* Value of 'pgOffset' column */ int szPage; /* Value of 'pgSize' column */ }; struct StatTable { sqlite3_vtab base; sqlite3 *db; }; ................................................................................ } static int statDecodePage(Btree *pBt, StatPage *p){ int nUnused; int iOff; int nHdr; int isLeaf; int szPage; u8 *aData = sqlite3PagerGetData(p->pPg); u8 *aHdr = &aData[p->iPgno==1 ? 100 : 0]; p->flags = aHdr[0]; p->nCell = get2byte(&aHdr[3]); p->nMxPayload = 0; ................................................................................ iOff = get2byte(&aHdr[1]); while( iOff ){ nUnused += get2byte(&aData[iOff+2]); iOff = get2byte(&aData[iOff]); } p->nUnused = nUnused; p->iRightChildPg = isLeaf ? 0 : sqlite3Get4byte(&aHdr[8]); szPage = sqlite3BtreeGetPageSize(pBt); if( p->nCell ){ int i; /* Used to iterate through cells */ int nUsable = szPage - sqlite3BtreeGetReserve(pBt); p->aCell = sqlite3_malloc((p->nCell+1) * sizeof(StatCell)); memset(p->aCell, 0, (p->nCell+1) * sizeof(StatCell)); for(i=0; i<p->nCell; i++){ StatCell *pCell = &p->aCell[i]; ................................................................................ /* Populate the StatCursor fields with the values to be returned ** by the xColumn() and xRowid() methods. */ if( rc==SQLITE_OK ){ int i; sqlite3_file *fd; sqlite3_int64 x[2]; StatPage *p = &pCsr->aPage[pCsr->iPage]; pCsr->zName = (char *)sqlite3_column_text(pCsr->pStmt, 0); pCsr->iPageno = p->iPgno; statDecodePage(pBt, p); /* The default page size and offset */ pCsr->szPage = sqlite3BtreeGetPageSize(pBt); pCsr->iOffset = pCsr->szPage * (p->iPgno - 1); /* If connected to a ZIPVFS backend, override the page size and ** offset with actual values obtained from ZIPVFS. */ fd = sqlite3PagerFile(pPager); x[0] = p->iPgno; if( sqlite3OsFileControl(fd, 230440, &x)==SQLITE_OK ){ pCsr->iOffset = x[0]; pCsr->szPage = x[1]; } switch( p->flags ){ case 0x05: /* table internal */ case 0x02: /* index internal */ pCsr->zPagetype = "internal"; break; case 0x0D: /* table leaf */ ................................................................................ break; case 6: /* unused */ sqlite3_result_int(ctx, pCsr->nUnused); break; case 7: /* mx_payload */ sqlite3_result_int(ctx, pCsr->nMxPayload); break; case 8: /* pgoffset */ sqlite3_result_int64(ctx, pCsr->iOffset); break; case 9: /* pgsize */ sqlite3_result_int(ctx, pCsr->szPage); break; } return SQLITE_OK; } static int statRowid(sqlite3_vtab_cursor *pCursor, sqlite_int64 *pRowid){ StatCursor *pCsr = (StatCursor *)pCursor; *pRowid = pCsr->iPageno; ................................................................................ } int SqlitetestStat_Init(Tcl_Interp *interp){ Tcl_CreateObjCommand(interp, "register_dbstat_vtab", test_dbstat, 0, 0); return TCL_OK; } #endif /* if defined(SQLITE_TEST) || TCLSH==2 */ |
Changes to test/stat.test.
32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 .. 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 ... 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 ... 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 ... 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 |
SELECT * FROM stat;
} {}
ifcapable wal {
do_execsql_test stat-0.1 {
PRAGMA journal_mode = WAL;
PRAGMA journal_mode = delete;
SELECT * FROM stat;
} {wal delete sqlite_master / 1 leaf 0 0 916 0}
}
do_test stat-1.0 {
execsql {
CREATE TABLE t1(a, b);
CREATE INDEX i1 ON t1(b);
INSERT INTO t1(rowid, a, b) VALUES(2, 2, 3);
INSERT INTO t1(rowid, a, b) VALUES(3, 4, 5);
}
} {}
do_test stat-1.1 {
execsql {
SELECT * FROM stat WHERE name = 't1';
}
} {t1 / 2 leaf 2 10 998 5}
do_test stat-1.2 {
execsql {
SELECT * FROM stat WHERE name = 'i1';
}
} {i1 / 3 leaf 2 10 1000 5}
do_test stat-1.3 {
execsql {
SELECT * FROM stat WHERE name = 'sqlite_master';
}
} {sqlite_master / 1 leaf 2 77 831 40}
do_test stat-1.4 {
execsql {
DROP TABLE t1;
}
} {}
................................................................................
CREATE TABLE t3(a PRIMARY KEY, b);
INSERT INTO t3(rowid, a, b) VALUES(2, a_string(111), a_string(222));
INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
SELECT * FROM stat WHERE name != 'sqlite_master';
} [list \
sqlite_autoindex_t3_1 / 3 internal 3 368 623 125 \
sqlite_autoindex_t3_1 /000/ 8 leaf 8 946 46 123 \
sqlite_autoindex_t3_1 /001/ 9 leaf 8 988 2 131 \
sqlite_autoindex_t3_1 /002/ 15 leaf 7 857 137 132 \
sqlite_autoindex_t3_1 /003/ 20 leaf 6 739 257 129 \
t3 / 2 internal 15 0 907 0 \
................................................................................
]
do_execsql_test stat-2.2 { DROP TABLE t3 } {}
do_execsql_test stat-3.1 {
CREATE TABLE t4(x);
CREATE INDEX i4 ON t4(x);
INSERT INTO t4(rowid, x) VALUES(2, a_string(7777));
SELECT * FROM stat WHERE name != 'sqlite_master';
} [list \
i4 / 3 leaf 1 103 905 7782 \
i4 /000+000000 9 overflow 0 1020 0 0 \
i4 /000+000001 10 overflow 0 1020 0 0 \
i4 /000+000002 11 overflow 0 1020 0 0 \
i4 /000+000003 12 overflow 0 1020 0 0 \
i4 /000+000004 13 overflow 0 1020 0 0 \
................................................................................
t4 /000+000005 18 overflow 0 1020 0 0 \
t4 /000+000006 17 overflow 0 1020 0 0 \
]
do_execsql_test stat-4.1 {
CREATE TABLE t5(x);
CREATE INDEX i5 ON t5(x);
SELECT * FROM stat WHERE name = 't5' OR name = 'i5';
} [list \
i5 / 5 leaf 0 0 1016 0 \
t5 / 4 leaf 0 0 1016 0 \
]
db close
forcedelete test.db
................................................................................
breakpoint
do_execsql_test stat-5.1 {
PRAGMA auto_vacuum = OFF;
CREATE VIRTUAL TABLE temp.stat USING dbstat;
CREATE TABLE t1(x);
INSERT INTO t1 VALUES(zeroblob(1513));
INSERT INTO t1 VALUES(zeroblob(1514));
SELECT * FROM stat WHERE name = 't1';
} [list \
t1 / 2 leaf 2 993 5 1517 \
t1 /000+000000 3 overflow 0 1020 0 0 \
t1 /001+000000 4 overflow 0 1020 0 0 \
]
finish_test
|
> | > | > | > | > | > | > | > | |
32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 .. 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 ... 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 ... 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 ... 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 |
SELECT * FROM stat;
} {}
ifcapable wal {
do_execsql_test stat-0.1 {
PRAGMA journal_mode = WAL;
PRAGMA journal_mode = delete;
SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload
FROM stat;
} {wal delete sqlite_master / 1 leaf 0 0 916 0}
}
do_test stat-1.0 {
execsql {
CREATE TABLE t1(a, b);
CREATE INDEX i1 ON t1(b);
INSERT INTO t1(rowid, a, b) VALUES(2, 2, 3);
INSERT INTO t1(rowid, a, b) VALUES(3, 4, 5);
}
} {}
do_test stat-1.1 {
execsql {
SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload
FROM stat WHERE name = 't1';
}
} {t1 / 2 leaf 2 10 998 5}
do_test stat-1.2 {
execsql {
SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload
FROM stat WHERE name = 'i1';
}
} {i1 / 3 leaf 2 10 1000 5}
do_test stat-1.3 {
execsql {
SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload
FROM stat WHERE name = 'sqlite_master';
}
} {sqlite_master / 1 leaf 2 77 831 40}
do_test stat-1.4 {
execsql {
DROP TABLE t1;
}
} {}
................................................................................
CREATE TABLE t3(a PRIMARY KEY, b);
INSERT INTO t3(rowid, a, b) VALUES(2, a_string(111), a_string(222));
INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload
FROM stat WHERE name != 'sqlite_master';
} [list \
sqlite_autoindex_t3_1 / 3 internal 3 368 623 125 \
sqlite_autoindex_t3_1 /000/ 8 leaf 8 946 46 123 \
sqlite_autoindex_t3_1 /001/ 9 leaf 8 988 2 131 \
sqlite_autoindex_t3_1 /002/ 15 leaf 7 857 137 132 \
sqlite_autoindex_t3_1 /003/ 20 leaf 6 739 257 129 \
t3 / 2 internal 15 0 907 0 \
................................................................................
]
do_execsql_test stat-2.2 { DROP TABLE t3 } {}
do_execsql_test stat-3.1 {
CREATE TABLE t4(x);
CREATE INDEX i4 ON t4(x);
INSERT INTO t4(rowid, x) VALUES(2, a_string(7777));
SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload
FROM stat WHERE name != 'sqlite_master';
} [list \
i4 / 3 leaf 1 103 905 7782 \
i4 /000+000000 9 overflow 0 1020 0 0 \
i4 /000+000001 10 overflow 0 1020 0 0 \
i4 /000+000002 11 overflow 0 1020 0 0 \
i4 /000+000003 12 overflow 0 1020 0 0 \
i4 /000+000004 13 overflow 0 1020 0 0 \
................................................................................
t4 /000+000005 18 overflow 0 1020 0 0 \
t4 /000+000006 17 overflow 0 1020 0 0 \
]
do_execsql_test stat-4.1 {
CREATE TABLE t5(x);
CREATE INDEX i5 ON t5(x);
SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload
FROM stat WHERE name = 't5' OR name = 'i5';
} [list \
i5 / 5 leaf 0 0 1016 0 \
t5 / 4 leaf 0 0 1016 0 \
]
db close
forcedelete test.db
................................................................................
breakpoint
do_execsql_test stat-5.1 {
PRAGMA auto_vacuum = OFF;
CREATE VIRTUAL TABLE temp.stat USING dbstat;
CREATE TABLE t1(x);
INSERT INTO t1 VALUES(zeroblob(1513));
INSERT INTO t1 VALUES(zeroblob(1514));
SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload
FROM stat WHERE name = 't1';
} [list \
t1 / 2 leaf 2 993 5 1517 \
t1 /000+000000 3 overflow 0 1020 0 0 \
t1 /001+000000 4 overflow 0 1020 0 0 \
]
finish_test
|
Changes to tool/spaceanal.tcl.
55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 .. 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 ... 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 ... 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 ... 270 271 272 273 274 275 276 277 278 279 280 281 282 283 |
mx_payload int, -- Maximum payload size int_pages int, -- Number of interior pages used leaf_pages int, -- Number of leaf pages used ovfl_pages int, -- Number of overflow pages used int_unused int, -- Number of unused bytes on interior pages leaf_unused int, -- Number of unused bytes on primary pages ovfl_unused int, -- Number of unused bytes on overflow pages gap_cnt int -- Number of gaps in the page layout );} mem eval $tabledef # Create a temporary "dbstat" virtual table. # db eval {CREATE VIRTUAL TABLE temp.stat USING dbstat} db eval {CREATE TEMP TABLE dbstat AS SELECT * FROM temp.stat ................................................................................ sum(path LIKE '%+000000') AS ovfl_cnt, max(mx_payload) AS mx_payload, sum(isinternal(pagetype, $is_index)) AS int_pages, sum(isleaf(pagetype, $is_index)) AS leaf_pages, sum(isoverflow(pagetype, $is_index)) AS ovfl_pages, sum(isinternal(pagetype, $is_index) * unused) AS int_unused, sum(isleaf(pagetype, $is_index) * unused) AS leaf_unused, sum(isoverflow(pagetype, $is_index) * unused) AS ovfl_unused FROM temp.dbstat WHERE name = $name } break # Column 'gap_cnt' is set to the number of non-contiguous entries in the # list of pages visited if the b-tree structure is traversed in a top-down # fashion (each node visited before its child-tree is passed). Any overflow # chains present are traversed from start to finish before any child-tree ................................................................................ $mx_payload, $int_pages, $leaf_pages, $ovfl_pages, $int_unused, $leaf_unused, $ovfl_unused, $gap_cnt ); } } proc integerify {real} { if {[string is double -strict $real]} { return [expr {wide($real)}] ................................................................................ int(sum(ovfl_cnt)) as ovfl_cnt, int(sum(leaf_pages)) AS leaf_pages, int(sum(int_pages)) AS int_pages, int(sum(ovfl_pages)) AS ovfl_pages, int(sum(leaf_unused)) AS leaf_unused, int(sum(int_unused)) AS int_unused, int(sum(ovfl_unused)) AS ovfl_unused, int(sum(gap_cnt)) AS gap_cnt FROM space_used WHERE $where" {} {} # Output the sub-report title, nicely decorated with * characters. # puts "" set len [string length $title] set stars [string repeat * [expr 65-$len]] ................................................................................ set ovfl_cnt_percent [percent $ovfl_cnt $nleaf {of all entries}] # Print out the sub-report statistics. # statline {Percentage of total database} $total_pages_percent statline {Number of entries} $nleaf statline {Bytes of storage consumed} $storage statline {Bytes of payload} $payload $payload_percent statline {Average payload per entry} $avg_payload statline {Average unused bytes per entry} $avg_unused if {[info exists avg_fanout]} { statline {Average fanout} $avg_fanout } if {$total_pages>1} { |
| > | > | > | > > > > > > |
55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 .. 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 ... 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 ... 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 ... 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 |
mx_payload int, -- Maximum payload size int_pages int, -- Number of interior pages used leaf_pages int, -- Number of leaf pages used ovfl_pages int, -- Number of overflow pages used int_unused int, -- Number of unused bytes on interior pages leaf_unused int, -- Number of unused bytes on primary pages ovfl_unused int, -- Number of unused bytes on overflow pages gap_cnt int, -- Number of gaps in the page layout compressed_size int -- Total bytes stored on disk );} mem eval $tabledef # Create a temporary "dbstat" virtual table. # db eval {CREATE VIRTUAL TABLE temp.stat USING dbstat} db eval {CREATE TEMP TABLE dbstat AS SELECT * FROM temp.stat ................................................................................ sum(path LIKE '%+000000') AS ovfl_cnt, max(mx_payload) AS mx_payload, sum(isinternal(pagetype, $is_index)) AS int_pages, sum(isleaf(pagetype, $is_index)) AS leaf_pages, sum(isoverflow(pagetype, $is_index)) AS ovfl_pages, sum(isinternal(pagetype, $is_index) * unused) AS int_unused, sum(isleaf(pagetype, $is_index) * unused) AS leaf_unused, sum(isoverflow(pagetype, $is_index) * unused) AS ovfl_unused, sum(pgsize) AS compressed_size FROM temp.dbstat WHERE name = $name } break # Column 'gap_cnt' is set to the number of non-contiguous entries in the # list of pages visited if the b-tree structure is traversed in a top-down # fashion (each node visited before its child-tree is passed). Any overflow # chains present are traversed from start to finish before any child-tree ................................................................................ $mx_payload, $int_pages, $leaf_pages, $ovfl_pages, $int_unused, $leaf_unused, $ovfl_unused, $gap_cnt, $compressed_size ); } } proc integerify {real} { if {[string is double -strict $real]} { return [expr {wide($real)}] ................................................................................ int(sum(ovfl_cnt)) as ovfl_cnt, int(sum(leaf_pages)) AS leaf_pages, int(sum(int_pages)) AS int_pages, int(sum(ovfl_pages)) AS ovfl_pages, int(sum(leaf_unused)) AS leaf_unused, int(sum(int_unused)) AS int_unused, int(sum(ovfl_unused)) AS ovfl_unused, int(sum(gap_cnt)) AS gap_cnt, int(sum(compressed_size)) AS compressed_size FROM space_used WHERE $where" {} {} # Output the sub-report title, nicely decorated with * characters. # puts "" set len [string length $title] set stars [string repeat * [expr 65-$len]] ................................................................................ set ovfl_cnt_percent [percent $ovfl_cnt $nleaf {of all entries}] # Print out the sub-report statistics. # statline {Percentage of total database} $total_pages_percent statline {Number of entries} $nleaf statline {Bytes of storage consumed} $storage if {$compressed_size!=$storage} { set pct [expr {$compressed_size*100.0/$storage}] set pct [format {%5.1f%%} $pct] statline {Bytes used after compression} $compressed_size $pct } statline {Bytes of payload} $payload $payload_percent statline {Average payload per entry} $avg_payload statline {Average unused bytes per entry} $avg_unused if {[info exists avg_fanout]} { statline {Average fanout} $avg_fanout } if {$total_pages>1} { |