Granted Patent B1

US 7,970,776 · App. 12/187,446 · Granted Jun 28, 2011

Abstract

Claims (65)

1. A method for displaying information related to performance of a database during a time interval where a given database statement is involved in multiple disk-sort operations, the method comprising:

a) determining an estimated aggregate disk-sort wait time for the given database statement for the time interval when:

i) the given database statement is involved in multiple distinct disk-sort operations which contribute to the aggregate disk-sort wait time; and

ii) the aggregate disk-sort wait time for the given database statement is only a fraction of a total disk-sort wait time for the database during the time interval; and

b) in accordance with the estimated aggregate disk-sort wait time for the given database statement, presenting, to a user, a description of performance of the database during the time interval.

2. The method of claim 1 wherein the estimated aggregate disk-sort wait time for the given database statement is an aggregated wait time for a proper sub-set of all database disk-sort operations carried out by execution of the given database statement during the time interval.

3. The method of claim 2 wherein:

i) the given database statement is invoked in a plurality of distinct associated database sessions during the time interval; and

ii) the proper sub-set is determined in accordance with database session identifier.

4. The method of claim 1 wherein the determining of the estimated aggregate disk-sort wait time for the given database statement for the time period includes:

i) for each distinct sample time of a plurality of sample times during the time interval:

A) respectively detecting a disk-sort operation;

B) effecting a respective one or more correlation operations to correlate respective results of the detecting with an identifier of the given database statement; and

ii) determining the aggregate disk-sort wait time for the given database statement in accordance with results of the correlation operations for the plurality of sample times.

5. The method of claim 4 wherein:

i) the respective effecting of one or more correlation operations further includes correlating the respective results of the detecting with an identifier or an invoking database session which invoked the given database statement; and

ii) the determining of the aggregate disk-sort wait time is carried out on a per-session basis in accordance with results of the correlating with the database session identifier.

6. The method of claim 1 wherein the presented performance description describes the aggregate disk-sort wait time for the given database statement during the time interval.

7. The method of claim 1 wherein the presented performance description describes a ratio between:

i) the aggregate disk-sort wait time for the given database statement during the time interval; and

ii) a total wait time for the given database statement during the time interval.

8. The method of claim 1 wherein:

i) step (a) is carried out for a plurality of distinct database statements; and

ii) the database performance description presenting include displaying a list of at least some of the database statements of the plurality of database statements in accordance with the respective estimated aggregate disk-sort wait times for each database statement of the plurality of database statements.

9. The method of claim 8 wherein the displayed list of database statements is sorted in accordance with relative values of the estimated aggregate disk-sort wait times.

10. The method of claim 8 wherein the database statement list displaying includes:

i) selecting a sub-plurality of database statements from the plurality of distinct database statements in accordance with the relative estimated aggregate disk-sort wait times;

ii) presenting only the selected sub-plurality of database statements.

11. The method of claim 1 wherein the performance-data presenting includes presenting a description of a relation between the estimated aggregate disk-sort wait time for the given database statement and at least one of:

i) an aggregate lock wait-time during the time interval for the given database statement;

ii) a total CPU wait time during the time interval for the given database statement;

iii) a total communication wait time during the time interval for the given database statement; and

iv) a total I/O wait time during the time interval for the given database statement.

12. A method for displaying information related to performance of a database during a time interval where a given database index or table is involved in multiple disk-sort operations, the method comprising:

a) determining an estimated aggregate disk-sort wait time for the given database index or table for the time interval when:

i) the given database table or index is involved in multiple disk-sort operations which contribute to the aggregate disk-sort time; and

ii) the aggregate disk-sort wait time for the given database table or index is only a fraction of a total disk-sort wait time for the database during for the time interval; and

b) in accordance with the estimated aggregate disk-sort wait time for the given database table or index, presenting, to a user, data describing performance of the database during the time interval.

13. The method of claim 12 wherein the determining of the estimated aggregate disk-sort wait time for the given database statement for the time period includes:

i) for each distinct sample time of a plurality of sample times during the time interval:

A) respectively detecting a disk-sort operation;

B) determining an identifier of a database statement involved in the disk-sort operation;

C) correlating the disk-sort operation with the involved database and with a table or index accessed by the database statement;

ii) determining the aggregate disk-sort wait time for the given database table or index in accordance with results of the correlation operations for the plurality of sample times.

14. A system for displaying information related to performance of a database during a time interval where a given database statement is involved in multiple disk-sort operations, the system comprising:

a) a processor operative to determine an estimated aggregate disk-sort wait time for the given database statement for the time interval when:

i) the given database statement is involved in multiple disk-sort operations which contribute to the aggregate disk-sort wait time; and

ii) the aggregate disk-sort wait time for the given database statement is only a fraction of a total disk-sort wait time for the database during for the time interval; and

b) a computer screen, in conjunction with the processor, operative to present, to a user, a description of performance of the database during the time interval in accordance with the estimated aggregate disk-sort wait time for the given database statement.

15. The system of claim 14 wherein the processor is operative such that the estimated aggregate disk-sort wait time for the given database statement is an aggregated wait time for a proper sub-set of all database disk-sort operations carried out by execution of the given database statement during the time interval.

16. The system of claim 15 wherein:

i) the given database statement is invoked in a plurality of distinct associated sessions during the time interval; and

ii) the processor is operative such that the proper sub-set is determined in accordance with session identifier.

17. The system of claim 14 wherein the processor is operative such that the determining of the estimated aggregate disk-sort wait time for the given database statement for the time period includes:

i) for each distinct sample time of a plurality of sample times during the time interval:

A) respectively detecting a disk-sort operation;

B) effecting a respective one or more correlation operations to correlate respective results of the detecting with an identifier of the given database statement; and

ii) determining the aggregate disk-sort wait time for the given database statement in accordance with results of the correlation operations for the plurality of sample times.

18. The system of claim 17 wherein:

i) the respective effecting of one or more correlation operations further includes correlating the respective results of the detecting with an identifier or an invoking database session which invoked the given database statement; and

ii) the determining of the aggregate disk-sort wait time is carried out on a per-session basis in accordance with results of the correlating with the database session identifier.

19. The system of claim 14 wherein the processor is operative such that the presented performance description describes the aggregate disk-sort wait time for the given database statement during the time interval.

20. The system of claim 14 wherein the processor is operative such that the presented performance description describes a ratio between:

i) the aggregate disk-sort wait time for the given database statement during the time interval; and

ii) a total wait time for the given database statement during the time interval.

Assignments (10)