Analysis method and apparatus for a parallel system

• US 20040205062A1
• Filed: 04/29/2004
• Published: 10/14/2004
• 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
• Granted Patent: US 7,599,923 B2
• Time in Patent Office: Days
• US Class Current: 707/3
• 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 environment information that indicates how the target system is configured;

storing 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; and

using the environment information and the cost data in determining a query plan for 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, further comprising defining one or more diagnostic SQL statements to activate use of the cost data.

15. The method of claim 14, 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.

16. The method of claim 14, wherein defining the one or more diagnostic SQL statements comprises defining a diagnostic Set Costs statement to direct a test system to use an identified portion of the cost data.

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

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

19. 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.

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

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

22. A test system for use in planning the execution of a query in a target system comprising:

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

a storage device storing 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; and

an optimizer module executable to determine an access plan of a the query based on the environment information and the cost data.

23. The system of claim 22, wherein the interface comprises an interface to a data network coupled to the target system.

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

25. The system of claim 24, wherein another one of the rows contains cost data associated with another target system.

26. The system of claim 22, wherein the environment information comprises parameters indicating a system arrangement of the target system.

27. The system of claim 26, wherein the environment information comprises a parameter identifying a number of nodes in the target system.

28. The system of claim 27, wherein the environment information comprises a parameter identifying a number of central processing units in each node.

29. The system of claim 22, further comprising a controller to dump the cost data into the test system in response to a first diagnostic SQL statement.

30. The system of claim 29, 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.