Automatic database query load assessment and adaptive processing

Abstract

Systems, methods, and other embodiments associated with evaluating and adaptively managing query load are described. In one embodiment, the method includes determining that a query invoked by a client terminal for an application object has a duration that exceeds a completion threshold. In response to receiving a subsequent invocation of the query, statistical information for the query is obtained. The obtained statistical information is analyzed to determine whether the query has an 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 future invocations is controlled based on the classification of the query in the modified data structure.

Description

Technical Field

[0001] Systems and methods are described herein that provide automatic assessment of database query load and adaptive processing of queries to maintain performance of cloud-based application objects. Background Art

[0002] For example, cloud-based applications such as Oracle Service Cloud include an application programming interface (API) that allows users of remote client terminals to specify custom queries to be executed for application objects. Query tools such as RightNow Object Query Language ("ROQL") enable remote client terminals to initiate queries, with few preliminary checks or restrictions on queries that users can create. Due to the lack of preliminary constraints on user-defined queries, it is possible to define and call inefficient or insufficiently defined problematic queries. Executing such problematic queries consumes a large amount of network and database management resources (CPU time, database connections, network bandwidth, network communications, memory, etc.). The large amount of resources consumed by executing problematic queries has a negative impact on the performance of application objects of client terminals that initiate queries and other client terminals that access application objects in the cloud environment at corporate entities. This negative impact may even affect client terminals at different entities that share the affected resources in the cloud application environment.

[0003] Attempting to limit the execution of problematic queries involves establishing a maximum allowed runtime for acceptable queries. All queries with runtimes exceeding the maximum allowed runtime will be terminated. This approach does limit the effect of executing the problematic query. However, performance impacts may still be experienced for a period of time until the runtime of the problematic query reaches the maximum allowed runtime. Repeated execution of the same problematic query subjects the application object to the same negative impact that the application object experienced the first time it invoked the problematic query. In addition, because the problematic query is terminated before completion, the client terminal that invoked the query does not receive the results of the query, although the performance of the application object is negatively impacted. Summary of the invention

[0004] In one embodiment, a non-transitory computer-readable medium stores computer-executable instructions that, when executed by at least a processor of a computing system, cause the computing system to perform the following operations: determining, by at least the processor, that a query executed by a database management system has an execution duration that exceeds a completion threshold; storing, by at least the processor, a fingerprint corresponding to the query in a data structure; marking, by at least the processor, the fingerprint for evaluation in response to the execution duration of the query exceeding the completion threshold; analyzing, in response to receiving a subsequent query request corresponding to the fingerprint and based on the fingerprint being marked, statistical information of the fingerprint to determine whether execution of the subsequent query request will have an unacceptable negative impact on the database management system; modifying, by at least the processor based on a result of the analysis, the data structure to classify the fingerprint into one of a plurality of different query classes; and controlling, by at least the processor, execution of future query requests based on the classification of the fingerprint.

[0005] According to another embodiment, the computer executable instructions further include instructions that, when executed by at least the processor, cause the computing system to perform the following operations: classify the fingerprint based on the results of the analysis by assigning the fingerprint to (i) an allowed query class, or (ii) a blocked query class. If the fingerprint is assigned to the allowed query class, then future queries will be executed to completion without repeating the analysis statistics, and if the fingerprint is assigned to the blocked query class, then future queries will be prevented from being executed.

[0006] According to another embodiment, the statistical information includes query explanation data or database server handler statistics.

[0007] According to another embodiment, the statistical information to be analyzed does not include database performance statistics that require utilization of inactive database resources to obtain.

[0008] According to another embodiment, the plurality of different query classes includes an allowed query class for fingerprints corresponding to queries to be performed against the first database.

[0009] According to another embodiment, the plurality of different query classes further includes a query class for redirection of fingerprints corresponding to queries to be executed against a second database different from the first database.

[0010] According to another embodiment, the computer executable instructions also include instructions that, when executed by at least the processor, cause the computing system to: cause queries to be executed against a first database; and cause future queries to be executed against a second database based on the fingerprint being classified in a redirected query class.

[0011] According to another embodiment, the computer executable instructions further include instructions that, when executed by at least the processor, cause the computing system to: refrain from analyzing the statistical information to classify the fingerprint in response to execution of a query that resulted in the fingerprint being marked for evaluation.

[0012] According to another embodiment, a computing system includes: a processor connected to a memory; and a correction module stored on a non-transitory computer-readable medium and configured with instructions, which when executed by the processor causes the processor to perform the following operations: receive application activity data from multiple remote devices; analyze the application activity data to determine a set of application activities associated with each of the multiple remote devices; analyze the set of application activities to determine one or more challenges associated with a set of remote devices in the multiple remote devices; determine a corrective action for each of the one or more challenges; and generate a corrective action data structure for the set of remote devices based on the corrective action.

[0013] According to another embodiment, a computing system includes: at least one processor connected to at least one memory; and an analysis module stored on a non-transitory computer-readable medium and including instructions, which when executed by the at least one processor causes the computing system to: determine by at least the processor that a query executed by a database management system has an execution duration exceeding a completion threshold; store by at least the processor a fingerprint corresponding to the query in a data structure; mark 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 receiving a subsequent query request corresponding to the fingerprint, and based on the fingerprint being marked, analyze statistical information of the fingerprint to determine whether the execution of the subsequent query request will have an unacceptable negative impact on the database management system; modify by at least the processor the data structure based on the results of the analysis to classify the fingerprint into one of a plurality of different query classes; and control by at least the processor the execution of future query requests based on the classification of the fingerprint.

[0014] According to another embodiment, the analysis module further includes instructions that, when executed by the at least one processor, cause the computing system to perform the following operations: classify the fingerprint based on the results of the analysis by assigning the fingerprint to (i) an allowed query class, or (ii) a blocked query class. If the fingerprint is assigned to the allowed query class, future queries will be executed to completion without repeating the analysis statistics, and if the fingerprint is assigned to the blocked query class, future queries will be prevented from being executed.

[0015] According to another embodiment, the statistical information includes query explanation data or database server handler statistics.

[0016] According to another embodiment, the statistical information to be analyzed does not include database performance statistics that require utilization of inactive database resources to obtain.

[0017] According to another embodiment, the plurality of different query classes includes an allowed query class for fingerprints corresponding to queries to be performed against the first database.

[0018] According to another embodiment, the plurality of different query classes further includes a query class for redirection of fingerprints corresponding to queries to be executed against a second database different from the first database.

[0019] According to another embodiment, the analysis module also includes instructions that, when executed by the at least one processor, cause the computing system to: cause queries to be executed against a first database; and cause future queries to be executed against a second database based on the fingerprint being classified in a redirected query class.

[0020] According to another embodiment, the analysis module further includes instructions that, when executed by the at least one processor, cause the computing system to: refrain from analyzing the statistical information to classify the fingerprint in response to execution of a query that resulted in the fingerprint being marked for evaluation.

[0021] According to another embodiment, a computer-implemented method includes: determining that a query executed by a database management system has an execution duration that exceeds a completion threshold; storing a fingerprint corresponding to the query in a data structure; marking the fingerprint for evaluation in response to the execution duration of the query exceeding the completion threshold; in response to receiving a subsequent query request corresponding to the fingerprint, and based on the fingerprint being marked, analyzing statistical information of the fingerprint to determine whether execution of the subsequent query request will have an unacceptable negative impact on the database management system; modifying the data structure based on the results of the analysis to classify the fingerprint into one of a plurality of different query classes; and controlling the execution of future query requests based on the classification of the fingerprint.

