Granted Patent B2

US 12,229,135 · App. 17/699,607 · Granted Feb 18, 2025

Inventors: Urvashi Oswal (Fremont, CA); Jian Wen (Hollis, NH); Farhan Tauheed (Zurich, CH); Onur Kocberber (Thalwil, CH); Seema Sundara (Nashua, NH); Nipun Agarwal (Saratoga, CA)

Assignee: Oracle International Corporation

G06F16/24544G06F11/3409G06F16/211G06F16/2282G06F16/278

Abstract

Claims (47)

1. A method comprising:

extracting one or more workload-specific features of a database workload running at a database system and one or more dataset-specific features of a database running on the database system;

wherein the one or more workload-specific features of the database workload characterize resource utilization of the database workload;

wherein the one or more dataset-specific features of the database characterize how data is logically organized within the database running on the database system;

identifying a plurality of candidate keys for determining how to partition data stored in the database across two or more computing nodes running the database system;

based at least in part, on the one or more workload-specific features, the one or more dataset-specific features, and the plurality of candidate keys, generating a set of candidate key combinations for partitioning data in the database over two or more computing nodes;

determining, using a machine-learning model, a particular candidate key combination from the set of candidate key combinations that optimizes query execution performance benefit based on the one or more workload-specific features and the one or more dataset-specific features of the database; and

generating one or more data placement commands to allocate one or more database tables of the database across the two or more computing nodes.

2. The method of claim 1 , wherein the one or more workload-specific features comprise at least one of: query execution times, a list of join tables, a list of group by tables, join attributes of tables, join keys for each table, table types, network compute times, actual size of data transferred over a network for each join operation and each group by operation, and heuristic-estimated size of data transferred over the network for each of the join operations and each of the group by operations.

3. The method of claim 1 , wherein the one or more dataset-specific features comprise at least one of: table cardinality, data placement type, actual number of distinct values in each column of the tables in the database, and estimated number of distinct values in each column of the tables in the database.

4. The method of claim 1 , further comprising, prior to identifying the plurality of candidate keys, receiving database execution logs for the database running on the database system;

wherein the database execution logs are post-execution logs comprising executed queries on the data system and their associated query plans; and

wherein the associated query plans include candidate keys from sets of join keys for each join operation and sets of group by keys for each group by operation.

5. The method of claim 4 , wherein the plurality of candidate keys represent at least one of a join key from a join operation recorded in the database execution logs, and a group by key from a group by operation recorded in the database execution logs.

6. The method of claim 1 , wherein the machine-learning model is a linear regression model.

7. The method of claim 1 , further comprising training the machine-learned model using a training corpus with data representing a plurality of different database workloads, including a plurality of different workload-specific features, a plurality of different dataset-specific features, a plurality of different database schemas, and a plurality of performance metrics associated with the plurality of different database workloads.

8. The method of claim 1 , wherein determining the particular candidate key combination from the set of candidate key combinations comprises, for each candidate key combination in the set of candidate key combinations:

calculating a first performance benefit for join operations based on a particular data placement, wherein the particular data placement is based upon the candidate key combination;

calculating a second performance benefit for group by operations based on the particular data placement; and

aggregating the first performance benefit and the second performance benefit to generate an overall performance benefit for the candidate key combination.

9. The method of claim 8 , further comprising:

determining the particular candidate key combination from the set of candidate key combinations that has the highest overall performance benefit.

10. The method of claim 1 , further comprising pruning a subset of candidate key combinations, from the set of candidate key combinations, that have join sizes below a minimum join size threshold.

11. The method of claim 1 , further comprising pruning a subset of candidate key combinations, from the set of candidate key combinations, that cause a skew of data that is above a data skew threshold.

12. The method of claim 1 , further comprising executing the one or more data placement commands to cause partitioning of data in the one or more database tables of the database and loading the partitioned data into the two or more computing nodes.

13. One or more non-transitory computer-readable media storing instructions which, when executed by one or more processors, cause:

extracting one or more workload-specific features of a database workload running at a database system and one or more dataset-specific features of a database running on the database system;

wherein the one or more workload-specific features of the database workload characterize resource utilization of the database workload;

wherein the one or more dataset-specific features of the database characterize how data is logically organized within the database running on the database system;

identifying a plurality of candidate keys for determining how to partition data stored in the database across two or more computing nodes running the database system;

based at least in part, on the one or more workload-specific features, the one or more dataset-specific features, and the plurality of candidate keys, generating a set of candidate key combinations for partitioning data in the database over the two or more computing nodes;

determining, using a machine-learning model, a particular candidate key combination from the set of candidate key combinations that optimizes query execution performance benefit based on the one or more workload-specific features and the one or more dataset-specific features of the database; and

generating one or more data placement commands to allocate one or more database tables of the database across the two or more computing nodes.

14. The one or more non-transitory computer-readable media of claim 13 , wherein the one or more workload-specific features comprise at least one of: query execution times, a list of join tables, a list of group by tables, join attributes of tables, join keys for each table, table types, network compute times, actual size of data transferred over a network for each join operation and each group by operation, and heuristic-estimated size of data transferred over the network for each of the join operations and each of the group by operations.

15. The one or more non-transitory computer-readable media of claim 13 , wherein the one or more dataset-specific features comprise at least one of: table cardinality, data placement type, actual number of distinct values in each column of the tables in the database, and estimated number of distinct values in each column of the tables in the database.

16. The one or more non-transitory computer-readable media of claim 13 storing additional instructions which, when executed by the one or more processors, further cause:

prior to identifying the plurality of candidate keys, receiving database execution logs for the database running on the database system;

wherein the database execution logs are post-execution logs comprising executed queries on the data system and their associated query plans; and

wherein the associated query plans include candidate keys from sets of join keys for each join operation and sets of group by keys for each group by operation.

17. The one or more non-transitory computer-readable media of claim 16 , wherein the plurality of candidate keys represent at least one of a join key from a join operation recorded in the database execution logs, and a group by key from a group by operation recorded in the database execution logs.

18. The one or more non-transitory computer-readable media of claim 13 storing additional instructions which, when executed by the one or more processors, further cause training the machine-learned model using a training corpus with data representing a plurality of different database workloads, including a plurality of different workload-specific features, a plurality of different dataset-specific features, a plurality of different database schemas, and a plurality of performance metrics associated with the plurality of different database workloads.

19. The one or more non-transitory computer-readable media of claim 13 , wherein determining the particular candidate key combination from the set of candidate key combinations comprises, for each candidate key combination in the set of candidate key combinations:

calculating a first performance benefit for join operations based on a particular data placement, wherein the particular data placement is based upon the candidate key combination;

calculating a second performance benefit for group by operations based on the particular data placement; and

aggregating the first performance benefit and the second performance benefit to generate an overall performance benefit for the candidate key combination.

20. The one or more non-transitory computer-readable media of claim 19 storing additional instructions which, when executed by the one or more processors, further cause:

determining the particular candidate key combination from the set of candidate key combinations that has the highest overall performance benefit.

Assignments (1)

Continuity (1)

References Cited (31)