Summary table management in a computer system

• US 6,023,695 A
• Filed: 10/31/1997
• Issued: 02/08/2000
• 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): Lintz, Paul R.
• Assistant Examiner(s): Shah, Sanjiv
• Application Number:
  US08/962,029
• Time in Patent Office: 830 Days
• Field of Search: 707/3, 707/2, 707/4, 707/5, 707/102
• 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 computer implemented method for managing a database, comprising:

collecting statistics on past queries submitted to a database management system;

generating summary table creation recommendations based on the statistics, in which the generation of summary table creation recommendation includes an evaluation of both the frequency and execution times of the past submitted queries, and in which generating summary table creation recommendations comprises ranking a plurality of past queries submitted over a time period, the ranking of a past query of the plurality of past queries based on the expression;

space="preserve" listing-type="equation">log(f).sup.2 (cpu+1)

where f is the frequency with which the past query was submitted during the time period, and cpu is the average CPU execution time for the past query during the time period; and

presenting the summary table creation recommendations to a user, or, automatically generating at least one of the summary tables in the summary table creation recommendations.

2. The method of claim 1, wherein the automatic generation of summary tables is based upon pre-selected ranking criteria.

3. The method of claim 1 further including automatically generating summary table deletion recommendations.

4. A self monitoring system that automatically tunes itself according to system demands, said system demands including system loads and patterns of use,

said tuning comprising the collection of statistics about system usage to assist in the selection of summary tables to create;

and

creation and population of summary tables resulting in the quick performance of user queries while minimizing system resources required to accomplish this,

in which the lion of summary tables to create includes an evaluation of both the frequency and execution times of past queries submitted to the system, and

in which the selection of summary tables to create comprises ranking a plurality of past queries submitted to the system over a time period, based on the expression;

space="preserve" listing-type="equation">log(f).sup.2 (cpu+1)

where f is the frequency with which a past query was submitted during the time period, and cpu is the average CPU execution time for the past query during the time period.

5. The self monitoring system of claim 4, in which the collection of statistics are generated from queries submitted to the system and are stored in a local stat table;

and, said statistics from the local stat table are used to create a system stat table.

6. A computer system comprising;

a computer; and

a data storage device, the data storage device having a program residing thereon for causing the computer to

collect statistics on past submitted database queries,

generate summary table creation recommendations based on the collected statistics,

in which the generation of summary table creation recommendations includes an evaluation of both the frequency and execution times of the past submitted database queries, and

in which generating summary table creation recommendations comprises ranking a plurality of past submitted database queries submitted over a given time period, the ranking of a past query of the plurality of past submitted database queries based on the expression;

space="preserve" listing-type="equation">log(f).sup.2 (cpu+1)

where f is the frequency with which the past query was submitted during the time period, and cpu is the average CPU execution time for the past query during the time period, and

present the summary table creation recommendations to a user, or, automatically generate at least one of the summary tables in the summary table creation recommendations.

7. The computer system of claim 6, in which the program causes the computer to automatically generate a summary table based upon pre-selected ranking criteria.

8. The computer system of claim 6, wherein the program further causes the computer to automatically generate summary table deletion recommendations.

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

collect statistics on past submitted queries;

generate summary table creation recommendations based on the collected statistics,

in which the generation of summary table creation recommendations includes an evaluation of both the frequency and execution times of the past submitted queries, and

in which generating summary table creation recommendations comprises ranking a plurality of past queries submitted over a given time period, based on the expression;

space="preserve" listing-type="equation">log(f).sup.2 (cpu+1)

where f is the frequency with which a past query was submitted during the time period, and cpu is the average CPU execution time for the past query during the time period, and

present the summary table creation recommendations to a user, or, automatically generate at least one of the summary tables in the summary table creation recommendations.