Global hints

• US 7,739,263 B2
• Filed: 09/07/2004
• Issued: 06/15/2010
• Est. Priority Date: 09/06/2003
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Das, Dinesh, Zait, Mohamed, Dageville, Benoit, Ziauddin, Mohamed
• Primary Examiner(s): Cottingham; John R.
• Assistant Examiner(s): LOVEL, KIMBERLY M
• Application Number:
  US10/936,781
• Publication Number:
  US 20050125398A1
• Time in Patent Office: 2,107 Days
• Field of Search: 707/2
• US Class Current: 707/713
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 16/24549 Run-time optimisation
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure

Abstract

A method for determining a name for a query block of a database query language statement, and associating one or more tuning hints with the query block using the name.

Claims

1. A computer implemented method, comprising:

associating a query language statement with a query profile;

determining a query block for the query language statement, wherein

the query block is a part of a rewritten query language statement for optimization and is generated or transformed from one or more other query blocks during compilation of the query language statement to become a part of the query language statement, and

the act of determining the query block is performed by a processor;

associating one or more tuning hints with the query block;

resolving an unresolved hint in conflict with another tuning hint, wherein the unresolved hint is stored in a hint data structure on a tangible computer readable storage medium or a computer storage device; and

storing the one or more tuning hints for the query block, wherein

at least one of the one or more tuning hints associated with the query block is retrieved for and applied to the query block during the compilation of the query language statement, and

the part of the rewritten query language statement is not the entire rewritten query language statement.

2. The method of claim 1, further comprising:

transforming the query block from a parent block into a child block during compilation of the statement.

3. The method of claim 2, further comprising:

determining a name for the child block based on a transformation applied to the parent block and a name of the parent block.

4. The method of claim 3, further comprising:

associating one or more tuning actions with the child block using the name of the child block.

5. The method of claim 1, further comprising:

associating the one or more tuning hints with a plurality of query language statements.

6. The method of claim 5, further comprising:

determining a name for each query block of each statement; and

associating the one or more tuning hints with one or more query blocks using the query block names.

7. The method of claim 1, wherein the query language statement is a SQL statement.

8. An apparatus, comprising:

at least a processor configured for performing;

associating a query language statement with a query profile;

determining a query block for a query language statement, wherein

the query block is a part of a rewritten query language statement for optimization and is generated or transformed from one or more other query blocks during compilation of the query language statement to become a part of the query language statement;

associating one or more tuning hints with the query block;

storing the one or more tuning hints for the query block, wherein at least one of the one or more tuning hints associated with the query block is retrieved for and applied to the query block during the compilation of the query language statement;

resolving an unresolved hint in conflict with another tuning hint, wherein;

the part of the rewritten query language statement is not the entire rewritten query language statement; and

a computer readable storage medium or a computer readable storage device configured for storing the unresolved hint in a hint data structure.

9. The apparatus of claim 8, in which the processor is further configured for performing:

transforming the query block from a parent block into a child block during compilation of the statement.

10. The apparatus of claim 9, in which the processor is further configured for performing:

determining a name for the child block based on a transformation applied to the parent block and a name of the parent block.

11. The apparatus of claim 10, in which the processor is further configured for performing:

associating one or more tuning actions with the child block using the name of the child block.

12. The apparatus of claim 8, in which the processor is further configured for performing:

associating the one or more tuning hints with a plurality of query language statements.

13. The apparatus of claim 12, in which the processor is further configured for performing:

determining a name for each query block of each statement; and

associating the one or more tuning hints with one or more query blocks using the query block names.

14. The apparatus of claim 8, wherein the query language statement is a SQL statement.

15. A volatile or non-volatile computer readable medium storing a computer program of instructions which, when executed by a processing system, cause the system to perform a method comprising:

associating a query language statement with a query profile;

determining a query block for the query language statement, wherein

the query block is a part of a rewritten query language statement for optimization and is generated or transformed from one or more other query blocks during compilation of the query language statement to become a part of the query language statement;

associating one or more tuning hints with the query block;

resolving an unresolved hint in conflict with another tuning hint, wherein the unresolved hint is stored in a hint data structure; and

storing the one or more tuning hints for the query block, wherein

at least one of the one or more tuning hints associated with the query block is retrieved for and applied to the query block during the compilation of the query language statement, and

the part of the rewritten query language statement is not the entire rewritten query language statement.

16. The medium of claim 15, wherein the computer program, when executed, further performs the method comprising:

transforming the query block from a parent block into a child block during compilation of the statement.

17. The medium of claim 16, wherein the computer program, when executed, further performs the method comprising:

determining a name for the child block based on a transformation applied to the parent block and a name of the parent block.

18. The medium of claim 17, wherein the computer program, when executed, further performs the method comprising:

associating one or more tuning actions with the child block using the name of the child block.

19. The medium of claim 18, wherein the computer program, when executed, further performs the method comprising:

determining a name for each query block of each statement; and

associating the one or more tuning hints with one or more query blocks using the query block names.

20. The medium of claim 15, wherein the computer program, when executed, further performs the method comprising:

associating the one or more tuning hints with a plurality of query language statements.

21. The medium of claim 15, wherein the query language statement is a SQL statement.

22. A computer system comprising:

at least a processor configured for performing;

associating a query language statement with a query profile;

determining a query block for the query language statement, wherein

the query block is a part of a rewritten query language statement for optimization and is generated or transformed from one or more other query blocks during compilation of the query language statement to become a part of the query language statement;

associating one or more tuning hints with the query block;

resolving an unresolved hint in conflict with another tuning hint, wherein the unresolved hint is stored in a hint data structure on a tangible computer readable storage medium or a computer storage device; and

a tangible computer readable storage medium or a computer readable storage device configured for;

storing the one or more tuning hints for the query block, wherein at least one of the one or more tuning hints associated with the query block is retrieved for and applied to the query block during the compilation of the query language statement.

23. The computer system of claim 22 in which the processor is further configured for:

transforming the query block from a parent block into a child block during compilation of the statement.

24. The computer system of claim 23 in which the processor is further configured for:

determining a name for the child block based on a transformation applied to the parent block and a name of the parent block.

25. The computer system of claim 24 in which the processor is further configured for:

associating one or more tuning actions with the child block using the name of the child block.