Workload reduction mechanism for index tuning
First Claim
Patent Images
1. An automated method for reducing a database workload to a representative set of database statements, the method comprising:
- selecting relevant database statements from the database workload;
processing the relevant database statements into a standardized format;
for each relevant database statement, executing at least one standardization rule from;
substituting a root value for a synonym in a relevant database statement;
substituting a simple operator keyword segment for a complex operator keyword segment in the relevant database statement;
substituting a common parameter for a parameter in the relevant database statement;
reordering a sequence of parameters in the relevant database statement to a predefined, parameter sequence;
standardizing use of delimiters in the relevant database statement;
standardizing a case of each expression in the relevant database statement;
eliminating a superfluous alias in the relevant database statement;
substituting a common alias for an alias in the relevant database statement; and
reordering a sequence of identifiers in the relevant database statement to a predefined, identifier sequence; and
reducing the formatted, relevant database statements into a representative set of database statements.
2 Assignments
0 Petitions
Accused Products
Abstract
A workload reduction mechanism for index tuning is described that selects relevant database statements from a database workload, standardizes a format of the relevant database statements and reduces the formatted, relevant database statements into a representative set of database statements. These statements may then be provided to an index tuning mechanism.
69 Citations
23 Claims
-
1. An automated method for reducing a database workload to a representative set of database statements, the method comprising:
-
selecting relevant database statements from the database workload;
processing the relevant database statements into a standardized format;
for each relevant database statement, executing at least one standardization rule from;
substituting a root value for a synonym in a relevant database statement;
substituting a simple operator keyword segment for a complex operator keyword segment in the relevant database statement;
substituting a common parameter for a parameter in the relevant database statement;
reordering a sequence of parameters in the relevant database statement to a predefined, parameter sequence;
standardizing use of delimiters in the relevant database statement;
standardizing a case of each expression in the relevant database statement;
eliminating a superfluous alias in the relevant database statement;
substituting a common alias for an alias in the relevant database statement; and
reordering a sequence of identifiers in the relevant database statement to a predefined, identifier sequence; and
reducing the formatted, relevant database statements into a representative set of database statements. - View Dependent Claims (2, 3, 4, 5)
selecting a database statement from the database workload if the database statement references a target database table.
-
-
3. The method of claim 1 wherein selecting relevant database statements comprises:
selecting a database statement from the database workload if the database statement includes a Data Manipulation Language (DML) keyword.
-
4. The method of claim 1 wherein reducing the formatted, relevant database statements comprises:
-
providing a hash table;
determining a first hash key for a first one of the formatted, relevant database statements;
storing the first hash key in the hash table;
determining a second hash key for a second one of the formatted, relevant database statements;
comparing the second hash key to the stored, first hash key; and
storing the second hash key in the hash table if there is no match between the second hash key and the stored first hash key.
-
-
5. The method of claim 4 wherein reducing the formatted, relevant database statements further comprises:
modifying a statistic associated with the stored, first hash key if there is a match between the second hash key and the stored first hash key.
-
6. An automated system for providing a reduced database workload to an index tuning mechanism, the system comprising:
-
a database workload source;
a workload filter, fed by the workload source, to select relevant database statements from the workload source;
a formatting system, fed by the workload filter, to standardize a format of the relevant database statements;
the formatting system including at least one of;
a synonym filter to standardize a representation of each occurrence of a synonym in the relevant database statements;
an operator filter to standardize a representation of each occurrence of an operator in the relevant database statements;
a parameter filter to standardize a representation of each occurrence of a parameter in the relevant database statements;
a delimiter filter to standardize usage of a delimiter in the relevant database statements;
a case filter to standardize a case of each expression in the relevant database statements;
an alias filter to standardize a representation of each occurrence of an alias in the relevant database statements; and
an identifier filter to standardize a representation of each occurrence of an identifier in the relevant database statements; and
a mechanism to collapse the standardized database statements into a unique set of statements. - View Dependent Claims (7, 8, 9)
a hash table including a hash key of each representative database statement selected from the relevant database statements and a statistic associated with each hash key.
-
-
8. The system of claim 6 wherein the parameter filter includes rules for replacing bound variables, literals, and user provided variables with a standardized character string.
-
9. The system of claim 6 wherein the parameter filter includes rules for replacing bound variables, literals, and user provided variables with a standardized character string.
-
10. A computer program product for reducing a database workload to a representative set of database statements, the computer program product comprising:
-
a computer usable medium having computer readable code therein, including program code for;
selecting relevant database statements from the database workload;
processing the relevant database statements into a standardized format, for each relevant database statement;
for each relevant database statement, executing at least one standardization rule from;
substituting a root value for a synonym in a relevant database statement;
substituting a simple operator keyword segment for a complex operator keyword segment in the relevant database statement;
substituting a common parameter for a parameter in the relevant database statement;
reordering a sequence of parameters in the relevant database statement to a predefined, parameter sequence;
standardizing a case of each expression in the relevant database statement;
eliminating a superfluous alias in the relevant database statement;
substituting a common alias for an alias in the relevant database statement; and
reordering a sequence of identifiers in the relevant database statement to a predefined, identifier sequence; and
reducing the formatted, relevant database statements into a representative set of database statements. - View Dependent Claims (11, 12, 13, 14)
selecting a database statement from the database workload if the database statement references a target database table.
-
-
12. The computer program product of claim 10 wherein the program code for selecting relevant database statements comprises:
selecting a database statement from the database workload if the database statement includes a Data Manipulation Language (DML) keyword.
-
13. The computer program product of claim 10 wherein the program code for reducing the formatted, relevant database statements comprises:
-
providing a hash table;
determining a first hash key for a first one of the formatted, relevant database statements;
storing the first hash key in the hash table;
determining a second hash key for a second one of the formatted, relevant database statements;
comparing the second hash key to the stored, first hash key; and
storing the second hash key in the hash table if there is no match between the second hash key and the stored first hash key.
-
-
14. The computer program product of claims 13 wherein the program code for reducing the formatted, relevant database statements further comprises:
modifying a statistic associated with the stored, first hash key if there is a match between the second hash key and the stored first hash key.
-
15. An automated system for reducing a database workload to a representative set of database statements, the system comprising:
-
means for selecting relevant database statements from the database workload;
means for processing the relevant database statements into a standardized format;
for each relevant database statement, the means for processing including at least one of;
means for substituting a root value for a synonym in a relevant database statement;
means for substituting a simple operator keyword segment for a complex operator keyword segment in the relevant database statement;
means for substituting a common parameter for a parameter in the relevant database statement;
means for reordering a sequence of parameters in the relevant database statement to a predefined, parameter sequence;
means for standardizing use of delimiters in the relevant database statement;
means for standardizing a case of each expression in the relevant database statement;
means for eliminating a superfluous alias in the relevant database statement;
means for substituting a common alias for an alias in the relevant database statement; and
means for reordering a sequence of identifiers in the relevant database statement to a predefined, identifier sequence; and
means for reducing the formatted, relevant database statements into a representative set of database statements. - View Dependent Claims (16, 17, 18, 19)
means for selecting a database statement from the database workload if the database statement references a target database table.
-
-
17. The system of claim 15 wherein the means for selecting relevant database statements comprises:
means for selecting a database statement from the database workload if the database statement includes a Data Manipulation Language (DML) keyword.
-
18. The system of claim 15 wherein the means for reducing the formatted, relevant database statements comprises:
-
means for providing a hash table;
means for determining a first hash key for a first one of the formatted, relevant database statements;
means for storing the first hash key in the hash table;
means for determining a second hash key for a second one of the formatted, relevant database statements;
means for comparing the second hash key to the stored, first hash key; and
means for storing the second hash key in the hash table if there is no match between the second hash key and the stored first hash key.
-
-
19. The system of claim 18 wherein the means for reducing the formatted, relevant database statements further comprises:
means for modifying a statistic associated with the stored, first hash key if there is a match between the second hash key and the stored first hash key.
-
20. A method for reducing a database workload to a representative set of database statements, the method comprising:
-
selecting relevant database statements from the database workload by selecting a database statement from the database workload if the database statement references a target database table or if the database statement includes a Data Manipulation Language (DML) keyword;
standardizing a format of the relevant database statements by, for each relevant database statement;
substituting a root value for a synonym in a relevant database statement;
substituting a simple operator keyword segment for a complex operator keyword segment in the relevant database statement;
substituting a common parameter for a parameter in the relevant database statement;
reordering a sequence of parameters in the relevant database statement to a predefined, parameter sequence;
standardizing use of delimiters in the relevant database statement;
standardizing a case of each expression in the relevant database statement;
eliminating a superfluous alias in the relevant database statement;
substituting a common alias for an alias in the relevant database statement; and
reordering a sequence of identifiers in the relevant database statement to a predefined, identifier sequence; and
reducing the formatted, relevant database statements into a representative set of database statements. - View Dependent Claims (21, 22)
providing a hash table;
determining a first hash key for a first one of the formatted, relevant database statements;
storing the first hash key in the hash table;
determining a second hash key for a second one of the formatted, relevant database statements;
comparing the second hash key to the stored, first hash key; and
storing the second hash key in the hash table if there is no match between the second hash key and the stored first hash key.
-
-
22. The method of claim 21 wherein reducing the formatted, relevant database statements further comprises:
modifying a statistic associated with the stored, first hash key if there is a match between the second hash key and the stored first hash key.
-
23. A system for providing a reduced database workload to an index tuning mechanism, the system comprising:
-
a database workload source;
a workload filter, fed by the workload source, to select relevant database statements from the workload source;
a formatting system, fed by the workload filter, to standardize a format of the relevant database statements, the formatting system including;
a synonym filter to standardize a representation of each occurrence of a synonym in the relevant database statements;
an operator filter to standardize a representation of each occurrence of an operator in the relevant database statements;
a parameter filter to standardize a representation of each occurrence of a parameter in the relevant database statements;
a delimiter filter to standardize usage of a delimiter in the relevant database statements;
a case filter to standardize a case of each expression in the relevant database statements;
an alias filter to standardize a representation of each occurrence of an alias in the relevant database statements; and
an identifier filter to standardize a representation of each occurrence of an identifier in the relevant database statements; and
a mechanism to collapse the standardized database statements into a unique set of statements, including;
a hash table including a hash key of each representative database statement selected from the relevant database statements and a statistic associated with each hash key.
-
Specification