System and method for providing sql quality analysis and sql optimization in relational database

A SQL quality analyzer system, comprising:

• a processor;
• a memory;
• and
• a communication circuit,
• wherein the processor is configured to:
• receive, via a cloud network, a SQL statement;
• analyze a first execution plan and a second execution plan for the SQL statement prior to execution of the SQL statement;
• detect a presence of at least one first anti-pattern included in the first execution plan and a presence of at least one second anti-pattern included in the second execution plan prior to execution of the SQL statement, wherein the at least one first-anti pattern and the at least one second anti-pattern is stored in a library of anti-patterns;
• determine whether the first execution plan including the detected at least one first anti-pattern is to be executed;
• determine whether the second execution plan including the detected at least one second anti-pattern is not to be executed;
• calculate a quality index value for each of the first execution plan and the second execution plan by performing a weighted analysis of a plurality of attributes including each of a number of anti-patterns detected, impact of the anti-patterns on overall response time, shared resource usage, response time and statistics profile;
• determine whether the quality index value for each of the first execution plan and the second execution plan is greater than or equal to a predetermined threshold value, wherein the predetermined threshold value is automatically adjusted according to an environment type;
• when the quality index value of the first execution plan is determined to be greater than or equal to the predetermined threshold value, the first execution plan is determined to be executed, sent to a production environment, and execute the first execution plan;
• when the quality index value of the second execution plan is determined to be less than the predetermined threshold value, the second execution plan is determined not to be executed and sent to a client terminal without executing the second execution plan for recoding and a list of recommendations for remediation of the at least one second anti-pattern detected is presented on the client terminal,
• wherein the impact of the antipatterns is based on:
• determining a presence of a missing optimizer statistic among optimizer statistics, the optimizer statistics including a collection of data describing a database, and objects included in the database,
• determining whether the optimizer is unable to accurately compute cardinality, and
• determining a number of objects used in the SQL statement;
• and
• based on the determined impact of the anti-patterns with respect to the predetermined threshold value, the library of anti-patterns is adjusted to add or remove one or more anti-patterns.

A non-transitory computer readable storage medium that stores a computer program for performing a projected graph based prediction, the computer program, when executed by a processor, causing a system to perform a process comprising:

• receiving, at a server and via a cloud network, a SQL statement;
• analyzing, at the server, a first execution plan and a second execution plan for the SQL statement prior to execution of the SQL statement;
• detecting a presence of at least one first anti-pattern included in the first execution plan and a presence of at least one second anti-pattern included in the second execution plan prior to execution of the SQL statement, wherein the at least one first-anti pattern and the at least one second anti-pattern is stored in a library of anti-patterns;
• determining whether the first execution plan including the detected at least one first anti-pattern is to be executed;
• determining whether the second execution plan including the detected at least one second anti-pattern is not to be executed;
• calculating a quality index value for each of the first execution plan and the second execution plan by performing a weighted analysis of a plurality of attributes including each of a number of anti-patterns detected, impact of the anti-patterns on overall response time, shared resource usage, response time and statistics profile;
• determining whether the quality index value for each of the first execution plan and the second execution plan is greater than or equal to a predetermined threshold value, wherein the predetermined threshold value is automatically adjusted according to an environment type;
• when the quality index value of the first execution plan is determined to be greater than or equal to the predetermined threshold value, determining that the first execution plan is to be executed and sending the execution plan to a production environment and executing the first execution plan;
• when the quality index value of the second execution plan is determined to be less than the predetermined threshold value, determining that the second execution plan is not to be executed and sending the second execution plan to a client terminal without executing the second execution plan for recoding and presenting a list of recommendations for remediation of the at least one second anti-pattern detected,
• wherein the impact of the antipatterns is based on:
• determining a presence of a missing optimizer statistic among optimizer statistics, the optimizer statistics including a collection of data describing a database, and objects included in the database,
• determining whether the optimizer is unable to accurately compute cardinality, and
• determining a number of objects used in the SQL statement;
• and
• based on the determined impact of the anti-patterns with respect to the predetermined threshold value, the library of anti-patterns is adjusted to add or remove one or more anti-patterns.