Query Plan Optimization for Prepared SQL Statements

1. A method for improving a query plan reuse in a database server system, comprising: receiving a query at a first node of a plurality of nodes of a database system, the query including one or more SQL commands; searching a SQL statement cache for SQL text corresponding to the one or more SQL commands; determining that a query plan in a global cache corresponding to the SQL statement cache matches the received query, wherein the query plan was prepared by a second node of the plurality of nodes; determining that the query plan is being executed by an executing node of the plurality of nodes of the database system; and reserving, by the first node, a clone of the query plan while it is being executed by the executing node, wherein the cloned query plan shares a portion of memory space allocated for the query plan, wherein the query plan and the cloned query plan are executed in parallel by the first node and the executing node, and wherein the reserving comprises: identifying a value portion of the query plan, wherein the value portion holds values associated with a prepared SQL statement; allocating a new storage space for the value portion of the cloned query plan; copying the value portion of the query plan into the new storage space; and storing values associated with the second client connection in the new storage space, the storing values comprising storing instructions shared between the cloned query plan and the query plan in the memo space allocated for the query plan.

2. The method of claim 1 , wherein the reserving a clone of the query plan comprises storing a reference to the cloned query plan in a local cache storage.

3. The method of claim 1 , wherein the determining that a query plan in a global cache is performed by a global cache storage manager.

4. The method of claim 1 , wherein an instruction portion of the query plan is shared by the query plan and the cloned query plan.

5. The method of claim 1 , wherein a prepared SQL statement comprises a prepared SQL name, and a prepared SQL text is retrieved from the database server system based on the prepared SQL name.

6. A database server system for improving a query plan reuse comprising: a memory; and at least one processor coupled to the memory and configured to: receive a query at a first node of a plurality of nodes of a database system, the query including one or more SQL commands; search a SQL statement cache for SQL text corresponding to the one or more SQL commands; determine that a query plan in a global cache corresponding to the SQL statement cache matches the received query, wherein the query plan was prepared by a second node of the plurality of nodes; determine that the query plan is being executed by an executing node of the plurality of nodes of the database system; and reserve, by the first node, a clone of the query plan while it is being executed by the executing node, wherein the cloned query plan shares a portion of memory space allocated for the query plan, wherein the query plan and the cloned query plan are executed in parallel by the first node and the executing node, and wherein the at least one processor that reserves is configured to: identify a value portion of the query plan, wherein the value portion holds values associated with a prepared SQL statement, allocate a new storage space for the value portion of the cloned query plan; copy the value portion of the query plan into the new storage space; and store values associated with the second client connection in the new storage space the at least one processor that stores values is configured to store instructions shared between the cloned query plan and the query plan in the memory space allocated for the query plan.

7. The system of claim 6 , wherein to reserving a clone of the query plan, the at least one processor is configured to store a reference to the cloned query plan in a local cache storage.

8. The system of claim 6 , wherein a prepared SQL statement comprises a prepared SQL name, and a prepared SQL text is retrieved from the database server system based on the prepared SQL name.

9. A tangible computer-readable device having instructions stored thereon that, when executed by at least one computing device, causes the at least one computing device to perform operations comprising: receiving a query at a first node of a plurality of nodes of a database system, the query including one or more SQL commands; searching a SQL statement cache for SQL text corresponding to the one or more SQL commands; determining that a query plan in a global cache corresponding to the SQL statement cache matches the received query, wherein the query plan was prepared by a second node of the plurality of nodes; determining that the query plan is being executed by an executing node of the plurality of nodes of the database system; and reserving, by the first node, a clone of the query plan while it is being executed by the executing node, wherein the cloned query plan shares a portion of memory space allocated for the query plan, wherein the query plan and the cloned query plan are executed in parallel by the first node and the executing node, and wherein the reserving comprises: identifying a value portion of the query plan, wherein the value portion holds values associated with a prepared SQL statement; allocating a new storage space for the value portion of the cloned query plan; copying the value portion of the query plan into the new storage space; and storing values associated with the second client connection in the new storage space, the storing values comprising storing instructions shared between the cloned query plan and the query plan in the memo space allocated for the query plan.

10. The computer-readable device of claim 9 , wherein a private cache storage is accessible to the client connection and the global cache storage is accessible to the plurality of client connections.

11. The method of claim 1 , wherein the generating the cloned query plan further comprises: allocating a new memory space to hold structures associated with the cloned query plan that contain values, wherein pointers that reference query plan values are mapped to the new memory space.

12. The method of claim 1 , wherein a second prepared SQL statement associated with the cloned query plan is identical to a first prepared SQL statement associated with the determined query plan.

13. The method of claim 1 , wherein the reserving is performed while the query plan is locked and without waiting for the lock to be released.

14. The method of claim 1 , wherein the reserving comprises generating a light-weight stored procedure object comprising SQL text and a query tree associated with the SQL text.