U.S. patent application number 12/139405 was filed with the patent office on 2008-10-09 for analysis of performance data from a relational database system for applications using stored procedures or sql.
This patent application is currently assigned to INTERNATIONAL BUSINESS MACHINES CORPORATION. Invention is credited to Kenneth Carlin Nelson.
Application Number | 20080250046 12/139405 |
Document ID | / |
Family ID | 37011586 |
Filed Date | 2008-10-09 |
United States Patent
Application |
20080250046 |
Kind Code |
A1 |
Nelson; Kenneth Carlin |
October 9, 2008 |
ANALYSIS OF PERFORMANCE DATA FROM A RELATIONAL DATABASE SYSTEM FOR
APPLICATIONS USING STORED PROCEDURES OR SQL
Abstract
Analyzing performance data from a relational database to
discover poorly performing SQL statements includes creating event
records of performance data for query statements executed by a
relational database management system, and condensing the
performance data in the event records. A performance summary can be
generated for each condensed event record. The performance
summaries can then be analyzed based on performance characteristics
to identify poorly performing SQL statements.
Inventors: |
Nelson; Kenneth Carlin;
(Hollister, CA) |
Correspondence
Address: |
SAWYER LAW GROUP LLP
2465 E. Bayshore Road, Suite No. 406
PALO ALTO
CA
94303
US
|
Assignee: |
INTERNATIONAL BUSINESS MACHINES
CORPORATION
Armonk
NY
|
Family ID: |
37011586 |
Appl. No.: |
12/139405 |
Filed: |
June 13, 2008 |
Related U.S. Patent Documents
|
|
|
|
|
|
Application
Number |
Filing Date |
Patent Number |
|
|
11080171 |
Mar 15, 2005 |
|
|
|
12139405 |
|
|
|
|
Current U.S.
Class: |
1/1 ;
707/999.101; 707/E17.009 |
Current CPC
Class: |
G06F 16/2453
20190101 |
Class at
Publication: |
707/101 ;
707/E17.009 |
International
Class: |
G06F 17/30 20060101
G06F017/30 |
Claims
1. An system for analyzing performance data comprising: means for
receiving event records of performance data for query statements
executed by a relational database management system; and means for
condensing the performance data in the event records.
2. The system of claim 1 further comprising means for generating,
from each condensed event record, a performance summary for the
executed statement.
3. The system of claim 1 further comprising means for analyzing the
performance summaries for the executed statements based on one or
more performance characteristics to identify one or more
statements.
4. The system of claim 1 further comprising means for linking each
performance summary to the text of the corresponding query
statement executed by the relational database management
system.
5. The system of claim 1 further comprising: means for identifying
a set of performance summaries for query statements issued by the
application program for each application program that issued query
statements to the relational database management system; and means
for generating a performance summary for the application program
from the corresponding identified set of query statement
performance summaries.
6. The system of claim 1 further comprising means for analyzing the
application program performance summaries to identify one or more
application programs.
7. The system of claim 1 further comprising means for analyzing the
performance summaries for the query statements issued by the
application program to identify one or more query statements issued
by the application program.
8. The system of claim 1 further comprising means for analyzing the
application programs and the query statements to improve their
performance.
9. A computer-readable storage medium storing program instructions,
the program instructions which when executed by a computer system
cause the computer system to execute a method comprising: receiving
event records of performance data for query statements executed by
a relational database management system; and condensing the
performance data in the event records.
10. The computer-readable storage medium of claim 9 further
comprising program instructions for generating, from each condensed
event record, a performance summary for the executed statement.
11. The computer-readable storage medium of claim 9 further
comprising program instructions for analyzing the performance
summaries for the executed statements based on one or more
performance characteristics to identify one or more statements.
12. The computer-readable storage medium of claim 9 further
comprising program instructions for linking each performance
summary to the text of the corresponding query statement executed
by the relational database management system.
13. The computer-readable storage medium of claim 9 further
comprising program instructions for: for each application program
that issued query statements to the relational database management
system, identifying a set of performance summaries for query
statements issued by the application program; and generating a
performance summary for the application program from the
corresponding identified set of query statement performance
summaries.
14. The computer-readable storage medium of claim 9 further
comprising program instructions for analyzing the application
program performance summaries to identify one or more application
programs.
15. The computer-readable storage medium of claim 9 further
comprising program instructions for, for each application program,
analyzing the performance summaries for the query statements issued
by the application program to identify one or more query statements
issued by the application program.
16. The computer-readable storage medium of claim 9 further
comprising program instructions for analyzing the application
programs and the query statements to improve their performance.
Description
CROSS-REFERENCE TO RELATED APPLICATIONS
[0001] Under 35 USC .sctn.120, this application is a continuation
application and claims the benefit of priority to U.S. patent
application Ser. No. 11/080,171, filed Mar. 15, 2005, entitled
"Analysis of Performance Data from a Relational Database System for
Applications Using Stored Procedures or SQL," which is incorporated
herein by reference.
FIELD OF THE INVENTION
[0002] The invention relates to the field of database management
and performance analysis.
BACKGROUND OF THE INVENTION
[0003] Data records in a relational database management system
(RDBMS) in a computer are maintained in tables, which are a
collection of rows having the same columns. Each column maintains
information on a particular type of data for the data records of
the rows. Data from the database tables may be accessed using
instructions in a language that the database management system
recognizes. For example, Structured Query Language (SQL) statements
are often used to query, access, and manipulate data in a
database.
[0004] A SQL statement includes set operators that define
operations to be performed by the database management system when
searching columns of database tables. For instance, a SQL set
operator may look for all records having a field that satisfies a
search condition, such as equal to or less than a certain value.
Also, the operator can be a join, which combines or concatenates
rows from different tables, then searches for those concatenated
rows that satisfy a search predicate. Rows from the tables involved
in the join operation that do not satisfy the predicate or
condition are excluded from the join result.
[0005] There are two methods of developing applications which
interface with a relational database system. The first is to use
static or dynamic SQL statement calls from within the application
program. A dynamic SQL statement is constructed by the application
program, "prepared" or complied with a call to the database
management system, and then executed. A static SQL statement is
coded into the application program, and prepared or compiled prior
to running the application program.
[0006] The second method of developing applications which interface
with a relational database system identifies common functions that
call one or more SQL statements, and encapsulates each function as
a Stored Procedure to be executed at the database server instead of
the application, improving performance and in many cases
simplifying application development.
[0007] When an application program or Stored Procedure is compiled,
a separate step is performed to create an access plan for each SQL
statement using the operators and search values in the statement to
retrieve data in an efficient manner. For a static SQL statement,
the access plan is based on the state of the database at the time
the plan is generated. The access plan can be recreated using the
current state of the database at any time by first updating
database statistics and then "re-binding". Most of the access plans
generated by this process are optimal.
[0008] However, a few poorly performing statements can consume a
significant amount of the database management system resources,
resulting in a slowdown of the system. To find statements which use
a large amount of CPU or I/O time, an event monitor in the database
management system creates an event record of each SQL statement or
stored procedure that is executed by the system, to record
performance information for the executed statement.
[0009] When a SQL statement has a performance problem, such as a
high execution time or a high number of rows read, written or
sorted, finding the SQL statement with this poor execution
performance is generally the first step in correcting the problem.
For example, the event records for the executed SQL statements are
studied to find the statements having these types of performance
problems. However, the event monitor can include records for
thousands or millions of executed statements, and each record can
contain many lines with performance information for a single SQL
statement. As a result, searching this amount of data to locate a
problem statement is very time consuming, and is therefore often
not done.
[0010] If the problem statement is executed infrequently, such as
once at start-up, then the overall impact on system performance may
be insignificant, and the problem statement can be left in place.
However, if the problem statement is executed frequently, and
accounts for a significant reduction in system performance, then
the problem statement needs to be identified so that its execution
performance, and the overall performance of the system, can be
improved.
[0011] Without a tool to summarize the performance records of the
executed statements, identifying the problem statement, and the
statement's overall impact on system performance, is very
difficult. There is therefore a need for a summary of the
performance of SQL statements so that poorly performing statements
can be identified.
[0012] Furthermore, the event monitor typically includes the text
of dynamic SQL statements, but not the text of static SQL
statements. For static SQL statements, which are coded into the
application program, their preparation or compilation is done prior
to running the application program. Therefore, the event monitor
contains only the package (or module) name and section number of
the SQL statement, which indicates the relative position of the SQL
statement within the module, rather than the actual text
itself.
[0013] As a result, most users who are not familiar with the
internal storage used by the database management system often
attempt to locate the text of the static SQL statement within the
source code of the application program using the package name and
section number from the event monitor, but this can be time
consuming and error prone. This may also be impossible, if the user
does not have access to the application source code. A user may
also be able to either use a tool, such as a DB2 control center, or
have the ability to query a system table, to find the static SQL
statement. However, if the event monitor is from another system,
then the user may have to ask a customer to find the text of the
static statement.
[0014] There is therefore also a need to be able to identify the
text of a poorly performing static SQL statement from the
performance data so that the statement can be further analyzed and
tuned for improved performance.
SUMMARY OF THE INVENTION
[0015] The compression of raw data from an event monitor into a
compact form, such as one line per SQL statement, is performed to
show relevant details for the statements, such as the text of
static or dynamic SQL statements. This allows a user to more easily
see the complete flow of operations and performance of each
statement, both within a single application context and across the
system.
[0016] In one embodiment, the compression technique produces a
summary of the event monitor data to provide statistics such as
frequency of execution and cost of execution of each statement.
[0017] An overall summary can also be produced, to show information
such as the elapsed time of the test, the number of stored
procedures executed, the number of SQL statements executed, and the
wait time, for example. Other data which a user deems as necessary
for understanding application performance may also be included in
the overall summary.
BRIEF DESCRIPTION OF THE DRAWINGS
[0018] FIG. 1 illustrates a computer system that includes an
analysis tool to perform an analysis of performance data.
[0019] FIG. 2 shows an example of a method of analyzing performance
data from a relational database to discover poorly performing SQL
statements.
[0020] FIG. 3 shows an example of an event record that is processed
by the method of analyzing performance data.
[0021] FIG. 4 shows an example of performance data for a
transaction that is processed by the method of analyzing
performance data.
[0022] FIG. 5 shows an example of a condensed report generated by
the method of analyzing performance data.
[0023] FIG. 6 shows an example of condensed summaries for a set of
executed SQL statements.
[0024] FIG. 7 shows a statement identified as a result of sorting
the SQL statements of FIG. 6 based on total execution time.
[0025] FIG. 8 shows an example of a summary of performance data for
statements issued by an application program.
[0026] FIG. 9 shows an example of a computer processing system for
analyzing performance data.
DETAILED DESCRIPTION OF THE INVENTION
[0027] The following description is presented to enable one of
ordinary skill in the art to make and use the invention, and is
provided in the context of a patent application and its
requirements. Various modifications to the preferred embodiment and
the generic principles and features described herein will be
readily apparent to those skilled in the art. Thus, the present
invention is not intended to be limited to the embodiments shown,
but is to be accorded the widest scope consistent with the
principles and features described herein.
[0028] An analysis of performance data from a relational database
can produce information from the raw performance data to aid in
improving SQL statements and stored procedures. A summary of
performance data for each executed statement can be generated by
the analysis process. Also, a second summary of performance data
for a statement executed multiple times can be generated from the
first summaries. A third summary of performance data for executed
statements associated with a given application program can also be
generated from the first summaries.
[0029] The analysis of performance data from a relational database
discovers poorly performing SQL-based applications by producing a
summary of execution information for SQL statements and Stored
Procedures. In one embodiment, an event monitor report is processed
to condense many lines of a performance record for an executed
statement into a smaller number of lines. For example, a
summarization tool can analyze event monitor data by reading a
formatted event monitor file, extracting static SQL statements from
a table, and creating a one line performance report for each
executed statement.
[0030] FIG. 1 illustrates a computer system that includes an
analysis tool to perform an analysis of performance data. One or
more application programs 110 issue database operations in the form
of Stored Procedure calls or SQL statements 115 to compiler 120 of
the relational database management system (RDBMS). A Stored
Procedure is an encapsulation of application logic and SQL
statements which is executed directly under control of the database
system instead of being executed from a remote client application.
Two types of SQL statements may be executed, static or dynamic. A
static SQL statement is fully defined as part of the application,
and is compiled along with the application. A dynamic SQL statement
is constructed at run time.
[0031] For each database operation, compiler 120 receives the
corresponding SQL statement and produces an execution plan for the
statement. The execution plan is sent to the execution engine 130,
which executes the plan to retrieve data from database 140 and
return the query result 150 to the application program 110 that
issued the query. The execution performance of the plan is recorded
by the event monitor 160.
[0032] This execution performance information is received by the
analysis tool 170, to produce a summary of the execution
performance information for the statement. Also, because each
application program can issue hundreds or thousands of database
queries, the analysis tool 170 can produce a summary of the
execution performance information for the set of SQL statements
issued by that application program.
[0033] A method of analyzing performance data from a relational
database to discover poorly performing SQL statements is shown in
FIG. 2. An event monitor creates event records containing
performance data for SQL statements that are executed by a database
management system, 210. In one embodiment, each event record
contains information about an execution of a SQL statement.
[0034] The data in the event records is condensed, 220. For
example, certain information from the event records can be selected
for preservation in a summary, and the non-selected information can
be discarded. Certain information that is not available in the
event record may also be added to the summary during this
procedure. For example, if the executed event is a static SQL
statement, then the text of the static SQL statement may be
extracted from a table and included with the other relevant
performance data. The form of presentation of the information
obtained from the event record can be changed, 230. For example,
the arrangement of the performance data can be changed from
multiple lines as displayed in the event records, into a collection
of formatted columns, to improve readability.
[0035] A summary of performance data for each executed SQL
statement is generated, 240. In one embodiment, the summarization
tool produces a one line summary of information about the
performance of each executed statement by inserting the condensed
performance data for each statement into a row under the formatted
columns. In addition to generating a one line summary for each
executed statement, the tool can produce a summary of a set of
executed statements, 250. For example, the tool can summarize the
performance of all SQL statements or stored procedure calls issued
by a given application.
[0036] The summaries for a given application can be sorted to rank
the statements based on one or more performance characteristics,
such as their elapsed execution time, 260. The summary information
for the SQL statements with a relatively high ranking can be
examined to identify poorly performing SQL statements, 270. These
poorly performing statements, and their corresponding application
programs, can be tuned or changed to improve the overall
performance of the database management system.
[0037] An example of an event record that is created by the event
monitor in 210 of FIG. 2 is shown in FIG. 3. This event record
shows an example of the performance related data associated with
one executed statement. Certain performance related data of FIG. 3
can be useful in analyzing the performance of the statement and in
identifying a poorly performing statement. For example, information
such as the application ID field, the text of the statement, the
start and stop time, and the execution time can be used to identify
a poorly performing statement.
[0038] The Application ID field is associated with one connection
from an application. A single event monitor report might contain
entries for hundreds or even thousands of concurrently executing
applications, so this field is important to identify the
application program that executed this statement. This field can
also be used to identify the set of statements issued by a given
application.
[0039] The event report shows that the executed statement in the
example of FIG. 3 is a dynamic SQL statement from package ICMPLSLM
and section 3. Since it is a dynamic SQL statement, the text is
included in the event monitor. If it had been a static SQL
statement, the text would not have been included in the event
monitor. The start and stop time shown by the event record can be
used to observer the relative order of execution, and to gain an
understanding of the relationship of this statement to other
statements executing at the same time.
[0040] The event record includes the execution time, which is
performance information that frequently gives significant insight
into whether the statement performed well. In this case, the
statement required 2.7 seconds to complete, which suggests that
further analysis may be performed to understand whether this
represents a performance problem. For example, since this is a
"close" statement, the elapsed time depends on the application
which is calling the stored procedure. The length of time could be
due to the application not processing the data quickly, or simply
leaving the cursor open. Without seeing the other statements
executed by the transaction at the same time, it is difficult to
speculate about the possible reason the cursor was open for 2.7
seconds.
[0041] The event monitor report in the example of FIG. 3 shows that
2 rows were fetched, 3 rows were read, and no rows were inserted,
updated or deleted. Physical buffer pool reads represent disk I/O,
and logical buffer pool reads represent access to pages already in
memory. In this example, these values are relatively low,
suggesting that there may not a problem with SQL performance for
this statement.
[0042] In addition to producing an event report for a statement,
the event monitor can produce a report for a transaction, as shown
in FIG. 4. A database application will typically execute multiple
SQL statements as part of a single transaction, with the end of the
transaction being caused either implicitly or explicitly by the
application. Except in rare cases, database cursors are closed when
a transaction ends. Since database locks can be held during a
transaction, it is important to understand the impact of
long-running transactions on the overall performance of the
application. FIG. 4 shows the data captured in an event monitor for
a transaction.
[0043] The exemplary record of transaction performance data of FIG.
4 shows that the transaction duration was over 23 seconds, there
were 31 locks held, and lock wait time was 15 milliseconds.
Although lock wait time is this example is short, a long-running
transaction such as this can cause other transactions to lock,
which could affect overall system performance. By capturing each
transaction event with the application ID, it is possible to
discover whether other transactions executing at the same time were
blocked, and the overall cost/elapsed time of each transaction in
an application.
[0044] As can be seen from the event record of FIG. 3, a large
amount of performance information, often more than one page, is
presented for each executed SQL statement, even though only a
subset of this information may be relevant in determining whether
the statement performed poorly. To better analyze the relevant
performance results, the relevant portions of the report are
filtered from the event monitor report. This may be done by
processing the raw event monitor file to identify and condense
reports for each Statement, Transaction, or Connection event.
[0045] For example, the process may express the relevant data
condensed from each report as a single line as shown in FIG. 5. The
condensed summary file produced in this example has one line of
relevant performance data for each event, and can be conveniently
viewed by many text editors. (For illustration purposes only, a
selection of a few lines of the file is shown as three separate
sections in FIG. 5, and the SQL statements are truncated to improve
readability.)
[0046] To condense the relevant performance measurements in a
summary, the process can select data such as, for example, the
application ID, stop time, and elapsed time from the event record
of FIG. 3. Then, the process can change the format of the condensed
performance data from the event records into a collection of
formatted columns, where each column identifies a performance
measurement preserved from the event record. The process can then
create a summarization table by adding each condensed record as a
row to the collection of formatted columns, such that each row in
the table contains relevant performance data for an executed SQL
statement. This process produces a performance summary of each
individual statement, as shown in FIG. 5.
[0047] The summary of a statement's performance can include the SQL
time, which is the amount of time that elapsed during execution of
the statement. The summary also includes the application time,
which is the amount of time between the end of execution of the
previous statement and the beginning of execution of the current
statement. This allows the analysis process to distinguish between
time spent within SQL statements and time spent between SQL
statements, so that the performance of the application code itself
can be understood without using a code profiler. A user is then
able to identify areas of the code that can be improved.
[0048] In this example, FIG. 5 includes a column labeled "App Time"
for Application time, which is the time between SQL statements.
This data can be used to compare and contrast the time spent
executing a SQL statement with the time spent executing the
application. This comparison can be valuable in determining whether
poor performance is caused by the statement or by the application.
For example, if the application time is high relative to SQL time,
then the focus of performance analysis would be on the application
logic rather than the statement.
[0049] The summary can also include the execution start time, the
package or module name containing the SQL statement, and the
section, which is used to find the text for a static SQL statement.
Other information about the statement, such as the number of rows
read, written and sorted, the type (static or dynamic), the
operation (e.g., commit, execute, prepare, rollback), the return
code, or the text of the SQL statement itself, can be included in
the summary. For example, during the creation of a summary for a
static SQL statement, the text of the static SQL statement can be
retrieved from a statement table and added to the summary. In one
embodiment, the user can select any information reported by the
event monitor to be included in the summary.
[0050] In addition to compressing performance information for each
executed SQL statement from the event monitor, the data compression
program can create summaries for Stored Procedure records in the
event monitor. A Stored Procedure is a collection of SQL statements
and application logic which is executed at the database server. The
summary for a Stored Procedure can contain the difference between
the stop time of a stored procedure and the start time of the next
for each Application ID. This performance data can indicate time
spent performing functions outside of the stored procedures, such
as application time, network time, or the overhead for the database
system to begin execution of a stored procedure following a call,
for example.
[0051] The condensed summaries of in FIG. 3 show that the 7 event
records extracted from the event monitor file are associated with 6
different database connections. By creating a single line in the
condensed summary file for each event, a user can sort the summary
file by Application ID (or connection) and the stop time of the
statement to see the performance activity for each application. By
extracting lines for a selected application ID, a user can see the
summary of each SQL statement in the order in which it was
executed, to provide insight into the behavior of the application.
Also, by extracting only the records where the Type column contains
TRX, it is possible to see the elapsed time, statistics for rows
and buffer pools, and the lock wait time for each transaction.
[0052] Although this example of 7 records is relatively easy to
view and understand, the event monitor captured for an actual test
can include thousands or even millions of records. To more clearly
understand the overall behavior of an application, the compressed
summaries shown in FIG. 5 can be further compressed into a second
set of summaries as shown in FIG. 6.
[0053] A second compression process uses the summaries of FIG. 5 to
generate information about a set of executed SQL statements as
shown in FIG. 6. For example, when one basic statement is expressed
as several dynamic SQL statements, each having the same package and
section identifiers but different text, the execution performance
information for the several statements is captured by the second
compression process in a summary report of the total performance of
the several statements. This eliminates multiple entries for the
same basic statement, so that the impact of the statement on
overall performance is clearer.
[0054] As shown in FIG. 6, the summary of performance data for a
basic statement executed multiple times includes three steps in
executing one dynamic SQL statement--prepare, open, and close. This
statement was executed 10 times, with an average SQL time of 0.294
seconds. Although the maximum number of rows read was 25, the
average was 3 with 2 rows fetched. The percent of total elapsed
time for all 3 steps in this statement is only 0.41%. Even though
one execution seemed to indicate that there could be a performance
concern, the overall impact of the statement is insignificant. By
using a text editor or other tool to sort the file based on percent
of SQL time, it is easy to find the statements contributing the
most to the elapsed time of the application.
[0055] The summary information of FIG. 6 can be analyzed to detect
poorly performing SQL statements or stored procedures. For example,
if the summary file includes an average elapsed time of each SQL
statement from an application, and the percentage of total
execution time for each of the SQL statements, a user can sort the
summary file based on the percentage of total execution time to
identify a SQL statement which has the greatest impact on system
performance. FIG. 7 shows a statement identified as a result of
sorting the SQL statements of FIG. 6 based on total execution
time.
[0056] The statement identified in FIG. 7 significantly contributes
to the elapsed time of an application. This statement can be
reviewed to determine whether design changes will reduce the
execution time. The overall system performance can then be improved
by reviewing the identified statements for coding changes, database
tuning changes, or application design changes to eliminate them
entirely.
[0057] This statement was executed 26 times with an average
execution time of 5.3 seconds. This statement alone accounted for
19% of the elapsed time of the test, so would represent a
significant opportunity to improve overall application performance.
In this example, the complete SQL statement is included, so that
this poorly performing statement can be analyzed for
improvement.
[0058] The large number of columns being inserted could certainly
affect performance. Additional analysis would show that some of
these columns are "character large objects" (CLOBs) which due to
the nature of the database management system are much slower to
insert. By selecting only the records for package ICMOSDOE and
section 17, we can see that one execution of this statement took
0.237 seconds, one took 10.5 seconds, one took 15.7 seconds, and
the rest took approximately 2.6 seconds. Further review of the
detail file shows that the transaction which was executed for the
statement that took 15.7 seconds had no lock wait time, so there is
no database design issue. From this analysis, a user could conclude
that there is probably an I/O or (less likely due to the long time)
a CPU constraint causing the insert to be very slow, and would use
operating system tools to find whether this is the cause of poor
performance.
[0059] The summaries of FIG. 5 can also be compressed into a
summary of performance data for the statements issued by each
application program, as shown in FIG. 8. While the summaries for
individual statements, as shown in FIG. 5 for example, can give a
clear view of the behavior of the entire system or an individual
application, and the cumulative summary files of FIGS. 6 and 7 can
help identify statements that should be investigated to improve
performance, further summarization can be performed to characterize
performance of an individual application, as shown in FIG. 8.
[0060] In this example, performance data was gathered from a test
that was run for 134 seconds, with 139 stored procedure and 2471
SQL statements executed. (Since a database system can manage
multiple concurrent applications, the total elapsed time can be
higher than the duration of the test in this example.) The time
within stored procedures was 288 seconds, and the time within SQL
statements 723 seconds. (SQL elapsed time is higher than stored
procedure elapsed time because the Close statement for a cursor
returned to the application occurs after the stored procedure
ends.) By including the number of SQL statements excluding Close
and Prepare, and the elapsed time for these statements, it is
possible to see that these accounted for approximately 50% of the
SQL time. The lock wait time of 38 seconds is high for such a short
test, and suggests that either database or application design
issues need to be investigated.
[0061] FIG. 9 shows an example of a computer processing system 900
that can perform an analysis of performance data from a relational
database system. A computer program of executable instructions can
be stored in a computer-readable medium, such as system memory 906,
static storage device 908, or disk drive 910, for example. For the
purposes of this specification, the terms "machine-readable medium"
and "computer-readable medium" shall be taken to include any medium
that is capable of storing or encoding a sequence of instructions
for execution by the machine and that cause the machine to perform
any one of the methodologies of the present invention. The terms
"machine-readable medium" and "computer-readable medium" shall
accordingly be taken to include, but not be limited to, solid-state
memories, optical and magnetic disks, and a carrier wave that
encodes a data signal.
[0062] A processor 904 can retrieve the instructions from memory
through bus 902, and execute the instructions. A user can also
input instructions to the processor through bus 902 using an input
device 916, such as a keyboard, or a cursor control device 918,
such as a mouse. The processing system 900 can display information
to the user using a display 914, such as a computer monitor. The
processing system may send and receive information to other
electronic devices over a network through communication interface
912, such as a modem for example, and communication link 920.
[0063] An analysis of performance data from a relational database
system for applications using stored procedures or SQL has been
discussed. The analysis can track a number of rows fetched, read,
written and sorted, and a number of buffer pool reads. The analysis
can report this information for the execution of the statement in a
summary line to allow tools such as grep to extract subsets of
information. The analysis can also provide a summary that contains
a number of stored procedures executed with an elapsed time, a
number of SQL statements with elapsed time, SQL statements
excluding close and total transaction time.
[0064] The present invention has been described in accordance with
the embodiments shown. One of ordinary skill in the art will
readily recognize that there could be variations to the
embodiments, and that any variations would be within the spirit and
scope of the present invention. Accordingly, many modifications may
be made by one of ordinary skill in the art without departing from
the spirit and scope of the appended claims.
* * * * *