JPH01102656A - Estimation processing system for system performance - Google Patents

Abstract

PURPOSE:To estimate the system performance in a short time and at a low cost by calculating automatically the retrieving performance of a data base system without using actually a data base. CONSTITUTION:The program information 4 is produced by an optimization processing means 21 based on a pseudo system control table 23 where the contents of a system control table are artificially set and in response to the input of a desired inquiry. Then the information 4 is used as the input of an estimate processing means 25 for the calculation of the performance value with no access carried out based on the information 4. In such a way, the system performance is automatically calculated without producing actually a data base. Thus the cost and the period can be reduced for the estimation of the system performance.

Description

[Detailed Description of the Invention] [Summary] A system that can automatically calculate system performance without the need to actually create a database, regarding the process of estimating system performance of a database system such as a relational database. For the purpose of a performance estimation processing method, a search path of a database system that responds to a query input is determined so as to satisfy predetermined optimization conditions using a system control table that indicates data definition and storage structure, and the search path is determined so as to satisfy predetermined optimization conditions. When estimating the performance of a system that has an optimization processing means that generates program information for access using the program information and executes access using the search route according to the program information, a pseudo-processing means that sets the contents of the system control table in a pseudo manner is used. A system control table, a system-specific value table consisting of performance information data for each unit process that constitutes the program specified by the program information, and a system-specific value table that includes performance information data when the program specified by the program information is executed. an estimation processing means that calculates based on performance information data of the eigenvalue table; the optimization processing means generates the program information based on the pseudo system control table in response to a required inquiry input; The performance value is calculated by inputting the program information to the estimation processing means without executing the program information.

[Industrial application field]

The present invention relates to estimating the system performance of a database system, particularly a system with a highly abstract data model such as a relational database. This invention relates to a system performance estimation processing method that can be calculated.

[Conventional technology]

In database systems with so-called highly abstract data models, such as well-known relational databases, users can obtain the data by inputting what kind of data they want as a request, and how to search for it. There is no need to specify the data acquisition method, such as how to reach the desired data.

A database system configured in this way has optimization technology that analyzes queries entered by users and automatically selects the search route with the lowest access cost based on information about the internal storage structure of the database. A mechanism is provided for processing.

FIG. 3 is a block diagram showing an example of the configuration of such a database system.

After analyzing the query input by the user and identifying the required data search conditions, the system optimization processing unit 1 analyzes the query statement input by the user and identifies the required data search conditions. By referring to Table 3, the data search conditions are mapped to the actual data storage structure and a search path for executing the search is determined.

That is, the user defines a relational database table in advance as shown in FIG.
A query like CK WHERE QOH <= 50 will change the value of column 008 to 50 from the 5TOCK table.
Assume that a request is made to retrieve data in the PRODUCT column of the following row.

The structure of this user's table 5TOCR and how it is actually stored shall be defined by the tables that constitute the system control table 3, and the system control table 3 is shown in FIG. 5, for example. It consists of multiple tables as shown.

Figure 5 (a) is a table control table that defines the user's tables. Refer to the column names shown in the first row of each table in the figure, indicate the table name in the "Table Name" column, and The "cluster" column indicates whether there is so-called clustering (Y/N), in which a file storing an index for searching data is provided separately.

Further, the "Stored Row Number" column indicates the maximum number of rows in the table, and the "Vacancy Rate" column specifies, as a percentage, the average value of the vacancy rate provided for the block storing the data in preparation for the addition of data.

Figure 5(b) is a column control table, with "table name"
The column is defined by the table name of the column and the column name of the "Column name" column,
The data attributes of the data in that column (e.g. integer (INTII)
, Japanese characters (NCHA), etc.) in the “Attribute” column,
In the "Precision" column, specify the number of characters, precision, etc., if necessary.

