Analysis method and apparatus for a parallel system

• US 7,599,923 B2
• Filed: 04/29/2004
• Issued: 10/06/2009
• Est. Priority Date: 06/30/2000
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Brown, Douglas P., Sinclair, Paul
• Primary Examiner(s): Alam; Shahid A
• Application Number:
  US10/834,515
• Publication Number:
  US 20040205062A1
• Time in Patent Office: 1,986 Days
• Field of Search: 707/2, 707/3, 707/4, 707/10, 707/102, 709/202, 709/203, 709/217, 709/229, 709/204, 703/20, 712/30, 714/4, 715/525, 715/713
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24532 of parallel queries
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation

Abstract

A method and apparatus of analyzing a target system comprises receiving information relating to an environment of the target system and storing cost data based on the environment information. The plan and its estimated performance for a query may be determined based on the cost data. The environment information may pertain to the environment of a parallel system (e.g., a multi-node parallel system, a single-node parallel system having plural central processing units or a system running plural virtual processors) that is running a parallel database. The cost data may identify a number of nodes in the target system, a number of central processing units in each node of the target system, and other types of system information. The cost data may be stored in a relational table having a plurality of rows. Each row of the relational table may correspond to a different target system. The cost data is used to emulate the environment of the target system and is usable by an optimizer module to derive a query plan for an SQL (structured query language) query having the lowest (or lower) cost.

Claims

1. A method of using a test system to plan the execution of a query in a target system, comprising:

receiving by the test system environment information that indicates how the target system is configured, wherein the test system is configured to emulate respective performances of multiple query-execution options of the target system;

storing cost data that indicates, for each of the multiple query-execution options, the cost in computing resources associated with choosing that option in the target system;

defining one or more diagnostic SQL statements comprising a diagnostic Set Costs statement to direct the test system to use an identified portion of the cost data to activate use of the cost data; and

using the environment information and the cost data in determining a most efficient query plan for execution by the target system.

2. The method of claim 1, further comprising determining an estimated performance of the query plan.

3. The method of claim 1, wherein receiving the environment information comprises receiving environment information of a multi-node parallel processing system.

4. The method of claim 1, wherein receiving the environment information comprises receiving environment information of a system on which plural virtual processors for handling access to data are run.

5. The method of claim 1, wherein receiving the environment information comprises receiving environment information of a parallel system running a parallel database, and wherein determining the query plan is further based on environment information related to the parallel database.

6. The method of claim 1, wherein determining the query plan comprises determining a query plan for the query, by an optimizer module, using the cost data.

7. The method of claim 1, wherein storing the environment information comprises storing data identifying a number of nodes in the target system.

8. The method of claim 7, wherein storing the environment information comprises storing data identifying a number of central processing units in each node of the target system.

9. The method of claim 1, wherein storing the cost data comprises storing the cost data into a relational table.

10. The method of claim 9, wherein plural target systems are capable of being analyzed, and wherein storing the cost data in the relational table comprises storing the cost data of each target system in a different row of the relational table.

11. The method of claim 10, further comprising activating an emulation feature and identifying one of the rows of the relational table to employ.

12. The method of claim 1, further comprising activating an emulation feature and setting one of plural levels for performing the emulation.

13. The method of claim 12, wherein setting one of the plural levels comprises setting one of a request level, a session level, an interface processor level, and a system level.

14. The method of claim 1, wherein defining the one or more diagnostic SQL statements comprises defining a diagnostic Dump Costs statement to dump cost data, based on environment information of the target system, into a test system.

15. The method of claim 1, further comprising storing cost data based on environment information of plural target systems.

16. The method of claim 1, wherein receiving the environment information comprises receiving cost-based information.

17. The method of claim 1, wherein the target system runs a parallel database, and wherein receiving the environment information comprises receiving information related to the database.

18. The method of claim 17, wherein receiving the environment information comprises receiving one or more of statistics information, data manipulation language statements, and data definition language statements.

19. The method of claim 18, wherein the target system comprises multiple nodes, and wherein receiving the environment information comprises receiving random samples of each node.

20. A software module for execution by a test system for use in planning the execution of a query in a target system, the software module comprising instructions embodied on a computer-readable media that, when executed, cause:

an interface to receive environment information that indicates how the target system is configured;

a storage device to store cost data that indicates, for each of multiple query-execution options, the cost in computing resources associated with choosing that option in the target system;

a controller to dump the cost data into the test system in response to a first diagnostic SQL statement; and

an optimizer module to determine a most efficient access plan for execution of the query by the target system based on emulation of each of the multiple query-execution options on the target system using the environment information and the cost data.

21. The software module of claim 20, wherein the interface comprises an interface to a data network coupled to the target system.

22. The software module of claim 20, wherein the storage device comprises a relational table containing plural rows, one of the rows containing cost data associated with the target system.

23. The software module of claim 22, wherein another one of the rows contains cost data associated with another target system.

24. The software module of claim 20, wherein the environment information comprises parameters indicating a system arrangement of the target system.

25. The software module of claim 24, wherein the environment information comprises a parameter identifying a number of nodes in the target system.

26. The software module of claim 25, wherein the environment information comprises a parameter identifying a number of central processing units in each node.

27. The software module of claim 20, further comprising a controller to identify a portion of the cost data to be used by the optimizer module in response to a second diagnostic statement.