Multi-system query execution plan

• US 10,585,887 B2
• Filed: 03/30/2015
• Issued: 03/10/2020
• 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
• Primary Examiner(s): Uddin, Md I
• Application Number:
  US14/673,560
• Publication Number:
  US 20160292167A1
• Time in Patent Office: 1,807 Days
• Field of Search: 707718, 707736, 707737
• 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:

a first database management system (DBMS) storing a first copy of particular data, and a second DBMS storing a second copy of the same particular data;

receiving a query to execute at the first DBMS, the query referencing the particular data;

estimating an initial operation cost of an operation of a plurality of operations in an execution plan of the query on the first DBMS, wherein the execution plan is represented by a tree of nodes, each node representing a corresponding operation of the execution plan and each subtree of nodes, of the tree of nodes, representing particular operations on which a particular operation at a root node of said each subtree of nodes depends;

wherein estimating the initial operation cost of the operation for the first DBMS includes a first operation cost for a subtree of the operation in the first DBMS and is based at least on the operation accessing the first copy of the particular data already available on the first DBMS prior to the receiving of the query;

determining feasibility to execute the operation on the second DBMS at least by determining that the second copy of the particular data is already stored in the second DBMS, wherein the operation is considered feasible if a) the particular data that the operation accesses is stored on the second DBMS, and b) the operation is supported by the second DBMS;

based on determining the feasibility, estimating an operation cost to execute the operation of the execution plan of the query on the second DBMS;

wherein estimating the operation cost of the operation for the second DBMS includes a second operation cost for the subtree of the operation in the second DBMS and is based at least on the operation accessing the second copy of the particular data already available on the second DBMS prior to the receiving of the query;

wherein the operation cost includes communication cost for parent and child node operations of the subtree of the operation in the second DBMS;

wherein the second DBMS is heterogeneous from the first DBMS;

comparing the operation cost of executing the subtree of the operation on the second DBMS with the initial operation cost of executing the subtree of the operation on the first DBMS thereby identifying whether to offload the operation of the execution plan to the second DBMS;

executing the query at least in part by executing the operation on either the first DBMS or the second DBMS to generate result data;

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

2. The method of claim 1, further comprising:

when the operation cost is less than the initial operation cost, then identifying the operation for offloading to the second DBMS.

3. The method of claim 1, wherein the operation cost includes a cost of communication between the first DBMS and the second DBMS.

4. 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 DBMS and a second operation cost of execution said each operation in the first set of operations at the second DBMS;

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 DBMS and a fourth operation cost of execution said each operation in the second set of operations at the second DBMS;

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, when the first plan operation cost is less than the second plan operation cost.

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

6. The method of claim 4, wherein the execution plan comprises of a different type of operation than the second execution plan.

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

8. The method of claim 1, wherein the second DBMS comprises a copy of a subset of data tables included in the first DBMS.

9. The method of claim 1, wherein the first DBMS and the second DBMS comprise different hardware architecture.

10. The method of claim 1, wherein the first DBMS stores the first copy of the particular data in a first storage format, and the second DBMS stores the second copy of the particular data in a second storage format different from the first storage format.

11. The method of claim 10, wherein the first storage format is a column-major format and the second storage format is a row-major format.

12. The method of claim 1, wherein the first DBMS stores the first copy of the particular data according to a first database schema, and the second DBMS stores the second copy of the particular data according to a second database schema different from the first database schema.

13. The method of claim 1, wherein the second DBMS is heterogeneous from the first DBMS based on different DBMS architecture.

14. The method of claim 1, wherein the different DBMS architecture includes one or more:

different storage architecture, different capability hardware components, different revision, model and/or vendor for DBMS software.

15. One or more non-transitory storage media storing a set of instructions which, when executed by one or more processors, causes:

a first database management system (DBMS) storing a first copy of particular data, and a second DBMS storing a second copy of the same particular data;

receiving a query to execute at the first DBMS, the query referencing the particular data;

estimating an initial operation cost of an operation of a plurality of operations in an execution plan of the query on the first DBMS, wherein the execution plan is represented by a tree of nodes, each node representing a corresponding operation of the execution plan and each subtree of nodes, of the tree of nodes, representing particular operations on which a particular operation at a root node of said each subtree of nodes depends;

wherein estimating the initial operation cost of the operation for the first DBMS includes a first operation cost for a subtree of the operation in the first DBMS and is based at least on the operation accessing the first copy of the particular data already available on the first DBMS prior to the receiving of the query;

determining feasibility to execute the operation on the second database management system DBMS at least by determining that the second copy of the particular data is already stored in the second DBMS, wherein the operation is considered feasible if a) the particular data that the operation accesses is stored on the second DBMS, and b) the operation is supported by the second DBMS;

based on determining the feasibility, estimating an operation cost to execute the operation of the execution plan of the query on the second DBMS;

wherein estimating the operation cost of the operation for the second DBMS includes a second operation cost for the subtree of the operation in the second DBMS and is based at least on the operation accessing the second copy of the particular data already available on the second DBMS prior to the receiving of the query;

wherein the operation cost includes communication cost for parent and child node operations of the subtree of the operation in the second DBMS;

wherein the second DBMS is heterogeneous from the first DBMS;

comparing the operation cost of executing the subtree of the operation on the second DBMS with the initial operation cost of executing the subtree of the operation on the first DBMS thereby identifying whether to offload the operation of the execution plan to the second DBMS;

executing the query at least in part by executing the operation on either the first DBMS or the second DBMS to generate result data.

16. The non-transitory storage media of claim 15, wherein the set of instructions include instructions which, when executed by the one or more processors, cause:

when the operation cost is less than the initial operation cost, then identifying the operation for offloading to the second DBMS.

17. The non-transitory storage media of claim 15, wherein the operation cost includes a cost of communication between the first DBMS and the second DBMS.

18. The non-transitory storage media of claim 15, wherein the set of instructions include instructions which, when executed by the one or more processors, 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 DBMS and a second operation cost of execution said each operation in the first set of operations at the second DBMS;

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 DBMS and a fourth operation cost of execution said each operation in the second set of operations at the second DBMS;

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, when the first plan operation cost is less than the second plan operation cost.

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

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

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

22. The non-transitory storage media of claim 15, wherein the second DBMS comprises a copy of a subset of data tables included in the first DBMS.

23. The non-transitory storage media of claim 15, wherein the first DBMS and the second DBMS comprise different hardware architecture.

24. The non-transitory storage media of claim 15, wherein the first DBMS stores the first copy of the particular data in a first storage format, and the second DBMS stores the second copy of the particular data in a second storage format different from the first storage format.

25. The non-transitory storage media of claim 24, wherein the first storage format ia a column-major format and the second storage format is a row-major format.

26. The non-transitory storage media of claim 15, wherein the first DBMS stores the first copy of the particular data according to a first database schema, and the second DBMS stores the second copy of the particular data according to a second database schema different from the first database schema.

27. The non-transitory storage media of claim 15, wherein the second DBMS is heterogeneous from the first DBMS baes on different DBMS architecture.

28. The non-transitory storage media of claim 15, wherein the different DBMS architecture includes one or more:

different storage architecture, different capability hardware components, different revision, model and/or vendor DBMS software.