Figure 5(e) is an index control table that defines index files when clustering is present.
The name of the index file in the "Index Name" column,
The "Table Name" column shows the name of the table associated with this index, the "Number of Columns" column shows the number of columns that correspond to the key of this index in that table, and the "Freedom Ratio" column shows the average number of columns to be set in the index file. Specify the vacancy rate.

FIG. 5(d) is a related control table showing the correspondence between indexes and table columns. Add the starting key element number and specify the column on the table that corresponds to that index in the "Table name" column and "Table definition number" column.

With the system control table in this example, the table in FIG. 4 actually becomes CLUSTE, as shown in FIG.
R index 10 and INDEXI index 11
, and a data body 12 that is stored in a clustered structure.

Note that in this example, CLUSTER index IO is PN
It is a so-called non-dense index that points to a block with each data from a key that sorts the values in column O in order of size, and INDEXI index 11 is a WHN
This is a dense index that uses the required value in column O as a key to find the address of the record where the data is located, and is a so-called inverted index.

The optimization processing unit 1 refers to such a system control table 3 and uses clustering, for example, by selecting a search route that is expected to have the least number of inputs and outputs from an external storage device that stores a database as the optimal route. A procedure table 4 for executing access using the search route is generated and passed to the search execution unit 5.

Procedure table 4 shows the program unit that executes the basic functions necessary for search access, along with instructions such as repetition if necessary.
The search execution unit 5 accesses the actual database 6 and executes the search process by sequentially calling the required program units according to the procedure table 4, which is specified in the order of execution. Note that instead of creating the procedure table 4 in the procedure table format as described above, a so-called executable module of a program for executing the search process may be generated.

[Problem that the invention seeks to solve]

When configuring a database system like the one above,
When making an estimate to pre-evaluate the system performance of the search process, conventionally the estimator analyzes the query statement, database definition, data storage status, etc. and applies the parameter values determined by the estimator to a calculation formula. However, when the optimal search route is dynamically determined as described above, it is necessary to take these conditions into account, so as the database becomes more sophisticated and complex, the estimation work using the above method becomes increasingly difficult. Become. Additionally, a pilot system is actually created and operated to measure its performance, but there is a problem in that creating a pilot system requires considerable cost and time.

The present invention aims at a system performance estimation processing method that can automatically calculate system performance without actually creating a database.

[Means for solving problems]

FIG. 1 is a block diagram showing the configuration of the present invention.

The figure shows the configuration of a database system, where 21 is an optimization processing unit that generates an optimal search route procedure table 4 by referring to the system control table 3 or pseudo system control table 23 of the system catalog 22, and 25 is a system-specific value. This is an estimation processing unit that calculates the execution performance value of the process shown in the procedure table 4 based on the performance data in the table 26.

[For production]

In the system estimation mode, the optimization processing unit 21 analyzes the query literary ability and identifies the required data search conditions, and then preferentially refers to the pseudo system control table 23 stored in the system catalog 22. Accordingly, the data search conditions of the query are mapped to the actual data storage structure, an optimal search route for executing the search is determined, and a procedure table 4 for executing access using the search route is generated.

The estimation processing unit 25 receives this procedure table 4, refers to the system specific value table 26, and calculates the system performance of the search process by integrating the performance values when each program unit runs in the order specified in the procedure table 4. Calculate. The system specific value table 26 is stored in advance as a table consisting of various tables defining the number of running steps of each program and other basic performance data.

With the above method, by providing a pseudo system control table regardless of the presence or absence of an actual database, it is possible to estimate the search processing performance for the database defined by the table.

〔Example〕

In the normal mode, the optimization processing unit 21 of the system shown in FIG. By referring to the system control table 3 stored in the system control table 3, the data search conditions are mapped to the actual data storage structure, and a search path for executing the search is determined.

Further, when the estimate mode is specified, when the query literary ability is analyzed, the pseudo system control table 23 is preferentially referred to as the system control table, and the search route is determined as described above.

