Summary table management in a computer system

• US 6,249,791 B1
• Filed: 05/18/1999
• Issued: 06/19/2001
• Est. Priority Date: 10/31/1997
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: Osborn, Andrew P., Lavender, Robert L., Cave, Stephen D.
• Primary Examiner(s): Alam, Hosain T.
• Assistant Examiner(s): Shah, Sanjiv
• Application Number:
  US09/314,457
• Time in Patent Office: 763 Days
• Field of Search: 707/200, 707/3, 707/2, 707/4, 707/5, 707/102, 707/101, 707/104, 707/503, 707/509, 707/1, 707/7, 707/201, 707/204, 707/103
• US Class Current: 1/1
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching

Abstract

A self monitoring system that identifies optimum summary table(s) for use by a database management system, and that automatically creates or deletes summary tables based on selected summary table recommendations.

Claims

1. A method for managing a database, comprising:

generating a plurality of statistics for each of a plurality of queries submitted to a database system;

storing one or more generated statistics for each of the plurality of queries;

generating a summary table creation recommendation based on one or more of the generated statistics for a query submitted to the database system; and

creating a summary table based on the summary table creation recommendation.

2. The method of claim 1, in which generating a plurality of statistics for each of a plurality of queries comprises determining the CPU execution time for each of the plurality of queries.

3. The method of claim 2, in which generating a plurality of statistics for each of a plurality of queries further comprises measuring the frequency with which a query of the plurality of queries is executed by the database system.

4. The method of claim 3, in which generating a summary table creation recommendation comprises generating the summary table creation recommendation from the frequency with which a query of the plurality of queries is executed by the database system and from an average of the CPU execution times for the query.

5. The method of claim 1, further comprising:

generating a plurality of summary table creation recommendations, each based on one or more of the generated statistics for a query submitted to the database system;

ranking the plurality of summary table creation recommendations;

providing one or more of the plurality of summary table creation recommendations to a database administrator; and

creating a summary table based on the database administrator choosing a summary table creation recommendation.

6. The method of claim 5, in which ranking the plurality of summary table creation recommendations comprises ranking each of the plurality of summary table creation recommendations into one of four groups, the first group comprising the summary table creation recommendations that are responsive to those queries of the plurality of queries submitted to the database system that were executed by the database system relatively more frequently and that took a relatively longer amount of CPU time to execute, the second group comprising the summary table creation recommendations that are responsive to those queries of the plurality of queries submitted to the database system that were executed by the database system relatively more frequently and that took a relatively shorter amount of CPU time to execute, the third group comprising the summary table creation recommendations that are responsive to those queries of the plurality of queries submitted to the database system that were executed by the database system relatively less frequently and that took a relatively longer amount of CPU time to execute, and the fourth group comprising the summary table creation recommendations that are responsive to those queries of the plurality of queries submitted to the database system that were executed by the database system relatively less frequently and that took a relatively shorter amount of CPU time to execute.

7. The method of claim 5, in which providing one or more of the plurality of summary table creation recommendations to a database administrator comprises supplying to the database administrator a query that a summary table would be responsive to for each of the one or more plurality of summary table creation recommendations.

8. The method of claim 1, in which storing one or more generated statistics for each of the plurality of queries comprises storing one or more generated statistics for each query submitted by a user to the database system in a record in a user table on the user'"'"'s processing device and thereafter storing each record in the user table in a system table maintained by the database system.

9. The method of claim 8, in which a representation of an executable SQL query for a query submitted to the database system is stored in one or more respective records of the system table, and a generated statistic for the query submitted to the database system is the number of records in the system table that comprise the representation of the executable SQL query.

10. A method for managing summary tables in a database, comprising:

determining the frequency that a user query was processed by a database system;

generating a summary table creation recommendation based on the frequency that the user query was processed by the database system; and

generating a summary table deletion recommendation based on the frequency that a summary table is accessed by user queries processed by the database system.

11. The method of claim 10, further comprising:

presenting the summary table creation recommendation to a database administrator;

creating a summary table based on the summary table creation recommendation if the summary table creation is requested by the database administrator;

presenting the summary table deletion recommendation to the database administrator; and

deleting a summary table based on the summary table deletion recommendation if the summary table deletion is requested by the database administrator.

12. The method of claim 10, further comprising:

determining the frequency that each of a plurality of user queries was processed by a database system;

generating a plurality of summary table creation recommendations, each summary table creation recommendation based on the frequency that a user query of the plurality of user queries was processed by the database system;

ranking the plurality of summary table creation recommendations based on the frequencies that the respective user queries were processed by the database system;

generating a plurality of summary table deletion recommendations each based on the frequency that a summary table is accessed by user queries processed by the database system; and

ranking the plurality of summary table deletion recommendations based on the frequency that the respective summary table is accessed by user queries processed by the database system.

13. The method of claim 10, in which generating a summary table creation recommendation is further based on an average CPU execution time for responding to the user query.

14. The method of claim 10, in which generating a summary table creation recommendation is based on the frequency that the user query was processed by the database system within a specified timeframe, and in which generating a summary table deletion recommendation is based on the frequency that a summary table is accessed by user queries processed by the database system within the specified timeframe.

15. The method of claim 10, further comprising:

automatically creating a summary table based on the generated summary table creation recommendation; and

automatically deleting a summary table based on the summary table deletion recommendation.

16. A machine readable medium having stored thereon a program for causing a computer to:

gather a plurality of query statistics for each of a plurality of queries processed by a database system;

rank a plurality of queries processed by the database system based on one or more gathered query statistics for each of the plurality of queries ranked;

generate a summary table creation recommendation based on a ranked query; and

create a summary table based on the generated summary table creation recommendation.

17. The machine readable medium of claim 16, in which a query statistic of the plurality of query statistics for a query processed by a database system comprises the number of times the query is processed by the database system within a timeframe.

18. The machine readable medium of claim 17, in which a query statistic of the plurality of query statistics for a query processed by a database system comprises the CPU execution time for processing the query.

19. The machine readable medium of claim 18, in which the rank of a query processed by the database system is based on the number of times the query is processed by the database system within the timeframe and the average CPU execution time for processing the query within the timeframe.

20. The machine readable medium of claim 16, in which the program further causes the computer to

generate a plurality of summary table deletion recommendations each based on the frequency that a summary table is accessed by queries processed by a database system within a timeframe, and

rank the plurality of summary table deletion recommendations.