Systems and methods for sharing of execution plans for similar database statements

• US 6,985,904 B1
• Filed: 02/28/2002
• Issued: 01/10/2006
• Est. Priority Date: 02/28/2002
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Jain, Namit, Kaluskar, Sanjay
• Primary Examiner(s): Corrielus, Jean M.
• Application Number:
  US10/086,277
• Time in Patent Office: 1,412 Days
• Field of Search: 707/2, 707/3, 707/6, 707/10, 707/100, 707/102, 707/104, 707/101, 717/143
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: G06F 16/2454 Optimisation of common expressions
• 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/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A database statement, such as a SQL text, reuses the execution plan compiled for a similar SQL text found in memory if the value of a system parameter is configured for cursor sharing among optimally shareable SQL texts and the SQL text is optimally shareable with the similar SQL text found in memory. Reuse also occurs if the value of a system parameter is configured for cursor sharing among all shareable SQL texts and the SQL text is suboptimally or optimally shareable with the similar SQL text found in memory. For SQL text that is non-shareable with a similar SQL text found in memory, no execution plan sharing takes place.

Claims

1. A computer implemented method for reusing a data structure in which a compiled cursor is stored, comprising:

receiving a database statement from a client;

analyzing the database statement;

determining if the database statement is optimally shareable, sub-optimally shareable or non-shareable based on the analysis;

searching server memory for a similar database statement; and

reusing the data structure compiled for the similar database statement to execute the database statement when a system parameter that is configurable to control data structure sharing is configured to enable data structure sharing for all shareable database statements.

2. The method of claim 1, wherein reusing said data structure compiled for said similar database statement occurs when a command-line parameter is configured to control data structure sharing.

3. The method of claim 2, wherein the command-line parameter is a hint for enabling a one-time system parameter override.

4. The method of claim 1, further comprising:

replacing all literals in said database statement with bind variables prior to said step of searching memory for said similar database statement.

5. The method of claim 1, further comprising returning a result set to the client if said database statement is a query.

6. The method of claim 1, wherein reusing said data structure includes executing the execution plan compiled from said similar database statement.

7. The method of claim 1, wherein analyzing the database statement comprising analyzing one or more of the group of:

a position of a literal, a value of the literal, a number of rows in a table, and the type of database statement.

8. A computer implemented method for reusing a data structure in which a compiled cursor is stored, comprising:

receiving a database statement from a client;

analyzing the database statement;

determining if the database statement is optimally shareable, sub-optimally shareable or non-shareable based on the analysis;

searching server memory for a similar database statement; and

reusing the data structure compiled for the similar database statement to execute the database statement when a system parameter that is configurable to control data structure sharing is configured to enable data structure sharing only for optimally shareable database statements.

9. The method of claim 8, wherein reusing said data structure compiled for said similar database statement occurs when a command-line parameter is configured to control data structure sharing.

10. The method of claim 9, wherein the command-line parameter is a hint for enabling a one-time system parameter override.

11. The method of claim 8, further comprising:

replacing all literals in said database statement with bind variables prior to said step of searching memory for said similar database statement.

12. The method of claim 8, further comprising returning a result set to the client if said database statement is a query.

13. The method of claim 8, wherein reusing said data structure includes executing the execution plan compiled from said similar database statement.

14. The method of claim 8, wherein analyzing the database statement comprising analyzing one or more of the group of:

a position of a literal, a value of the literal, a number of rows in a table, and the type of database statement.

15. A computer implemented method for reusing a data structure in which a compiled cursor is stored, comprising:

receiving a database statement from a client;

analyzing the database statement;

determining if the database statement is optimally shareable, sub-optimally shareable or non-shareable based on the analysis;

searching server memory for an exact matching database statement if the statement is non-shareable; and

executing the data structure compiled from said exact matching database statement if said exact matching database is found in said memory.

16. The method of claim 15, wherein reusing said data structure compiled for said similar database statement occurs when a command-line parameter is configured to control data structure sharing.

