Parallel virtual optimization

• US 7,702,676 B2
• Filed: 03/12/2007
• Issued: 04/20/2010
• Est. Priority Date: 12/29/2006
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Gnau, Scott E., Morris, John Mark, Brown, Douglas P.
• Primary Examiner(s): Wong; Don
• Assistant Examiner(s): DANG, THANH HA T
• Application Number:
  US11/716,892
• Publication Number:
  US 20080162419A1
• Time in Patent Office: 1,135 Days
• Field of Search: 707/1, 707/2, 707/3, 707/4, 707/5
• US Class Current: 707/713
• CPC Class Codes: G06F 16/10 File systems; File servers
• CPC Class Codes: G06F 16/17 Details of further file system functions
• CPC Class Codes: G06F 16/21 Design, administration or maintenance of databases
• CPC Class Codes: G06F 16/90335 Query processing
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing

Abstract

A system and method for managing a domain comprised of a plurality of database systems, wherein each of the database systems performs database queries to retrieve data stored by the database systems. There are one or more virtual regulators for managing the domain and one or more closed-loop system management (CLSM) regulators for managing each of the database systems in the domain. Both the virtual regulators and CLSM regulators perform parallel virtual optimization of the database queries performed by the database systems.

Claims

1. A system for managing a plurality of database systems, comprising:

(a) a domain comprised of a plurality of database systems, wherein each of the database systems performs database queries to retrieve data stored by the database systems;

(b) one or more virtual regulators, performed by one or more processors, for managing the domain; and

(c) one or more closed-loop system management (CLSM) regulators, performed by one or more processors, for managing each of the database systems in the domain;

(d) wherein both the virtual regulators and CLSM regulators perform parallel virtual optimization of the database queries performed by the database systems;

(e) wherein the virtual regulators and CLSM regulators include proxy query optimizers that emulate a query optimizer function found in the database systems;

(f) wherein the proxy query optimizers in the virtual regulators and CLSM regulators execute in parallel, and each of the proxy query optimizers is assigned a different portion of a query execution plan space to explore; and

(g) wherein each proxy query optimizer explores its portion of the query execution plan space and upon completion of its analysis or expiration of a specified resource or time period, submits its best query execution plan to its superordinate proxy query optimizer for comparison with the query execution plans submitted by other proxy query optimizers, in order that the best query execution plan identified by all of the proxy query optimizers is communicated to the proxy query optimizer of the virtual regulator.

2. The system of claim 1, wherein the proxy query optimizers are arranged in a tree, which is used both for dividing the query execution plan space and for merging results to find a superior query execution plan.

3. The system of claim 1, wherein the virtual regulator is assigned the query execution plan space in its entirety and divides the query execution plan space into a portion to retain for itself and a remaining portion that is further divided between subordinate CLSM regulators.

4. The system of claim 3, wherein each of the subordinate CLSM regulators divides the query execution plan space into a portion to retain for itself and a remaining portion that is further divided between subordinate CLSM regulators.

5. The system of claim 3, wherein each of the subordinate CLSM regulators divides the query execution plan space between subordinate CLSM regulators.

6. The system of claim 1, wherein the virtual regulator is assigned the query execution plan space in its entirety and divides the query execution plan space between subordinate CLSM regulators.

7. The system of claim 6, wherein each of the subordinate CLSM regulators divides the query execution plan space into a portion to retain for itself and a remaining portion that is further divided between subordinate CLSM regulators.

8. The system of claim 6, wherein each of the subordinate CLSM regulators divides the query execution plan space between subordinate CLSM regulators.

9. The system of claim 1, wherein the virtual regulator communicates the best query execution plan to a query optimizer function of the database systems actually performing the query, which may then bypass their own query execution plan search.

10. A method of managing a domain comprised of a plurality of database systems, wherein each of the database systems performs database queries to retrieve data stored by the database systems, the method comprising:

(a) managing the domain using one or more virtual regulators performed by one or more processors; and

(b) managing each of the database systems in the domain using one or more closed-loop system management (CLSM) regulators performed by one or more processors;

(c) wherein both the virtual regulators and CLSM regulators perform virtual parallel optimization of the database queries performed by the database systems;

(d) wherein the virtual regulators and CLSM regulators include proxy query optimizers that emulate a query optimizer function found in the database systems;

(e) wherein the proxy query optimizers in the virtual regulators and CLSM regulators execute in parallel, and each of the proxy query optimizers is assigned a different portion of a query execution plan space to explore; and

(f) wherein each proxy query optimizer explores its portion of the query execution plan space and upon completion of its analysis or expiration of a specified resource or time period, submits its best query execution plan to its superordinate proxy query optimizer for comparison with the query execution plans submitted by other proxy query optimizers, in order that the best query execution plan identified by all of the proxy query optimizers is communicated to the proxy query optimizer of the virtual regulator.

11. The method of claim 10, wherein the proxy query optimizers are arranged in a tree, which is used both for dividing the query execution plan space and for merging results to find a superior query execution plan.

12. The method of claim 10, wherein the virtual regulator is assigned the query execution plan space in its entirety and divides the query execution plan space into a portion to retain for itself and a remaining portion that is further divided between subordinate CLSM regulators.

13. The method of claim 12, wherein each of the subordinate CLSM regulators divides the query execution plan space into a portion to retain for itself and a remaining portion that is further divided between subordinate CLSM regulators.

14. The method of claim 12, wherein each of the subordinate CLSM regulators divides the query execution plan space between subordinate CLSM regulators.

15. The method of claim 10, wherein the virtual regulator is assigned the query execution plan space in its entirety and divides the query execution plan space between subordinate CLSM regulators.

16. The method of claim 15, wherein each of the subordinate CLSM regulators divides the query execution plan space into a portion to retain for itself and a remaining portion that is further divided between subordinate CLSM regulators.

17. The method of claim 15, wherein each of the subordinate CLSM regulators divides the query execution plan space between subordinate CLSM regulators.

18. The method of claim 10, wherein the virtual regulator communicates the best query execution plan to a query optimizer function of the database systems actually performing the query, which may then bypass their own query execution plan search.