[0022] According to another embodiment, the fingerprint is classified based on the results of the analysis by assigning the fingerprint to (i) an allowed query class, or (ii) a blocked query class. If the fingerprint is assigned to the allowed query class, future queries will be executed to completion without repeating the analysis statistics, and if the fingerprint is assigned to the blocked query class, future queries will be prevented from executing.

[0023] According to another embodiment, the fingerprints in the allowed query class correspond to queries to be executed against a first database, and the different query classes also include a redirected query class for fingerprints corresponding to queries designated for execution against a second database different from the first database.

[0024] According to another embodiment, controlling execution of future queries includes: causing queries corresponding to the fingerprint to be executed against a first database to completion; and causing future queries corresponding to the fingerprint to be executed against a second database based on the fingerprint being classified in a redirected query class.

[0025] The above summary gives a simplified overview in order to provide a basic understanding of some aspects of the systems and / or methods discussed herein. This summary is not an extended overview of the systems and / or methods discussed herein. It is not intended to identify key / important elements or to delineate the scope of such systems and / or methods. Its sole purpose is to present some concepts in a simplified form as a prelude to a more detailed description that will be presented later. BRIEF DESCRIPTION OF THE DRAWINGS

[0026] The accompanying drawings that are incorporated in this specification and form a part thereof illustrate various systems, methods and other embodiments of the present disclosure. It will be appreciated that the element boundaries (e.g., boxes, groups of boxes, or other shapes) illustrated in the figures represent an embodiment of boundaries. In certain embodiments, an element may be implemented as a plurality of elements, or a plurality of elements may be implemented as an element. In certain embodiments, an element that is shown as an internal component of another element may be implemented as an external component, and vice versa. In addition, the element may not be drawn to scale.

[0027] Figure 1 An embodiment of a cloud-based database system is illustrated that automatically assesses and adaptively handles database query loads to efficiently utilize computing and network resources.

[0028] Figures 2A-2F A flow chart graphically depicting an embodiment of a method associated with evaluating and adaptively processing a query load is illustrated.

[0029] Figure 3 One embodiment of a computing system configured with embodiments of the disclosed systems and / or methods is illustrated. DETAILED DESCRIPTION

[0030] Systems and methods are described herein that provide automatic assessment of database query load and adaptive processing of queries to maintain the performance of cloud-based application objects. For example, a cloud-based application (such as Oracle Service Cloud) includes an application programming interface ("API") that allows a user of a remote client terminal to specify custom queries to be executed against an application object. Query tools (such as RightNow Object Query Language ("ROQL")) enable remote client terminals to initiate queries, with few upfront checks or restrictions on the queries that users can create. Therefore, it is inevitable that the execution of some problematic queries will generate a large load on the database, which seriously affects the performance of the application object.

[0031] Previous attempts to block problematic queries have involved adding such queries to a data structure that defines problematic queries, so-called "blacklists," which constitute a class of queries that will be blocked from execution. Calls to blacklisted queries are blocked from execution simply because they take too long to complete. However, adding a query to a blacklist for the sole reason that problematic queries take a long time to complete is too restrictive and results in an unacceptable number of "false positives." A false positive is a query that, while exceeding a threshold runtime, is not the root cause of the extended runtime required to complete the query. After all, there are many factors unrelated to the query (e.g., network traffic, number of concurrent database connections, etc.) that may cause a query's runtime to exceed the threshold runtime.

[0032] Another previous attempt to reduce the number of false positives added to a blacklist has been to create another class of allowed queries defined by a so-called "whitelist." The whitelist includes a collection of allowed queries that will be executed when called by a remote client terminal. In order to be added to the whitelist, the query will be analyzed to determine whether some aspect of the query itself has an unacceptable negative impact on the performance of the application object. However, analyzing each query unnecessarily consumes database and network computing resources and may result in a very negative impact on the performance to be mitigated. For example, a one-time query that is only called once will be analyzed to determine whether the one-time query is added to a blacklist or a whitelist. Computing resources and network resources are consumed to perform the evaluation, thereby negatively affecting the performance of the application object. However, the benefits of performing the analysis will never offset future negative effects because the one-time query will never be called again.

[0033] In addition, the use of a strict blacklist and whitelist structure in a conventional manner may not adequately adapt to database changes or other changes that are unrelated to the parameters of the query itself. Such changes may alter the negative impact of a query on the performance of an application object, even if the query has been previously analyzed and whitelisted. For example, as the data in the database evolves over time (e.g., increases dramatically in size) or the schema of the database changes, subsequent calls to whitelisted queries may cause unacceptable negative impacts on application objects. An adaptive method for handling such future calls to queries should recognize the impact of such changes that occur after the queries have been analyzed and classified. In response to identifying such changes, the adaptive nature of the system and method can automatically update the classification of affected queries to address negative impacts that may be caused by future calls to the query.

[0034] The present disclosure relates to systems and methods for analyzing database queries and controlling the execution of queries that the system suspects will cause a large load on the system. In one embodiment, in response to the second or subsequent execution of a query with a fingerprint that has been evaluated and is considered to have a negative impact on the system, the suspicious identified query is classified. Future execution of the query is controlled based on the classification (e.g., blacklist classification). The system and method use a metric that just exceeds the time it takes to complete the query to determine whether the query should be blacklisted or otherwise classified. According to one embodiment, the system and method use the time required to execute the query when the query is first called to determine whether the query with the fingerprint associated with the query should be analyzed in response to subsequent calls to queries with matching fingerprints. This analysis evaluates the load of executing queries with fingerprints and the impact on the performance of application objects that have multiple concurrent users and can be accessed through a communication network. If the duration of the query called for the first time (or other early instances, such as the second, third, fourth call, etc.) exceeds the completion threshold time, the fingerprint of the query will be specified as being evaluated in a data structure (e.g., a table of queries pending analysis). A query fingerprint may be marked or otherwise designated in a data structure as pending evaluation to determine whether the query corresponding to the query fingerprint is allowed to execute, or should be blocked in response to future calls to the query.

[0035] To mitigate the negative impact on application objects caused by analyzing the fingerprint of a one-time query, queries executed in response to the initial call will be executed until completion, even if the query exceeds the completion threshold time. The results of the fully completed query can be achieved, and the completion notification can be optionally returned to the client via a communication network (such as the Internet, a dedicated network or other suitable communication network). However, query fingerprint analysis is not performed in response to the first call to the query to determine the impact of the query on the application object or system performance, resulting in the fingerprint of the query being designated for evaluation.

[0036] In response to receiving a second or subsequent query, the second or subsequent query includes a fingerprint that has been marked or otherwise specified as requiring evaluation, and the analysis module uses the available information to evaluate the query load corresponding to the fingerprint. For example, the available information may include any low-overhead information, such as query interpretation data and database management server handler statistics (referred to herein as "handler statistics"). Such available information is generated through standard management of the database for the purpose of reading and / or writing data to the database. Query interpretation and handler statistics can be utilized because such data is easy to be used by the analysis module without activating database resources (e.g., network communications, internal tools required for measuring database performance statistics, etc.) specifically for the purpose of collecting data. Activating such database resources specifically to measure database performance itself will bring additional burdens to the system, and it is desirable to reduce such burdens. Therefore, it is optionally possible to exclude from consideration separately measured database performance statistics that are not available for executing queries due to standard operation of the database.

