On Thu, May 23, 2013 at 4:31 AM, tomkrej <respektive@...> wrote:

> Hi, I'd like to get the count of all types of db pages.
>

The easy answer is to use gstat which will tell you how many pages are
used by data and indexes for each table. It does not track blobs that are
larger than a page, nor does it track the pages that determine the internal
structure of the database.

> If I run the query
> select count(*), rdb$page_type from rdb$pages group by rdb$page_type
>
> I got only about 2200 pages, but there are about 320 000 pages in DB.
>

That's expected. RDB$PAGES maps only those pages that cannot be found
in other ways. Data pages, for example, are found through pointer pages, and
index pages are found starting with the index root page, then traversing
the
index structure. Blob pages start from the blob id,

>
> And the result of query contains only - 3, 4, 6 and 9 type of page.
>

Those are the Transaction Pages, Pointer Pages, Index Root pages, and
Generator
Pages, respectively. Transaction Pages occur at irregular intervals and
map the
state of a fixed number of transactions. Pointer Pages contain arrays of
page numbers
of data pages. Index Root pages list the indexes defined for a table,
their key types,
and the page number of the page a the top of the index.

Page type 1 is the header page. There's only one and it is the first page
in the database.

Page type 2 is the Page Information Page (PIP) which indicates whether the
pages it
maps are in use or free. PIPs occur regularly in the database because each
one
maps a fixed number of pages. The last page on one PIP contains the next
PIP.

> How can I get the real numbers??
>


> There isn't a utility to get a count of all page types, but a very simple
> program
> can do it. A database is comprised of fixed-sized pages, each of which
> has a header.
> The common part of the header includes the page type. The structures are
> all defined
> in ods.h in the sources. The program should read a full page, get whatever
> information it needs from the header, and aggregate its statistics. The
> page
> type is the first byte on each page.
>
> Here are the page types:
>
> /* Page types */
>
> const SCHAR pag_undefined = 0;
> const SCHAR pag_header = 1; /* Database header page */
> const SCHAR pag_pages = 2; /* Page inventory page */
> const SCHAR pag_transactions = 3; /* Transaction inventory page */
> const SCHAR pag_pointer = 4; /* Pointer page */
> const SCHAR pag_data = 5; /* Data page */
> const SCHAR pag_root = 6; /* Index root page */
> const SCHAR pag_index = 7; /* Index (B-tree) page */
> const SCHAR pag_blob = 8; /* Blob data page */
> const SCHAR pag_ids = 9; /* Gen-ids */
> const SCHAR pag_log = 10; // Write ahead log information DEPRECATED
> const SCHAR pag_max = 10; /* Max page type */
>
> Here's the common part of the header which is the first part of each page:
>
> struct pag
> {
> SCHAR pag_type;
> SCHAR pag_flags;
> USHORT pag_checksum;
> ULONG pag_generation;
> // We renamed pag_seqno for SCN number usage to avoid major ODS version
> bump
> ULONG pag_scn; /* WAL seqno of last update */
> ULONG reserved; /* Was used for WAL */
> };
>
> The WAL (Write Ahead Log) is a bad idea that was introduced to InterBase
> during the Borland days and never
> worked.
>
> For a few page types, you might like to collect other information. For
> index pages you might want
> the index id and relation (table) id:
>
> struct btree_page
> {
> pag btr_header;
> SLONG btr_sibling; // right sibling page
> SLONG btr_left_sibling; // left sibling page
> SLONG btr_prefix_total; // sum of all prefixes on page
> *USHORT btr_relation; // relation id for consistency*
> USHORT btr_length; // length of data in bucket
> *UCHAR btr_id; // index id for consistency*
> UCHAR btr_level; // index level (0 = leaf)
> btree_nod btr_nodes[1];
> };
>
> For data pages you might want the relation id
>
> struct data_page
> {
> pag dpg_header;
> SLONG dpg_sequence; /* Sequence number in relation */
> *USHORT dpg_relation; /* Relation id */*
> USHORT dpg_count; /* Number of record segments on page */
> struct dpg_repeat {
> USHORT dpg_offset; /* Offset of record fragment */
> USHORT dpg_length; /* Length of record fragment */
> } dpg_rpt[1];
> };
>
>

Good luck,

Ann


[Non-text portions of this message have been removed]