JP2007293723A - Database managing system and managing method - Google Patents

Abstract

<P>PROBLEM TO BE SOLVED: To provide an optimal performance plan in consideration of cache in a short time. <P>SOLUTION: A database managing system includes: a cache area 11 for storing an SQL query; an SQL query performing part 21 for performing the SQL query; an SQL query syntax analyzing/normalizing part 22 for analyzing and normalizing a syntax; an SQL query optimizing part 23 for determining an performance plan by optimization processing; a DBMS control part 26 for managing the cach area 11, etc.; an performance plan storage file 28 for storing the determined performance plan and information concerning a cost; and a cost calculating part 24 for calculating the cost of the performance plan. The DBMS control part 26 determines whether the SQL query being the same as that stored in the performance plan storage file is stored or not. When the same SQL query is stored, the cost calculating part 24 calculates the cost of the performance plan of the SQl query by interruption processing. The SQL performing part 21 selects the performance plan with the minimum cost as the result of the calculation and performs the plan. <P>COPYRIGHT: (C)2008,JPO&INPIT

Description

  The present invention relates to a database management system and a database management method for managing a database as shared data and processing a query for data, and in particular, a database capable of speeding up an execution plan which is a procedure for processing a query for data. The present invention relates to a management system and a database management method.

  Generally, a database management system (DBMS: DataBase Management System) according to a conventional technique, when a query from a user is input, analyzes and normalizes whether or not the SQL query that has been queried is correct syntax and deletes redundant syntax. A step, an examination of an index to be used at the time of inquiry, determination of a joining order of tables, a cost calculation of an execution plan and an optimization step of selecting an optimal execution plan, and executing the selected execution plan, The execution result of storing the data read from the database in the cache is sequentially executed, so that the execution result is presented to the user.

  The optimization step may have a very large effect on the response time to a query. For example, if an inappropriate execution plan is selected, the processing time of the execution step may increase several times or more. There is a tendency that the query content of the query to be executed becomes complicated, the pattern of execution plans considered during the optimization process increases, and the time taken to select an appropriate execution plan increases. That is, in order to shorten the query response time of the database management system (DBMS), it is important to select an optimal execution plan in the shortest possible time.

As a document describing a technique for selecting this optimal execution plan in a short time, for example, the following patent document can be cited. This document description technology acquires the cost of a candidate execution plan for a given query, stores the execution plan with the lowest cost in the file together with the query content, and when the same query is executed, Instead of performing an optimization step, the execution plan is read from this file and executed.
JP 2005-18430 A

  The technique for selecting the optimal execution plan in the previous technology in a short time can greatly reduce the time of the optimization step by reusing the previous execution plan when the same query is executed. Although it does not consider the data read into the cache during execution, depending on the data that has already been read into the cache, the overhead of reading data into the cache newly (the existing cache when the memory area is insufficient) (Including deletion and saving to a page file) may outweigh the effect of reducing execution plan reuse. In general, IO (Input / Output) with a physical disk is slower than processing on a memory, and such a situation is sufficiently conceivable.

  An object of the present invention is to eliminate the problems caused by the prior art described above, and to provide a database management system and a management method capable of providing an optimal execution plan in consideration of the memory on the cache in a short time. is there.

  To achieve the above object, the present invention provides a cache area for temporarily storing an SQL query for a database management system, an SQL query execution unit for executing an accepted SQL query according to an execution plan, and an SQL of an input query. An SQL query syntax analysis / normalization unit that parses and normalizes a query, an SQL query optimization unit that determines an execution plan by the optimization processing of the SQL query, management of the cache area, and interrupt processing A database management system comprising: a DBMS control unit to perform; an execution plan storage file for storing information related to the execution plan and cost determined by the SQL query optimization unit; and a cost calculation unit for calculating the cost of the execution plan. The DBMS control unit stores the execution plan in the execution plan storage file. If the same SQL query is stored as a result of the determination, if the same SQL query is stored as a result of the determination, the cost calculation of the execution plan of the SQL query is performed by interrupt processing. The first feature is that the SQL execution unit selects and executes an execution plan that results in the minimum cost of the calculation result. In the database management system, the DBMS control unit A second feature is that a predetermined number of execution plans having a relatively low cost among the execution plans optimized by the SQL query optimization unit are stored in the execution plan storage file.

  Further, the present invention provides a cache area for temporarily storing an SQL query for a database management system, an SQL query execution unit for executing an accepted SQL query according to an execution plan, and parsing the SQL query of the input query. A SQL query syntax analysis / normalization unit that performs normalization, an SQL query optimization unit that determines an execution plan by the optimization processing of the SQL query, a DBMS control unit that performs management of the cache area and interrupt processing, A database management method for a database management system, comprising: an execution plan storage file that stores an execution plan determined by the SQL query optimization unit and information related to costs; and a cost calculation unit that performs cost calculation of the execution plan, The DBMS control unit stores the execution plan storage file. A step of determining whether or not the same SQL query as the executed SQL query is stored, and if the same SQL query is stored as a result of the determination, the cost calculation of the execution plan of the SQL query is performed by interrupt processing The database management method has the third feature of executing the steps of: performing by the cost calculating unit; and executing the step of selecting and executing the execution plan having the minimum cost as a result of the calculation by the SQL execution unit. The DBMS control unit stores, in the execution plan storage file, a predetermined number of execution plans having a relatively low cost among the execution plans optimized by the SQL query optimization unit. And

  In the database management system and management method according to the present invention, when the SQL query for the database management system is already stored in the cache area, the syntax check of the SQL query by the SQL query parsing / normalization unit and the deletion of redundant syntax are performed. By determining whether or not normalization has already been performed based on the presence or absence of the execution plan stored in the execution plan storage file, the cost calculation of the determined execution plan is performed, and the optimal execution plan is selected. It is possible to provide an optimal execution plan in consideration of the above memory in a short time.

