JP2001142898A - Inquiry process execution availability determination method - Google Patents

Abstract

(57) [Summary] [Problem] To suppress the execution of a long-running query that is a query that is executed longer than expected by a user. An execution time prediction (123) for estimating an execution time of an inquiry process, an execution availability determination (124) for determining whether or not to execute an inquiry based on the execution time prediction, and correcting the execution time predicted during the inquiry process. Execution time prediction correction unit (1
26). As a result, the execution of a long-running query can be suppressed, so that the throughput of the data system hub or the DBMS is improved.

Description

DETAILED DESCRIPTION OF THE INVENTION

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a computer system, and more particularly to a method for determining whether a query can be executed by a data processing system that processes a user's query using one or more databases.

[0002]

2. Description of the Related Art At present, a computer system of a company has many database management systems (DBMS). These DBMSs have been historically added for different purposes with the development of companies. At present, deregulation among industries is progressing rapidly, and with this, each company tends to add new businesses. At this time, with the introduction of a new business, there are many cases where a new DBMS is introduced.

On the other hand, with the deregulation, each company is trying to provide better services to customers with new services not provided by other companies, and as a result, to acquire more excellent customers. At this time, the need to analyze past corporate activities, customer trends, and the like accumulated in the DBMS group has increased, and the number of companies that build data warehouses and data marts has increased.

To build a data warehouse or a data mart, it is necessary to convert the data stored in a large number of DBMSs into one logically integrated database. In addition to building a database that is the basis for analytical processing such as data warehouses and data marts, in order to quickly launch new businesses,
2. Description of the Related Art Logical integration of conventional DBMS groups is performed for the purpose of increasing the competitiveness of a company. This is because, based on a logically integrated database, it is possible to speed up the construction of an application program (application) for a new business.

[0005] As a method of logically integrating the DBMS group, a DBMS is provided between the DBMS group and the application group.
There is a way to set up a "database hub" system that provides unified access to the herd. The database hub queries the application (typically St
structured Query Language (S
(QL) language, and the query is decomposed and converted into a query for a DBMS group. Then, the database hub issues the decomposed and converted query to the DBMS group, collects data for creating a query result from the DBMS group, obtains a final result for the application query, and returns the result to the application. .

[0006] Inquiries about various applications are issued to such a database hub. Such a group of queries is diverse from a complex query with a long execution time issued in an analysis system task to a simple query with a short execution time issued in a routine task.

[0009] Since the query issued in the analysis-related work is complicated, a query having a longer execution time than the user predicts (a long-running query) may be issued. In addition, a long-running query may be issued unintentionally due to a typo or the like.

[0008] Such a long-running query causes a decrease in the throughput of a data processing system such as a database hub or a DBMS. Because a long-running query uses computer resources such as a CPU and a memory for a long time, it prevents other queries from being executed. Also, since the process is not completed by the time the user needs the query result,
There is a possibility that the business will be hindered. For this reason, in order to operate the data processing system efficiently, a function for suppressing a long-running query is required.

In order to suppress the execution of a long-running query, the prior art predicts costs such as the CPU time and the number of I / Os to the secondary storage device required for executing the query processing. The cost is used as a criterion for determining whether the query can be executed. Document "DB2 Universal Dat
base for OS / 390 Release Pl
anning Guide Version 6 "(pp. 10-26).
67-pp. 79, IBM Corporation, June 1999; hereinafter referred to as Document 1) (hereinafter referred to as Known Example 1).

[0010]

The query processing cost reflects the execution time, but not the execution time itself.
Even with the same processing cost, the execution time differs depending on the available computer resources and the computer load at the time of execution. Therefore, in the execution possibility determination based on the cost, execution of a query that is not a long-running query may be actually suppressed.
Conversely, execution of the query may not be suppressed even though the query is a long-running query. For this reason, it is necessary to determine whether execution is possible based on the execution time.

That is, a first problem to be solved by the present invention is to judge whether execution is possible based on the execution time and to suppress the execution of a long-running query.

In addition, since the execution time prediction of the query processing before execution may include an error, a long-running query may be erroneously executed. For this reason, it is necessary to stop a query that is being executed for a long time, even for a query that is being executed.

That is, a second problem to be solved by the present invention is to suppress the execution of a long-running query during execution.

In the case of a database hub, when processing a query (first query) issued from an application, a query (second query) is issued to the DBMS. Since the DBMS also processes queries issued from sources other than the database hub, it is desirable that the second query does not hinder the processing of queries issued from sources other than the database hub. Since the DBMS is usually managed independently of the database hub, it is difficult for the DBMS to suppress a second query that hinders other query processing. For this reason, it is necessary to suppress the database hub from issuing the second query that hinders the query processing of the DBMS.

[0015] That is, a third problem to be solved by the present invention is to suppress the issuance of a query that hinders the query processing of the DBMS by the database hub.

When estimating the execution time of query execution, it is necessary to generate an accurate execution plan. In the case of a database hub, an execution plan in DBMS cannot be specified.
For this reason, the database hub predicts the execution plan in the DBMS.

That is, a fourth problem to be solved by the present invention is to obtain an accurate execution plan processed by the DBMS at the database hub.

It is an object of the present invention to enable efficient operation of a database hub and a DBMS in an analysis business by solving the above four problems.

[0019]

As a means for solving the first problem, the present invention provides a database hub (or D).
In BMS), an execution time prediction is introduced. The execution time prediction is a method of predicting the execution time from a query issued from an application. First, the cost required for query processing is calculated from the query execution plan. The execution time is predicted from the cost using the computer performance and the computer load for executing the query processing from the cost. Execution feasibility is determined based on the predicted execution time (predicted execution time). This solves the first problem of determining whether execution is possible based on the execution time and suppressing the execution of a long-running query.

