System for categorizing database statements for performance tuning
View Patent ↗A database statement categorization system can enable DBAs or other users to more easily find tunable database statements by categorizing the database statements. The database categorization system can analyze a DBMS to identify the types of database statements executed on the DBMS and can then categorize the statements automatically, outputting the categorization for presentation to a DBA (or other user). This categorization can advantageously separate less relevant statements that warrant less tuning or investigation from more relevant statements that may have a measurable impact on the performance of the database. By allowing users to focus on statements that are more likely to warrant attention, in certain embodiments the database categorization system streamlines the tuning process.
1. A system for categorizing database statements, the system comprising:
a data collector configured to electronically access, from a database management system, a plurality of structured query language (SQL) statements that have executed on the database management system (DBMS);
a statement classifier comprising computer hardware, the statement classifier configured to:
programmatically analyze the SQL statements to identify characteristics of the SQL statements, and
categorize the SQL statements into categories that reflect tunability of the SQL statements based at least in part on the identified characteristics of the SQL statements; and
a tuning user interface configured to be electronically generated for presentation to a user, the tuning user interface comprising a description of the categories, thereby enabling the user to select for tuning a subset of the SQL statements based on the categories.
2. The system of claim 1 wherein the categories comprise a category that represents relatively more tunable statements and a category that represents relatively less tunable statements.
3. The system of claim 2 , wherein the categories comprise one or more of the following categories: system activity, background activity, high rate activity, short lived activity, ad hoc activity, end user activity, moderate activity, and application activity.
4. The system of claim 3 , wherein at least the following categories reflect relatively more tunable statements: moderate activity, ad hoc activity, and end user activity.
5. The system of claim 3 , wherein at least the following categories reflect relatively less tunable statements: system activity, background activity, and short lived activity.
6. The system of claim 1 , wherein the characteristics of the SQL statements comprises one or more of the following: a user who executed a given statement, a database upon which the given statement was executed, a machine that executed the given statement, and text of the statement.
7. A method of categorizing database statements, the method comprising:
by a computer system comprising computer hardware:
electronically accessing, from a database management system, a plurality of database statements executed on the database management system;
programmatically analyzing the database statements to identify characteristics of the database statements;
categorizing the database statements into categories that reflect tunability of the database statements based at least in part on the identified characteristics of the database statements;
electronically generating a tuning user interface that includes a description of the categories; and
outputting the tuning user interface for presentation to a user, thereby enabling the user to select for tuning a subset of the database statements based on the categories.
8. The method of claim 7 , further comprising providing functionality for the user to adjust the categorizations.
9. The method of claim 7 , wherein the categories comprise a tunable category and a non-tunable category.
10. The method of claim 7 , wherein the categories comprise a tunable category and a less-tunable category.
11. The method of claim 7 , wherein the categories comprise one or more of the following categories: system activity, background activity, high rate activity, short lived activity, ad hoc activity, end user activity, moderate activity, and application activity.
12. The method of claim 7 , wherein said programmatically analyzing the database statements to identify characteristics of the database statements comprises identifying one or more of the following: database contexts of the database statements, user contexts of the database statements, machine contexts of the database statements, and internal handles of the statements.
13. The method of claim 7 , wherein the database statements comprise one or more of the following: SQL statements and non-relational statements.
14. The method of claim 7 , wherein the computer system comprises a plurality of computing devices.