Hereinafter, an embodiment of a database management system and a management method according to the present invention will be described in detail with reference to the drawings. FIG. 1 is a diagram showing a configuration example of a database management system according to the present invention, FIG. 2 is an explanatory diagram showing an example of table information relating to an SQL text of an inquiry stored in an execution plan storage file, and FIG. 3 is an execution plan storage file FIG. 4 is an explanatory diagram showing an example of cache information stored in the DBMS cache area managed by the DBMS control unit, and FIG. 5 is a diagram in the business database. FIG. 6 is a flowchart showing cost recalculation processing performed by the cost recalculation unit, and FIG. 7 shows processing performed in the database system in response to a query from the user. It is a flowchart to show.
<Description of configuration>

  As shown in FIG. 1, the database management system according to the present embodiment is a computer system including a server connected to a plurality of clant 40 via a public line 30 and is operated at least on the main storage device 10 by the DBMS control unit 26. Managed DBMS cache area 11, SQL query execution unit 21 that executes the accepted SQL query according to the execution plan, check whether the SQL query of the input query is written in the correct syntax, and The SQL query syntax analysis / normalization unit 22 that deletes redundant syntax, the SQL query optimization unit 23 that performs optimization processing to determine an optimal execution plan, and information stored in the execution plan storage file 28 are managed. Execution plan storage file management unit 25, management of cache area 11 and business database The DBMS control unit 26 that controls the entire DBMS including IO to the server 27, the business database 27, the top 10 execution plans examined during optimization by the query SQL query and the SQL query optimization unit 23, and Execution plan storage file 28 that stores information about costs for each step in the course of examination, and recalculation of the cost of the registered execution plan when the same SQL query as the SQL query stored in the execution plan storage file 28 is executed And a cost recalculation unit 24 that returns the execution plan with the lowest cost to the SQL execution unit.

  The SQL text table information relating to the SQL text of the query stored in the execution plan storage file 28 includes, for example, as shown in FIG. 2, the SQL query ID uniquely assigned to the SQL query as the item and the executed SQL query text. The table of the execution plan of the query and its cost stored in the execution plan storage file 28 corresponds to the SQL query ID of FIG. 2 as an item, for example, as shown in FIG. SQL query ID to be executed, plan ID assigned to each plan, step ID assigned to each step in the plan, processing contents of the step, ID of the object used in the step, execution cost for the step, Of the execution costs, the cost of physical IO It consists of a physical IO cost. It should be noted that the steps described here indicate procedures that constitute an execution plan.

  The table shown in FIG. 2 and FIG. 3 in the execution plan storage file 28 includes the execution plans of the top 10 execution plans from the lowest cost among the execution plans examined by the SQL query optimization unit 23. Stored by the storage file management unit 25. It should be noted that the numerical values in the physical IO cost item stored in the table of FIG. 3 do not consider cache data.

  As shown in FIG. 4, the cache information on the DBMS cache area managed by the DBMS control unit 26 includes a cache ID that is uniquely assigned to each read object on the cache, and an object ID that uniquely indicates an object in the database. And the number of cache blocks read as a cache. The object written here refers to a table or an index constituting a database, and the block refers to an IO unit size managed by the DBMS.