As a means for solving the second problem, in the present invention, correction of execution time prediction is introduced into a database hub (or DBMS). The execution time prediction correction is a method of correcting the predicted execution time before execution using the progress information of the query process being executed. For example, when the execution time up to a certain process is 1.5 times the predicted execution time up to the process, the correction is made such that the predicted execution time of the entire query process is 1.5 times. It is determined whether or not the query being executed is executable based on the corrected predicted execution time. This solves the second problem of suppressing the execution of a long-running query during execution.

As a means for solving the third problem, according to the present invention, it is possible to set a criterion for determining whether a database hub can execute an inquiry issued to a DBMS.
The execution permission / non-permission judgment is also performed for the query issued to the user. This solves the third problem of suppressing the issuance of a query that hinders the query processing of the DBMS by the database hub.

As a means for solving the fourth problem, the present invention introduces an execution plan modification for modifying an execution plan using an execution plan obtained from a DBMS in a database hub. After generating an execution plan from a query, DB
Obtain the execution plan on the MS and modify the execution plan on the database hub. If a cost can also be acquired when acquiring an execution plan in the DBMS, the cost is used. This has solved the fourth problem of obtaining an accurate execution plan processed by the DBMS at the database hub.

[0023]

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS One embodiment of the present invention will be described with reference to the drawings.

Overall Configuration One embodiment (first embodiment) of the present invention will be described with reference to FIG.
Will be described.

FIG. 1 shows a computer system in which the first embodiment is preferably used. The entire first embodiment is 1
One or more computers (data processing systems 100, 1
One or more client computers 101, 101 ',
.., A management computer 102 and one or more DB servers 105, 105 ′,...) Are mutually connected by a client-side network 103 and a server-side network 104.

Each of the client-side network 103 and the server-side network 104 may be a LAN often used by a certain organization (company, school, or similar organization) or in one department, or may be a plurality of geographically dispersed points. May be part or all of the WAN that connects Further, these networks may be a connection network between computers or a connection network between processor elements inside a parallel computer. Further, the client side network 103 and the server side network 104
May be the same network.

The data processing system 100, client computers 101, 101 ', ..., management computer 102, DB servers 105, 105', ... are all personal computers, workstations, parallel computers, large computers, small portable computers. Any computer may be used. In FIG. 1, the data processing system 100, the client computers 101, 10
1 ′,..., Management computer 102, DB server 10
5, 105 ′,... Are shown as independent computers.
One computer is acceptable. The numbers and configurations of the data processing system 100, the client computers 101, 101 ',..., The management computer 102, and the DB servers 105, 105',... Shown in FIG. Is not limited.