17. The method of claim 16, wherein the command-line parameter is a hint for enabling a one-time system parameter override.

18. The method of claim 15, further comprising:

replacing all literals in said database statement with bind variables prior to said step of searching memory for said similar database statement.

19. The method of claim 15, further comprising returning a result set to the client if said database statement is a query.

20. The method of claim 15, wherein reusing said data structure includes executing the execution plan compiled from said similar database statement.

21. The method of claim 15, wherein said data structure comprises a cursor.

22. A computer implemented system for sharing a data structure in which a compiled cursor is stored, comprising:

a server configured to accept a database statement from a client, analyze the database statement, and to determine if the database statement is optimally shareable, sub-optimally shareable, or non-shareable based on the analysis;

a search engine for searching shared memory for a similar database statement; and

a cursor sharing monitor for executing a data structure compiled for said similar database statement if the value of a system parameter is configured to enable data structure sharing, wherein the cursor sharing monitor reuses an execution plan of the data structure compiled for the similar database statement to execute the database statement only if the system parameter is configured to enable data structure sharing for all shareable database statements.

23. The system of claim 22, further comprising a compiler for hard parsing said database statement.

24. The system of claim 23, wherein the compiler hard parses the database statement only if (1) the value of said system parameter is not configured to force data structure sharing and said database statement and said similar database statement are sub-optimally similar, or (2) if said system parameter is configured for data structure sharing only if said database statement exactly matches said similar database statement and said database statement is optimally shareable with said similar database statement, or (3) said database statement is non-shareable with said similar database statement.

25. The system of claim 24, wherein the compiler further comprises a typechecker for resolving data type conflicts between the server and the client.

26. The system of claim 24, wherein the compiler further comprises a parser for hard parsing the database statement.

27. The system of claim 26, wherein the parser further comprises a tree builder for building an expression tree from the database statement.

28. The system of claim 26, wherein the parser further comprises a semantic analyzer for verifying user permissions and access privileges for one or more objects referenced in the database statement.

29. The system of claim 24, wherein the parser further comprises a syntactic analyzer for checking the syntax of the database statement.

30. The system of claim 23, wherein the compiler further comprises an optimizer for creating an optimal execution plan for carrying out the database statement from the client.

31. A computer implemented system for sharing a data structure in which a compiled cursor is stored, comprising:

a server configured to accept a database statement from a client, analyze the database statement, and to determine if the database statement is optimally shareable, sub-optimally shareable, or non-shareable based on the analysis;

a search engine for searching shared memory for a similar database statement; and

a cursor sharing monitor for executing a data structure compiled for said similar database statement if the value of a system parameter is configured to enable data structure sharing, wherein the cursor sharing monitor reuses an execution plan of the data structure compiled for the similar database statement to execute the database statement only if the system parameter is configured to enable data structure sharing for all optimally shareable database statements.

32. The system of claim 31, further comprising a compiler for hard parsing said database statement.

33. The system of claim 32, wherein the compiler hard parses the database statement only if (1) the value of said system parameter is not configured to force data structure sharing and said database statement and said similar database statement are sub-optimally similar, or (2) if said system parameter is configured for data structure sharing only if said database statement exactly matches said similar database statement and said database statement is optimally shareable with said similar database statement, or (3) said database statement is non-shareable with said similar database statement.

34. The system of claim 33, wherein the compiler further comprises a typechecker for resolving data type conflicts between the server and the client.

35. The system of claim 33, wherein the compiler further comprises a parser for hard parsing the database statement.

36. The system of claim 35, wherein the parser further comprises a tree builder for building an expression tree from the database statement.

37. The system of claim 35, wherein the parser further comprises a semantic analyzer for verifying user permissions and access privileges for one or more objects referenced in the database statement.

38. The system of claim 33, wherein the parser further comprises a syntactic analyzer for checking the syntax of the database statement.

39. The system of claim 32, wherein the compiler further comprises an optimizer for creating an optimal execution plan for carrying out the database statement from the client.

