Granted Patent B1

US 7,974,973 · App. 12/188,179 · Granted Jul 5, 2011

Abstract

Claims (40)

1. A method for displaying information related to performance of a database during a time interval, the method comprising:

a) during the time interval, effecting a plurality of index I/O operation or table I/O operation detectings, each index I/O operation or table I/O operation detecting being associated with a respective quantity of wait-time;

b) for each index I/O or table I/O operation detecting of the plurality of index I/O or table I/O operation detectings:

i) determining an identity of a respective target index or table for the detected index I/O or table I/O;

ii) determining an identity and an execution plan of a respective invoking database statement;

iii) determining a respective database index-execution plan relationship or database table-execution plan relationship between the respective target index and the execution plan of the respective database statement; and

iv) categorizing, in accordance with the index-execution plan or table-execution plan relationship, the detected table or index I/O operation into an I/O category selected from a plurality of I/O categories;

c) in accordance with:

i) the wait-time quantities; and

ii) the results of the categorization,

determining, for a particular category of the plurality of I/O categories, for a given index or table of the database and for the time interval, a fractional aggregate index-I/O or table I/O wait time for the particular category, the fractional aggregate index-I/O or table I/O being only a fraction of a total index IO wait time or table I/O wait time for the given index or table for the time interval; and

d) in accordance with the determine fractional aggregate index-read wait time for at least one of the categories, presenting, to a user, a description of performance of the database during the time period.

2. The method of claim 1 wherein the I/O categories are selected from the group consisting of full index scan, index skip scan, index range scan, table I/O after index I/O and full table scan.

3. The method of claim 1 wherein the execution plans are estimated execution plans.

4. The method of claim 1 wherein the execution plans are real execution plans, the method further comprises:

e) monitoring the database during the time intervals to detect one or more real execution plans associated with the invoking database statement.

5. The method of claim 1 wherein:

i) the method further comprises, for a particular category:

e) determining a ratio between (A) the fractional aggregate index or fractional aggregate table I/O wait time for the given index or table and a given category and a (B) total wait time for the given index or table; and

ii) the description of the performance of the database during the time period is presented in accordance with the determined ratio.

6. The method of claim 5 wherein the describing of the performance of the database further includes:

i) displaying a description of the particular category; and

ii) associating the description of the particular category with the description of the determined ratio.

7. The method of claim 5 wherein:

i) the method is carried out to determine, for each I/O category of the plurality of I/O categories, a respective aggregate I/O time; and

ii) the presenting includes presenting, for each I/O category of the plurality of I/O categories, a respective aggregate I/O wait time for said each I/O category;

iii) the describing includes presenting descriptions of the I/O categories that are associated with respective ration values.

8. A system for displaying information related to performance of a database during a time interval, the method comprising:

A) a processor operative to:

a) during the time interval, effecting a plurality of index I/O operation or table I/O operation detectings, each index I/O operation or table I/O operation detecting being associated with a respective quantity of wait-time;

b) for each index I/O or table I/O operation detecting of the plurality of index I/O or table I/O operation detectings:

i) determining an identity of a respective target index or table for the detected index I/O or table I/O;

ii) determining an identity and an execution plan of a respective invoking database statement;

iii) determining a respective database index-execution plan relationship or database table-execution plan relationship between the respective target index and the execution plan of the respective database statement; and

iv) categorizing, in accordance with the index-execution plan or table-execution plan relationship, the detected table or index I/O operation into an I/O category selected from a plurality of I/O categories;

c) in accordance with:

i) the wait-time quantities; and

ii) the results of the categorization,

determining, for a particular category of the plurality of I/O categories, for a given index or table of the database and for the time interval, a fractional aggregate index-I/O or table I/O wait time for the particular category, the fractional aggregate index-I/O or table I/O being only a fraction of a total index IO wait time or table I/O wait time for the given index or table for the time interval; and

B) a computer screen, in conjunction with the processor, operative, in accordance with the determine fractional aggregate index-read wait time for at least one of the categories, to present, to a user, a description of performance of the database during the time period.

Assignments (10)