Client computers 101, 10
At 1 ′,..., Applications 110, 110 ′,. The application 110, 110 ′,... Queries (eg, St.
structured Query Language (SQ
L) a query described in a query language such as a language).

In the DB servers 105, 105 ',...
A DBMS 112 that manages one or more databases,
112 ', ... are operating. DBMS 112, 11
2 ′,... Hold the managed database on the secondary storage devices 106, 106 ′,... And refer to or update the database in response to an inquiry from another program. A database can be a relational database consisting of one or more tables of one or more records consisting of one column above the data, and can be other types of databases (network-based, hierarchical, object-oriented, etc.). (Database, object storage) or file system.

In the management computer 102, a management application 111 is running. The management application 111 is a program for managing the data processing system 100, and is used by an administrator of the data processing system 100 or the entire system of FIG.

The data processing system 100 receives the first inquiry issued by the client computers 101, 101 ',.
Create and issue one or more second queries to 5 ′,.
The reference or update specified by the first query is performed, and the resulting data is returned to the issuer of the first query. That is, the data processing system 100 includes the DB servers 105, 105 ',.
Is a database hub that realizes unified access to the database group held by the client computers 101 and provides an integrated database to the client computers 101, 101 ′,.

Input / output processing unit 120, query analysis / optimization unit 121, execution plan modification unit 122, execution time prediction unit 1
23, execution possibility determination units 124 and 127, execution control unit 12
5. Execution time prediction correction unit 126, DBMS information acquisition unit 1
28, the execution availability determination management unit 129 is a constituent element of the data processing system 100. A detailed description of each component will be described later, and will be described only briefly here.

The input / output processing unit 120 receives inquiry requests from the client computers 101, 101 ',..., Receives management requests from the management computer 102, and replies to these queries.

The query analysis / optimization unit 121 performs query analysis and query optimization on the query received by the input processing unit 120, and creates an execution plan.

The execution plan correction unit 122 performs query analysis /
The execution plan generated by the optimization unit 121 is
A correction is made using the execution plan obtained from S.

The execution time prediction unit 123 predicts the execution time using the execution plan created by the execution plan correction unit 122.

The execution possibility determination units 124 and 127 compare the execution time predicted by the execution time prediction unit 123 and the execution time prediction correction unit 126 with the execution availability determination criterion obtained from the execution availability determination criterion control unit 129. Then, it is determined whether the query can be executed.

The execution control unit 125 processes the query based on the execution plan and creates a result for the query.

The execution time prediction correcting section 126 corrects the predicted execution time using the progress information of the query being executed from the execution control section 125.

The DBMS information acquisition unit 128 manages various information of the DBMS, and issues an information acquisition inquiry corresponding to the type of the DBMS in response to a request from the execution plan correction unit 122 and the execution time prediction unit 123.

The executability judging management section 129 manages the executability judging criteria used by the executability judging sections 124 and 127.

The above is the overall configuration of the first embodiment.

The embodiment of the data processing system also serving as the DBMS 112, 112 ',... Has a configuration in which the execution plan correction unit 122 and the DBMS information acquisition unit 128 are removed. Also, the processing performed by the data processing system described below is essentially the same as that of the first embodiment.
The detailed description of the embodiment of the data processing system serving also as 2, 112 ',... Is omitted.

Processing Performed by Data Processing System Processing performed by the data processing system 100 includes inquiry processing,
There is an executability determination criterion setting process.

Inquiry Processing Flow The inquiry processing flow of the data processing system will be described with reference to FIG.

The first inquiry issued by the application 110 reaches the input / output processing unit 120 via the client side network (130).

When the input / output processing unit 120 recognizes that the request of the application 110 is an inquiry, the first
Pass the query to the query analysis / optimization unit 121 (13
1).

The query analysis / optimization unit 121 performs query analysis and query optimization on the first query to create a first execution plan, and passes the first execution plan to the execution plan correction unit 122 (132). ). In the query analysis, lexical analysis, syntax analysis, and semantic analysis are performed on the first query.
Generates a first parse tree of the query of In the query optimization, a first execution plan is created from a first parse tree generated by query analysis. The query optimization includes a rule-based optimization and a cost-based optimization. In the data processing system 100, either may be used. The operations performed in query analysis and query optimization are techniques used in compilers and database management systems, and will not be described in further detail here.