[0037] For queries against a particular fixed set of data in an operational database, the query interpretation data and handler statistics for the query are generally constant compared to the elapsed time of the query. The elapsed time of the query can vary for different invocations of the same query, depending on factors unrelated to the query itself, such as the load of the database when the query is executed. Therefore, the query interpretation data and handler statistics are appropriate considerations when analyzing query fingerprints to determine the true resource load imposed by the query using the query fingerprint itself, and therefore the impact such queries will have on application objects. Although the present system and method use the elapsed time of query execution compared to a threshold as a trigger for evaluating query fingerprints that may be blacklisted, these elapsed times are not the sole determining factor in determining whether a query fingerprint is actually blacklisted. Instead, the interpretation data and handler statistics will be used to perform an analysis that remains consistent for fingerprints without changes to the database or the data in the database. The results of the analysis will be the same regardless of whether the query is executed against an operational database or a reporting database.

[0038] According to another embodiment, other information indicating the load imposed by the query or the impact of the query may be considered as part of the analysis. For example, MySQL performance information may be a factor to be considered during query analysis. "Show profile" or Performance_schema include useful information. However, since these tools introduce overhead by activating internal tools that have not yet been activated to execute the query, but only to collect data, such data may be optionally excluded from the analysis.

[0039] For example, consideration of available information such as query interpretation and handler statistics also allows the present system and method to adapt to changes in the database. For example, changes to data volumes, table statistics, indexes, etc. will be reflected in such available information. The present system and method can detect such changes based on the execution trends of the queries, and specify the query fingerprints affected by the changed data in the data structure for re-evaluation. Further, stale queries that have not been updated after a given number of calls have occurred can also be marked or otherwise specified for re-evaluation in the data structure. The analysis of the query fingerprints described herein can be performed again based on the changed data to re-evaluate the load and potential negative impact of the queries on the application objects.

[0040] Based on the evaluation results of the query fingerprints, the analysis module modifies the data structure to include classifying the query fingerprints into one of a plurality of different query classes. For example, the different query classes may include at least: (i) an allowed query class (e.g., a whitelist), which includes queries that are designated to be executed when subsequently invoked, and (ii) a blocked query class (e.g., a blacklist), which includes queries that are blocked from being executed when subsequently invoked.

[0041] In response to subsequently receiving the same or similar query for execution, the system uses, at least in part, the classification of the query fingerprint to control the execution of queries having such fingerprints. Subsequent queries that classify the query fingerprint as an allowed query class can be executed against an operational database. In response to future invocations of such queries, the execution of queries having query fingerprints classified as blocked query classes can be blocked. For example, when the same or similar query (including a query of the same fingerprint) is submitted for execution and is detected as being in a blocked class, the system blocks the execution of the query. Thus, the system avoids consuming a large amount of computing resources that would be used by executing blocked queries.

[0042] According to an alternative embodiment, the query fingerprint can be classified into a third class, referred to herein as the redirected query class. Queries with query fingerprints in the redirected query class have a negative impact on the application object, but not as severe a negative impact as the negative impact caused by queries with query fingerprints in the blocked query class. Queries with fingerprints in the redirected query class will be executed against a second database, such as, for example, a reporting database that is different from and separate from the operational database. Therefore, the performance of the operational database can be maintained by blocking queries that have a profound negative impact on the application object. Queries with query fingerprints in the redirected query class can still be executed against the reporting database, although with a moderate negative impact.

[0043] refer to Figure 1, shows an embodiment of a cloud-based database system 100 associated with automatically assessing and adaptively processing database query loads to efficiently utilize computing and network resources. The database system 100 is operably connected to communicate with a plurality of client terminals 105, 110, 115 via a communication network 120. The client terminals 105, 110, 115 can receive user-defined parameters for a query and remotely invoke the query against a database 125. Invocation of a query refers to the database system 100 receiving a request to execute a query transmitted by one or more of the client terminals 105, 110, 115. The database 125 can be accessed by a cloud-based application object hosted by the database system 100 or associated with the database system 100.

[0044] According to one embodiment, for example, the client terminal 105, 110, 115 or the database system 100 may be a computing device 300, as shown in FIG. Figure 3 As shown and described. For example, the database system 100 includes a query execution engine 130. The query execution engine 130 can be operated to receive user-defined query parameters and, for example, generate an explain plan for a query to be executed against a database such as the operational database 125 based on the received parameters. The explain plan includes an ordered set of execution steps for accessing data in the database. Each execution step has information about the selection type (e.g., SIMPLE, PRIMARY, UNION, etc.), the join type (e.g., CONST, REF, RANGE, etc.), the table name, and optionally other aspects of the query. The query execution engine 130 determines an execution plan for a query and defines the execution plan in the explain plan when instructed to do so (such as in response to an invocation of an "explain" statement).

[0045] The information included in the explain plan can be included in a generic definition of the query, referred to herein as a "fingerprint" or "query fingerprint". The fingerprint of a query consists of a "normalized" or "generic" signature of the original query. For example, values ​​specific to the called query are excluded from the signature, query tokens are converted to lower case, standard name aliases are applied in the signature, etc. The result is a generic description of the functional aspects of the query, expressed in generic or standardized terms. The generic description of the signature is not specific to the called query and can represent the structure of different queries. For example, the following query:

[0046] SELECT i.id FROM incidents i WHERE due_date>"11-MAY-2018"AND queue_id=3

[0047] Can be turned into a query signature:

[0048] select t1.id fromincidents t1 where t1.due_date>? date and t1.queue_id=? number

[0049] And the fingerprint can be a fixed-size hash of the query signature (e.g., 6f9d069c301dce23bae834f08795a110766de334). After deriving the query fingerprint, the explain plan can be obtained. Therefore, the fingerprint of a query can be considered a unique identifier of the query based at least on the common functions to be performed during the execution of the query.

[0050] In one embodiment, query-specific information and parameters, such as a specific location in a database at which data to be accessed during a specific call to the query may be omitted from the fingerprint, for example. Thus, the fingerprint may indicate functional aspects of the first query, and these functional aspects may be discovered together with one or more other queries that are different from the first query through the query-specific parameters of each query. In other words, two queries may have the same fingerprint but not be identical queries.

[0051] References herein to the evaluation of queries encompass the evaluation of queries or query fingerprints. Information associated with a query (such as an execution plan, statements included in an explanation plan, and other such information used to characterize a query fingerprint) is referred to herein as "query explanation data." Query explanation data for database 125 may be stored in memory or retrieved from memory by an operational explanation module 135 provided to database system 100. In one embodiment, two queries may have the same matching fingerprint but not be identical queries. For example, queries A and B, both having fingerprint XYZ, may have different explanation plans, but the explanation plan resulting from the most recent (or other previous) evaluation is associated with the query.

[0052] Similarly, queries having fingerprints that, if executed, would negatively impact the performance of an application object may be redirected to or otherwise executed against the reporting database 140, as described herein. The reporting database 140 may be configured to store data that is also stored in the operational database 125. However, the reporting database 140 may be used by a database system 100 that is different from the operational database 125. For example, the client terminals 105, 110, 115 may repeatedly access the operational database 125 via the database system 100 to read data from and write data to the operational database 125. The bidirectional communication is generally represented by bidirectional arrows 145. As indicated by arrow 150, at least some of the data maintained by the operational database 125 may be redundantly stored in the reporting database 140. Access to the reporting database 140 may be limited to read operations, as indicated by unidirectional arrow 155. According to another embodiment, the reporting database 140 may execute a role associated with an application object that is role-restricted relative to the operational database 125. Query interpretation data for reporting database 140 may be stored in or retrieved from memory by report interpretation module 160 provided to database system 100 .