Note that the actual system control table 3 alone can also serve as a pseudo system control table.

The pseudo system control table 23 is used to pseudo-define user tables and their storage structure by defining a group of tables having the configuration shown in FIG. 5, for example, without providing an actual database. .

As in the conventional case, the optimization processing unit 21 sets the search route that is expected to have the least number of input/outputs to/from the external storage device storing the database as the optimal route, and takes into account the use of clustering, the vacancy rate, etc. A search route is determined using appropriate logic, and the above-mentioned procedure table 4 for executing access using the search route is generated.

If it is not the estimate mode, the search execution unit 5 accesses the actual database 6 and executes the search process by sequentially calling the required program units according to the procedure table 4, as in the prior art.

In the case of the estimation mode, the estimation processing unit 25 receives the procedure table 4, refers to the system specific value table 26, and calculates the system performance of the search process by integrating the running performance of each program according to the procedure table 4. calculate. - For this purpose, the system specific value table 26 includes a table for the number of running steps of each program, a table for the maximum work area for output images and search processing, a table for hardware performance conditions, and various other tables to determine the basis of performance. Define the data.

For example, a table of the number of running steps for each program called an access module is held in a format as shown in FIG. In Figure 2, the "Access Name" column indicates the name of each program unit specified in Procedure Table 4, and the "Access Name" column indicates the name of each program unit specified in Procedure Table 4.
Specify the calculation formula for calculating the number of running steps of the program in the "Step" column. - In addition, variables such as p and q in the calculation formula in the example in the figure are values determined depending on the data storage structure, etc. For example, the required index is determined from the access name, and as a result, the configuration of that index For example, the average number of loops and binary search times of index key comparison are estimated from the defined value indicating , and then the average number of times of 110 is determined and added to the performance calculation.

The estimate processing unit 25 outputs the calculated results to an output device such as a printer or a display.

As is clear from the above, according to this method, an actual database is not required when making an estimate, so an estimate can be obtained in a short period of time. Furthermore, since no real system is required, a computer completely different from the actual system can be used to estimate the pseudo system defined by the system eigenvalue table.

[Effects of the Invention] As is clear from the above description, according to the present invention, in estimating the system performance of a database system, it is possible to automatically calculate the system performance without actually creating a database. There is a significant industrial effect of reducing the cost and period of performance estimation, especially for highly abstract systems such as relational databases.

[Brief explanation of the drawing]

FIG. 1 is a block diagram showing the configuration of the present invention, FIG. 2 is an explanatory diagram of a system eigenvalue table, FIG. 3 is a block diagram showing an example of a conventional configuration, and FIG. 4 is a diagram showing an example of a user's table. FIG. 5 is an explanatory diagram of a system control table, and FIG. 6 is an explanatory diagram of an example of a data storage structure. In the figure, 1.21 is an optimization processing unit, 2.22 is a system catalog, 3 is a system control table, 4 is a procedure table, 5 is a search execution unit, 6 is a database, 10 is a CLUSTER index, and 11 is an INDEXI index. , 12
is the data body, 23 is the pseudo system control table,

Claims (1)

[Claims] A search route of a database system in response to a query input is determined based on a system control table indicating data definition and storage structure so as to satisfy a predetermined optimization condition, and access via the search route is determined. The content of the system control table is set in a pseudo manner when estimating the performance of a system that executes access using the search route according to the program information. A pseudo system control table (23) and a system specific value table (26) consisting of performance information data for each unit process that constitutes the program specified by the program information.
and an estimation processing means (25) that calculates a performance value when the program specified in the program information is executed, based on the performance information data of the system-specific value table, and in response to a required inquiry input, the pseudo system The program information (4) based on the control table (23) is generated by the optimization processing means (21), and the program information (4) is generated by the estimation processing means without accessing the program information. (25) A system performance estimation processing method, characterized in that the system performance estimation processing method is configured to calculate the performance value as an input.