Database execution cost and system performance estimator

• US 6,003,022 A
• Filed: 08/22/1997
• Issued: 12/14/1999
• Est. Priority Date: 06/24/1994
• Status: Expired due to Term

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Hall, Harold, Lakshmi, Seetha, Eberhard, Rainer
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): Choules, Jack M.
• Application Number:
  US08/916,560
• Time in Patent Office: 844 Days
• Field of Search: 364/578, 707/2, 707/200
• US Class Current: 1/1
• CPC Class Codes: G06F 11/34 Recording or statistical evaluation of computer activity, e.g. of down time, of input/output operation ; Recording or statistical evaluation of user activity, e.g. usability assessment
• CPC Class Codes: G06F 11/3452 Performance evaluation by statistical analysis
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: G06F 2201/87 Monitoring of transactions
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing

Abstract

The software tool of this invention estimates the costs of an application program accessing a database. These costs may be execution costs of the application or of a transaction, SQL statement, and/or a utility. Execution costs include CPU time, I/O time and minimum elapsed time. For estimating the execution costs, the tool receives simplified and partial definitions of tables, utilities, SQL statements, transactions, and applications. The estimator tool requires only a minimal amount of information to calculate the various execution costs.

Claims

1. A tool, for use in a computer system, for estimating execution costs of at least a portion of an application having at least one query statement for accessing a database;

said tool comprising;

a user interface for receiving input from a user corresponding to a partial definition of at least one table in said database;

means for building, from user input, a simplified query statement, said simplified query statement having a place-holder for each expression detail within said at least one query statement, using at least one of said at least one table; and

means, based on said simplified query statement, for estimating a cost of executing said at least one query statement against said database.

2. The tool of claim 1 wherein the simplified query statement comprises only those portions of the query statement that are essential in determining the execution costs of the application accessing the database with said query statement and wherein the tool provides for a possibility of user input for an estimate of a number of rows resulting in an execution of the query statement, an estimate of a number of pages touched and an estimate of a number of pages read.

3. The tool of claim 1 wherein the cost of executing includes CPU time, I/O time, and minimum elapsed time.

4. The computer program on the computer usable medium of claim 1 wherein the simplified query statement comprises only those portions of the query statement that are essential in determining the execution costs of the application accessing the database with said query statement and wherein the computer program provides for a possibility of user input for an estimate of a number of rows resulting in an execution of the query statement, an estimate of a number of pages touched and an estimate of a number of pages read.

5. The computer program on the computer readable medium of claim 1 wherein the cost of executing includes CPU time, I/O time, and minimum elapsed time.

6. A tool, for use in a computer system, for estimating execution costs of a transaction having at least one query statement for accessing a database;

said tool comprising;

a user interface for generating, from user input, a simplified transaction definition including at least one predefined simplified query statement definition for said at least one query statement, said simplified query statement definition having a place-holder for each expression detail within said at least one query statement, and a frequency of execution of said query statement in said transaction;

means for including in the transaction definition information necessary to define transaction overhead costs; and

means for determining an estimation of a cost of said at least one transaction based on said costs of said at least one simplified query statement within said transaction, said frequency of execution, and said overhead costs.

7. A tool, for use in a computer system, for estimating system performance of at least one application having at least one transaction for accessing a database;

said tool comprising;

a user interface for generating, from user input, a simplified application definition including at least one predefined simplified transaction definition having at least one simplified query statement for each of said at least one transaction and a frequency of execution of said simplified transaction in said computer system;

means for including in the application definition information necessary to define a system load from external sources; and

means for computing the system performance of said at least one application based on said costs of said at least one simplified transaction definition having at least one simplified query statement definition, said frequency of execution of said transaction, and said system load from external sources.

8. The tool of claim 7, wherein the system performance includes CPU utilization, I/O device utilization, and elapsed time including service and queuing time for CPU and I/O devices.

9. A tool, for use in a computer system, for estimating execution costs of an application having at least one query statement for accessing a database;

said tool comprising;

a user interface for receiving input from a user corresponding to a simplified definition of at least one table in said database and a simplified definition of at least one utility command;

means for building, from user input, at least one simplified query statement, said simplified query statement having a place-holder instead of expression details within said at least one query statement, using at least one of said at least one tables;

means, through said user interface, for generating a simplified transaction definition including at least one predefined simplified query statement definition and a frequency of execution of said simplified query statement in a transaction;

means for including in the transaction definition information necessary to define transaction overhead costs;

means for determining an estimation of a cost of said at least one utility based on said simplified definitions of said at least one table and said utility command;

means, based on said at least one simplified query statement, for estimating a cost of executing said at least one query statement against said database;