[0053] The query interpretation data from the operation interpretation module 135 and the query interpretation data from the report interpretation module 160 may be stored in a data structure as query interpretation data 165. The query interpretation data 165 is linked to the corresponding query corresponding to the query interpretation data 165 and may be accessed by the analysis module 170 for evaluating the impact on the application object by the query having the fingerprint corresponding to the query interpretation data 165. Examples of the query interpretation data 165 include, but are not limited to, the type of key to be resolved into a single row, the type of query invoked, and the identifier of the invoked query.

[0054] The analysis module 170 may also access handler statistics 175 stored in memory for queries executed by the query execution engine 130. The handler statistics 175 may include any data related to how often or how frequently certain data in the database 125 is accessed, read, added, etc. Examples of handler statistics include, but are not limited to: (i) the number of times the first entry in an index is read, (ii) the number of requests to read a row in a table based on a key, (iii) the number of requests to read the last key in an index, (iv) the number of requests to read the next row in key order, (v) the number of requests to read the previous row in key order, (vi) the number of requests to read a row based on a fixed position, (vii) the number of requests to read the next row in a data file, and (viii) the number of requests to insert a row into a table.

[0055] The analysis module 170 is configured to generate a data structure 180 that identifies a query and, if the duration of the query exceeds a completion threshold time after full execution, marks or otherwise designates the query as requiring evaluation. For example, the data structure may include a table of records, wherein the record identifies a fingerprint corresponding to a query that is currently designated as requiring evaluation (marked for evaluation) or previously evaluated and has since been classified as one of a plurality of different query classes. The fingerprint for each query may optionally be designated as requiring evaluation in the data structure in response to the first call of these queries. An evaluation is performed to determine whether the query itself is a substantial cause of an unacceptable negative impact on the application object. Such an evaluation may be postponed until a query utilizing the fingerprint is subsequently called. By postponing the evaluation, the evaluation is not performed in response to the first call of the query (when the fingerprint is designated for evaluation). Therefore, if the query is a one-time query, system and network resources are not consumed to unnecessarily evaluate fingerprints corresponding to queries that will not be called again. Therefore, before evaluating the fingerprint, the system needs to receive at least two queries with the fingerprint.

[0056] Figure 1 The embodiment of the database system 100 shown may be a computing / data processing system including an application or a collection of distributed application objects for an enterprise organization. The application objects and database system 100 may be configured to operate with or implemented as a cloud-based networked system, software as a service ("SaaS") architecture, or other types of networked computing solutions. In one embodiment, the database system 100 provides content to one or more of the client terminals 105, 110, 115, thereby allowing the recipient client terminal 105, 110, 115 to perform at least the functions disclosed herein with respect to receiving query parameters and invoking queries against the database 125. Many client terminals 105, 110, 115 may concurrently access the database system 100 as separate, remotely located computing devices / terminals communicating with the database system 100 (e.g., acting as a server) via a communication network 120.

[0057] In one embodiment, one or more of the components or aspects of the database system 100 described herein may be configured as a program module stored in a non-transitory computer-readable medium. The program module is configured with stored instructions that, when executed by at least one processor, cause one or more computing devices of the database system 100 to perform (one or more) corresponding functions as described herein.

[0058] Figures 2A-2FAn embodiment of a method 200 associated with a database system 100 is illustrated, which automatically evaluates query loads and adaptively processes these query loads to maintain the expected performance of an application object. The method involves evaluating the fingerprint of a query based on query interpretation data 165 and handler statistics 175 to evaluate the query load imposed on the database system 100 or the application object when executing the query. The evaluation of this load is hereinafter referred to as evaluating or determining the "query cost". The fingerprint of an "expensive" or "high-cost" query imposes a large burden on the database system 100, which consumes an undesirable amount of computing resources (e.g., based on a threshold). Therefore, when the query uses an amount of resources exceeding a set threshold amount, the high-cost query is considered to have an unacceptable negative impact on the performance of the application object and / or the system. Computing resources may include, but are not limited to, CPU time, database connections, network bandwidth, network communications, memory, and / or other resources for processing queries. In one embodiment, high-cost queries that are executed are blocked in response to detecting future calls to the same or similar queries to mitigate the poor application object performance experienced by the client 105, 110, 115. In contrast, fingerprinting of "low-cost" or "cheap" queries having a "small cost" is generally considered to be permissible, and fingerprinting is performed in response to detecting future calls to the same or similar low-cost queries.

[0059] exist Figure 2A In block 202, a query request submitted for execution by a client terminal 105 is received by the analysis module 170. In one embodiment, before executing the query, the analysis module 170 intercepts the query request to allow the system to determine whether to control the execution of the query as previously explained. The query request is received via the communication network 120 and includes one or more statements that define one or more operations to be performed during the execution of the query. The statements received as part of the query request define a fingerprint of the query, which the analysis module 170 uses to uniquely identify a combination of operations to be performed as part of the query. At block 204, the fingerprint of the received query is compared to an entry in the data structure 180 that includes fingerprint information for a previously received query to determine whether another query request with the same fingerprint has been previously received.

[0060] If there is no existing record of the fingerprint for the received query request, then the query is executed to completion at block 206 and an error code is obtained that is returned. The error code includes information indicating whether the execution of the query exceeds the completion threshold time. For example, the error code returned may be "OK", which indicates that the time taken for the full execution of the query is less than the completion threshold time. The error code may be "DIVERT" or "TOO LONG", each of which indicates that the duration of the query exceeds the completion threshold time. The error code "TOO LONG" may indicate that the duration of the query execution is so long that future calls to the query should be prevented from being executed against the operational database 125 or the reporting database 140. The error code "DIVERT" may indicate that the duration of the query execution exceeds the completion threshold time and is therefore costly, but the query may still be executed against the reporting database 140 instead of against the operational database 125.

[0061] At block 208, based on the returned error code, the analysis module 170 determines whether the query has exceeded the completion threshold time. If not, then at block 210, the analysis module 170 may optionally generate a data structure 180 ( Figure 1 ), which includes information about the query, such as, for example, a query fingerprint, and designates the query as not requiring evaluation at this time. Instead, the query fingerprint is classified into an "allowed" query class, which includes queries assigned to the whitelist. Whitelisted queries do not have a significant negative impact on the application object and are considered executable against the operational database 125 in response to future calls. Additionally, a negative impact of whitelisted queries is that further evaluation of the query based on the interpretation data and handler statistics is not required to classify these queries in response to a second or subsequent call to a query with the same fingerprint.

[0062] To further improve system performance, the generation of the data structure 180 (or other modification of the data structure) for adding query fingerprints to the whitelist at block 210 may optionally be omitted. In order to maintain a library of whitelisted queries, a significant storage cost would be incurred. Instead of maintaining a library of whitelisted queries, when such a query is invoked, a query associated with a fingerprint that is not blacklisted or otherwise classified as described herein may simply be executed against the operational database 125.

