Optimized collection of just-in-time statistics for database query optimization
View Patent ↗A system and method are provided for updating database statistics for use in generating query execution plans. A first query for a database is received, for example by a compiler, and the compiler accesses statistics associated with the first query for optimizing a query execution plan for the first query. The statistics may be accessed from a first cache. If the statistics retrieved from the first cache are determined to be stale, the system obtains updated statistics and may store them in a temporary cache. The stale statistics in the first cache are marked as invalid. Existing queries using the first cache may continue to use the first cache, while new queries may be provided with updated statistics.
1. A method for updating database statistics for use in generating query execution plans, comprising:
receiving a first query for a table in a database;
determining whether valid and updated statistics for compiling the first query are available from a first store comprising a first entry with statistics associated with the table in the database;
if valid and updated statistics for compiling the first query are not available from the first store, obtaining updated statistics from a further store comprising statistics associated with the table in the database; and
storing the obtained updated statistics associated with the table in the database in the first store as a second entry, thereby storing multiple entries for the statistics associated with the same table in the database,
wherein the first store is a metadata cache and the further store is a temporary cache.
2. The method of claim 1 , wherein obtaining updated statistics from the further store comprises:
determining whether sufficiently updated statistics for compiling the first query are available from a second store comprising statistics associated with the table in the database;
if sufficiently updated statistics for compiling the first query are not available from the second store,
collecting the updated statistics from the database,
storing the updated statistics in the second store, and
retrieving the updated statistics for compiling the first query from the second store.
3. The method of claim 2 , wherein retrieving the updated statistics for compiling the first query from the second store comprises loading the updated statistics from the second store into the first store, and retrieving the updated statistics from the first store.
4. The method of claim 2 , wherein the first store further comprises an indicator for indicating whether the statistics are valid.
5. The method of claim 4 , further comprising:
upon storing the updated statistics in the second store, altering the indicator associated with corresponding statistics in the first store to indicate that the corresponding statistics are invalid.
6. The method of claim 1 , wherein obtaining updated statistics from a further store comprises:
determining whether sufficiently updated statistics for compiling the first query are available from a second store comprising statistics associated with the table in the database;
if sufficiently updated statistics for compiling the first query are not available from the second store,
retrieving the updated statistics from a third store comprising statistics associated with the table in the database;
storing the updated statistics in the first store as the second entry; and
retrieving the updated statistics for compiling the first query from the second entry in the first store.
7. The method of claim 1 , wherein obtaining updated statistics from a further store comprises:
determining whether sufficiently updated statistics for compiling the first query are available from a second store or a third store comprising statistics associated with the table in the database;
if sufficiently updated statistics for compiling the first query are not available from the second store or the third store,
collecting the updated statistics from the database,
storing the updated statistics in the second store, and
retrieving the updated statistics for compiling the first query from the second store.
8. The method of claim 7 , wherein retrieving the updated statistics for compiling the first query from the second store comprises loading the updated statistics from the second store into the first store, and retrieving the updated statistics from the first store.
9. The method of claim 8 , wherein the first store further comprises an indicator for indicating whether the statistics are valid, the method further comprising:
upon storing the updated statistics in. the second store, altering the indicator associated with corresponding statistics in the first store to indicate that the corresponding statistics are invalid.
10. The method of claim 1 , wherein, only one of the multiple entries is valid, while all other of the multiple entries are invalid.
11. A system for updating database statistics for use in generating query execution plans, the system comprising:
a database;
a first store comprising statistics associated with a table in the database;
a second store comprising a first entry with statistics associated with the table in the database; and
a compiler in communication with the database and with at least the first store, the compiler being configured for receiving queries for the table in the database, accessing statistics for compiling each query from at least the first store, and generating at least one query execution plan for executing each query, wherein the compiler is further configured to:
determine whether valid and updated statistics for compiling a first query are available from the first store;
if valid and updated statistics for compiling the first query are not available from the first store, obtain updated statistics from the second store; and
store the obtained updated statistics associated with the table in the database in the first store as a second entry, thereby storing multiple entries for the statistics associated with the same table in the database,
wherein the first store is a metadata cache and the second store is a temporary cache.
12. The system of claim 11 , wherein the compiler is further configured to obtain updated statistics from the second store by causing the updated statistics to be loaded from the second store into the first store, and retrieving the updated statistics from the first store.
13. The system of claim 11 , further comprising a third store comprising statistics associated with the database, and wherein the compiler is further configured to obtain updated statistics from the second store by:
determining whether sufficiently updated statistics for compiling the first query are available from the second store or the third store;
if sufficiently updated statistics for compiling the first query are not available from the second store or the third store,
collecting the updated statistics from the database,
storing the updated statistics in the second store, and
retrieving the updated statistics for compiling the first query from the second store.
14. The system of claim 13 , wherein the compiler is further configured to retrieve the updated statistics for compiling the first query from the second store by loading the updated statistics from the second store into the first store, and retrieving the updated statistics from the first store.
15. The system of claim 11 , wherein, only one of the multiple entries is valid, while all other of the multiple entries are invalid.
16. A computer program product comprising a computer-readable medium, the computer-readable medium being encoded with a computer program for updating database statistics, wherein the computer program, when executed on a computer system, causes the computer system to:
receive a first query for a table in the database;
determine whether valid and updated statistics for compiling the first query are available from a first store comprising a first entry with statistics associated with the table in the database;
if valid and updated statistics for compiling the first query are not available from the first store, obtain updated statistics from a further store comprising statistics associated with the table in the database; and
store the obtained updated statistics associated with the table in the database in the first store as a second entry, thereby storing multiple entries for the statistics associated with the same table in the database,
wherein the first store is a metadata cache and the further store is a temporary cache.
17. The computer program product of claim 16 , wherein, only one of the multiple entries is valid, while all other of the multiple entries are invalid.
18. The computer program product of claim 16 , further causes the computer system to:
determine whether sufficiently updated statistics for compiling the first query are available from a second store comprising statistics associated with the table in the database;
if sufficiently updated statistics for compiling the first query are not available from the second store,
collect the updated statistics from the database,
store the updated statistics in the second store, and
retrieve the updated statistics for compiling the first query from the second store.
19. The computer program product of claim 18 , wherein the first store further comprises an indicator for indicating whether the statistics are valid.
20. The computer program product of claim 19 , further causes the computer system to:
upon storing the updated statistics in the second store, alter the indicator associated with corresponding statistics in the first store to indicate that the corresponding statistics are invalid.
21. The computer program product of claim 16 , further causes the computer system to:
determine whether sufficiently updated statistics for compiling the first query are available from a second store comprising statistics associated with the table in the database;
if sufficiently updated statistics for compiling the first query are not available from the second store,
retrieve the updated statistics from a third store comprising statistics associated with the table in the database;
store the updated statistics in the first store as the second entry; and
retrieve the updated statistics for compiling the first query from the second entry in the first store.
22. The computer program product of claim 16 , further comprises causing the computer system to:
determine whether sufficiently updated statistics for compiling the first query are available from a second store or a third store comprising statistics associated with the table in the database;
if sufficiently updated statistics for compiling the first query are not available from the second store or the third store,
collect the updated statistics from the database,
store the updated statistics in the second store, and
retrieve the updated statistics for compiling the first query from the second store.