means for determining an estimation of a cost of said at least one transaction based on costs of said simplified query statements within said transaction, said frequency of execution, and said overhead costs; and

means for determining an estimation of the execution costs of an application based upon said means for determining the estimation of the cost of the at least one utility and said means for estimating the cost of executing said at least one query statement and means for determining the estimation of the cost of said at least one transaction.

10. A computer system for estimating execution costs of at least a portion of an application having at least one query statement for accessing a database;

said system comprising;

a user front end for receiving input from a user corresponding to a partial definition of at least one table in said database;

means for building, from user input, a simplified query statement, said simplified query statement having a place-holder for each expression detail within said at least one query statement, using at least one of said at least one table; and

means, based on said simplified query statement, for estimating a cost of executing said at least one query statement against said database.

11. A computer system for estimating execution costs of a transaction having at least one query statement for accessing a database;

said system comprising;

a user interface for generating, from user input, a simplified transaction definition including at least one predefined simplified query statement definition for said at least one query statement, said simplified query statement definition having a place-holder for each expression detail within said at least one query statement, and a frequency of execution of said query statement in said transaction;

means for including in the transaction definition information necessary to define transaction overhead costs; and

means for determining an estimation of a cost of said at least one transaction based on costs of said at least one simplified query statement within said transaction, said frequency of execution, and said overhead costs.

12. A computer system, for estimating system performance of at least one application having at least one transaction for accessing a database;

said system comprising;

a user interface for generating, from user input, a simplified application definition including at least one predefined simplified transaction definition having at least one simplified query statement for each of said at least one transaction and a frequency of execution of said transaction in said computer system;

means for including in the application definition information necessary to define a system load from external sources; and

means for determining an estimation of system performance of said at least one application based on costs of said at least one simplified transaction definition having at least one simplified query statement, said frequency of execution of said transaction, and said system load from external sources.

13. A method, for use in a computer system, for estimating execution costs of at least a portion of an application having at least one query statement for accessing a database;

said method comprising;

receiving input from a user corresponding to a partial definition of at least one table in said database;

building, from user input, a simplified query statement, said simplified query statement having a place-holder for each expression detail within said at least one query statement, using at least one of said at least one table;

estimating, based on said simplified query statement, a cost of executing said at least one query statement against said database.

14. A method, for use in a computer system, for estimating execution costs of a transaction having at least one query statement for accessing a database;

said method comprising;

generating, from user input, a simplified transaction definition, through a user interface, including at least one predefined simplified query statement definition for said at least one query statement, said simplified query statement definition having a place-holder for each expression detail within said at least one query statement, and a frequency of execution of said query statement in said transaction;

including in the transaction definition, information necessary to define transaction overhead costs; and

determining an estimation of a cost of said at least one transaction based on execution costs of said at least one simplified query statement within said transaction, said frequency of execution, and said overhead costs.

15. A method, for use in a computer system, for estimating system performance of at least one application having at least one query statement for accessing a database;

said method comprising;

generating a simplified application definition, from user input through a user interface, including at least one predefined simplified transaction definition having at least one simplified query statement and a frequency of execution of said simplified transaction in said computer system;

including, in the application definition, information necessary to define a system load from external sources; and

determining the system performance of said at least one application based on execution costs of said at least one simplified transaction definition having at least one simplified query statement, said frequency of execution of said transaction, and said system load from external sources.

16. A method, for use in a computer system, for estimating execution costs of an application having at least one query statement for accessing a database;

said method comprising;

receiving, through a user interface, input from a user corresponding to a simplified definition of at least one table in said database and a simplified definition of at least one utility command;

building, from user input, at least one simplified query statement, said simplified query statement having a place-holder for each expression detail within said at least one query statement, using at least one of said at least one table;

generating, through said user interface, a simplified transaction definition including at least one predefined simplified query statement definition and a frequency of execution of said simplified query statement in a transaction;

including, in the transaction definition, information necessary to define transaction overhead costs;

determining an estimation of a cost of said at least one utility based on said simplified definitions of said at least one table and said utility command;

estimating, based on said at least one simplified query statement, a cost of executing said at least one query statement against said database;

determining an estimation of a cost of said at least one transaction based on said costs of said simplified query statements within said transaction, said frequency of execution, and said overhead costs; and

determining an estimation of the execution costs of an application based upon said step for determining the estimation of the cost of the at least one utility and said step for estimating the cost of executing said at least one query statement and said step for determining the estimation of the cost of said at least one transaction.

17. A computer program, on a computer usable medium, for estimating execution costs of at least a portion of an application having at least one query statement for accessing a database, comprising:

first program code for providing a user interface for receiving input from a user corresponding to a partial definition of at least one table in said database;

