Identifying and mitigating high-risk database queries through ranked variance analysis

A computerized method for identifying and mitigating high-risk database queries through ranked variance analysis, the method comprising:

• identifying, by a server computing device, a plurality of database queries executed against one or more databases in a production computing environment within a predetermined time period, each database query associated with a plurality of execution plans, each execution plan having corresponding plan data elements comprising a query id number, a parsing schema, a number of plan executions, an average execution time, an average number of buffer gets, and a query text;
• for each database query:
• generating, by the server computing device, execution variance data for the plurality of execution plans for a database query based upon the corresponding plan data elements, comprising:
• determining an execution time variance between the plurality of execution plans for the database query, comprising:
• identifying the average execution time for each of the plurality of execution plans for the database query;
• selecting a maximum average execution time (MaxAET) and a minimum average execution time (MinAET) from the identified average execution times;
• determining a variance between MaxAET and MinAET by:
• (MaxAET−MinAET)/MinAET;
• and
• assigning the determined variance as the execution time variance;
• and
• determining a buffer gets variance between the plurality of execution plans for the database query;
• ranking, by the server computing device, the plurality of database queries according to (i) the execution time variance for each database query, and (ii) the buffer gets variance for each database query;
• identifying, by the server computing device, one or more high-risk database queries based upon the rank of the plurality of database queries;
• and
• adjusting, by the server computing device, one or more execution settings for each of the identified high-risk database queries to mitigate performance degradation in the production computing environment caused by execution of the identified high-risk database queries, comprising changing an execution status for each of the identified high-risk database queries in the database which prevents the identified high-risk database queries from being executed in the production computing environment, and
• when the production computing environment requests execution of any of the identified high-risk database queries, analyzing, by the production computing environment, the execution status for the requested high-risk database queries and determining that the requested high-risk database queries are unavailable for execution.

A computer program product, tangibly embodied in a non-transitory computer readable storage device, for identifying and mitigating high-risk database queries through ranked variance analysis, the computer program product including instructions that, when executed by a processor of a server computing device, cause the server computing device to:

• identify a plurality of database queries executed against one or more databases in a production computing environment within a predetermined time period, each database query associated with a plurality of execution plans, each execution plan having corresponding plan data elements comprising a query id number, a parsing schema, a number of plan executions, an average execution time, an average number of buffer gets, and a query text;
• for each database query:
• generate execution variance data for the plurality of execution plans for a database query based upon the corresponding plan data elements, comprising:
• determining an execution time variance between the plurality of execution plans for the database query, comprising:
• identifying the average execution time for each of the plurality of execution plans for the database query;
• selecting a maximum average execution time (MaxAET) and a minimum average execution time (MinAET) from the identified average execution times;
• determining a variance between MaxAET and MinAET by:
• (MaxAET−MinAET)/MinAET;
• and
• assigning the determined variance as the execution time variance;
• and
• determining a buffer gets variance between the plurality of execution plans for the database query;
• rank the plurality of database queries according to (i) the execution time variance for each database query, and (ii) the buffer gets variance for each database query;
• identify one or more high-risk database queries based upon the rank of the plurality of database queries;
• and
• adjust one or more execution settings for each of the identified high-risk database queries to mitigate performance degradation in the production computing environment caused by execution of the identified high-risk database queries, comprising changing an execution status for each of the identified high-risk database queries in the database which prevents the identified high-risk database queries from being executed in the production computing environment, and
• when the production computing environment requests execution of any of the identified high-risk database queries, the production computing environment analyzes the execution status for the requested high-risk database queries and determines that the requested high-risk database queries are unavailable for execution.