Database performance monitoring method and tool

Abstract

The present invention provides a method and system for monitoring the process performance of a database that accepts and records SQL statements and that records the status of a session of use of the database. The invention obtains the SQL address and hash value for each SQL statement, the current session status corresponding to each SQL statement and the previous session status corresponding to each SQL statement. The invention also records a time stamp at the time the session status information is obtained. The information gathering steps are repeated at a predetermined interval of time. Using the information gathered, the run time for each SQL statement is calculated. The run time for each SQL statement may then be reviewed to determine which SQL statements experience the greatest run time, which allows DBAs to locate the source of any performance problems.

1 . A method for use in a computer system for monitoring the process performance of a database, the database accepting and recording SQL statements and recording the status of a session of use of the database, the method comprising: 
 obtaining a SQL address and hash value for each SQL statement;    obtaining a current session status corresponding to each SQL statement;    recording a time stamp at the time the current session status is obtained;    repeating the obtaining steps and the time stamp recording step at a plurality of time intervals; and    calculating a run time for each SQL statement based upon the obtained SQL address and hash value, the time stamp and the current session status,    whereby the run time for each SQL statement may be reviewed to determine which SQL statements experience the greatest run time.    
     
     
         2 . The method of  claim 1 , wherein the calculating step further comprises: 
 determining whether each SQL statement is new to the database session, and if so, determining whether the current session status is active and applying an addition to the run time for the SQL statement if the current session status is active.    
     
     
         3 . The method of  claim 2 , where the addition to the run time is a portion of the time interval.  
     
     
         4 . The method of  claim 3 , where the portion is one-quarter of the time interval.  
     
     
         5 . The method of  claim 1 , wherein the calculating step further comprises: 
 obtaining the previous session status corresponding to each SQL statement;    determining whether each SQL statement is new to the database session, and if not, determining the previous session status for the SQL statement and the current session status for the SQL statement; and    applying a first addition to the run time for the SQL statement if the previous session status is active.    
     
     
         6 . The method of  claim 5 , further comprising applying a second addition to the run time for the SQL statement if the previous session status is inactive and the current session status is active.  
     
     
         7 . The method of  claim 6 , where the second addition to the run time if the previous session status is inactive and the current session status is active is a portion of the time interval.  
     
     
         8 . The method of  claim 5 , where the first addition to the run time if the previous session status and the current session status is active is the fall amount of the time interval.  
     
     
         9 . The method of  claim 5 , where the first addition to the run time if the previous session status is active and the current session status is inactive is a portion of the time interval.  
     
     
         10 . The method of  claim 1 , further comprising generating a report containing a listing of each SQL statement and the run time corresponding to each SQL statement.  
     
     
         11 . The method of  claim 10 , where the report contains the SQL address and hash value.  
     
     
         12 . A computer-readable medium having computer-executable instructions for performing a method for monitoring the process performance of a database, the database accepting and recording SQL statements and recording the status of a session of use of the database, comprising: 
 obtaining the SQL address and hash value for each SQL statement;    obtaining the current session status corresponding to each SQL statement;    recording a time stamp at the time the session status is obtained;    repeating the obtaining steps and the time stamp recording step at a plurality of time intervals; and    calculating the run time for each SQL statement based upon the obtained SQL address and hash value, the time stamp and upon the current session status,    whereby the run time for each SQL statement may be reviewed to determine which SQL statements experience the greatest run time.    
     
     
         13 . A computer system having a processor, a memory, and an operating environment, the computer system operable to execute a method for monitoring the process performance of a database, the database accepting and recording SQL statements and recording the status of a session of use of the database, comprising: 
 obtaining the SQL address and hash value for each SQL statement;    obtaining the current session status corresponding to each SQL statement;    recording a time stamp at the time the session status is obtained;    repeating the obtaining steps and the time stamp recording step at a plurality of time intervals; and    calculating the run time for each SQL statement based upon the obtained SQL address and hash value, the time stamp and upon the current session status,    whereby the run time for each SQL statement may be reviewed to determine which SQL statements experience the greatest run time.