Shared scans utilizing query monitor during query execution to improve buffer cache utilization across multi-stream query environments
View Patent ↗A system for data base management using memory devices holding a database containing tables of data occupying extents. A database engine reviews each query to determine an extents list of extents containing the data needed by that said query. A query monitor then re-orders the extents lists so that extents are retrieved and stored in a buffer cache for more efficient use when executing the queries. The database engine retrieves needed extents from the memory devices, as ordered in the extents lists, and stores those extents in the buffer cache. The database engine then executes one or more queries on the data in the extents in the buffer cache to determine respective results.
1. A method for data base management, comprising the steps of:
(a) receiving a plurality of queries with respect to data in at least one of a plurality of tables in a database, wherein each table occupies at least one extent;
(b) reviewing each said query and determining a respective extents list of said extents containing said data needed by that said query;
(c) re-ordering said extents lists based on an order calculated to be more efficient for execution of said queries;
(d) loading said extents from said database as ordered in said extents lists into a buffer cache; and
(e) executing said queries on said data in respective said extents in said buffer cache to determine respective results.
2. The method of claim 1 , wherein:
said step (c) includes monitoring which said extents are presently in said buffer cache and re-ordering said extents lists so that at least some said extents already in said buffer cache are used more efficiently by said plurality of queries.
3. The method of claim 2 , wherein:
said step (c) includes monitoring which said extents in said buffer cache some said queries are currently being executed against and re-ordering said extents lists so that at least some said extents already in said buffer cache are used more efficiently by other said queries.
4. The method of claim 1 , wherein:
said step (c) includes re-ordering said extents lists so that some said queries are executed at least partially concurrently in said step (e) using at least one same said extent in said buffer cache.
5. The method of claim 1 , wherein:
said step (c) includes re-ordering said extents lists so that some said queries are executed contiguously in said step (e) using at least one same said extent in said buffer cache.
6. The method of claim 1 , wherein:
said step (a) includes queuing said plurality of queries into a query list; and
said step (c) includes re-ordering said query list so that at least some said extents in said buffer cache are used more efficiently by said queries.
7. A system for data base management, comprising:
means for receiving a plurality of queries with respect to data in at least one of a plurality of tables in a database, wherein each table occupies at least one extent;
means for reviewing each said query and determining a respective extents list of said extents containing said data needed by that said query;
means for re-ordering said extents lists based on an order calculated to be more efficient for execution of said queries;
means for loading said extents from said database as ordered in said extents lists into a buffer cache; and
means for executing said queries on said data in respective said extents in said buffer cache to determine respective results.
8. The system of claim 7 , wherein:
said means for re-ordering includes means for monitoring which said extents are presently in said buffer cache and said means for re-ordering re-orders said extents lists so that at least some said extents already in said buffer cache are used more efficiently by said plurality of queries.
9. The system of claim 7 , wherein:
said means for re-ordering includes means for monitoring which said extents in said buffer cache some said queries are currently being executed against and said means for re-ordering re-orders said extents lists so that at least some said extents already in said buffer cache are used more efficiently by other said queries.
10. The system of claim 7 , wherein:
said means for re-ordering re-orders said extents lists so that some said queries are executed at least partially concurrently by said means for executing using at least one same said extent in said buffer cache.
11. The system of claim 7 , wherein:
said means for re-ordering re-orders said extents lists so that some said queries are executed contiguously by said means for executing using at least one same said extent stored in said buffer cache.
12. The system of claim 7 , wherein:
said means for receiving queues said plurality of queries into a query list; and
said means for re-ordering re-orders said query list so that at least some said extents that are retrieved and stored in said buffer cache are used more efficiently by said queries.
13. A computer program, embodied on a computer readable storage medium, the computer program for executing in a computer having a database and comprising;
a code segment that receives a plurality of queries with respect to data in at least one of a plurality of tables in a database, wherein each table occupies at least one extent;
a code segment that reviews each said query and determines a respective extents list of said extents containing said data needed by that said query;
a code segment that re-orders said extents lists based on an order calculated to be more efficient for execution of said queries;
a code segment that loads said extents from said database as ordered in said extents lists into a buffer cache; and
a code segment that executes said queries on said data in respective said extents in said buffer cache to determine respective results.