Executing a parallel single cursor model

• US 7,685,095 B2
• Filed: 09/16/2004
• Issued: 03/23/2010
• Est. Priority Date: 12/16/2003
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Ghosh, Bhaskar, Dageville, Benoit, Cruanes, Thierry
• Primary Examiner(s): Wong; Don
• Assistant Examiner(s): Hwa; Shyue Jiunn
• Application Number:
  US10/944,175
• Publication Number:
  US 20050131881A1
• Time in Patent Office: 2,014 Days
• Field of Search: 707/3, 707/4, 719/312
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24532 of parallel queries
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing

Abstract

Techniques are provided for executing database statements, or portions thereof, in parallel without using slave SQL to communicate to each slave the operations to be performed by the slave. Techniques are provided for incorporating within a shared cursor the code fragments that govern both sides of the interaction between a query coordinator (QC) and remotely-located slaves. Further, techniques are provided for the QC to communicate with each slave on how and which portions of the execution plan to execute and when. A state-transition engine for slave execution under the control of the query-coordinator is also provided.

Claims

1. A method for processing a database statement, the method comprising the steps of:

receiving the database statement at a database server;

determining that at least one operation required by the database statement can be parallelized;

within the database server, a query coordinator generating a first execution plan that specifies how to execute all operations required by the database statement;

sending the database statement to a remote slave, wherein the database statement sent to the remote slave is the same entire database statement that was received by the database server;

causing the remote slave to generate a second execution plan that specifies how to execute all operations required by the database statement, wherein the database statement for which the remote slave generates the second execution plan is the same entire database statement that was received by the database server;

causing the remote slave to execute logic contained within the second execution plan, wherein the logic dictates a sequence of interactions between the query coordinator and the remote slave;

wherein the steps of the method are performed by one or more computing devices.

2. The method of claim 1 wherein the logic causes the remote slave to perform the steps of:

generating signatures based on the second execution plan;

comparing the signatures based on the second execution plan with signatures based on the first execution plan; and

sending a message to the query coordinator that indicates whether the signatures based on the second execution plan match the signatures based on the first execution plan.

3. The method of claim 1 wherein:

the remote slave is assigned to perform an operation that produces input consumed by one or more other slaves; and

the logic causes the remote slave to respond to an execute message from the query coordinator by sending a ready message to the query coordinator.

4. The method of claim 3 wherein the logic causes the remote slave to perform the steps of:

after sending the ready message, responding to a resume message from the query coordinator by performing the operation that produces input.

5. The method of claim 1 wherein:

the remote slave is assigned to perform an operation that consumes input produced by one or more other slaves; and

the logic causes the remote slave to respond to an execute message from the query coordinator by sending a started message to the query coordinator.

6. The method of claim 5 wherein the query coordinator sends a resume message to the one or more other slaves in response to receiving started messages from all slaves consuming input from the one or more other slaves.

14. The computer-readable storage medium of claim 1, wherein the logic causes the remote slave to perform the steps of:

generating signatures based on the second execution plan;

comparing the signatures based on the second execution plan with signatures based on the first execution plan; and

sending a message to the query coordinator that indicates whether the signatures based on the second execution plan match the signatures based on the first execution plan.

15. The computer-readable storage medium of claim 1, wherein:

the remote slave is assigned to perform an operation that produces input consumed by one or more other slaves; and

the logic causes the remote slave to respond to an execute message from the query coordinator by sending a ready message to the query coordinator.

16. The computer-readable storage medium of claim 15, wherein the logic causes the remote slave to perform the steps of:

after sending the ready message, responding to a resume message from the query coordinator by performing the operation that produces input.

17. The computer-readable storage medium of claim 1, wherein:

the remote slave is assigned to perform an operation that consumes input produced by one or more other slaves; and

the logic causes the remote slave to respond to an execute message from the query coordinator by sending a started message to the query coordinator.

18. The computer-readable storage medium of claim 17, wherein the query coordinator sends a resume message to the one or more other slaves in response to receiving started messages from all slaves consuming input from the one or more other slaves.

7. A method for processing a database statement, the method comprising the steps of:

receiving the database statement at a database server;

determining that at least one operation required by the database statement can be parallelized;

within the database server, a query coordinator generating a first execution plan that specifies how to execute all operations required by the database statement;

sending the database statement to a remote slave, wherein the database statement sent to the remote slave is the same entire database statement that was received by the database server;

causing the remote slave to generate a second execution plan that specifies how to execute all operations required by the database statement, wherein the database statement for which the remote slave generates the second execution plan is the same entire database statement that was received by the database server;

causing the query coordinator to execute logic contained within the first execution plan, wherein the logic dictates a sequence of interactions between the query coordinator and the remote slave;

wherein the steps of the method are performed by one or more computing devices.

8. The method of claim 7 further comprising the step of:

causing the remote slave to execute logic contained within the second execution plan, wherein the logic dictates a sequence of interactions between the query coordinator and the remote slave.

9. The method of claim 8 wherein:

both the first execution plan and the second execution plan include

first logic to be executed by the query coordinator during the sequence of interactions;

second logic to be executed by the remote slave during the sequence of interactions; and

branch logic for branching to one of the first logic and the second logic based on which of the query coordinator and the remote slave is executing the branch logic.

10. A method for processing a database statement, the method comprising the steps of:

receiving the database statement at a database server;

generating an execution plan for the database statement, wherein the execution plan specifies a first operation that produces data and a second operation that consumes data produced by the first operation;

sending the database statement to one or more remote nodes where a first plurality of slave processes reside, wherein the database statement sent to the one or more remote nodes is the same entire database statement that was received by the database server;

generating, from the database statement, at each of the one or more remote nodes, a second execution plan, wherein the database statement from which the second execution plan is generated at each of the one or more remote nodes is the same entire database statement that was received by the database server;

