Autonomic system-wide sql query performance advisor

Abstract

A method, computer program product and computer system for providing SQL query performance advices to optimize SQL queries of a database, which includes providing a query cache to store records of optimized queries of the database, creating an event-driven web service, sending the records from the query cache to the web service, and analyzing the records using the web service to form SQL query performance advices. The method, computer program product and computer system can further includes outputting the SQL query performance advices to a viewer for display, or outputting the advices to a post-processing application for additional actions.

1 . A method for providing SQL query performance advices to optimize an SQL query of a database, comprising:
 providing a query information repository to store records of optimized queries of the database;   creating an event-driven web service;   sending the records from the query information repository to the web service; and   analyzing the records using the web service to form the SQL query performance advices.   
   
   
       2 . The method of  claim 1 , further comprising outputting the SQL query performance advices to a viewer for display. 
   
   
       3 . The method of  claim 1 , further comprising outputting the SQL query performance advices to a post-processing application. 
   
   
       4 . The method of  claim 1 , further comprising, before sending the records to the web service, obtaining the records from the query information repository and formatting the records. 
   
   
       5 . The method of  claim 4 , wherein the formatting comprises converting the records into a stream of a standard format. 
   
   
       6 . The method of  claim 5 , wherein the standard format is an XML format. 
   
   
       7 . The method of  claim 1 , wherein the SQL query performance advices comprise tuning recommendations based on a database engine that generates the optimized queries. 
   
   
       8 . The method of  claim 1 , wherein the query information repository is an SQE Plan Cache. 
   
   
       9 . A computer program product for providing SQL query performance advices to optimize an SQL query of a database, the computer program product comprising:
 a computer usable medium having computer usable program code embodied therewith, the computer usable program code comprising:
 instructions to provide a query information repository to store records of optimized queries of the database; 
 instructions to create an event-driven web service; 
 instructions to send the records from the query information repository to the web service; and 
 instructions to analyze the records using the web service to form the SQL query performance advices. 
   
   
   
       10 . The computer program product of  claim 9 , further comprising instructions to output the SQL query performance advices to a viewer for display. 
   
   
       11 . The computer program product of  claim 9 , further comprising instructions to output the SQL query performance advices to a post-processing application. 
   
   
       12 . The computer program product of  claim 9 , further comprising instructions, before sending the records to the web service, to obtain the records from the query information repository and to format the records. 
   
   
       13 . The computer program product of  claim 12 , wherein the instructions to format comprise instructions to convert the records into a stream of a standard format. 
   
   
       14 . The computer program product of  claim 13 , wherein the standard format is an XML format. 
   
   
       15 . The computer program product of  claim 9 , wherein the SQL query performance advices comprise tuning recommendations based on a database engine that generates the optimized queries. 
   
   
       16 . The computer program product of  claim 9 , wherein the query information repository is an SQE Plan Cache. 
   
   
       17 . A computer system comprising:
 a processor;   a memory operatively coupled with the processor;   a storage device operatively coupled with the processor and the memory; and   a computer program product for providing SQL query performance advices to optimize an SQL query of a database, the computer program product comprising:
 a computer usable medium having computer usable program code embodied therewith, the computer usable program code comprising:
 instructions to provide a query information repository to store records of optimized queries of the database; 
 instructions to create an event-driven web service; 
 instructions to send the records from the query information repository to the web service; and 
 instructions to analyze the records using the web service to form the SQL query performance advices. 
 
   
   
   
       18 . The computer system of  claim 17 , further comprising instructions to output the SQL query performance advices to a viewer for display. 
   
   
       19 . The computer system of  claim 17 , further comprising instructions to output the SQL query performance advices to a post-processing application. 
   
   
       20 . The computer system of  claim 17 , further comprising instructions, before sending the records to the web service, to obtain the records from the query information repository and to format the records. 
   
   
       21 . The computer system of  claim 20 , wherein the instructions to format comprise instructions to convert the records into a stream of a standard format. 
   
   
       22 . The computer system of  claim 21 , wherein the standard format is an XML format. 
   
   
       23 . The computer system of  claim 17 , wherein the SQL query performance advices comprise tuning recommendations based on a database engine that generates the optimized queries. 
   
   
       24 . The computer system of  claim 17 , wherein the query information repository is an SQE Plan Cache.