[0063] If the analysis module 170 determines at block 208 that the execution of the query exceeds the completion time threshold, then at block 212, the analysis module 170 generates a data structure 180. The data structure 180 is generated to include information about the query and to set a corresponding flag bit or other type of state designation of the query fingerprint to indicate that the query fingerprint is marked for evaluation. Since this is the first time the query is marked, the query will be fully executed against the operational database 125.

[0064] However, after being marked and designated for evaluation in the data structure 180, if the query is subsequently submitted for execution again, the same or similar query will be evaluated. For example, in response to a second or later call to a query with the same fingerprint in the future, the query fingerprint will be analyzed based on the explanation data and the handler statistics. This additional analysis will determine whether to classify the query fingerprint as a "blocked" query class. The blocked query class includes fingerprints of queries that have been blacklisted. When the same or similar query is subsequently submitted or called for execution by the client terminal 105, 110, 115, this classification by the analysis module 170 will control the operation of the query execution engine 130 to prevent the query with the blacklisted query fingerprint from being executed against the operational database 125 and / or the reporting database 140.

[0065] If in Figure 2B At box 214 in the analysis module 170 determines that the query fingerprint corresponding to the received query request is included in the existing data structure and has been designated as "pending evaluation". Being designated as "pending evaluation" means that the query has been previously marked for evaluation in response to another query request with the fingerprint being received in the future. The query execution engine 130 prepares to obtain handler statistics at box 216 to evaluate the query. The handler statistics collected by the query execution engine 130 include both session values ​​and global values. In order to obtain handler statistics for executing this particular query, the query execution engine 130 can, for example, determine the difference between the statistics to be collected before and after the query.

[0066] According to another embodiment, for example, prior to executing a query, the session counter may be reset to zero using an appropriate command such as "FLUSH STATUS". The handler statistics values ​​collected after executing a query contain only those statistics values ​​attributed to the execution of this particular query. Regardless of which method is used to obtain handler statistics, the use of the query cache should not be considered to obtain accurate handler statistics. If the query cache includes the exact same query, the query execution engine will return the cached results instead of the results for executing the current particular query. Therefore, the session query cache should be temporarily closed to obtain current handler statistics for the current search.

[0067] After appropriate preparations have been made, the query execution engine 130 initiates execution of the received query at block 218 and obtains a returned error code. As mentioned above, the returned error code indicates the time taken to complete execution of the query execution. The error code may indicate that the execution of the query did not exceed the completion threshold time (e.g., "OK"), exceeded the completion threshold time significantly (e.g., "TOO LONG"), or exceeded the completion threshold time but only exceeded a limited margin defined as tolerable (e.g., "DIVERT").

[0068] At block 220, the analysis module 170 retrieves, receives, or otherwise accesses the handler statistics 175 for the current query obtained by the query execution engine 130 at block 220. Similarly, at block 222, the analysis module 170 accesses the query interpretation data 165. The accessed handler statistics 175 and query interpretation data 165 may be temporarily stored in a memory coupled to the analysis module 170 for the current evaluation and updated each time an updated evaluation is required.

[0069] The evaluation performed by the analysis module 170 at block 224 is based on the returned error code, the handler statistics 175, and the explanation data 165 of the currently invoked query. The evaluation of the query or query fingerprint classifies the query or fingerprint as belonging to one of a plurality of different classes. For example, a query may be classified into one of the following four classes:

[0070] Simple query

[0071] The analysis module 170 determines at block 226 whether the query fingerprint corresponds to a simple query. Simple queries are not defined primarily based on cost, but rather use only constants or unique keys that will resolve to a single row at minimal cost. Thus, testing for simple queries can be performed based on the query interpretation data 165, independent of the handler statistics 175. To test a "simple" query, a row or other portion of the query interpretation data can be examined. One embodiment of a simple query requires:

[0072] (1) The first row of the first select ID must have a join_type of "system" or "const" and rows = 1;

[0073] (2) Subsequent rows of the first select ID must have join_type "eq_ref"

[0074] (3) For each selection ID after the first selection ID:

[0075] (a) If select_type is not "UNION", then all rows selecting ID must have join_type "eq_ref;"

[0076] (b) Otherwise, if select_type is "UNION", then:

[0077] (i) The first row of the selection ID must be "system" or "const" and rows = 1

[0078] (ii) Subsequent rows of the select ID must have join_type "eq_ref".

[0079] As a specific example, a query with a fingerprint to "select object by ID" is a simple query. At block 228, the fingerprint for the simple query identified at block 226 is classified as an allowed query class (e.g., added to a whitelist), which means that the query execution engine 130 will utilize the fingerprint to execute queries in response to future calls. At block 228, the data structure 180 for this allowed query is also modified to remove a flag or other designation indicating that this query requires evaluation. Therefore, future calls to the allowed query fingerprint will cause the query execution engine 130 to execute the corresponding query without performing the evaluation performed to classify the query fingerprint as allowed.

[0080] Acceptable Queries

[0081] If it is determined at box 226 that the query is not simple, then an evaluation is performed at box 230 based on the cost of the query to determine whether the query is at least acceptable. Such a cost can be obtained as the sum of weighted handler statistics according to the present embodiment. The query interpretation data for the query can be used to change the weights assigned to the various handler statistics 175. However, according to another embodiment, static weights can be assigned to each different handler statistic. For example, weights can be assigned to handler statistics to indicate the cost associated with each handler statistic. A weight of two (2) can be assigned to handler statistics, such as the number of times the first entry in an index is read ("handler_read_first"). A weight of one (1) can be assigned to statistics for sequential operations, such as the number of requests to read the next row in key order ("handler_read_next"). The handler_read_first statistic has a relatively high cost weight compared to the handler_read_next statistic because the handler_read_first operation requires a data block read to load row information more frequently than the handler_read_next statistic. Items with a weight of one (1), such as handler_read_next, are accessed in sequential order and require only incrementing the row value. In contrast, handler_read_first may involve locating the row with related data instead of locating the next row in the table and is computationally more expensive.

[0082] An acceptable query is a query that is not simple but has a cost that does not exceed an acceptable threshold cost (e.g., has a higher cost than a simple query). Based on weighted handler statistics, the acceptable threshold cost roughly corresponds to a query that can be fully executed in less than X seconds against a database during a period when the load on the database is relatively low. For example, X can be any integer greater than zero (0) and less than thirty (30) (e.g., 1 second, 5 seconds, 10 seconds, etc.). Although specific examples of X are used herein for illustrative purposes, other values ​​can be assigned to X without departing from the scope of the present disclosure.

[0083] If the query is deemed acceptable based on the evaluation at block 230, the classification of the query also depends on the error code returned as a result of the execution of the query. At block 232, the analysis module 170 determines whether the returned error code is "OK" or "DIVERT". If so, the query fingerprint is classified as allowed (e.g., whitelisted) at block 234, and the data structure 180 is modified to reflect this classification and remove the flag or other designation that the query requires evaluation. Thus, the query execution engine 130 can execute future query calls that utilize the query fingerprint without repeating the evaluation.

[0084] If the analysis module 170 determines at block 232 that the error code returned by the current call to the query is any other code (such as "TOO LONG"), then at block 236 the data structure 180 is modified ( Figure 2C ) to reflect the classification of the query fingerprint in the redirected query class. Future calls to the query can be executed, but should be redirected by the query execution engine 130 to the reporting database 140 rather than to the operational database 125.

[0085] Marginal Query

