# US 11,030,195 B2 · Identifying and mitigating high-risk database queries through ranked variance analysis > Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt - Number: US 11,030,195 B2 - Document ID: US11030195B2 - Status: Granted - Kind: B2 - Application number: 15/874,539 - Filed: 2018-01-18 - Published: 2019-07-18 - Granted: 2021-06-08 - Earliest claimed: 2018-01-18 - Assignees: Fmr LLC - Applicants: Fmr LLC - Inventors: Ratnesh Kumar Singh; Ambica Rajagopal; Akhilesh Raghavendrachar Srinivasachar Kaddi; Harikrishnan Choondani Velayudhan; Stephanie Trethaway - Web page: https://patentgrep.com/patent/US11030195B2 ## CPC - G06F 16/24542: {Plan optimisation} - G06F 16/2365: {Ensuring data consistency and integrity} - G06F 16/2453: Query optimisation - G06F 16/2455: Query execution - G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N) ## Abstract Methods and apparatuses are described for system for identifying and mitigating high-risk database queries through ranked variance analysis. A server identifies database queries executed against databases in a production computing environment within a predetermined time period, each database query associated with execution plans and each execution plan having corresponding plan data elements. For each database query: the server generates execution variance data for the execution plans for a database query based upon the corresponding plan data elements, comprising: determining an execution time variance between the execution plans; and determining a buffer gets variance between the execution plans. The server ranks the database queries according to (i) the execution time variance, and (ii) the buffer gets variance. The server computing device identifies high-risk database queries based upon the rank, and adjusts execution settings for each of the identified high-risk database queries to mitigate performance degradation in the production computing environment. ## Claims ### Claim 1 A system for identifying and mitigating high-risk database queries through ranked variance analysis, the system comprising a server computing device that: identifies 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: generates 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; ranks 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; identifies one or more high-risk database queries based upon the rank of the plurality of database queries; and adjusts 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. ### Claim 2 (depends on 1) The system of claim 1, wherein determining the buffer gets variance comprises: identifying the average number of buffer gets for each of the plurality of execution plans for the database query; selecting a maximum average number of buffer gets (MaxABG) and a minimum average execution time (MinABG) from the identified average numbers of buffer gets; generating the variance between MaxABG and MinABG by: ((MaxABG−MinABG)/MinABG; and assigning the determined variance as the buffer gets variance. ### Claim 3 (depends on 2) The system of claim 2, wherein ranking the plurality of database queries comprises sorting the plurality of database queries in a list from a highest value for the execution time variance for each database query to a lowest value for the execution time variance for each database query. ### Claim 4 (depends on 2) The system of claim 2, wherein ranking the plurality of database queries comprises sorting the plurality of database queries in a list from a highest value for the buffer gets variance for each database query to a lowest value for the buffer gets variance for each database query. ### Claim 5 (depends on 3) The system of claim 3, wherein identifying one or more high-risk database queries comprises selecting database queries from the plurality of database queries that are associated with an execution time variance exceeding a predetermined threshold. ### Claim 6 (depends on 4) The system of claim 4, wherein identifying one or more high-risk database queries comprises selecting database queries from the plurality of database queries that are associated with a buffer gets variance exceeding a predetermined threshold. ### Claim 7 (depends on 1) The system of claim 1, wherein adjusting 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 comprises, for each of the identified high-risk database queries: removing one or more of the execution plans associated with the identified high-risk database query from the database in the production computing environment, the removed one or more execution plans being associated with high query execution times and/or high buffer get values, and changing the execution status for the identified high-risk database query in the database to re-enable execution of the identified high-risk database query in the production computing environment. ### Claim 8 (depends on 1) The system of claim 1, wherein adjusting 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 comprises, for each of the identified high-risk database queries: recompiling one or more of the execution plans associated with the identified high-risk database query to generate new execution plans and storing the new execution plans in the database in the production computing environment, and changing the execution status for the identified high-risk database query in the database to re-enable execution of the identified high-risk database query in the production computing environment. ### Claim 9 (depends on 1) The system of claim 1, wherein identifying a plurality of database queries executed against one or more databases in a production computing environment within a predetermined time period comprises: filtering one or more database queries from the plurality of database queries based upon a user ID that executed the database query. ### Claim 10 (depends on 1) The system of claim 1, wherein identifying a plurality of database queries executed against one or more databases in a production computing environment within a predetermined time period comprises: filtering one or more database queries from the plurality of database queries based upon a number of executions of the database query during the predetermined time period. ### Claim 11 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. ### Claim 12 (depends on 11) The method of claim 11, wherein determining the buffer gets variance comprises: identifying, by the server computing device, the average number of buffer gets for each of the plurality of execution plans for the database query; selecting, by the server computing device, a maximum average number of buffer gets (MaxABG) and a minimum average execution time (MinABG) from the identified average numbers of buffer gets; generating, by the server computing device, the variance between MaxABG and MinABG by: ((MaxABG−MinABG)/MinABG; and assigning, by the server computing device, the determined variance as the buffer gets variance. ### Claim 13 (depends on 12) The method of claim 12, wherein ranking the plurality of database queries comprises sorting, by the server computing device, the plurality of database queries in a list from a highest value for the execution time variance for each database query to a lowest value for the execution time variance for each database query. ### Claim 14 (depends on 12) The method of claim 12, wherein ranking the plurality of database queries comprises sorting, by the server computing device, the plurality of database queries in a list from a highest value for the buffer gets variance for each database query to a lowest value for the buffer gets variance for each database query. ### Claim 15 (depends on 13) The method of claim 13, wherein identifying one or more high-risk database queries comprises selecting, by the server computing device, database queries from the plurality of database queries that are associated with an execution time variance exceeding a predetermined threshold. ### Claim 16 (depends on 14) The method of claim 14, wherein identifying one or more high-risk database queries comprises selecting, by the server computing device, database queries from the plurality of database queries that are associated with a buffer gets variance exceeding a predetermined threshold. ### Claim 17 (depends on 11) The method of claim 11, wherein adjusting 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 comprises, for each of the identified high-risk database queries: removing, by the server computing device, one or more of the execution plans associated with the identified high-risk database query from the database in the production computing environment, the removed one or more execution plans being associated with high query execution times and/or high buffer get values, and changing, by the server computing device, the execution status for the identified high-risk database query in the database to re-enable execution of the identified high-risk database query in the production computing environment. ### Claim 18 (depends on 11) The method of claim 11, wherein adjusting 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 comprises, for each of the identified high-risk database queries: recompiling, by the server computing device, one or more of the execution plans associated with the identified high-risk database query to generate new execution plans and storing the new execution plans in the database in the production computing environment, and changing, by the server computing device, the execution status for the identified high-risk database query in the database to re-enable execution of the identified high-risk database query in the production computing environment. ### Claim 19 (depends on 11) The method of claim 11, wherein identifying a plurality of database queries executed against one or more databases in a production computing environment within a predetermined time period comprises: filtering, by the server computing device, one or more database queries from the plurality of database queries based upon a user ID that executed the database query. ### Claim 20 (depends on 11) The method of claim 11, wherein identifying a plurality of database queries executed against one or more databases in a production computing environment within a predetermined time period comprises: filtering, by the server computing device, one or more database queries from the plurality of database queries based upon a number of executions of the database query during the predetermined time period. ### Claim 21 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.