Using MON$IO_STATS

As mentioned above, the table MON$IO_STATS can be used to help you determine how well your buffer cache is performing. The table has the following structure:

MONS$STAT_ID

The statistic id.

MONS$STAT_GROUP

The statistic group. Statistics are gathered for the following groups:

0: The database as a whole.
1: Attachments.
2: Transactions.
3: Statements.
4: Calls.

MON$PAGE_READS

The number of pages read. These are the pages read from the disc and not from the cache.

MON$PAGE_WRITES

The number of pages written back to disc.

MON$PAGE_FETCHES

The number of pages read from the cache as opposed to from disc.

MON$PAGE_MARKS

The number of pages changed while in the cache. It is possible that not all of these have been written back to disc.

To inspect the current statistics for the database as a whole, we would use the following query in isql:

tux> isql employee
Database:  employee

SQL> SELECT MON$PAGE_READS, MON$PAGE_WRITES, MON$PAGE_FETCHES, MON$PAGE_MARKS
CON> FROM MON$IO_STATS
CON> WHERE MON$STAT_GROUP = 0;

     MON$PAGE_READS     MON$PAGE_WRITES    MON$PAGE_FETCHES      MON$PAGE_MARKS
=================== =================== =================== ===================
                134                 526               13851                 529

The results of the above show that:

134 pages have had to be physically read from the disc into the cache so far.
13,851 pages, on the other hand, have been read directly from the cache.
529 pages, in the cache, have been changed in some way.
526 changed pages have been copied from the cache to disc.

We can assume, therefore, that although a small number of pages have been read into the cache, there is nothing we can do to avoid that. When the database is started up the cache is empty, when applications connect and access the database, various pages must be read and the cache must be filled, so physical reads will be a necessity. In this example, it appears that once pages are in the cache they are being accessed quite frequently given that there have been approximately 103 cache reads for every physical read.

Of the 529 updated pages - and these are system as well as user pages - 526 have been written back to the physical discs but three still remain in cache, as yet, unwritten.

The results shown above show the performance of the cache over the life of the database so far. We can narrow this down to our current attachments by modifying the query to select those rows where the MON$STAT_GROUP is 1.

SQL> SELECT MON$PAGE_READS, MON$PAGE_WRITES, MON$PAGE_FETCHES, MON$PAGE_MARKS
CON> FROM MON$IO_STATS
CON> WHERE MON$STAT_GROUP = 1;

     MON$PAGE_READS     MON$PAGE_WRITES    MON$PAGE_FETCHES      MON$PAGE_MARKS
=================== =================== =================== ===================
                  0                   4                  87                   5
                134                 520               13619                 522

Interpretation of the above statistics is exactly the same as for the database as a whole.

We can further diagnose the statistics by individual transactions, as follows:

SQL> SELECT MON$PAGE_READS, MON$PAGE_WRITES, MON$PAGE_FETCHES, MON$PAGE_MARKS
CON> FROM MON$IO_STATS
CON> WHERE MON$STAT_GROUP = 2;

     MON$PAGE_READS     MON$PAGE_WRITES    MON$PAGE_FETCHES      MON$PAGE_MARKS
=================== =================== =================== ===================
                  0                   0                  60                   0
                  0                   0                   1                   0
                  0                   0                   1                   0
                  0                   0                  69                   0
                  0                   0                  93                   0
                  0                   0                  85                   0
                  0                   0                   1                   0
                  0                   0                   1                   0

And, by individual statements:

SQL> SELECT MON$PAGE_READS, MON$PAGE_WRITES, MON$PAGE_FETCHES, MON$PAGE_MARKS
CON> FROM MON$IO_STATS
CON> WHERE MON$STAT_GROUP = 3;

     MON$PAGE_READS     MON$PAGE_WRITES    MON$PAGE_FETCHES      MON$PAGE_MARKS
=================== =================== =================== ===================
                  0                   0                   1                   0
                  0                   0                  38                   0
                  0                   0                   4                   0
                  0                   0                  18                   0
                  0                   0                 158                   0
                  0                   0                   1                   0
                  0                   0                   1                   0
                  0                   0                   1                   0
                  0                   0                   1                   0
                  0                   0                   1                   0
                  0                   0                   0                   0
                  0                   0                   1                   0
                  1                   0                  12                   0
                  0                   0                   2                   0
                  3                   0                1436                   0
                  0                   0                 101                   0
                  7                   0                 613                   0

Finally, it is possible - and probably most useful - to determine the statistics for your own session. You can find your attachment id from CURRENT_CONNECTION and use that in a query that joins with MON$IO_STATS using the MON$STAT_ID column.

SQL> SET LIST;

SQL> SELECT T.MON$ATTACHMENT_ID, T.MON$TRANSACTION_ID,
CON> IO.MON$PAGE_READS, IO.MON$PAGE_WRITES,
CON> IO.MON$PAGE_FETCHES, IO.MON$PAGE_MARKS
CON> FROM MON$TRANSACTIONS AS T
CON> JOIN MON$IO_STATS as IO
CON> ON (IO.MON$STAT_ID = T.MON$STAT_ID)
CON> WHERE T.MON$ATTACHMENT_ID = CURRENT_CONNECTION;

MON$ATTACHMENT_ID               12
MON$TRANSACTION_ID              218
MON$PAGE_READS                  5
MON$PAGE_WRITES                 0
MON$PAGE_FETCHES                66
MON$PAGE_MARKS                  0

MON$ATTACHMENT_ID               12
MON$TRANSACTION_ID              217
MON$PAGE_READS                  0
MON$PAGE_WRITES                 0
MON$PAGE_FETCHES                1
MON$PAGE_MARKS                  0

Appendices

Using MON$IO_STATS

Appendices

Document history

docnext count = 1

License notice