[0086] If at block 230 ( Figure 2B ) determines that the query is unacceptable, then at block 238 ( Figure 2D ) is evaluated to determine whether a query is marginal. The cost evaluation performed to identify a query as marginal is similar to the cost evaluation described for identifying acceptable queries. However, the cost of a marginal query is higher than the cost of an acceptable query. Recall that an acceptable threshold cost roughly corresponds to a query that can be fully executed against a database in less than X seconds during a period when the load on the database is relatively low. The value of X for an acceptable query is defined as any positive integer less than thirty (30). However, in order to be a marginal query, under comparable database load conditions, the value of X can be at least thirty (30) seconds, but less than sixty (60) seconds. Although specific examples of X are used herein for illustrative purposes, other values ​​can be assigned to X without departing from the scope of the present disclosure.

[0087] If the query is deemed to be at least not marginal based on the evaluation at block 238, the query fingerprint is classified into a blocked query class (e.g., blacklisted) at block 240. This classification by the analysis module 170 will cause the query execution engine 130 to prevent the query from being executed against the operational database 125 or the reporting database 140 in response to future calls for the query. Blocked queries are considered to be too computationally expensive to execute and, if executed, would have a significant negative impact on the performance of the application object.

[0088] If the query fingerprint is deemed marginal based on the evaluation at block 238, the classification of the query next depends on the error code returned as a result of the current query execution. At block 242, the analysis module 170 determines whether the returned error code is "OK". If so, the query fingerprint is classified as allowed (e.g., whitelisted) at block 244, and the data structure 180 is modified to reflect this classification and remove the flag or other designation that the query requires evaluation. Thus, future calls to the query fingerprint can be executed by the query execution engine 130 for queries without repeating the classification evaluation in response to those calls.

[0089] If the analysis module 170 determines at block 242 that the error code returned by the current call to the query is any other code (such as "DIVERT" or "TOO LONG"), then the data structure 180 is modified to reflect the classification of the query fingerprint in the redirected query class at block 246. Thus, future calls to the query may be executed, but should be redirected by the query execution engine 130 to be executed against the reporting database 140 instead of the operational database 125.

[0090] The above description addresses situations where a flag or other designation in the data structure 180 for a query indicates that an evaluation is required. Setting the flag or designation may be triggered in response to a long duration of a query when the query is first invoked. Once the query has been evaluated and classified as described above, the flag or other designation is cleared from the data structure 180. Referring again to the decision at block 214 ( Figure 2B ), the present systems and methods may adapt to changes in operational database 125 or reporting database 140 that may result in a change in the classification of a query. For example, since the query fingerprint was classified, the amount of data in operational database 125 or reporting database 140 may have increased significantly. Changes may have been implemented in communication network 120. For example, a large number of additional client terminals may have been added to an enterprise using operational database 125 or reporting database 140. Factors such as these that are unrelated to a query fingerprint that has not changed since classification may affect the correct classification of a query fingerprint. For example, a fingerprint that was previously classified as an allowed query class may now be too expensive to perform against operational database 125 due to the increased amount of data in operational database 125.

[0091] If the analysis module 170 determines at block 214 that the query fingerprint corresponding to the invoked query is not a pending evaluation, then at block 248, the analysis module 170 determines whether the query has been previously classified as a blocked query class (e.g., blacklisted). If so, then at block 250, the analysis module 170 controls the operation of the query execution engine 130 based on the classification in the data structure 180 to prevent the query from being executed against the operational database 125 or the reporting database 140. A notification indicating that the query is not allowed can optionally be transmitted by the database system 100 to notify the client terminal 105, 110, 115 that the query was not executed.

[0092] If the invoked query is not blocked, as determined at block 248, then based on the classification of the corresponding query fingerprint in data structure 180, at block 252 ( Figure 2E ) to execute the query. For example, if the classification for the corresponding query fingerprint is "allowed," then the query is executed against the operational database 125 without making the above evaluation in response to the current invocation of the query. Otherwise, if the classification for the corresponding query fingerprint is "divert," then the query is executed but redirected to be executed against the reporting database 140 instead of the operational database 125. Again, the query may be executed in response to the current invocation of the query without making the above evaluation.

[0093] The data structure 180 for the executed query is analyzed by the analysis module 170 to determine whether the fingerprint for the query was initially determined to be a simple query or an acceptable (e.g., whitelisted) query at block 254. If so, the analysis module 170 then determines whether the classification in the data structure 180 of the query fingerprint needs to be re-evaluated based at least in part on: (i) the returned error code for the current call to the corresponding query, and (ii) the current classification of the query fingerprint.

[0094] At block 256, the analysis module 170 determines whether the execution time of the query exceeds the completion threshold time. If so, the execution time of the query with the fingerprint previously classified as "allowed" now exceeds the completion threshold time. As a result of this situation, changes unrelated to the structure of the query fingerprint may have occurred, and the classification of the query fingerprint stored in the data structure 180 may need to be modified. Similarly, at block 254, the analysis module 170 may determine that the query executed is not a simple or acceptable (e.g., not whitelisted) query, but a query classified as a deflected query class. However, as determined at block 264, the query currently invoked may result in an "OK" error being returned. The return error code "OK" is more indicative of a simple or acceptable query than a query classified in the deflected query class. Again, changes unrelated to the structure of the corresponding query fingerprint may have occurred, and the classification of the query fingerprint in the data structure 180 may need to be modified.

[0095] To determine whether the classification should be modified, explanation data for the current invocation of the query is obtained by the analysis module 170. At block 258, the analysis module 170 compares the obtained explanation data for the current invocation with explanation data corresponding to the existing classification of the query fingerprint stored in the data structure 180. If the comparison at block 260 indicates that the explanation data corresponding to the query fingerprint of the current invocation is different from the explanation data of the query invocation that resulted in the classification, then a flag or other designation may be reset at block 262. Resetting the flag or other designation in the data structure 180 indicates that the query fingerprint once again requires evaluation. Resetting the flag or other designation will cause the analysis module 170 to reset the query fingerprint at block 214 ( Figure 2B ) determines that the query fingerprint requires evaluation. This determination causes the analysis module 170 to begin the evaluation process described herein in response to future invocations of queries having the subject query fingerprint. Repeatedly designating query fingerprints for evaluation in response to evolving triggering events allows query loads that may result from changes in the database environment to be adaptively handled even if the query fingerprint itself has not changed.

[0096] The determination at block 256 may indicate that the execution time of the query did not exceed the completion threshold time expected for allowed queries. Similarly, the error code returned for a query in the deflected query class may indicate that the execution time of the query did exceed the completion threshold time at block 264, which is also expected. Even if such queries are executed as expected, the query fingerprint corresponding to each of these queries may still be re-evaluated to improve the performance of the application object.

[0097] The query currently being invoked may have a query fingerprint that is to be re-evaluated based on trends related to the query, rather than the query being the result of a single invocation of the triggering event. For example, the data structure 180 for the query fingerprint may include a counter field. Each invocation of the query executed causes a return code to be returned for the corresponding query fingerprint. A running total of error codes returned for the query fingerprint may be stored in the counter field and incremented as each error code is returned at block 266 ( Figure 2F ). Once a predetermined number of error codes have been returned for a query fingerprint, the trend of the error codes may be analyzed to determine whether the query fingerprint should be re-evaluated. As part of this analysis, at block 268, the analysis module 170 determines whether at least a timeout threshold percentage of the total number of error codes returned for the query fingerprint is TOO LONG. If so, then at block 270, the analysis module 170 determines whether the cost of the query fingerprint for the currently executing query is at least the same as the cost of the marginal query. If so, then at block 272, the data structure 180 for the query fingerprint is modified to classify the query fingerprint as a blocked query class at block 272, and set a flag or other designation indicating that the query fingerprint needs to be evaluated.

