Database Statistics Options

From InterBase

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.

Note:
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.

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:

Data page Information
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:

Header Page Information
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:

  • 1 HP Apollo Domain OS
  • 2 Sun Solaris SPARC, HP9000 s300, Xenix, Motorola IMP UNIX, UnixWare, NCR UNIX, NeXT, Data General DG-UX Intel
  • 3 Sun Solaris x86
  • 4 VMS
  • 5 VAX Ultrix
  • 6 MIPS Ultrix
  • 7 HP9000 s700/s800
  • 8 Novell NetWare
  • 9 Apple Macintosh 680x0
  • 10 IBM AIX POWER series, IBM AIX PowerPC
  • 11 Data General DG-UX 88K
  • 12 HP MPE/xl
  • 13 SGI IRIX
  • 14 Cray
  • 15 SF/1
  • 16 Microsoft Windows 7 (32-bit and 64-bit)
  • 17 OS/2
  • 18 Windows 16 bit
  • 19 LINUX on Intel series
  • 20 LINUX on Sparc systems
  • 21 DARWIN on Intel
  • 22 DARWIN on PowerPC
  • 23 DARWIN on iOS ARM architecture
  • 24 Android on x86 architecture (emulator)
  • 25 Android on ARM architecture (device

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

  • force write—indicates that forced database writes are enabled.
  • no_reserve—indicates that space is not reserved on each page for old generations of data. This enables data to be packed more closely on each page and therefore makes the database occupy less disk space.
  • shutdown—indicates database is shut down.

Variable header data

  • sweep interval
  • secondary file information


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
Index Pages Information
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.

Advance To:

Retrieved from "https://docwiki.embarcadero.com/InterBase/2020/e/index.php?title=Database_Statistics_Options&oldid=30838"