This is an example of an execution plan in which the data structure shown in FIG. 3 is generated. The execution plan has a tree structure, and each node 311, 312, 313, 314, 31
5, 316, and 317 have the same configuration as the intra-node data structure 300. The node ID 301 is a unique identifier within the execution plan. For example, a continuous integer may be used for each node of the execution plan, or an address having data of the node may be used. The operation content 302 indicates the operation content performed in the node. For example, "join
-At-DBMS1 "is" Join with DBMS1 "
"Access-DBMS1.T3"
Indicates that "refer to table T3 of DBMS1".
“Database-hub-join” indicates that “join with the data processing system 100”. The number of predicted result rows 303 is a predicted value of the number of result rows of the operation content 302. When the number of predicted result rows 303 is −1, it indicates that the number of predicted result rows 303 has not been calculated. The predicted cost 304 indicates the cost required to process the subtree below the node. CPU time, the number of CPU steps, the number of I / Os, and the like are used for the cost. When the value of the prediction cost 304 is −1, it indicates that the prediction cost 304 has not been calculated. The predicted execution time 305 indicates the execution time required to process the subtree below the node. If the predicted execution time 305 is -1, it indicates that the predicted execution time 305 has not been calculated.

The execution plan modification unit 122 modifies the first execution plan using the information on the execution plan obtained from the DBMS, generates a second execution plan, and causes the execution time prediction unit 123 to execute the second execution plan. Hand the plan (133). The operation of modifying the execution plan will be described in detail in the flow of the execution plan modification process.

The execution time prediction unit 123 predicts an execution time using the second execution plan received from the execution plan correction unit 122, and uses the predicted execution time (first predicted execution time) as a second execution plan. Is set to the predicted time 305 of the node, and the second execution plan is passed to the execution possibility determination unit 124 (134). The operation of calculating the execution time from the execution plan will be described in detail in the flow of the execution time prediction process.

The execution availability determination unit 124 determines the availability of the first query based on the first predicted execution time predicted by the execution time prediction unit 123, and if the execution is possible, the execution control unit 125 The second execution plan is passed (135), and if execution is not possible, an error is returned to the input / output processing unit 120 as a result of the first query (160). The details of the execution possibility determination will be described in the flow of the execution possibility determination process.

The execution controller 125 processes the first query based on the second query processing operation, and returns the result to the input / output processor 120. DBM according to the second query processing operation
In S112, 112 ',..., A second inquiry is issued via the server-side network 104 (136), and the result of the second inquiry is obtained (137). If processing is required, the result of the second query is processed to create the result of the first query. Input / output processing unit 1
Return to 20 (138). At the time of execution of the first inquiry processing, execution time prediction correction and execution feasibility determination processing are performed. If it is determined that execution is not possible, the processing is stopped, and an error is returned to the input / output processing unit 120 as a result of the first inquiry (174). . For the operation of the execution time prediction correction and the execution feasibility determination process,
The details of the execution time prediction correction and execution availability determination processing will be described in detail. The timing for performing the execution time prediction correction and the execution availability determination processing may be any time. For example, a specification for the entire data processing system 100 may be made, such as "perform for all queries executed every 30 minutes", or "execution time prediction after query to DBMS 112" may be included in the query processing operation. Perform correction and execution feasibility determination process "for each inquiry.

The input / output processing unit 120 receives the result of the first query from the execution possibility determination unit 124 or the execution control unit 125, and receives the result of the first query from the application 1 that issued the first query.
10, the result of the first inquiry is returned via the client-side network 103 (138).

The above is the flow of the inquiry process.

Flow of Execution Plan Correction Process The flow of the execution plan correction process will be described with reference to FIGS.

In step 200, a second query group to be issued to each DBMS is generated from the first execution plan (predicted execution plan) received from the query analysis / optimization unit 121.

In step 201, a DBMS execution plan (acquisition execution plan) is received from the second inquiry group via the DBMS information acquisition unit 128.

The DBMS information acquisition unit 128 receives a query group from the execution plan modification unit 122 (140), and issues a query for acquiring the execution plan according to the DBMS (141). For the query for acquiring the execution plan, for example, in the case of DB2 / OS390, an EXPLAIN statement can be used. When the result is received from each DBMS (14
2), the data structure of the execution plan used in the data processing system 100 is returned to the execution plan correction unit 122 (143).