assigning producing slaves, of the first plurality of slave processes, to perform the first operation;

assigning consuming slaves, of the first plurality of slave processes, to perform the second operation;

sending, to all producing slaves, a first message that indicates which portion of the second execution plan the producing slaves are to execute;

receiving, from each producing slave, a second message that indicates that the producing slave is ready to execute;

in response to receiving the second message from each producing slave, sending a third message to all consuming slaves that indicates which portion of the second execution plan the consuming slaves are to execute;

receiving a fourth message, from each consuming slave, indicating that the consuming slave has begun to execute; and

in response to receiving the fourth message from each consuming slave, sending a fifth message to each producing slave to resume execution;

wherein the steps of the method are performed by one or more computing devices.

11. The method of claim 10 wherein:

sending the database statement includes sending a first set of signatures, based on the execution plan, to the one or more remote nodes;

the method further comprising performing the following steps prior to sending the first message;

generating a second set of signatures for each of the second execution plans; and

determining whether the first set of signatures and the second set of signatures are equivalent.

12. The method of claim 11, wherein the step of generating the second set of signatures is performed by generating the second set of signatures based only on those portions of the second execution plans that must match the first execution plan in order to ensure correctness of operations on the execution plan.

13. A computer-readable storage medium storing one or more sequences of instructions for processing a database statement which instructions, when executed by one or more processors, cause the one or more processors to perform steps comprising:

receiving the database statement at a database server;

determining that at least one operation required by the database statement can be parallelized;

within the database server, a query coordinator generating a first execution plan that specifies how to execute all operations required by the database statement;

sending the database statement to a remote slave, wherein the database statement sent to the remote slave is the same entire database statement that was received by the database server;

causing the remote slave to generate a second execution plan that specifies how to execute all operations required by the database statement, wherein the database statement for which the remote slave generates the second execution plan is the same entire database statement that was received by the database server; and

causing the remote slave to execute logic contained within the second execution plan, wherein the logic dictates a sequence of interactions between the query coordinator and the remote slave.

19. A computer-readable storage medium storing one or more sequences of instructions for processing a database statement which instructions, when executed by one or more processors, cause the one or more processors to perform steps comprising:

receiving the database statement at a database server;

determining that at least one operation required by the database statement can be parallelized;

within the database server, a query coordinator generating a first execution plan that specifies how to execute all operations required by the database statement;

sending the database statement to a remote slave, wherein the database statement sent to the remote slave is the same entire database statement that was received by the database server;

causing the remote slave to generate a second execution plan that specifies how to execute all operations required by the database statement, wherein the database statement for which the remote slave generates the second execution plan is the same entire database statement that was received by the database server; and

causing the query coordinator to execute logic contained within the first execution plan, wherein the logic dictates a sequence of interactions between the query coordinator and the remote slave.

20. The computer-readable storage medium of claim 19, wherein the one or more sequences of instructions further comprise instructions which, when executed by the one or more processors, cause the one or more processors to perform the step of:

causing the remote slave to execute logic contained within the second execution plan, wherein the logic dictates the sequence of interactions between the query coordinator and the remote slave.

21. The computer-readable storage medium of claim 20, wherein:

both the first execution plan and the second execution plan include;

first logic to be executed by the query coordinator during the sequence of interactions;

second logic to be executed by the remote slave during the sequence of interactions; and

branch logic for branching to one of the first logic and the second logic based on which of the query coordinator and the remote slave is executing the branch logic.

22. A computer-readable storage medium storing one or more sequences of instructions for processing a database statement which instructions, when executed by first one or more processors, cause the first one or more processors to perform steps comprising:

receiving the database statement at a database server;

generating an execution plan for the database statement, wherein the execution plan specifies a first operation that produces data and a second operation that consumes data produced by the first operation;

sending the database statement to one or more remote nodes where a first plurality of slave processes reside, wherein the database statement sent to the one or more remote nodes is the same entire database statement that was received by the database server;

generating, from the database statement, at each of the one or more remote nodes, a second execution plan, wherein the database statement from which the second execution plan is generated at each of the one or more remote nodes is the same entire database statement that was received by the database server;

assigning producing slaves, of the first plurality of slave processes, to perform the first operation;

assigning consuming slaves, of the first plurality of slave processes, to perform the second operation;

sending, to all producing slaves, a first message that indicates which portion of the second execution plan the producing slaves are to execute;

receiving, from each producing slave, a second message that indicates that the producing slave is ready to execute;

in response to receiving the second message from each producing slave, sending a third message to all consuming slaves that indicates which portion of the second execution plan the consuming slaves are to execute;

receiving a fourth message, from each consuming slave, indicating that the consuming slave has begun to execute; and

in response to receiving the fourth message from each consuming slave, sending a fifth message to each producing slave to resume execution.

23. The computer-readable storage medium of claim 22, wherein:

the instructions that cause sending the database statement include instructions which, when executed by the first one or more processors, cause the first one or more processors to perform the step of sending a first set of signatures, based on the execution plan, to the one or more remote nodes; and

the one or more sequences of instructions further comprise instructions which, when executed by second one or more processors at each of the one or more remote nodes, cause the second one or more processors to perform the following steps prior to sending the first message;

generating a second set of signatures for each of the second execution plans;

and determining whether the first set of signatures and the second set of signatures are equivalent.

24. The computer-readable storage medium of claim 23, wherein the instructions that cause the second one or more processors to perform the step of generating the second set of signatures comprise instructions which, when executed by the second one or more processors, cause the second one or more processors to perform the step of generating the second set of signatures based only on those portions of the second execution plan that must match the first execution plan in order to ensure correctness of operations on the execution plan.