Database Statistics Options
Go Up to Viewing Statistics using IBConsole
When you request a statistic option, InterBase generates and displays information for that database statistic. Possible statistic option values include: All Options, Data Pages, Database Log, Header Pages, Index Pages, and System Relations.
In addition to the selected statistic, header page information is displayed, regardless which statistic has been selected to report. If Header Pages is the selected option value, then only header page information will be displayed.
Contents
All Options
Displays statistic information for all options including Data Pages, Database Log, Header Pages, Index Pages, and System Relations.
This function corresponds to the -all
option of gstat
.
Data Pages
Displays data page information in the database summary. Below is an example of data page information, followed by an explanation of each item.
COUNTRY (31) Primary pointer page: 246, Index root page: 247 Data pages: 1, data page slots: 1, average fill: 59% Fill distribution: 0 - 19% = 0 20 - 39% = 0 40 - 59% = 1 60 - 79% = 0 80 - 99% = 0
The first line displays a database table name while the remaining lines contain item information pertaining to the table. These items include:
Item | Description |
---|---|
Primary pointer page |
The page that is the first pointer page for the table. |
Index root page |
The page number that is the first pointer page for indexes. |
Data pages |
The total number of data pages. |
Data page slots |
The number of pointers to database pages, whether the pages are still in the database or not. |
Average fill |
The average percentage to which the data pages are filled. |
Fill distribution |
A histogram that shows the number of data pages that are filled to the percentages. |
Database Log
Displays the database log in the database summary. Below is an example of database log information.
This function corresponds to the -log
option of gstat
.
Database log page information: Creation date Dec 20, 1998 11:38:19 Log flags: 2 No write ahead log Next log page: 0 Variable log data: Control Point 1: File name: Partition offset: 0 Seqno: 0 Offset: 0 Control Point 2: File name: Partition offset: 0 Seqno: 0 Offset: 0 Current File: File name: Partition offset: 0 Seqno: 0 Offset: 0
Header Pages
Displays header page information in the database summary. Below is an example of database summary header page information, followed by an explanation of each item.
This function corresponds to the -header
option of gstat
.
Database "C:\Embarcadero\InterBase\examples\Database\employee.ib" Database header page information: Flags 0 Checksum 12345 Generation 41 Page size 4096 ODS version 12.0 Oldest transaction 29 Oldest active 30 Oldest snapshot 30 Next transaction 34 Bumped transaction 1 Sequence number 0 Next attachment ID 0 Implementation ID 16 Shadow count 0 Page buffers 0 Next header page 0 Database dialect 1 Creation date Aug 26, 2006 17:05:03 Variable header data: Sweep interval: 20000 *END* Service ended at 9/3/2006 4:59:05 PM
The first line displays the name and location of the primary database file while the remaining lines contain information on the database header page. These items include:
Item | Description |
---|---|
Checksum |
InterBase supports true checksums only for ODS 8 and earlier. For ODS 9 and later, the checksum value is always “12345”. |
Generation |
Counter incremented each time header page is written. |
Page size |
The current database page size, in bytes. |
ODS version |
The version of the database’s on-disk structure. |
Oldest transaction |
The transaction ID number of the oldest “interesting” transaction (those that are active, in limbo, or rolled back, but not committed). |
Oldest active |
The transaction ID number of the oldest active transaction. |
Next transaction |
The transaction ID number that InterBase assigns to the next transaction. The difference between the oldest transaction and the next transaction determines when database sweeping occurs. For example, if the difference is greater than this difference (set to 20,000 by default), then InterBase initiates a database sweep. See Overview of Sweeping. |
Sequence number |
The sequence number of the header page (zero is used for the first page, one for second page, and so on). |
Next connection ID |
ID number of the next database connection. |
Implementation ID |
The architecture of the system on which the database was created. These ID definitions are platform-dependent #define directives for a macro class named CLASS:
|
Shadow count |
The number of shadow files defined for the database. |
Number of cache buffers |
The number of page buffers in the database cache. |
Next header page |
The ID of the next header page. |
Database dialect |
The SQL dialect of the database |
Creation date |
The date when the database was created. |
Attributes |
|
Variable header data |
|
Index Pages
Displays index information in the database summary. Below is an example of index page information, followed by an explanation of each item.
Index CUSTNAMEX (2) Depth: 2, leaf buckets: 2, nodes: 27 Average data length: 45.00, total dup: 0, max dup: 0 Fill distribution: 0 - 19% = 0 20 - 39% = 0 40 - 59% = 1 60 - 79% = 0 80 - 99% = 1
Item | Description |
---|---|
Index |
The name of the index. |
Depth |
The number of levels in the index page tree. If the depth of the index page tree is greater than three, then sorting may not be as efficient as possible. To reduce the depth of the index page tree, increase the page size. If increasing the page size does not reduce the depth, then return it to its previous size. |
Leaf buckets |
The number of leaf (bottom level) pages in the index page tree. |
Nodes |
The total number of index pages in the tree. |
Average data length |
The average length of each key, in bytes. |
Total dup |
The total number of rows that have duplicate indexes. |
Max dup |
The number of duplicates of the index with the most duplicates |
Fill distribution |
A histogram that shows the number of index pages filled to the specified percentages. |
System Relations
Displays information for system tables in the database.
RDB$CHECK_CONSTRAINTS (24) Primary pointer page: 54, Index root page: 55 Data pages: 5, data page slots: 5, average fill: 59% Fill distribution: 0 - 19% = 0 20 - 39% = 1 40 - 59% = 0 60 - 79% = 4 80 - 99% = 0 Index RDB$INDEX_14 (0) Depth: 1, leaf buckets: 1, nodes: 68 Average data length: 0.00, total dup: 14, max dup: 1 Fill distribution: 0 - 19% = 0 20 - 39% = 0 40 - 59% = 1 60 - 79% = 0 80 - 99% = 0
The statistics contained here are similar to that of data pages and index pages. For information on the items see Data Pages and Index Pages above.