MULTI-SYSTEM QUERY EXECUTION PLAN

• US 20160292167A1
• Filed: 03/30/2015
• Published: 10/06/2016
• Est. Priority Date: 03/30/2015
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: TRAN, KHAI, SEN, RAJKUMAR, PETRIDE, SABINA, IDICULA, SAM
• Granted Patent: US 10,585,887 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24542 Plan optimisation

Abstract

Techniques are described to evaluate an operation from an execution plan of a query to offload the operation to another database management system for less costly execution. In an embodiment, the execution plan is determined based on characteristics of the database management system that received the query for execution. One or more operations in the execution plan are then evaluated for offloading to another heterogeneous database management system. In a related embodiment, the offloading cost for each operation may also include communication cost between the database management systems. The operations that are estimated to be less costly to execute on the other database management system are then identified for offloading to the other database management system. In an alternative embodiment, the database management system generates permutations of execution plans for the same query, and similarly evaluates each permutation of the execution plans for offloading its one or more operations. Based on the total cost of each permutation, which may include offloading cost for one or more operations to another database management system, the least costly plan is selected for the query execution.

Claims

1. A method comprising:

receiving a query to execute at a first database management system;

estimating an operation cost to execute an operation of an execution plan of the query on a second database management system , which is heterogenous from the first database management system;

based on the operation cost, identifying whether to offload the operation of the execution plan to the second database management system;

wherein the method is performed by one or more computing devices.

2. The method of claim 1, further comprising:

estimating an initial operation cost of executing the operation on the first database management system;

if the operation cost is less than the initial operation cost, then identifying the operation for offloading to the second database management system.

3. The method of claim 1, further comprising:

determining a feasibility of the operation to execute on the second database management system;

if the operation is determined to be feasible, estimating the operation cost on the second database management system.

4. The method of claim 1, wherein the operation cost includes a cost of communication between the first database management system and the second database management system.

5. The method of claim 1, further comprising:

generating the execution plan, for the query, that comprises a first set of operations;

generating a second execution plan, for the query, that is different from the execution plan and comprises a second set of operations;

estimating, for each operation in the first set of operations of the execution plan, a first operation cost of executing said each operation in the first set of operations at the first database system and a second operation cost of execution said each operation in the first set of operations at the second database system;

estimating a first plan operation cost for the execution plan based on a lesser of the first operation cost and the second operation cost;

estimating, for each operation in the second set of operations of the second execution plan, a third operation cost of executing said each operation in the second set of operations at the first database system and a fourth operation cost of execution said each operation in the second set of operations at the second database system;

estimating a second plan operation cost for the second execution plan based on a lesser of the third operation cost and the fourth operation cost;

selecting the execution plan for executing the query, if the first plan operation cost is less than the second plan operation cost.

6. The method of claim 5 wherein the execution plan comprises a different join operation order than the second execution plan.

7. The method of claim 5 wherein the execution plan comprises of a different type of an operation than the second execution plan.

8. The method of claim 7 wherein the different type of the operation has a same row order as a sort based operation of the execution plan.

9. The method of claim 1, wherein the second database management system comprises a copy of a subset of data tables included in the first database management system.

10. The method of claim 1, wherein the first database management system and the second database management system comprise different hardware architecture.

11. One or more non-transitory storage media storing instructions which, when executed by one or more computing devices, cause:

receiving a query to execute at a first database management system;

estimating an operation cost to execute an operation of an execution plan of the query on a second database management system , which is heterogenous from the first database management system;

based on the operation cost, identifying whether to offload the operation of the execution plan to the second database management system;

wherein the method is performed by one or more computing devices.

12. The non-transitory storage media of claim 11, wherein the instructions, when executed, cause:

estimating an initial operation cost of executing the operation on the first database management system;

if the operation cost is less than the initial operation cost, then identifying the operation for offloading to the second database management system.

13. The non-transitory storage media of claim 11, wherein the instructions, when executed, cause:

determining a feasibility of the operation to execute on the second database management system;

if the operation is determined to be feasible, estimating the operation cost on the second database management system.

14. The non-transitory storage media of claim 11, wherein the operation cost includes a cost of communication between the first database management system and the second database management system.

15. The non-transitory storage media of claim 11, wherein the instructions, when executed, cause:

generating the execution plan, for the query, that comprises a first set of operations;

generating a second execution plan, for the query, that is different from the execution plan and comprises a second set of operations;

estimating, for each operation in the first set of operations of the execution plan, a first operation cost of executing said each operation in the first set of operations at the first database system and a second operation cost of execution said each operation in the first set of operations at the second database system;

estimating a first plan operation cost for the execution plan based on a lesser of the first operation cost and the second operation cost;

estimating, for each operation in the second set of operations of the second execution plan, a third operation cost of executing said each operation in the second set of operations at the first database system and a fourth operation cost of execution said each operation in the second set of operations at the second database system;

estimating a second plan operation cost for the second execution plan based on a lesser of the third operation cost and the fourth operation cost;

selecting the execution plan for executing the query, if the first plan operation cost is less than the second plan operation cost.

16. The non-transitory storage media of claim 15 wherein the execution plan comprises a different join operation order than the second execution plan.

17. The non-transitory storage media of claim 15 wherein the execution plan comprises of a different type of a operation than the second execution plan.

18. The non-transitory storage media of claim 17 wherein the different type of the operation has a same row order as a sort based operation of the execution plan.

19. The non-transitory storage media of claim 11, wherein the second database management system comprises a copy of a subset of data tables included in the first database management system.

20. The non-transitory storage media of claim 11, wherein the first database management system and the second database management system comprise different hardware architecture.