Methods to estimate existing cache contents for better query optimization

Abstract

A method for estimating contents of a cache determines table descriptors referenced by a query, and scans each page header stored in the cache for the table descriptor. If the table descriptor matches any of the referenced table descriptors, a page count value corresponding to the matching referenced table descriptor is increased. Alternatively, a housekeeper thread periodically performs the scan and stores the page count values in a central lookup table accessible by threads during a query run. Alternatively, each thread independently maintains a hash table with page count entries corresponding to table descriptors for each table in the database system. A thread increases or decreases the page count value when copying or removing pages from the cache. A page count value for each referenced table descriptor is determined from a sum of the values in the hash tables. A master thread performs bookkeeping and prevents hash table overflows.