In step 203, when the acquisition execution plan is received, the subtrees to be processed by each DBMS in the prediction execution plan to be corrected are listed.

In step 204, it is determined whether all the subtrees listed in step 203 have been compared with the corresponding acquisition execution plans. If all subtrees have not been compared (N), the process proceeds to step 205. If all the subtrees have been compared (Y), the execution plan modification process ends.

In step 205, when the cost or the number of result rows is set in the acquisition execution plan, the prediction result row number 303 or the prediction cost 304 of the corresponding node of the prediction execution plan is set.

At step 206, the subtrees of the acquisition execution plan and the prediction execution plan are compared to determine whether they are the same execution plan. In the case of the same execution plan (Y), the process returns to step 204. If the execution plans are different (N), the process proceeds to step 207.

At step 207, the subtree of the predicted execution plan is modified so that it becomes the same execution plan as the acquisition execution plan.

An example of the execution plan correction process is shown in FIGS.
As shown in FIG. The execution plan in FIG. 3 is the predicted execution plan before correction, FIG. 4 is the acquisition execution plan obtained from the DBMS 1, and FIG. 5 is the corrected prediction execution plan. Node 3
Although 12 or less subtrees correspond to the acquisition execution plan in FIG. 4, the execution plan is different, and therefore, in FIG. Also DBMS1
In the acquisition execution plan, the cost is also acquired, and thus the value of the predicted cost of the node 502 is set.

The execution plan obtained by modifying the execution plan in the DBMS is passed to the execution time prediction unit 123 (133).

The above is the flow of the inquiry processing operation correction processing.

Flow of Execution Time Prediction Process The flow of the execution time prediction process will be described with reference to FIGS.

In step 601, the execution time prediction unit 12
3 acquires DBMS statistical information and DBMS performance information 700 from the DBMS information acquiring unit 128 (144).

The DBMS statistical information is information necessary for calculating the cost of the subtree of the execution plan processed by the DBMS. For example, the size of the table (the number of records in the table), the maximum value, the minimum value, the average value of the values in the column, the number of different values in the column, the distribution of the values in the column, and the like.

The DBMS performance information 700 has the structure shown in FIG. The DBMS name 701 is a unique name in the data processing system 100 for identifying the DBMS. 70 CPUs
2 is the number of CPUs of the computer on which the DBMS operates. The MIPS value 703 is the number of instructions that can be processed per second by one CPU of the computer on which the DBMS operates. l
The load average 704 is the number of execution processes per unit time of the computer on which the DBMS operates.

In step 602, subtrees of the execution plan processed by each DBMS are listed.

In step 603, it is determined whether the predicted execution times 305 of all the subtrees listed in step 602 have been calculated. Predicted execution time 30 for all subtrees
If 5 has not been calculated (N), the process proceeds to step 604. If the predicted execution times 305 of all subtrees have been calculated (Y), the process proceeds to step 607.

In step 604, it is determined whether the predicted cost 304 of the target subtree has been calculated.
If the predicted cost 304 has been calculated (Y) Step 6
06, if not calculated (N) Step 60
Go to 5.

In step 605, the prediction cost of the subtree is calculated using the DBMS statistical information acquired in step 601.
04 is requested. The method of calculating the prediction cost 304 of the subtree has been studied for a long time in the field of databases, and will not be described in detail here.

In step 606, a predicted execution time 305 is calculated from the predicted cost 304 of the subtree using the DBMS performance information 700. The predicted execution time 305 is calculated from the predicted cost 304 and the DBMS performance information 700 using the function F. For example, when the predicted cost 304 is calculated by the number of CPU steps, the predicted execution time 305 = F (predicted cost 304, DBMS performance information 700) = predicted cost 304 / ｛CPU number 702 * MIPS value 703 / lo
ad average 704}.