As shown in FIG. 5, for example, the management information related to the table and index in the business database 27 includes an object ID uniquely assigned to each object in the database, an object type indicating the type (table, index) of the object, It consists of the object name, the total number of blocks used by the object, and the object ID of the parent table when the object type is an index.
<Description of operation>
<Minimum execution plan determination process>

  In the database management system configured as described above, the cost recalculation unit 24 recalculates the cost. This processing procedure starts with an execution plan cost table (FIG. 3) as shown in FIG. Step S1 for extracting a record relating to the executed plan, step S2 for calculating the next extracted record based on the plan ID 1 and step ID 1, and cache information matching the object ID of the execution plan cost table (FIG. 3) 4 is determined in the cache area shown in FIG. 4, and if there is data in the cache area, the number of blocks in the database management table (FIG. 5), the ratio of the number of cache blocks, and the execution plan cost table Step S4 for calculating the cost of the step from the physical IO cost of .

  In the calculation in step S4, in this example, since the data of the index surname of the customer table is not in the cache, the physical IO cost of the plan ID 1 and step ID 1 remains “150”, and all the data of the customer table is cached. Therefore, the physical IO cost of plan ID 2 and step ID 1 is “0”, and the step cost is also “150” (= 650−500).

  Next, when there is no data on the cache or when the recalculation of the step is completed, the system executes Step S5 for determining whether there is a step that has not been calculated for the execution plan. If there is, the process returns to step S3 via step S6 for performing the process of proceeding to the next step. If it is determined that there is no uncalculated step, step S7 for obtaining a recalculation result for the execution plan, Step S8 for determining whether there is a calculation plan, and if it is determined in step S8 that there is an uncalculated plan, step S9 for calculating the next plan in the same procedure, and no uncalculated plan. If it is determined that the calculation of all plans has been completed, step S10 is executed to return the execution plan with the lowest cost to the SQL query execution unit 21.

As described above, the database management system according to the present embodiment operates to return the minimum execution plan to the SQL query execution unit 21 by calculating the cost of the step and determining the cost.
<Execution process>

