Methods and systems for database statement execution plan optimization

• US 7,092,931 B1
• Filed: 05/10/2002
• Issued: 08/15/2006
• Est. Priority Date: 05/10/2002
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: Jain, Namit, Kaluskar, Sanjay, Wood, Graham S., Jakobsson, Hakan
• Primary Examiner(s): Robinson, Greta
• Assistant Examiner(s): Lu, Kuen S.
• Application Number:
  US10/144,681
• Time in Patent Office: 1,558 Days
• Field of Search: 707/2, 707/1, 707/4, 707/3, 707/104.1, 707/101
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation

Abstract

The methods and systems for database statement execution plan optimization exploit bind variable data available on the network to build and optimize an execution plan for the statement. A system for database statement execution plan optimization comprises a bind variable analyzer, a frame allocator, and an optimizer, in addition to a parser for parsing and analyzing the statement, a type checker for type checking the statement, and a tree builder for building an expression tree.

Claims

1. A method for executing an execution plan comprising:

compiling a database statement before the database statement is executed, wherein the database statement is compiled by

obtaining a value of a database statement placeholder prior to execution, and

optimizing said execution plan based on said value; and

executing said execution plan;

wherein said value is obtained by;

allocating a frame in a memory,

allocating a buffer within said frame, and

reading said value into said buffer.

2. The method of claim 1, wherein

said memory is for execution of said database statement.

3. The method of claim 1, further comprising parsing said database statement.

4. The method of claim 1, further comprising performing semantic and syntactic analysis on said database statement.

5. The method of claim 1, wherein said value is from a request to execute the database statement.

6. The method of claim 1, further comprising using the execution plan for a subsequent invocation of the database statement with a different value.

7. A system for executing an execution plan comprising:

logic for compiling a database statement before the database statement is executed, wherein the logic for compiling the database statement comprises

logic for obtaining a value of a database statement placeholder prior to execution, and

logic for optimizing said execution plan based on said value; and

logic for executing said execution plan;

wherein said logic for obtaining the value comprises

logic for allocating a frame in a memory,

logic for allocating a buffer within said frame, and

logic for reading said value into said buffer.

8. The system of claim 7, wherein

said memory is for execution of said database statement.

9. The system of claim 7, wherein said value is from a request to execute the database statement.

10. The system of claim 7, further comprising logic for allowing the execution plan to be used for a subsequent invocation of the database statement with a different value.

11. A computer readable medium having stored thereon one or more sequences of instructions for controlling execution of one or more processors and for executing an execution plan, the one or more sequences of instructions comprising instructions for:

compiling a database statement before the database statement is executed, wherein the database statement is compiled by

obtaining a value of a database statement placeholder prior to execution, and

optimizing said execution plan based on said value; and

executing said execution plan;

wherein said value is obtained by

allocating a frame in a memory,

allocating a buffer within said frame, and

reading said value into said buffer.

12. The computer readable medium of claim 11, wherein

said memory is for execution of said database statement.

13. The computer readable medium of claim 11, wherein said value is from a request to execute the database statement.

14. The computer readable medium of claim 11, further comprising instruction for using the execution plan for a subsequent invocation of the database statement with a different value.

15. A method for optimizing an execution plan comprising:

receiving a statement including a bind variable;

compiling the statement before the statement is executed, wherein the statement is compiled by

parsing the statement, the parsing comprising;

performing semantic and syntactic analysis on the statement, and

performing type checking on the statement; and

reading a bind value of the bind variable, the reading including;

allocating frames for the bind variable,

allocating buffers in the frames, and

reading the bind value into the buffer; and

optimizing the statement using the bind value, wherein the reading and the optimizing occur prior to performing the type checking on the statement.

16. The method of claim 15, wherein the bind value is stored in a network buffer.

17. The method of claim 15, further comprising allocating a frame in a memory for execution of said statement.

18. The method of claim 15, wherein the bind value is the first value of the bind variable.

19. The method of claim 15, further comprising executing said execution plan.

20. The method of claim 15, wherein the semantic analysis includes verifying user permissions and access privileges for one or more objects referenced in the database statement.

21. The method of claim 15, wherein the parsing further comprises building an expression tree from said database statement.

22. The method of claim 15, wherein the syntactic analysis includes checking the syntax of said database statement.

23. A computer readable medium having stored thereon one or more sequences of instructions for controlling execution of one or more processors and for optimizing an execution plan, the one or more sequences of instructions comprising instructions for:

receiving a statement including a bind variable;

compiling the statement before the statement is executed, wherein the statement is compiled by

parsing the statement, the parsing comprising;

performing semantic and syntactic analysis on the statement, and

performing type checking on the statement, and

reading a bind value of the bind variable, the reading including;

allocating frames for the bind variables,

allocating buffers in the frames, and

reading the bind value into the buffer; and

optimizing the statement using the bind values, wherein the reading and the optimizing occur prior to performing the type checking on the statement.

24. The computer readable medium of claim 23, wherein the bind value is stored in a network buffer.

25. The computer readable medium of claim 23, further comprising allocating a frame in a memory for execution of said statement.

26. The computer readable medium of claim 23, wherein the bind value is the first value of the bind variable.

27. The computer readable medium of claim 23, further comprising executing said execution plan.

28. The computer readable medium of claim 23, wherein the semantic analysis includes verifying user permissions and access privileges for one or more objects referenced in the database statement.

29. The computer readable medium of claim 23, wherein the parsing further comprises building an expression tree from said database statement.

30. The computer readable medium of claim 23, wherein the syntactic analysis includes checking the syntax of said database statement.

31. A system for optimizing an execution plan comprising:

means for receiving a statement including a bind variable;

means for compiling the statement before the statement is executed, the means for compiling the statement comprising;

means for parsing the statement, the means for parsing comprising;

means for performing semantic and syntactic analysis on the statement, and

means for performing type checking on the statement, and

means for reading a bind value of the bind variable, the means for reading including;

means for allocating frames for the bind variable,

means for allocating buffers in the frames, and

means for reading the bind value into the buffer; and

means for optimizing the statement using the bind values, wherein the reading and the optimizing occur prior to performing the type checking on the statement.

32. The system of claim 31, wherein the bind value is stored in a network buffer.

33. The system of claim 31, further comprising means for allocating a frame in a memory for execution of said statement.

34. The system of claim 31, wherein the bind value is the first value of the bind variable.

35. The system of claim 31, further comprising means for executing said execution plan.

36. The system of claim 31, wherein the means for semantic analysis include means for verifying user permissions and access privileges for one or more objects referenced in the database statement.

37. The system of claim 31, wherein the means for parsing further comprises means for building an expression tree from said database statement.

38. The system of claim 31, wherein the means for syntactic analysis includes means for checking the syntax of said database statement.