At step 607, the execution time 305 of the execution plan is obtained from the predicted execution time 305 of the partial tree. First, the cost of a subtree composed of nodes processed by the data processing system 100 is determined, and the execution time is calculated from the cost. Since the calculation method of the cost and the execution time of the subtree composed of the nodes processed by the data processing system 100 is the same as the calculation method of the prediction cost 304 and the prediction execution time 305 of the subtree processed by the above-described DBMS. Omitted. The predicted execution time 305 of the execution plan is D
The calculation is performed from the predicted execution time 305 of the subtree processed by the BMS and the calculated execution time of the subtree composed of the nodes processed by the data processing system 100. For example, D
There is a method in which the sum of the predicted execution time of the subtree processed by the BMS and the calculated execution time of the subtree composed of the nodes processed by the data processing system 100 is used as the predicted execution time 305 of the execution plan. As another calculation method,
In some cases, the sum of the maximum value of the predicted execution time of the subtree processed by the DBMS and the calculated execution time of the subtree composed of nodes processed by the data processing system 100 is used as the predicted execution time of the execution plan. .

The execution plan for which the predicted execution time 305 has been calculated is passed to the execution possibility determination section 124 (134).

The above is the flow of the execution time prediction process.

Flow of Execution Applicability Determination Process The flow of the execution availability determination process will be described with reference to FIGS.

In step 801, the execution availability determination unit 12
4 is an execution availability determination criterion 9 from the execution availability determination management unit 129.
00 is received (182).

The executability judging criterion 900 has the structure shown in FIG. The DBMS name 901 indicates a DBMS to which a query is issued in response to a query for determining whether execution is possible. For example, DB
A name unique to the data processing system 100 for identifying the MS, or “*” indicating the data processing system 100 and all DBMSs. The user name 902 indicates a user who issues a query for determining whether or not execution is possible. For example, “*” indicating a user account using the data processing system 100 or all users. Execution time 903 is DBMS
User name 9 issued to the DBMS specified by the name 901
It is the maximum value of the estimated execution time 305 that can be executed by the user's inquiry designated by 02. Number of CPU steps 904
Is the maximum value of the number of CPU steps that can be executed by the user designated by the user name 902 issued to the DBMS designated by the DBMS name 901. Each line of the execution availability determination criterion 900 in FIG. 9 is, in order from the top, “all user inquiries issued to the data processing system 100 are 7,200 seconds or less based on the execution time, and 720 based on the number of CPU steps. 000M steps or less ",
"The USER1 query issued to the DBMS1 must be 1,200 seconds or less in terms of execution time and 480,000 M steps or less in terms of the number of CPU steps."
"The inquiries of all users issued to the DBMS 1 are 600 seconds or less based on the execution time and 240,000 M steps or less based on the number of CPU steps", "DB
The inquiry of all users issued to the MS 2 indicates that the execution time is 3,600 seconds or less and the CPU step number is 360,000 M steps or less.

In step 802, it is determined whether or not the execution of the execution plan has been determined based on all the target execution determination criteria 900. Target execution availability determination criteria 900
Is the row where the user name 902 is equal to the name of the user who issued the query, or the DBMS name 901 of the row where the user name 902 is "*" is the DBMS name 901 of the row where the user name 902 is equal to the name of the user who issued the query. Is not equal to If the execution is not judged by all target execution judgment criteria 900 (N), the process proceeds to step 803, and if the execution is judged by all target execution judgment 900, step (Y) is performed. Proceed to 805.

In step 803, it is determined whether or not the execution plan can be executed based on one execution criterion. Since there are a plurality of criteria for determining whether the execution plan can be executed, it may be determined that the execution plan can be executed only when all the conditions are satisfied. In addition, a case where a condition of a specific number of criteria among a plurality of criteria is satisfied may be determined to be executable, and a case where a specific standard is satisfied may be determined to be executable. If it is determined that execution is possible (Y), the process returns to step 802, and if it is not determined that execution is possible (N), the process proceeds to step 804.

In step 804, it is determined that the execution plan cannot be executed, and an error is returned to the input / output processing unit 120 as a result of the inquiry (160).

At step 805, it is determined that the execution plan is executable, and the execution plan is passed to the execution control unit 125 (13).
5).

The above is the flow of the execution availability determination processing.

Flow of Execution Time Prediction Correction and Execution Availability Determination Process The flow of execution time prediction correction and execution availability determination processing will be described with reference to FIGS.

In step 1001, the execution control unit 125
Receives the query execution progress information 1100 from (17)
0).