second program code for causing a building, from user input, of a simplified query statement, said simplified query statement having a place-holder for each expression detail within said at least one query statement, using at least one of said at least one table; and

means, based on said simplified query statement, third program code for causing an estimation of a cost of executing said at least one query statement against said database.

18. A computer program, on a computer usable medium, for estimating execution costs of a transaction having at least one query statement for accessing a database, comprising:

first program code for providing a user interface for generating a simplified transaction definition including at least one predefined simplified query statement definition for said at least one query statement, said simplified query statement definition having a place-holder for each expression detail within said at least one query statement, and a frequency of execution of said query statement in said transaction;

second program code for including in the transaction definition information necessary to define transaction overhead costs; and

third program code for determining an estimation of a cost of said at least one transaction based on said costs of said at least one simplified query statement within said transaction, said frequency of execution, and said overhead costs.

19. A computer program on a computer usable medium, for estimating system performance of at least one application having at least one transaction for accessing a database, comprising:

first program code for providing a user interface for generating, from user input, a simplified application definition including at least one predefined simplified transaction definition having at least one simplified query statement for each of said at least one transaction and a frequency of execution of said simplified transaction in said computer system;

second program code for including in the application definition information necessary to define a system load from external sources; and

third program code for computing the system performance of said at least one application based on said costs of said at least one simplified transaction definition having at least one simplified query statement definition, said frequency of execution of said transaction, and said system load from external sources.

20. The computer program on a computer usable medium of claim 19, wherein the system performance includes CPU utilization, I/O device utilization, and elapsed time including service and queuing time for CPU and I/O devices.

21. A computer program on a computer usable medium, for estimating execution costs of an application having at least one query statement for accessing a database, comprising:

first program code for providing a user interface for receiving input from a user corresponding to a simplified definition of at least one table in said database and a simplified definition of at least one utility command;

second program code for building, from user input, at least one simplified query statement, said simplified query statement having a place-holder instead of expression details within said at least one query statement, using at least one of said at least one table;

third program code, through said user interface, for generating a simplified transaction definition including at least one predefined simplified query statement definition and a frequency of execution of said simplified query statement in a transaction;

fourth program code for including in the transaction definition information necessary to define transaction overhead costs;

fifth program code for determining an estimation of a cost of said at least one utility based on said simplified definitions of said at least one table and said utility command;

sixth program code, based on said at least one simplified query statement, for estimating a cost of executing said at least one query statement against said database;

seventh program code for determining an estimation of a cost of said at least one transaction based on costs of said simplified query statements within said transaction, said frequency of execution, and said overhead costs; and

eighth program code for determining an estimation of the execution costs of an application based upon said means for determining the estimation of the cost of the at least one utility and said means for estimating the cost of executing said at least one query statement and means for determining the estimation of the cost of said at least one transaction.

22. A computer program, on a computer usable medium, for estimating execution costs of at least a portion of an application having at least one query statement for accessing a database, comprising:

means for providing a user interface for receiving input from a user for defining columns of a table by data type and quantity and for defining indexes by specifying a defined column as an index;

means for generating a partial table definition from the defined columns and defined indexes and from a user specifiable DASD type;

means for providing a user interface for receiving input from a user specifying a cpu and DASD type;

means for providing a user interface for receiving input from a user specifying a partial definition of a SQL statement, the specification including an identifier for the partial definition of the SQL statement, and a type of the SQL statement;

means for generating a simplified SQL statement from user input received through an interface providing selectable options, based upon the type of the SQL statement specified, the interface including i) a list of selectable statement clause types, selectable defined columns, selectable operators, and selectable values where a selectable value is selected as being a host variable, a constant, or an expression, if the specified type is SELECT, ii) a list of types of insert including columns or sub-select clauses, if the specified type is INSERT, iii) a list of selectable defined columns and selectable values where a selectable value is selected as being null, a host variable, a constant, or an expression, if the specified type is UPDATE or DELETE; and

means, based on said generated simplified query statement, for causing an estimation of a cost of executing said at least one query statement against said database.

23. The program of claim 22 further comprising means for adding, deleting and modifying entries in the partial table definition.

24. The program of claim 22 wherein the generated table definition comprises scrollable column names with a corresponding data type for each column name and a list of index names and a list of index columns.

25. The program of claim 22 further comprising means for displaying table and index space requirements.

26. The program of claim 22 wherein a cpu and DASD type is specified from a list of default cpu and DASD types.

27. The program of claim 22 wherein a cpu or DASD type is specified by configuring a new cpu or DASD type.

28. The program of claim 22 wherein the simplified query statement comprises only those portions of the query statement that are essential in determining the execution costs of the application accessing the database with said query statement.