[0098] If the timeout threshold percentage of returned error codes is not met at block 268, the analysis module 170 determines whether the combined number of DIVERT error codes and TOO LONG error codes constitutes at least the combined threshold percentage of all returned error codes at block 274. If so, and if it is determined at block 276 that the cost of the query fingerprint of the currently executing query is at least as expensive as the marginal query, then the data structure 180 for the query fingerprint is modified at block 278. At block 278, the data structure 180 is modified to classify the query fingerprint as a redirected query class, and a flag or other designation is set indicating that the query fingerprint requires evaluation.

[0099] If neither the timeout threshold percentage at block 268 nor the combined threshold percentage of all returned error codes at block 274 is met after a predetermined number of error codes have been returned for a query fingerprint, then the data structure 180 may also be modified. For example, at block 280, the data structure 180 may be modified to classify the query fingerprint corresponding to the currently executing query in an allowed query class. A flag or other designation indicating that the query fingerprint requires evaluation in response to future calls to the corresponding query may also be set at block 280.

[0100] Similar modifications may also be made at block 280 in response to different query trends. For example, after a predetermined number of error codes have been returned for a query fingerprint, and if a timeout threshold percentage has not been met at block 268, the cost of the query fingerprint may be analyzed. If it is determined at block 270 that the cost of the query fingerprint corresponding to the currently executing query is lower than the cost of the marginal query, the data structure 180 may be modified to classify the query fingerprint corresponding to the currently executing query in the allowed query class. Such a scenario indicates a previously classified query that exhibits a trend of decreasing impact on the performance of the application object. A flag or other designation may also be set at block 280 indicating that the query fingerprint needs to be evaluated in response to future calls to the corresponding query.

[0101] As another example, after a predetermined number of error codes have been returned for a query fingerprint, if: (i) the timeout threshold percentage has not been met at block 268, (ii) the combined threshold percentage has not been met at block 274, and (iii) the cost of the query fingerprint corresponding to the currently executing query is determined to be lower than the cost of the marginal query at block 276, then the data structure 180 can be modified at block 280. The data structure 180 can be modified to classify the query fingerprint corresponding to the currently executing query in the allowed query class. Such a scenario also indicates a previously classified query that exhibits a trend of decreasing impact on the performance of the application object. A flag or other designation indicating that the query fingerprint needs to be evaluated in response to future calls to the corresponding query can also be set at block 280.

[0102] Computing Device Embodiments

[0103] Figure 3 An example computing device configured and / or programmed with one or more of the example systems and methods described herein and / or equivalents is illustrated. The example computing device may be a computer 300 that includes a processor 320, a memory 335, and an input / output port 345 operably connected via a bus 325. In one example, the computer 300 may include logic defining the query execution engine 130 and the analysis module 170, which is configured to facilitate query load evaluation and adaptive processing of the query load, similar to Figure 1 325. In various examples, the logic defining the query execution engine 130 and the analysis module 170 may be implemented in hardware, a non-transitory computer-readable medium 305 having stored instructions, firmware, and / or a combination thereof. Although the logic defining the query execution engine 130 and the analysis module 170 is shown as a hardware component attached to the bus 325, it should be appreciated that in other embodiments, the logic may be implemented in the processor 320, stored in the memory 335, or stored in the disk 355.

[0104] In one embodiment, the logic or computing device 300 defining the query execution engine 130 and the analysis module 170 is a means (e.g., structure: hardware, non-transitory computer readable medium, firmware) for performing the described actions. In some embodiments, the computing device may be a server operating in a cloud computing system, a server configured in a software as a service (SaaS) architecture, a smart phone, a laptop computer, a tablet computing device, etc.

[0105] The apparatus may be implemented, for example, as an ASIC programmed to evaluate and adaptively process query loads to mitigate negative impacts on the performance of application objects. The apparatus may also be implemented as stored computer executable instructions that are presented to the computing device 300 as data 310, which are temporarily stored in the memory 335 and then executed by the processor 320.

[0106] The logic of the query execution engine 130 and the analysis module 170 may also provide means (eg, hardware, non-transitory computer-readable media storing executable instructions, firmware) for performing the evaluation and adaptive processing of the query load.

[0107] An example configuration of the computer 300 is generally described, and the processor 320 can be a variety of processors including dual microprocessors and other multi-processor architectures. The memory 335 can include volatile memory and / or non-volatile memory. The non-volatile memory can include, for example, ROM, PROM, etc. The volatile memory can include, for example, RAM, SRAM, DRAM, etc.

[0108] Storage disk 355 may be operably connected to computing device 300 via, for example, input / output (I / O) interface (e.g., card, device) 340 and input / output port 345. Disk 355 may be, for example, a magnetic disk drive, a solid state disk drive, a floppy disk drive, a tape drive, a Zip drive, a flash memory card, a memory stick, etc. In addition, disk 355 may be a CD-ROM drive, a CD-R drive, a CD-RW drive, a DVD ROM, etc. Memory 335 may, for example, store processes and / or data 310. Disk 355 and / or memory 335 may store an operating system that controls and allocates resources of computing device 300.

[0109] The computing device 300 may interact with input / output (I / O) devices via an I / O interface 340 and input / output ports 345, which are connected to the bus 325 through an I / O controller 330. The input / output devices may be, for example, keyboards, microphones, pointing and selection devices, cameras, video cards, displays, disks 355, network devices 350, etc. The input / output ports 345 may include, for example, serial ports, parallel ports, and USB ports.

[0110] The computing device 300 can operate in a network environment and thus can be connected to a network device 350 via an I / O interface 340 and / or an I / O port 345. Through the network device 350, the computing device 350 can interact with a network. Through the network, the computing device 300 can be logically connected to a remote computer, such as a client terminal 105, 110, 115. The networks with which the computing device 300 can interact include, but are not limited to, LANs, WANs, and other networks.

[0111] Definitions and Other Examples

[0112] In another embodiment, the described methods and / or their equivalents may be implemented with computer executable instructions. Thus, in one embodiment, a non-transitory computer readable / storage medium is configured with computer executable instructions of a stored algorithm / executable application, which when executed by (one or more) machines causes the (one or more) machines (and / or associated components) to perform the described methods. Example machines include, but are not limited to, processors, computers, servers operating in a cloud computing system, servers configured with a software as a service (SaaS) architecture, smart phones, etc. In one embodiment, a computing device is implemented with one or more executable algorithms configured to perform any of the disclosed methods.

[0113] In one or more embodiments, the disclosed methods or their equivalents are performed by any of the following: computer hardware configured to perform the methods; or computer instructions embodied in a module stored in a non-transitory computer-readable medium, wherein the instructions are configured as an executable algorithm that is configured to perform the methods when executed by at least one processor of a computing device.

[0114] Although for the purpose of simplifying the description, the method shown in the accompanying drawings is shown and described as a series of square blocks of the algorithm, it should be appreciated that these methods are not limited by the order of the square blocks. Some square blocks may appear in a different order than shown and described and / or occur simultaneously with other square blocks. Moreover, the example method can be implemented using square blocks less than all the illustrated square blocks. Square blocks can be combined or divided into multiple actions / components. In addition, additional and / or alternative methods can adopt additional actions not illustrated in the square blocks.