The progress information 1100 has the structure shown in FIG. The node ID 1101 is a node ID 301 of a node configuring an execution plan. Execution time 1102
Is the execution time of the subtree below the node specified by the node ID 1101.

In step 1002, step 1001
The predicted execution time 305 of the execution plan is corrected using the progress information 1100 received in step (1). As an example of a method for correcting the predicted execution time, there is a method in which the predicted execution time 305 of the node specified by the node ID 1101 is set to the execution time 1102, and the predicted execution time 305 of the execution plan is calculated again in the same manner as in step 607. . In addition, the execution time 11
02 is the predicted execution time 305 of the node ID 1101.
If it is five times, the predicted execution time 305 of the execution plan is set to 1.
A method of multiplying by 5 is also conceivable. The execution plan whose predicted execution time has been corrected is passed to the execution possibility determination unit 127 (17).
1).

In step 1003, the execution determination section 127 determines whether or not the execution plan having the corrected predicted execution time 305 can be executed. The operation of the execution availability determination is the same as the flow of the above-described execution availability determination process, and thus the description is omitted here.

In step 1004, step 1003
Is determined. If it is determined that the execution plan is executable (Y), the process proceeds to step 1005. If it is determined that the execution plan is not executable (N), the process proceeds to step 1006.

At step 1005, the execution of the inquiry process is continued.

In step 1006, the execution is notified to the execution control unit 125 (172).

In step 1007, post-processing of the query processing that has become impossible is performed, the query processing is stopped, and an error is returned to the input / output processing unit 120 as a result of the query (1).
73).

The flow of the execution time prediction correction and execution feasibility determination processing has been described above.

Flow of Executability Determination Criteria Setting Process The flow of the executability determination criterion setting process will be described with reference to FIG.

An execution permission / non-permission criterion setting request is issued from the management application 111 to the data processing system 100 (180).

When the input / output processing unit 120 identifies the request as the execution permission / criterion setting request, the input / output processing unit 120 sends the execution permission / criterion setting request to the execution permission / judgment management unit 129 (181).

The execution availability determination management unit 129 changes the execution availability determination criterion 900 according to the management method in response to the execution availability determination criterion setting request. If the execution criterion is managed by the DBMS, the execution criterion is converted into an inquiry according to the request, and the DB that manages the execution criterion
Issued to MS. In the case where it is managed on the memory, the execution availability determination management unit 129 directly rewrites the execution availability determination criterion 900.

The above is the flow of setting the execution feasibility determination criteria.

[0103]

According to the present invention, the following effects can be obtained.

(1) The execution time prediction unit 123, the DBMS performance information 700, and the execution availability determination unit 124 can predict the query execution time to determine whether the execution is possible, and can suppress the execution of a long-running query.

(2) The execution time prediction correction unit 126, the progress information 1100, and the execution availability determination unit 127 can suppress the execution of a long-running query that has been executed due to an error in the execution time prediction.

(3) DBMS 900
By making it possible to set each time, queries issued from the database hub that hinders the query processing of the DBMS can be suppressed by the database hub.

(4) The execution plan modifying unit 122
You will be able to get accurate execution plans at the database hub.

With the above effects, the throughput of the database hub and the DBMS in the analysis-related business can be improved, and efficient operation becomes possible.

[Brief description of the drawings]

FIG. 1 is a block diagram showing the overall configuration of a first embodiment.

FIG. 2 is a flowchart of an execution plan correction process.

FIG. 3 is a diagram showing an example of a prediction execution plan.

FIG. 4 is a diagram showing an example of an acquisition execution plan.

FIG. 5 is a diagram showing a prediction execution plan after correction.

FIG. 6 is a flowchart of an execution time prediction process.

FIG. 7 is a configuration diagram of DBMS performance information.

FIG. 8 is a flowchart of an execution availability determination process;

FIG. 9 is a configuration diagram of an execution availability determination criterion.

FIG. 10 is a flowchart of execution time prediction correction and execution availability determination processing.

FIG. 11 is a configuration diagram of progress information.

[Explanation of symbols]

