Usage and Symptom Oriented SQL Statement Optimization
A system, method, and computer-readable medium are disclosed for providing usage and symptom oriented SQL statement optimization. More specifically, in certain embodiments, a SQL Optimizer is provided with a SQL Statement Usage and Symptom Oriented Tuning Tool. More specifically, the SQL Statement Usage and Symptom Oriented Tuning Tool assists users in composing a correct test run method and selecting a correct resource saving item. Additionally, in certain embodiments, the SQL Statement Usage and Symptom Oriented Tuning Tool includes a tuning tool user interface which allows users to provide the tool with specific information about the performance symptoms and usage patterns of their SQL statement. By providing such a user interface, users do not need to have knowledge of the actual settings used to tune the SQL statement. The SQL Statement Usage and Symptom Oriented Tuning Tool automatically composes settings to provide a SQL statement test run method on behalf of the user.
1 . A computer-implementable method for providing usage and symptom oriented structured query language (SQL) optimization, comprising:
presenting a set of different possible usage patterns and symptoms to a user of a SQL statement;
defining symptoms to SQL statement problems based upon the set of different possible usage patterns presented to the user such that the user does not need to have knowledge of the actual settings used to tune the SQL statement to address an identified problem; and,
providing solutions to the SQL statement problems via a tuning tool, the tuning tool providing solutions without having the user translated the problems into options to be provided to a tuning and benchmarking system.
2 . The method of claim 1 , wherein:
the tuning tool comprises a SQL expert system.
3 . The method of claim 2 , wherein:
the SQL expert system interacts with a test matrix and a user interface when providing the solutions.
4 . The method of claim 3 , wherein:
the user interface allows users to provide the tuning tool with specific information about the performance symptoms and usage patterns of the SQL statement.
5 . The method of claim 1 , wherein:
the tuning tool analyzes where the SQL statement is used, when analyzing where the SQL statement is used, the SQL statement may be used in a report where all records are retrieved from the SQL statement, the SQL statement may be used in an online query program where normally less than 100 records are retrieved for review, but all records may also be retrieved, the SQL statement may be used in an online query program where normally only a specified number of rows are retrieved.
6 . The method of claim 1 , wherein:
the tuning tool analyzes how the SQL statement is used, when analyzing how the SQL statement is used, the SQL statement may be executed within a Procedural Language/Structured Query Language (PL/SQL) block, the SQL statement may be executed as a dynamic SQL statement sent from an application program or the answer may be undefined or all of the options.
7 . A system comprising:
a processor;
a data bus coupled to the processor; and
a non-transitory, computer-readable storage medium embodying computer program code, the non-transitory, computer-readable storage medium being coupled to the data bus, the computer program code interacting with a plurality of computer operations and comprising instructions executable by the processor and configured for:
presenting a set of different possible usage patterns and symptoms to a user of a SQL statement;
defining symptoms to SQL statement problems based upon the set of different possible usage patterns presented to the user such that the user does not need to have knowledge of the actual settings used to tune the SQL statement to address an identified problem; and,
providing solutions to the SQL statement problems via a tuning tool, the tuning tool providing solutions without having the user translated the problems into options to be provided to a tuning and benchmarking system.
8 . The system of claim 7 , wherein:
the tuning tool comprises a SQL expert system.
9 . The system of claim 7 , wherein:
the SQL expert system interacts with a test matrix and a user interface when providing the solutions.
10 . The system of claim 7 , wherein:
the user interface allows users to provide the tuning tool with specific information about the performance symptoms and usage patterns of the SQL statement.
11 . The system of claim 7 , wherein:
the tuning tool analyzes where the SQL statement is used, when analyzing where the SQL statement is used, the SQL statement may be used in a report where all records are retrieved from the SQL statement, the SQL statement may be used in an online query program where normally less than 100 records are retrieved for review, but all records may also be retrieved, the SQL statement may be used in an online query program where normally only a specified number of rows are retrieved.
12 . The system of claim 7 , wherein:
the tuning tool analyzes how the SQL statement is used, when analyzing how the SQL statement is used, the SQL statement may be executed within a Procedural Language/Structured Query Language (PL/SQL) block, the SQL statement may be executed as a dynamic SQL statement sent from an application program or the answer may be undefined or all of the options.
13 . A non-transitory, computer-readable storage medium embodying computer program code, the computer program code comprising computer executable instructions configured for:
presenting a set of different possible usage patterns and symptoms to a user of a SQL statement;
defining symptoms to SQL statement problems based upon the set of different possible usage patterns presented to the user such that the user does not need to have knowledge of the actual settings used to tune the SQL statement to address an identified problem; and,
providing solutions to the SQL statement problems via a tuning tool, the tuning tool providing solutions without having the user translated the problems into options to be provided to a tuning and benchmarking system.
14 . The non-transitory, computer-readable storage medium of claim 13 , wherein:
the tuning tool comprises a SQL expert system.
15 . The non-transitory, computer-readable storage medium of claim 13 , wherein:
the SQL expert system interacts with a test matrix and a user interface when providing the solutions.
16 . The non-transitory, computer-readable storage medium of claim 13 , wherein:
the user interface allows users to provide the tuning tool with specific information about the performance symptoms and usage patterns of the SQL statement.
17 . The non-transitory, computer-readable storage medium of claim 13 , wherein:
the tuning tool analyzes where the SQL statement is used, when analyzing where the SQL statement is used, the SQL statement may be used in a report where all records are retrieved from the SQL statement, the SQL statement may be used in an online query program where normally less than 100 records are retrieved for review, but all records may also be retrieved, the SQL statement may be used in an online query program where normally only a specified lumber of rows are retrieved.
18 . The non-transitory, computer-readable storage medium of claim 13 , wherein:
the tuning tool analyzes how the SQL statement is used, when analyzing how the SQL statement is used, the SQL statement may be executed within a Procedural Language/Structured Query Language (PL/SQL) block, the SQL statement may be executed as a dynamic SQL statement sent from an application program or the answer may be undefined or all of the options.