Method and system for implementing a SQL profile

• US 7,664,730 B2
• Filed: 09/07/2004
• Issued: 02/16/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: Yagoub, Khaled, Zait, Mohamed, Dageville, Benoit, Ziauddin, Mohamed
• Primary Examiner(s): Vo; Tim T.
• Assistant Examiner(s): Morrison; Jay A
• Application Number:
  US10/936,205
• Publication Number:
  US 20050125452A1
• Time in Patent Office: 1,988 Days
• Field of Search: 707/2
• US Class Current: 1/1
• 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, apparatus, and computer program product with a persistent data structure for storing a profile of tuning information for a query statement separately from the query statement. The profile in the persistent data structure includes a statement signature to identify the query statement, and an action to tune the statement.

Claims

1. A computer program product comprising a volatile or non-volatile computer usable medium having executable code to execute a process for optimizing a query statement, the process comprising:

storing a profile for the query statement in a data structure on a tangible computer usable medium or a computer storage device separately from the query statement;

optimizing, by using a processor of a computer, the query statement using at least the profile, wherein the profile comprises;

a tuning action for guiding generation or changing of an execution plan for the query statement without modifying the query statement;

a statement signature to identify the query statement for retrieval of the profile for the query statement during compilation of the query statement; and

tuning data to tune the query statement based at least in part on execution history of the query statement from at least one request with the query statement, wherein

the at least one request was executed prior to a current compilation of the query statement, and

without modifying the query statement, the tuning data are used to identify at least one optimizer setting for the current compilation of the query statement to generate or change the execution plan for the query statement.

2. The computer program product of claim 1, wherein the tuning data further comprises:

execution behavior information for the query statement; and

an action to set a parameter of an execution engine based on the execution behavior information.

3. The computer program product of claim 2, wherein the execution behavior information is selected from the group consisting of a degree of parallel execution of the query statement, a hardware characteristic of the execution engine, a data caching effect, and a degree of concurrent users.

4. The profile of claim 2, wherein the execution engine parameter is associated with a use of a resource to execute the query statement or with an amount of time to execute the query statement.

5. The profile of claim 1, wherein the tuning data further comprises:

optimization information for the query statement; and

an action to set a parameter of an optimizer based on the optimization information.

6. The profile of claim 5, wherein the optimization information is related to an execution history of the query statement.

7. The profile of claim 6, wherein the optimizer parameter is set to produce a plan for the query statement based on the execution history information.

8. The profile of claim 1, wherein the tuning data further comprises:

information related to statistics referenced by the query statement; and

an action to modify the statistics based on the information.

9. The profile of claim 8, wherein the information comprises a correction factor, and the action comprises an action to modify the statistics based on the correction factor.

10. The profile of claim 1, wherein the tuning data further comprises:

information related to a cardinality estimate for the query statement; and

an action to modify the cardinality estimate based on the information.

11. The computer program product of claim 1, wherein the query statement is a SQL statement.

12. A method of optimizing a query statement comprising:

receiving the query statement at an optimizer;

retrieving a profile for the query statement during compilation of the query statement at the optimizer;

optimizing, by using a processor of a computer, the query statement using at least the profile, wherein

the profile comprises;

a tuning action for guiding generation or changing of an execution plan for the query statement without modifying the query statement;

tuning data to tune the query statement based at least in part on execution history of the query statement from at least one request with the query statement; and

a statement signature to identify the query statement for retrieval of the profile for the query statement during compilation of the query statement; and

the at least one request was executed prior to a current compilation of the query statement, wherein the tuning data are used to identify or change one or more optimizer settings for a current compilation of the query statement to generate or change an execution plan for the query statement without modifying the query statement; and

storing the profile in a first data structure on a tangible computer usable medium or a computer storage device separately from the query statement;

storing the execution plan in the data structure or a second data structure on the tangible computer usable medium, a second tangible computer usable medium, or a computer storage device separately from the query statement or displaying the execution plan on a display apparatus.

13. The method of claim 12, further comprising:

generating the execution plan for the query statement based on the profile.

14. The method of claim 13, wherein the act of generating the execution plan for the query statement based on the profile comprises:

generating a plan to set a parameter of an execution engine based on the profile.

15. The method of claim 13, wherein the act of generating the execution plan comprises:

determining a number of rows to return as a result of executing the query statement.

16. The method of claim 13, wherein the act of generating the execution plan comprises:

determining a statistic referenced by the query statement based on the profile.

17. The method of claim 13, wherein the act of generating the execution plan comprises:

changing a cardinality estimate of the query statement based on the profile.

18. The method of claim 12, wherein the query statement is a SQL statement.

19. An apparatus for optimizing a statement comprising:

means for receiving a query statement at an optimizer; and

means for retrieving a profile for the query statement during compilation of the query statement;

means for optimizing the query statement, wherein

the means for optimizing the query statement comprises a computer processor, the profile comprises;

a tuning action for guiding generation or changing of an execution plan for the query statement without modifying the query statement;

tuning data to tune the query statement based at least in part on execution history of the query statement from at least one request with the query statement; and

a statement signature to identify the query statement for retrieval of the profile for the query statement during compilation of the query statement;

the at least one request was executed prior to a current compilation of the query statement, wherein the tuning data are used to identify or change one or more optimizer settings for a current compilation of the query statement to generate an execution plan for the query statement without modifying the query statement; and

a tangible computer usable medium or a computer storage device configured for storing the profile separately from the query statement in a first data structure; and

the tangible computer usable medium, a second tangible computer usable medium, the computer storage device, or a second computer storage device configured for storing the execution plan separately from the query statement in the data structure or a second data structure or a display apparatus configured for displaying the execution plan.

20. The apparatus of claim 19, further comprising:

means for generating the execution plan for the query statement based on the profile.

21. The apparatus of claim 20, wherein the means for generating the execution plan for the query statement based on the profile comprises:

means for generating a plan to set a parameter of an execution engine based on the profile.

22. The apparatus of claim 20, wherein the means for generating the execution plan comprises:

means for determining a number of rows to return as a result of executing the query statement.

23. The apparatus of claim 20, wherein the means for generating the execution plan comprises:

means for determining a statistic referenced by the query statement based on the profile.

24. The apparatus of claim 20, wherein the means for generating the execution plan comprises:

means for changing a cardinality estimate of the query statement based on the profile.

25. The apparatus of claim 19, wherein the query statement is a SQL statement.