Workload reduction mechanism for index tuning

• US 6,598,038 B1
• Filed: 09/17/1999
• Issued: 07/22/2003
• Est. Priority Date: 09/17/1999
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Wilson, Geoffrey, Smith, Gregory, Rossiter, Jay, Guay, Todd
• Primary Examiner(s): ALAM, SHAHID AL
• Application Number:
  US09/398,616
• Time in Patent Office: 1,404 Days
• Field of Search: 707/2-6, 707/10, 707/100-104, 707/201, 707/205, 711/121, 711/122, 711/144, 714/11, 717/141
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2272 Management thereof
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99953 Recoverability

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.

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.

2. The method of claim 1, wherein selecting relevant database statements comprises:

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.

7. The system of claim 6 wherein the collapse mechanism further includes:

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.

11. 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 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.

16. 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 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.

21. The method of claim 20 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.

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.