100: data processing system 101, 101 ',...: Client computer 102: management computer 103: client-side network, 104: server-side network 105, 105' ...: DB server 106, 106, 106 '...: secondary storage device 110, 110 '...: client application 111: management application 112, 112' ...: DBMS, 120: input / output processing unit 121: query analysis / optimization unit, 122: execution plan modification unit 123: execution time prediction unit, 124 Executability determination unit 125: Execution control unit 126: Execution time prediction correction unit 127: Executability determination unit 128: DBMS information acquisition unit 129: Executability determination management unit

 ──────────────────────────────────────────────────続 き Continuing on the front page (72) Inventor Tadashi Nishizawa 1-280 Higashi Koikekubo, Kokubunji-shi, Tokyo Inside the Hitachi, Ltd.Central Research Laboratory (72) Inventor Nobuntoshi Sagawa 1-280 Higashi Koikekubo, Kokubunji-shi, Tokyo Hitachi, Ltd. F-term in the Central Research Laboratory (reference) 5B075 KK07 ND34 QR00 QS20 5B082 GA08 GC04

Claims (8)

[Claims] A first program for performing data processing, one or more second programs corresponding to an application program, and one or more third programs for performing database management.
Program runs on one computer or on a plurality of computers connected by a network,
A third program maintains one or more databases of one or more tables of one or more records having one or more columns, and the first program executes a first program from the second program. Upon receiving a query, the first program generates a query group issued from the first query to the third program and an execution plan of the query group, and the first program generates the query group according to the execution plan. 3 is a query processing method for issuing a first query using data in the database obtained from the third program, wherein the first program obtains a result for the first query by executing the first query from the execution plan. And a process for determining whether or not the first query can be executed using the predicted execution time. 2. The method according to claim 1, wherein said predicting process includes a process of predicting an execution time from an execution plan using a load of a computer on which a third program is running. 3. A first program for performing data processing, one or more second programs corresponding to an application program, and one or more third programs for performing database management.
Program runs on one computer or on a plurality of computers connected by a network,
A third program maintains one or more databases with one or more tables having one or more codes, the first program accepts a first query from the second program, Generates a query group to be issued from the first query to the third program and an execution plan of the query group, and the first program issues the query group to the third program according to the execution plan. An inquiry processing method in which a first program obtains a result of a first inquiry using data in the database obtained from a third program, wherein the first program comprises one of a group of queries issued to the third program. A method for determining whether to execute an inquiry process, comprising: a process of estimating an execution time of two queries; and a process of determining whether or not to execute a first query using the predicted execution time. 4. A process according to claim 1, wherein said determining process includes a process of obtaining information on query execution from a third program.
Or a method for determining whether or not to execute the inquiry process described in 3. 5. The execution possibility of the query processing according to claim 4, wherein the processing for obtaining the information on query execution includes an execution plan correction processing for correcting an execution plan using information on query execution obtained from a third program. Judgment method. 6. A first program for performing data processing, one or more second programs corresponding to an application program, and one or more third programs for performing database management.
Program runs on one computer or on a plurality of computers connected by a network,
A third program maintains one or more databases of one or more tables of one or more records having one or more columns, and the first program executes a first program from the second program. Upon receiving a query, the first program generates a query group issued from the first query to the third program and an execution plan of the query group, and the first program generates the query group according to the execution plan. 3 is a query processing method in which the first program obtains a result of the first query using data in the database obtained from the third program. Executable query processing, comprising: a process of correcting the predicted execution time using information; and a process of determining whether to continue query execution using the corrected execution time. Determination method. 7. The computer according to claim 1, wherein said first program also functions as a third program, and said computer includes one or more first databases instead of one or more third programs. 2. The method according to claim 1, wherein the program obtains data necessary for the inquiry from the first database in place of the third program. 8. A first program for performing data processing, one or more second programs corresponding to an application program, and one or more third programs for performing database management.
Program runs on one computer or on a plurality of computers connected by a network,
A third program maintains one or more databases of one or more tables of one or more records having one or more columns, and the first program executes a first program from the second program. Upon receiving a query, the first program generates a query group issued from the first query to the third program and an execution plan of the query group, and the first program generates the query group according to the execution plan. 3 is a data processing system of a computer system, wherein the first program obtains a result of the first query using data in the database obtained from the third program. Executing a query process, comprising: a process of predicting from an execution plan; and a process of determining whether to execute a query using the predicted data transfer amount. Determination method.