> > In your example we see 7149 physical reads and 214192 times this > > pages was referenced by the engine. To read a record engine needs to > > access pointer page and (at least one) data page. You have ~100K records > so we can explain ~200K fetches. > > Ok, but is there a way then to tell how many pages have been fetched from > the cache as the number above for fetched is more likely "referenced" and > not real number of pages fetched from memory? > > I guess the same applies to MON$IO_STATS.MON$PAGE_FETCHES? If so, > isn't comparing MON$PAGE_READS with MON$PAGE_FETCHES a bit > misleading if one wants to check to possibly increase the database page > buffers?
Depending on the size of a query and the nature of the data, it is possible that data from a related table accessed via a FK would be: - loaded into cache (count as 1 disk read), - be referenced 4 subsequent times (count as 4 fetches) and - then be dropped from cache (due to LRU algorithm) - to be re-loaded into cache at a later point in the query execution (count as ANOTHER disk read) - be referenced 4 more subsequent times (count as 4 fetches) The fact that (in the above example) there are 20% disk reads (2 out of 10 total operations) could suggest that cache should be increased. The ideal statistic that would help in knowing whether the cache needs to be increased would be "disk re-reads", but that would require that the engine track a list of the RDB$Key of every row accessed in a query to make the distinction between "read" (initial load) and "re-read" (re-load into cache due to LRU). That list would likely require more overhead than really its worth. So, they are not "misleading", they are just "not ideal". Sean ------------------------------------------------------------------------------ RSA(R) Conference 2012 Save $700 by Nov 18 Register now http://p.sf.net/sfu/rsa-sfdev2dev1 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