Next, the database management system according to the present embodiment executes an SQL query that is a query by the user, and this process will be described with reference to FIG.
As shown in FIG. 7, in this processing procedure, first, the SQL query execution unit 21 executes step S11 for determining whether the query SQL query is registered in the table (FIG. 2) in the execution plan storage file 28. .

  Next, when the system determines that the query SQL query is registered in step S11, the system delivers the information related to the SQL query (SQL query ID shown in FIG. 2) to the cost recalculation unit 24, and recalculates the cost. Step S12 in which the unit 24 recalculates the cost by using the cache information (FIG. 4) on the cache area and the table information about the cost (FIG. 4) according to the procedure shown in FIG. Step S13 for obtaining an execution plan with the minimum cost considering the data is executed.

  On the other hand, when it is determined in step S11 that the SQL query of the query is not registered in the table (FIG. 2) in the execution plan storage file 28, the SQL query parsing / normalizing unit 22 executes the SQL query. Step S14 in which syntax analysis / optimization processing is performed, SQL syntax is checked and redundant syntax is deleted, cost calculation is performed by the SQL query optimization unit 23, and an optimal execution plan is determined in step S15. Step S16 in which the execution plan storage file management unit 25 stores the information related to the top 10 execution plans examined in step 1 and the cost for each step in the middle of the examination in the execution plan storage file 28, and Step S17 are executed.

  Subsequently, following the step S13 or 16, the present system performs step 17 in which the SQL query execution unit 21 executes the SQL query according to the execution plan determined by the above procedure, and displays the execution result on the display unit of the user. Step S18 is executed.

  As described above, the database management system according to the present embodiment determines whether or not the SQL query that matches the execution plan file has already been registered. When the SQL query has already been registered, that is, when the SQL query has been cached, the registered plan cost is determined. The execution plan is determined after recalculation, and when it is not registered, by performing parsing and other processes, a series of processes of SQL cache (syntax analysis-optimization-execution plan generation) already cached are performed. In other words, an effective execution plan can be provided in a short time.

The figure which shows the structural example of the database management system which concerns on this invention. The figure which shows an example of the table information regarding the SQL text of the inquiry stored in an execution plan storage file. The figure which shows an example of the table information regarding the execution plan of a query and its cost stored in an execution plan storage file. The figure which shows an example of the cache information on the DBMS cache area | region which a DBMS control part manages. The figure which shows an example of the management information regarding the table and index in a business database. The flowchart which shows the cost recalculation process performed by the cost recalculation part. The flowchart which shows the process performed in a database system with respect to the inquiry from a user.

Explanation of symbols

  10: Main storage device 11: Cache area 21: Query execution unit 22: Query syntax analysis / normalization unit 23: Query optimization unit 24: Cost recalculation unit 25: Execution plan storage file management unit 26: DBMS control unit, 27: business database, 28: execution plan storage file, 28: execution plan storage file, 30: public line, 40: clantant.

Claims (4)

A cache area for temporarily storing an SQL query for a database management system, an SQL query execution unit for executing an accepted SQL query according to an execution plan, and parsing and normalizing the SQL query of the input query SQL query parsing and normalization unit, SQL query optimization unit for determining an execution plan by the optimization processing of the SQL query, DBMS control unit for managing the cache area and interrupt processing, and the SQL query optimization A database management system comprising an execution plan storage file that stores an execution plan determined by a section and information on costs, and a cost calculation section that performs cost calculation of the execution plan,
The DBMS control unit determines whether or not the same SQL query as the SQL query stored in the execution plan storage file is stored. If the same SQL query is stored as a result of the determination, an interrupt is generated. A database management system, wherein a cost calculation unit performs cost calculation of the execution plan of the SQL query by processing, and the SQL execution unit selects and executes an execution plan that has a minimum cost as a result of the calculation .   2. The DBMS control unit stores, in the execution plan storage file, a predetermined number of execution plans having relatively low costs among the execution plans optimized by the SQL query optimization unit. The database management system described. A cache area for temporarily storing an SQL query for a database management system, an SQL query execution unit for executing an accepted SQL query according to an execution plan, and parsing and normalizing the SQL query of the input query SQL query parsing and normalization unit, SQL query optimization unit for determining an execution plan by the optimization processing of the SQL query, DBMS control unit for managing the cache area and interrupt processing, and the SQL query optimization A database management method of a database management system comprising an execution plan storage file that stores an execution plan determined by a unit and information about costs, and a cost calculation unit that performs cost calculation of the execution plan,
When the DBMS control unit determines whether or not the same SQL query as the SQL query stored in the execution plan storage file is stored, and as a result of the determination, the same SQL query is stored Performing a cost calculation of the execution plan of the SQL query by the interrupt processing by the cost calculation unit, and a step of the SQL execution unit selecting and executing the execution plan having the minimum cost as a result of the calculation A database management method characterized by:   4. The DBMS control unit stores, in the execution plan storage file, a predetermined number of execution plans having relatively low costs among the execution plans optimized by the SQL query optimization unit. The database management method described.

Images