[0115] The following includes definitions of selected terms used herein. The definitions include various examples and / or forms of components that fall within the scope of the terms and can be used for implementation. The examples are not intended to be limiting. Both singular and plural forms of the terms may be within the definitions.

[0116] References to "one embodiment," "an embodiment," "an example," "an example," etc. indicate that the embodiment(s) or examples described herein may include particular features, structures, characteristics, properties, elements, or limitations, but not every embodiment or example necessarily includes the particular features, structures, characteristics, properties, elements, or limitations. Furthermore, repeated use of the phrase "in one embodiment" does not necessarily refer to the same embodiment, but may.

[0117] As used herein, a "data structure" is an organization of data stored in a memory, storage device, or other computerized system in a computing system. A data structure can be, for example, any of a data field, a data file, a data array, a data record, a database, a data table, a graph, a tree, a linked list, etc. A data structure can be formed by and include many other data structures (e.g., a database includes many data records). Other examples of data structures are also possible according to other embodiments.

[0118] As used herein, "computer-readable medium" or "computer storage medium" refers to a non-transitory medium that stores instructions and / or data configured to perform one or more functions of the disclosed functions when executed. In some embodiments, data can be used as instructions. Computer-readable media can take the form of, but not limited to, non-volatile media and volatile media. Non-volatile media can include, for example, optical disks, magnetic disks, etc. Volatile media can include, for example, semiconductor memories, dynamic memories, etc. Common forms of computer-readable media can include, but are not limited to, floppy disks, flexible disks, hard disks, magnetic tapes, other magnetic media, application specific integrated circuits (ASICs), programmable logic devices, compact disks (CDs), other optical media, random access memories (RAMs), read-only memories (ROMs), memory chips or cards, memory sticks, solid-state storage devices (SSDs), flash drives, and other media that computers, processors, or other electronic devices can work with. If each type of medium is selected for implementation in one embodiment, it can include storage instructions for algorithms configured to perform one or more functions of the disclosed and / or claimed functions.

[0119] As used herein, "logic" means a component implemented using a computer or electrical hardware, a non-transient medium with instructions of an executable application or program module stored, and / or a combination of these, to perform any function or action as disclosed herein, and / or to cause a function or action from another logic, method, and / or system to be performed as disclosed herein. Equivalent logic may include firmware, a microprocessor programmed with an algorithm, discrete logic (e.g., ASIC), at least one circuit, an analog circuit, a digital circuit, a programmed logic device, a memory device containing instructions of an algorithm, etc., any of which may be configured to perform one or more functions of the disclosed functions. In one embodiment, logic may include one or more gates, a combination of gates, or other circuit components configured to perform one or more functions of the disclosed functions. In the case of describing multiple logics, it is possible to merge multiple logics into one logic. Similarly, in the case of describing a single logic, it is possible to distribute the single logic between multiple logics. In one embodiment, one or more of these logics are corresponding structures associated with performing the disclosed and / or claimed functions. Choosing which type of logic to implement can be based on desired system conditions or specifications. For example, if higher speed is considered, hardware will be selected to implement the function. If lower cost is a consideration, stored instructions / executable applications will be chosen to implement the functionality.

[0120] An "operable connection" or a connection through which entities are "operably connected" is a connection in which signals, physical communications, and / or logical communications can be sent and / or received. An operable connection may include a physical interface, an electrical interface, and / or a data interface. An operable connection may include different combinations of interfaces and / or connections sufficient to allow operable control. For example, two entities may be operably connected to transmit signals to each other directly or through one or more intermediate entities (e.g., a processor, an operating system, logic, a non-transitory computer-readable medium). Logical communication channels and / or physical communication channels may be used to create an operable connection.

[0121] As used herein, a "user" includes, but is not limited to, one or more persons, computers or other devices, or a combination of these.

[0122] Although the disclosed embodiments have been illustrated and described in considerable detail, it is not intended that the scope of the appended claims be limited or in any way restricted to such details. Of course, it is not possible to describe every expected combination of components or methods for the purpose of describing various aspects of the subject matter. Therefore, the present disclosure is not limited to the specific details or illustrative examples shown and described. Therefore, the present disclosure is intended to cover changes, modifications and variations that fall within the scope of the appended claims.

[0123] To the extent that the term "comprising" is employed in the detailed description or claims, it is intended to be inclusive in a manner similar to how the term "comprises" is interpreted when employed as a transitional word in a claim.

[0124] To the extent the term "or" is employed in the detailed description or claims (e.g., A or B), it is intended to mean "A or B or both." When the applicants intend to indicate "only A or B but not both," then the phrase "only A or B but not both" will be used. Thus, the use of the term "or" herein is inclusive, rather than exclusive.

Claims

1. A computer-implemented method, the method comprising: receiving a query requested to be executed on a database system; defining a fingerprint of the query based on one or more statements from the query, wherein the fingerprint is a unique identifier of the query based on at least the one or more statements from the query; comparing the fingerprint of the received query to a fingerprint record in a data structure, wherein the fingerprint record includes existing fingerprints of previously received queries; In response to the fingerprint not matching an existing fingerprint: executing the query by the database system to completion; determining that the query that has been executed has an execution duration that exceeds a completion threshold; storing a fingerprint corresponding to the query in a data structure; marking the fingerprint for evaluation in response to an execution duration of the query exceeding a completion threshold; Responsive to the fingerprint matching an existing fingerprint, identifying the query as a previously received query: (i) determining whether the fingerprint has been previously marked for evaluation; (ii) in response to the fingerprint being previously marked for evaluation: (a) evaluating statistical information of the fingerprint to determine whether execution of the query request will cause a negative impact on the database system; as well as (b) modifying a data structure based on the results of the analysis to classify the fingerprint into one of a plurality of different query classes, wherein the query classes include (i) an allowed query class, (ii) a blocked query class, and (iii) a redirected query class; as well as (iii) controlling the execution of future query requests based on the same fingerprint based on the classification of the fingerprint.

2. The method of claim 1, wherein: Controlling the execution of future query requests includes: (i) if the fingerprint is assigned to the allowed query class, then the future queries will be executed on the first database without analyzing the statistical information, (ii) if the fingerprint is assigned to the blocked query class, then the future queries will be prevented from being executed, and (iii) if the fingerprint is assigned to the redirected query class, then the future queries will be executed on a second database different from the first database.

3. The method of claim 1, wherein the statistical information comprises query explanation data or database management server handler statistical information, wherein the database management server handler statistical information comprises data related to how often or how frequently data in the database is accessed, read and / or added.

4. The method of claim 1, wherein the statistical information to be analyzed does not include database performance statistics that require utilization of inactive database resources in order to be obtained.

5. A non-transitory computer-readable medium storing computer-executable instructions, which, when executed by at least a processor of a computing system, cause the computing system to perform the method according to any one of claims 1 to 4.

6. A computing system comprising: at least one processor connected to at least one memory; as well as An analysis module stored on a non-transitory computer readable medium and comprising instructions that, when executed by the at least one processor, cause the computing system to perform the method of any one of claims 1-4.

7. An apparatus comprising means for performing the method as claimed in any one of claims 1 to 4.

Images