Automatic database query load assessment and adaptive handling

Abstract

Systems, methods, and other embodiments associated with assessing and adaptively managing query loads are described. In one embodiment, a method includes determining that a query invoked by a client terminal against an application object has a duration that exceeds a completion threshold. In response to receiving a subsequent invocation of the query, statistics for the query are obtained. The obtained statistics are analyzed to determine whether the query causes the unacceptable negative impact on the application object. A data structure is modified to include a classification of the query into one of a plurality of different query classes based on a result of the analysis, and execution of the query in response to a future invocation is controlled based on the classification of the query in the modified data structure.

What is claimed is: 
     
       1. A non-transitory computer-readable medium storing computer-executable instructions that, when executed by at least a processor of a computing system, cause the computing system to:
 receive a query that is requested for execution on a database system; 
 define a fingerprint for the query based at least in part on one or more statements from the query, wherein the one or more statements from the query define one or more operations in the query to be performed, wherein multiple queries can have the same fingerprint based on the one or more statements but are not identical queries; 
 compare the fingerprint of the received query to fingerprint records in a data structure, wherein the fingerprint records include existing fingerprints for previously received queries where each existing fingerprint for an associated query is defined based on one or more statements from the associated query; 
 in response to the fingerprint not matching an existing fingerprint:
 execute the query to completion by the database system; 
 determine, by at least the processor, that the query executed has an execution duration that exceeds a completion threshold; 
 store, by at least the processor, the fingerprint corresponding to the query in the data structure; 
 flag, by at least the processor, the fingerprint for evaluation in response to the execution duration of the query exceeding the completion threshold; 
 
 in response to the fingerprint matching an existing fingerprint:
 identify the query as a subsequent query request of the same query or a similar query that was previously flagged for evaluation; 
 based on the fingerprint matching and being flagged for evaluation in the data structure, analyze statistics for the fingerprint to determine whether execution of the subsequent query request will cause an unacceptable negative impact on the database system; and 
 modify, by at least the processor, the data structure to classify the fingerprint into one classification of a plurality of different query classes based on a result of the analysis; and 
 
 control, by at least the processor, execution of the subsequent query request based on the one classification of the fingerprint. 
 
     
     
       2. The non-transitory computer-readable medium of  claim 1 , further comprising instructions that, when executed by at least the processor, cause the computing system to:
 classify the fingerprint based on the result of the analysis by assigning the fingerprint to: (i) a permissible query class, or (ii) a blocked query class; 
 wherein (i) if the fingerprint is assigned to the permissible query class, the subsequent query request is to be executed to completion without repeatedly analyzing the statistics, and (ii) if the fingerprint is assigned to the blocked query class, the subsequent query request is to be blocked from being executed. 
 
     
     
       3. The non-transitory computer-readable medium of  claim 1 , wherein the statistics comprise query explain data or database server handler statistics. 
     
     
       4. The non-transitory computer-readable medium of  claim 1 , wherein the statistics to be analyzed exclude database performance statistics that require utilization of an inactive database resource to obtain. 
     
     
       5. The non-transitory computer-readable medium of  claim 1 , wherein the plurality of different query classes comprise a permissible query class for fingerprints corresponding to queries that are to be executed against a first database. 
     
     
       6. The non-transitory computer-readable medium of  claim 5 , wherein the plurality of different query classes further comprise a redirected query class for fingerprints corresponding to queries that are to be executed against a second database, which is different than the first database. 
     
     
       7. The non-transitory computer-readable medium of  claim 6 , further comprising instructions that, when executed by at least the processor, cause the computing system to:
 cause execution of the query against the first database; and 
 cause execution of the subsequent query request against the second database based on classification of the fingerprint in the redirected query class. 
 
     
     
       8. The non-transitory computer-readable medium of  claim 1 , further comprising instructions that, when executed by at least the processor, cause the computing system to:
 prevent analysis of the statistics to classify the fingerprint in response to execution of the query that resulted in the fingerprint being flagged for evaluation. 
 
     
     
       9. A computing system, comprising:
 at least one processor connected to at least one memory; and 
 an analysis module stored on the non-transitory computer readable medium and including instructions that, when executed by the at least one processor, cause the computing system to:
 receive a query that is requested for execution on a database system; 
 define a fingerprint for the query based at least in part on one or more statements from the query, wherein the one or more statements from the query define one or more operations in the query to be performed, wherein multiple queries can have the same fingerprint based on the one or more statements but are not identical queries; 
 compare the fingerprint of the received query to fingerprint records in a data structure, wherein the fingerprint records include existing fingerprints for previously received queries; 
 in response to the fingerprint not matching an existing fingerprint:
 execute the query to completion by the database system; 
 determine, by at least the processor, that the query executed has an execution duration that exceeds a completion threshold; 
 store, by at least the processor, the fingerprint corresponding to the query in the data structure; 
 flag, by at least the processor, the fingerprint for evaluation in response to the execution duration of the query exceeding the completion threshold; 
 
 in response to the fingerprint matching an existing fingerprint:
 identifying the query as a subsequent query request of the same query or a similar query that was previously flagged for evaluation; 
 based on the fingerprint matching and being flagged for evaluation in the data structure, analyze statistics for the fingerprint to determine whether execution of the subsequent query request will cause an unacceptable negative impact on the database system; and 
 modify, by at least the processor, the data structure to classify the fingerprint into one classification of a plurality of different query classes based on a result of the analysis; and 
 
 control, by at least the processor, execution of the subsequent query request based on the one classification of the fingerprint. 
 
 
     
     
       10. The computing system of  claim 9 , wherein the analysis module further includes instructions that, when executed by the at least one processor, cause the computing system to:
 classify the fingerprint based on the result of the analysis by assigning the fingerprint to: (i) a permissible query class, or (ii) a blocked query class; 
 wherein (i) if the fingerprint is assigned to the permissible query class, the subsequent query request is to be executed to completion without repeatedly analyzing the statistics, and (ii) if the fingerprint is assigned to the blocked query class, the subsequent query request is to be blocked from being executed. 
 
     
     
       11. The computing system of  claim 9 , wherein the statistics comprise query explain data or database server handler statistics. 
     
     
       12. The computing system of  claim 9 , wherein the statistics to be analyzed exclude database performance statistics that require utilization of inactive database resources to obtain. 
     
     
       13. The computing system of  claim 9 , wherein the plurality of different query classes comprise a permissible query class for fingerprints corresponding to queries that are to be executed against a first database. 
     
     
       14. The computing system of  claim 13 , wherein the plurality of different query classes further comprise a redirected query class for fingerprints corresponding to queries that are to be executed against a second database, which is different than the first database. 
     
     
       15. The computing system of  claim 14 , wherein the analysis module further includes instructions that, when executed by the at least one processor, cause the computing system to:
 cause execution of the query against the first database; and 
 cause execution of the subsequent query request against the second database based on classification of the fingerprint in the redirected query class. 
 
     
     
       16. The computing system of  claim 9 , wherein the analysis module further includes instructions that, when executed by the at least one processor, cause the computing system to:
 prevent analysis of the statistics to classify the fingerprint in response to execution of the query that resulted in the fingerprint being flagged for evaluation. 
 
     
     
       17. A computer-implemented method, the method comprising:
 receive a query that is requested for execution on a database system; 
 defining a fingerprint for the query based at least in part on one or more statements from the query, wherein multiple queries can have the same fingerprint based on the one or more statements but are not identical queries; 
 compare the fingerprint of the received query to fingerprint records in a data structure, wherein the fingerprint records include existing fingerprints for previously received queries; 
 in response to the fingerprint not matching an existing fingerprint:
 execute the query to completion by the database system; 
 determine, by at least the processor, that the query executed has an execution duration that exceeds a completion threshold; 
 storing the fingerprint corresponding to the query in the data structure; 
 flagging the fingerprint for evaluation in response to the execution duration of the query exceeding the completion threshold; 
 
 in response to the fingerprint matching an existing fingerprint:
 identifying the query as a subsequent query request of the same query or a similar query that was previously flagged for evaluation; 
 based on the fingerprint matching and being flagged for evaluation in the data structure, analyzing statistics for the fingerprint to determine whether execution of the subsequent query request will cause an unacceptable negative impact on the database system; and 
 modifying the data structure to classify the fingerprint into one classification of a plurality of different query classes based on a result of the analysis; and 
 
 controlling execution of the subsequent query request based on the one classification of the fingerprint. 
 
     
     
       18. The method of  claim 17 , wherein the fingerprint is classified based on the result of the analysis by assigning the fingerprint to: (i) a permissible query class, or (ii) a blocked query class; and
 wherein (i) if the fingerprint is assigned to the permissible query class, the subsequent query request is to be executed to completion without repeatedly analyzing the statistics, and (ii) if the fingerprint is assigned to the blocked query class, the subsequent query request is to be blocked from being executed. 
 
     
     
       19. The method of  claim 18 , wherein the fingerprints in the permissible query class correspond to queries that are to be executed against a first database, and wherein the different query classes further comprise a redirected query class for fingerprints corresponding to queries designated for execution against a second database, which is different than the first database. 
     
     
       20. The method of  claim 19 , wherein controlling execution of the subsequent query request comprises:
 causing executing of the query corresponding to the fingerprint to completion against the first database; and 
 causing execution of the futurc subsequent query request corresponding to the fingerprint against the second database based on the classification of the fingerprint in the redirected query class.