Method, system, and program for accessing performance data

Details Method, system, and program for accessing performance data Method, system, and program for accessing performance data

1999-09-10

2004-11-09

Vu, Kim (Department: 2172)

Data processing: database and file management or data structures

Database design

Data structure types

C707S793000, C707S793000, C707S793000, C707S793000

Reexamination Certificate

active

06816874

ABSTRACT:

BACKGROUND OF THE INVENTION
1. Field of the Invention
The present invention relates to a method, system, and program for determining performance data with respect to the execution of database program statements in an application program.
2. Description of the Related Art
The Structured Query Language (SQL) command language provides commands to use to search and perform operations with respect to a relational database. The SQL language statements instruct a database manager program, which manages access to the data in the database, on what data to access. SQL statements are typically included in an application program that sequentially executes the SQL statements to perform specific database operations. In large scale databases, storing millions and perhaps billions of records, the performance of an application consisting of SQL statements can have a significant affect on overall system performance, especially if the SQL statements search on all the records in such large scale databases.
Database programs typically include a performance measurement tool to measure the performance of SQL statements and other database operations For instance, the International Business Machines Corporation (IBM) DB2 database family of products includes a performance monitor tool. Oftentimes, database users want to determine the performance of SQL operations for the purpose of fine tuning the SQL commands to optimize performance of the SQL application. Certain of the performance statistics for SQL statements that can be monitored include, the CPU consumed by each individual SQL statement or application, the time to run a statement, the number of rows read and returned, and the use of database resources, such as buffer pool, prefetcher, and SQL cache. These run-time metrics are useful in tuning queries for optimal utilization of database resources. Modifying a query or certain system parameters can result in dramatic performance improvements. Further, the impact of modifications can be measured with the database system monitor.
DB2 generates performance data referred to as instrumentation data and provides the capability to gather, analyze, and report on DB2 instrumentation data. To begin collecting performance data, a user would activate an event monitor that starts a new process or thread, which records monitor data to either memory, a named pipe or a file as events occur. The event may be monitored as soon as the database is started, so that all activity is monitored from start-up. The DB2 performance monitor further includes snapshot monitoring which provides information as of a point in time. The event monitor is activated using DB2 commands. The event monitor program would return information on all the SQL statements executing in all threads during the activation of the event monitor, and return statement start/stop time, CPU used, text of dynamic SQL, SQLCA (return code of SQL statement), and other metrics such as fetch count. In current implementations, the DB2 event monitor may return SQL performance data on all threads being executed within the database. In this way, the event monitor would trace the execution of all SQL statements and other activity ongoing in the database during the execution of the event monitor, such as detection of a deadlock, end of unit of work, end of a connection, etc. Further details of the current DB2 Performance Monitor is described in the IBM publication “IBM DB2 Universal Database: System Monitor Guide and Reference, Version 5,” IBM Publication No. S10J-8164-00 (Copyright IBM Corp., 1997).
There is a need in the art for improved performance monitors that provide more fine grained control over the ability to monitor and the performance of SQL statements.
SUMMARY OF THE PREFERRED EMBODIMENTS
To provide improvements to the prior art described above, preferred embodiments disclose a method, system, and program for accessing performance data. A call from an application program including database program statements executing on a thread is processed. This call is used to access performance data for at least one database program statement executing on the thread.
The database program statements may comprise database access calls, SQL statements, and/or database commands.
In further embodiments, the performance data returned in response to the call includes performance data from when the thread began processing database program statements in the application program. Alternatively, the returned performance data includes performance data with respect to the execution of at least one database program statement within the application program.
In yet further embodiments an additional program, referred to herein as the performance monitor program and which may be implemented as a stored procedure, accesses performance data for the thread on with the application program was executing. This performance monitor program processes the performance data to determine requested performance data and returns the requested performance data to the application program.
Preferred embodiments provide calls which may be included in an application program, such as an SQL application including a series of SQL statements, to return performance data just for the thread on which the application is executing. Performance data may be provided for the entire time during which the thread was executing the application up until the time the call was made or include the performance data for one or more SQL statements within the application. The preferred calls may be placed at different locations in the application to determine performance with respect to different parts of the application program. The call is processed by a separate application program, e.g., the performance monitor program, which gathers the performance data specific to the application from which the call was made and returns the performance data to the calling application.
Preferred embodiments provide a straightforward mechanism to obtain performance data by providing calls that may be inserted at different locations within the application, e.g., SQL application, being tested. In this way software developers can determine the performance of one or more SQL statements within an application and determine whether adjustments need to be made to such SQL statements to optimize performance. Moreover, calls may be made within the adjusted and optimized application program to determine whether such adjustments did in fact improve performance.
Preferred embodiments are especially useful in a large scale database environment where the performance of an SQL application program processing millions or billions of records can have a significant impact on overall system performance.


REFERENCES:
patent: 5440692 (1995-08-01), Janicek
patent: 5574900 (1996-11-01), Huang et al.
patent: 5644768 (1997-07-01), Periwal
patent: 5758144 (1998-05-01), Eberhard et al.
patent: 5956509 (1999-09-01), Kevner
patent: 5970248 (1999-10-01), Meier
patent: 5983225 (1999-11-01), Anfindsen
patent: 5987517 (1999-11-01), Firth et al.
patent: 6006230 (1999-12-01), Ludwig et al.
patent: 6038562 (2000-03-01), Anjur et al.
patent: 6067604 (2000-05-01), Ramachandran et al.
patent: 6112024 (2000-08-01), Almond et al.
patent: 6324683 (2001-11-01), Fuh et al.
IBM, “DB2 for OS/390 Version 5 Administration Guide”, SC26-8957-02, Jun. 1997, pp. 4-24 -4-47.

Affiliated with

Also associated with

Rating

Method, system, and program for accessing performance data does not yet have a rating. At this time, there are no reviews or comments for this patent.

If you have personal experience with Method, system, and program for accessing performance data, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Method, system, and program for accessing performance data will most certainly appreciate the feedback.

Rate now

Comments { 0 }

Profile ID: LFUS-PAI-O-3306824