40. A computer program product embodied on computer readable medium, the computer readable medium having stored thereon a sequence of instructions which, when executed by a processor, causes the processor to execute a method for reusing a data structure in which a compiled cursor is stored, the method comprising:

receiving a database statement from a client;

analyzing the database statement;

determining if the database statement is optimally shareable, sub-optimally shareable, or non-shareable based on the analysis;

searching server memory for a similar database statement; and

reusing the data structure compiled for the similar database statement to execute the database statement when a system parameter that is configurable to control data structure sharing is configured to enable data structure sharing for all shareable database statements.

41. The computer program product of claim 40, wherein reusing said data structure compiled for said similar database statement occurs when a command-line parameter is configured to control data structure sharing.

42. The computer program product of claim 41, wherein the command-line parameter is a hint for enabling a one-time system parameter override.

43. The computer program product of claim 40, further comprising:

replacing all literals in said database statement with bind variables prior to said step of searching memory for said similar database statement.

44. The computer program product of claim 40, further comprising returning a result set to the client if said database statement is a query.

45. The computer program product of claim 40, wherein reusing said data structure includes executing the execution plan compiled from said similar database statement.

46. The computer program product of claim 40, wherein analyzing the database statement comprising analyzing one or more of the group of:

a position of a literal, a value of the literal, a number of rows in a table, and the type of database statement.

47. A computer program product embodied on computer readable medium, the computer readable medium having stored thereon a sequence of instructions which, when executed by a processor, causes the processor to execute a method for reusing a data structure in which a compiled cursor is stored, the method comprising:

receiving a database statement from a client;

analyzing the database statement;

determining if the database statement is optimally shareable, sub-optimally shareable, or non-shareable based on the analysis;

searching server memory for a similar database statement; and

reusing the data structure compiled for the similar database statement to execute the database statement when a system parameter that is configurable to control data structure sharing is configured to enable data structure sharing only for optimally shareable database statements.

48. The computer program product of claim 47, wherein reusing said data structure compiled for said similar database statement occurs when a command-line parameter is configured to control data structure sharing.

49. The computer program product of claim 48, wherein the command-line parameter is a hint for enabling a one-time system parameter override.

50. The computer program product of claim 47, further comprising:

replacing all literals in said database statement with bind variables prior to said step of searching memory for said similar database statement.

51. The computer program product of claim 47, further comprising returning a result set to the client if said database statement is a query.

52. The computer program product of claim 47, wherein reusing said data structure includes executing the execution plan compiled from said similar database statement.

53. The computer program product of claim 47, wherein analyzing the database statement comprising analyzing one or more of the group of:

a position of a literal, a value of the literal, a number of rows in a table, and the type of database statement.

54. A computer program product embodied on computer readable medium, the computer readable medium having stored thereon a sequence of instructions which, when executed by a processor, causes the processor to execute a method for reusing a data structure in which a compiled cursor is stored, the method comprising:

receiving a database statement from a client;

analyzing the database statement;

determining if the database statement is optimally shareable, sub-optimally shareable, or non-shareable based on the analysis;

searching server memory for an exact matching database statement if the statement is non-shareable; and

executing the data structure compiled from said exact matching database statement if said exact matching database statement is found in said memory.

55. The computer program product of claim 54, wherein reusing said data structure compiled for said similar database statement occurs when a command-line parameter is configured to control data structure sharing.

56. The computer program product of claim 55, wherein the command-line parameter is a hint for enabling a one-time system parameter override.

57. The computer program product of claim 54, further comprising:

replacing all literals in said database statement with bind variables prior to said step of searching memory for said similar database statement.

58. The computer program product of claim 54, further comprising returning a result set to the client if said database statement is a query.

59. The computer program product of claim 54, wherein reusing said data structure includes executing the execution plan compiled from said similar database statement.

60. The computer program product of claim 54, wherein analyzing the database statement comprising analyzing one or more of the group of:

a position of a literal, a value of the literal, a number of rows in a table, and the type of database statement.