meta data for this page
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