JP7242343B2 - Analysis device and analysis method - Google Patents

Description

The present invention relates to an analysis apparatus and analysis method for analyzing relationships between multiple tables stored in a database.

Conventionally, in order to use databases efficiently, it has been common practice to combine two highly related tables into one or to preferentially cache the results of joining two highly related tables in memory. It is

To identify highly related tables, for example, Patent Document 1 discloses that the relationships between multiple tables are identified by analyzing text included in a query executed in a database system and specifying table join conditions. An analytical method for analyzing is disclosed.

JP 2017-021463 A

By the way, in a database system, when joining three or more tables, the table joining order is determined based on the size of the tables and the cost of the binary operation for joining two tables, and two tables are joined based on the joining order. Join three or more tables by repeating the joining of the tables.

On the other hand, the analysis method disclosed in Patent Document 1 calculates the degree of association between tables based on the relationship between columns specified in the text included in the query. Only relevance can be identified. Therefore, there is a problem that the degree of association between tables specified based on the text included in the query does not necessarily match the degree of association between tables based on the join order determined by the database system. Therefore, it is required to accurately specify a highly relevant table.

Therefore, the present invention has been made in view of these points, and it is an object of the present invention to provide an analysis apparatus and an analysis method that can accurately identify highly relevant tables.

An analysis apparatus according to a first aspect of the present invention includes an execution plan acquisition unit that acquires an execution plan generated for a query executed in a database system by the database system to perform an operation corresponding to the query; Based on the execution plan acquired by the execution plan acquisition unit, a binary operation indicating an operation for joining tables performed in the database system in correspondence with the execution plan is specified, and an operation related to the binary operation. As related information, an analysis unit that identifies a first table group including one or more tables that are combined by the binary operation, and a second table group that includes one or more tables, and and an output unit that outputs the calculation-related information.

The execution plan has a tree-structured data structure that defines the processing order of operations performed in the database system, and the analysis unit is defined in the execution plan acquired by the execution plan acquisition unit. The binary operation may be identified by parsing the data structure.

The analysis unit may specify, as the operation-related information, information related to a processing load when combining the first table group and the second table group in the database system.

The execution plan acquisition unit acquires the plurality of execution plans corresponding to each of the plurality of queries, and the analysis unit acquires the plurality of execution plans acquired by the execution plan acquisition unit as the operation-related information. further comprising a statistical information generating unit that identifies the binary operations performed in each and generates information indicating the statistical value of the processing load as statistical information of the binary operations in which the combined table group is the same; The unit may output the statistical information generated by the statistical information generation unit.

The execution plan acquisition unit acquires the plurality of execution plans corresponding to each of the plurality of queries, and the analysis unit acquires the plurality of execution plans acquired by the execution plan acquisition unit as the operation-related information. Statistical information generation for specifying the binary operations performed in each and generating information indicating the number of times the binary operations are performed in the database system as statistical information of the binary operations in which the table group to be linked is the same. A unit may be further provided, and the output unit may output the statistical information generated by the statistical information generation unit.

An analysis method according to a second aspect of the present invention is a computer-executed step of acquiring, for a query executed in a database system, an execution plan generated by the database system for performing an operation corresponding to the query. and identifying, based on the obtained execution plan, a binary operation indicating an operation that joins tables to be performed in the database system corresponding to the execution plan; and associated with the identified binary operation. identifying a first table group including one or more tables to be combined by the binary operation and a second table group including one or more tables as operation-related information to be performed; and and outputting relevant information.

ADVANTAGE OF THE INVENTION According to this invention, it is effective in being able to specify a table with high relevance accurately.

BRIEF DESCRIPTION OF THE DRAWINGS It is a figure explaining the outline|summary of the analyzer which concerns on this embodiment. It is a figure which shows the structure of the analyzer which concerns on this embodiment. It is a figure which shows the flowchart which shows the flow of a process in the control part which concerns on this embodiment. It is a figure which shows an example (1) of the query performed in the database system which concerns on this embodiment. It is a figure which shows an example (2) of the query performed in the database system which concerns on this embodiment. 5 is a diagram showing an execution plan generated by the database system corresponding to the query shown in FIG. 4; FIG. 6 is a diagram showing an execution plan generated by the database system corresponding to the query shown in FIG. 5; FIG. 7 is a diagram showing a data structure corresponding to the execution plan shown in FIG. 6; FIG. 8 is a diagram showing a data structure corresponding to the execution plan shown in FIG. 7; FIG. 6 is a flow chart showing the flow of processing in execution plan analysis processing according to the present embodiment; 7 is a diagram showing operation-related information corresponding to the execution plan shown in FIG. 6; FIG. FIG. 7 is a diagram showing part of operation-related information and statistical information corresponding to the execution plan shown in FIG. 6; 8 is a diagram showing operation-related information corresponding to the execution plan shown in FIG. 7; FIG. FIG. 13 is a diagram showing an example in which statistical information corresponding to the execution plan shown in FIG. 7 is reflected in the statistical information shown in FIG. 12; It is a figure which shows the virtual integration environment which virtual-integrated two database systems. FIG. 3 is a diagram showing tables used in a virtual integration environment and capacities of the tables;

[Overview of analyzer 10]
FIG. 1 is a diagram illustrating an overview of an analysis device 10 according to this embodiment. The analysis device 10 is a computer that analyzes relationships between multiple tables stored in a database.

The analysis device 10 is communicably connected to the database system 1 via a LAN (Local Area Network), the Internet, or the like. The analysis device 10 acquires an execution plan generated by the database system 1 for queries executed in the database system 1 . A query is a text sentence such as SQL. The execution plan is information generated in order for the database system 1 to perform multiple operations corresponding to a query, and has a tree-structured data structure that defines the processing order of the multiple operations. Queries and execution plans are contained in query logs, for example. A query log is information about queries executed by the database system 1 . The query log includes, for example, an execution plan, a query, an ID identifying the query, execution time, processing time, and user identification information identifying the user who executed the query.

Based on the acquired execution plan, the analysis device 10 identifies a binary operation among the operations performed by the database system 1 corresponding to the execution plan. A binary operation is an operation that joins tables. The analysis apparatus 10 stores a first table group including one or more tables that are combined by the specified binary operation and a second table group including one or more tables as operation-related information related to the binary operation. , and outputs the calculation-related information.

The execution plan has a data structure that defines the processing order of a plurality of operations. Based on the processing order of the binary operations, the analysis device 10 includes a first table group that is combined by the binary operations, A second group of tables can be specified. As a result, the analysis apparatus 10 can accurately identify a highly relevant table even when three or more tables are combined.

[Configuration of analyzer 10]
Next, the configuration of the analysis device 10 will be described. FIG. 2 is a diagram showing the configuration of the analysis device 10 according to this embodiment. As shown in FIG. 2 , the analysis device 10 includes a communication section 11 , an operation section 12 , a display section 13 , a storage section 14 and a control section 15 .

The communication unit 11 is a communication interface for transmitting and receiving data to and from the database system 1 . The operation unit 12 is composed of, for example, a keyboard, a mouse, and the like, and receives operation input from the user of the analysis device 10 . The display unit 13 is configured by, for example, a liquid crystal display or an organic EL (Electro-Luminescence) display. The display unit 13 displays various information under the control of the control unit 15 .

The storage unit 14 is a storage medium including ROM (Read Only Memory), RAM (Random Access Memory), and the like. The storage unit 14 stores programs executed by the control unit 15 . For example, the storage unit 14 stores an analysis program that causes the control unit 15 to function as an execution plan acquisition unit 151 , an analysis unit 152 , a statistical information generation unit 153 and an output unit 154 .

The control unit 15 is, for example, a CPU (Central Processing Unit). The control unit 15 functions as an execution plan acquisition unit 151 , an analysis unit 152 , a statistical information generation unit 153 and an output unit 154 by executing analysis programs stored in the storage unit 14 .

FIG. 3 is a diagram showing a flowchart showing the flow of processing in the control unit 15 according to this embodiment. The functions of the execution plan acquisition unit 151, the analysis unit 152, the statistical information generation unit 153, and the output unit 154 will be described below with reference to the flowchart shown in FIG.

The execution plan acquisition unit 151 acquires, for a query executed in the database system 1, an execution plan generated in order for the database system 1 to perform an operation corresponding to the query (S10). The execution plan acquisition unit 151 acquires multiple execution plans corresponding to multiple queries executed in the database system 1 .

For example, when the operation unit 12 receives an operation to acquire an execution plan from the user of the analysis apparatus 10, the execution plan acquisition unit 151 accesses the database system 1 and acquires queries executed within a predetermined period from the current time. Identify query logs to include. Then, the execution plan acquisition unit 151 acquires an execution plan included in the specified query log. The execution plan acquisition unit 151 may receive from the user a period during which the query was executed, and acquire an execution plan corresponding to the query executed within that period.

4 and 5 are diagrams showing examples of queries executed in the database system 1 according to this embodiment. FIG. 6 is a diagram showing an execution plan generated by the database system 1 corresponding to the query shown in FIG. FIG. 7 is a diagram showing an execution plan generated by the database system 1 corresponding to the query shown in FIG. In this embodiment, the execution plan acquisition unit 151 acquires the execution plans shown in FIGS. 6 and 7. FIG.

The execution plan defines the processing order of operations corresponding to queries executed in the database system 1, and has a tree-structured data structure. 8 is a diagram showing a data structure corresponding to the execution plan shown in FIG. 6, and FIG. 9 is a diagram showing a data structure corresponding to the execution plan shown in FIG. As shown in FIGS. 8 and 9, the execution plan has a binary tree structure. As shown in FIGS. 8 and 9, the binary tree has nodes indicating the content of processing and edges indicating data input from other nodes. The binary tree shown in FIG. 8 has nodes N1 to N7, and the binary tree shown in FIG. 9 has nodes N11 to N23. The database system 1 performs operations in order from the lowest node in the binary tree shown in FIGS. 8 and 9 .

The execution plan also includes information indicating the estimated or actually calculated processing cost and the number of rows of data for each operation. For this reason, in the binary trees shown in FIGS. 8 and 9, on the right side of each node are the processing cost (Cost) when performing the processing corresponding to the node, and the data output by performing the processing corresponding to the node. and the number of rows (Rows). Depending on the type of database system 1, the execution plan may not include information indicating the estimated or actually calculated processing cost and the number of rows of data for each operation. In this case, the analysis device 10 may refer to the meta information of the tables stored in the database system 1 to estimate the processing cost and the number of data rows.

Subsequently, the analysis unit 152 selects one execution plan from among the execution plans acquired by the execution plan acquisition unit 151 (S20). In the present embodiment, first, the description will proceed assuming that the execution plan shown in FIG. 6 is selected.

Subsequently, the analysis unit 152 executes execution plan analysis processing, which is processing for analyzing the execution plan, for the execution plan selected in S20 (S30). The analysis unit 152 repeats execution of the execution plan analysis process for the selected execution plan, thereby performing a binary operation performed in the database system 1 corresponding to each of the plurality of execution plans acquired by the execution plan acquisition unit 151. Identify. The analysis unit 152 identifies a binary operation by analyzing the tree-structured data structure defined in the execution plan. The analysis unit 152 identifies, as operation-related information related to the identified binary operation, a first table group and a second table group that are combined by the binary operation. Here, the table group shall include one or more tables. Note that even when only one table is included, it is also called a table group.

FIG. 10 is a flow chart showing the flow of processing in execution plan analysis processing according to this embodiment. Details of the execution plan analysis process will be described with reference to FIG.
First, the analysis unit 152 selects nodes in order from the node corresponding to the root of the execution plan (S31). For example, in the binary tree shown in FIG. 8, the node N1 is the root node, so the analysis unit 152 first selects the node N1.

Subsequently, the analysis unit 152 determines whether or not a binary operation indicating an operation for joining tables is performed at the selected node (S32). Here, binary operations refer to, for example, associative operations and set operations. Join operations include, for example, Natural join, Inner join, Left/Right/Full outer join, Cross join, and Semi-join. Set operations include Union and Union all, Difference, Intersect, and Division.

If the analysis unit 152 determines that the binary operation is performed in the selected node, the process proceeds to S33, and if it determines that the binary operation is not performed, the process proceeds to S31. In the example shown in FIG. 8, since the nodes N1 and N2 are not binary operations, the analysis unit 152 repeats S31 and S32, and then identifies that the node N3 is a binary operation.

When the analysis unit 152 determines in S32 that a binary operation is being performed, it identifies operation-related information corresponding to the binary operation (S33). Operation-related information is information related to two table groups to be combined corresponding to the specified binary operation and processing load when the two table groups are combined.

Specifically, the analysis unit 152 includes a node corresponding to a binary operation, and by scanning a subtree that is a binary tree under the node, the analysis unit 152 extracts two table groups as a group of tables to be combined with the operation-related information. Identify as In the example shown in FIG. 8, the analysis unit 152 determines that a binary operation is performed at the node N3, and then scans the nodes N3 to N7, which are subtrees of the node N3, to obtain the first table group. Identify the lineitem table, and identify the second group of tables as the orders table and the customer table.

Further, the analysis unit 152 identifies the output result of the node corresponding to the binary operation and the output results of the two nodes immediately below the node as the information related to the processing load included in the operation related information. FIG. 11 is a diagram showing operation-related information corresponding to the execution plan shown in FIG. As shown in FIG. 11, as operation related information corresponding to the binary operation, the first table group, the number of input data corresponding to the first table group, the second table group, the number of input data corresponding to the second table group, It can be confirmed that the join type, join condition, and number of output data are specified. In the example illustrated in FIG. 8 , the analysis unit 152 identifies operation-related information shown in the first line for the binary operation of node N3. Note that the analysis unit 152 may specify, as the operation-related information, the processing cost corresponding to the node on which the binary operation is performed.

Subsequently, the statistical information generation unit 153 updates the statistical information including the statistical value of the calculation related information (S34). Specifically, the statistical information generation unit 153 generates information indicating the statistical value of the processing load and the number of times the binary operation is performed in the database system 1 as the statistical information of the binary operation with the same group of tables to be joined. to generate the information shown. The statistical information is stored in the storage unit 14, and the statistical information generation unit 153 updates the statistical information so that the information indicating the statistical value of the processing load and the number of binomial operations performed in the database system 1 information indicating the number of times. FIG. 12 is a diagram showing part of operation-related information and statistical information corresponding to the execution plan shown in FIG. Here, part of the calculation-related information shown in FIG. 12 is information indicating the first table group and the second table group.

The number of executions shown in FIG. 12 indicates the number of times the binary operation was performed. Also, the total number of data shown in FIG. 12 indicates the total number of input data corresponding to the first table group, input data corresponding to the second table group, and output data. In the example shown in FIG. 8, the statistical information generation unit 153 generates statistical information shown in the first line for the binary operation of node N3.

After the processing of S34 is completed, the analysis unit 152 determines whether or not all nodes have been selected (S35). If the analysis unit 152 determines that all the nodes have been selected, it ends the processing according to this flowchart, and if it determines that all the nodes have not been selected, the processing moves to S31. In the example illustrated in FIG. 8 , the analysis unit 152 identifies that the binary operation is performed at the node N5 after identifying that the binary operation is performed at the node N3. After that, for the binary operation of node N5, the analysis unit 152 identifies operation-related information shown in the second line of FIG. information is generated.

Returning to FIG. The analysis unit 152 determines whether or not all execution plans acquired in S10 have been selected (S40). If the analysis unit 152 determines that all the execution plans have been selected, it moves the process to S50, and if it determines that all the execution plans have not been selected, it moves the process to S20.

After executing the execution plan analysis process for the execution plan shown in FIG. 6, the analysis unit 152 does not select the execution plan shown in FIG. to select. Then, the analysis unit 152 executes execution plan analysis processing on the execution plan shown in FIG. As a result, the analysis unit 152 identifies the operation-related information corresponding to the execution plan shown in FIG. 7, and the statistical information generation unit 153 updates the statistical information based on the operation-related information.

13 is a diagram showing operation-related information corresponding to the execution plan shown in FIG. 7, and FIG. 14 shows the statistical information corresponding to the execution plan shown in FIG. 7 reflected in the statistical information shown in FIG. and FIG. 11 is a diagram showing an example.

Referring to FIG. 9, which shows the binary tree corresponding to the execution plan shown in FIG. 7, binary operations are performed at nodes N13, N14, N15, N17, N20. Correspondingly, as shown in FIG. 13, it can be confirmed that the tables are joined five times. Also, as shown in FIG. 14, as a result of analyzing the execution plan shown in FIG. 7, the first table group is the lineitem table, and the second table group is the orders table and the customer table. In addition, it can be confirmed that the first table group is the orders table and the second table group is the customer table, and that the tables are joined twice. In the join of these tables, the total value of the number of data is also large, so it can be confirmed that these table groups are highly related.

Returning to FIG. When it is determined in S40 that all execution plans have been selected, the output unit 154 outputs the operation-related information specified by the analysis unit 152 and the statistical information generated by the statistical information generation unit 153 (S50). For example, the output unit 154 outputs information indicating part of the calculation-related information (first table group and second table group) shown in FIG. output to By confirming the information shown in FIG. 14, the user can specify a group of tables with high relevance and consider the layout of the tables.

[Table arrangement example]
An example utilizing the results shown in FIG. 14 is provided below. FIG. 15 is a diagram showing a virtual integration environment in which two database systems are virtually integrated. As shown in FIG. 15, the virtual integration environment includes two database systems 1A and 1B and a data virtualization system 2. In FIG. A data virtualization system 2 is connected to two database systems 1A and 1B via a communication network such as a LAN or the Internet.

The data virtualization system 2 has a query input interface and integrates only the schemas of the two database systems 1A and 1B. The data virtualization system 2 acquires the physical data corresponding to the query from the two database systems 1A and 1B in response to receiving the input of the query. In the virtual integration environment, when two table groups to be joined are included in one database system, the table group joining process is completed within the database system. On the other hand, if the table groups to be combined are stored in different database systems, the data virtualization system 2 needs to acquire data corresponding to the table groups through the communication network.

For this reason, it is preferable to place tables that are frequently combined in the same database system. FIG. 16 is a diagram showing tables used in the virtual integration environment and the capacities of the tables. The table shown in FIG. 16 corresponds to the table shown in FIG. 14 and the like. It is also assumed that the capacity limit of the two database systems 1A and 1B is 3,000 GB.

In the results shown in FIG. 14, the lineitem table, which is the first table group, and the orders table and the customer table, which are the second table group, are frequently joined. Therefore, these tables are arranged in the database system 1A. At this point, the total capacity of the tables placed in the database system 1A reaches 3,000 GB, so other tables are placed in the database system 1B. By doing so, the table arrangement is optimized in the virtual integrated environment, and the performance can be improved.

[Effects of this embodiment]
As described above, the analysis device 10 according to this embodiment identifies a binary operation based on the execution plan generated by the database system 1 to perform an operation corresponding to a query. Then, the analysis device 10 identifies the first table group and the second table group that are combined by the binary operation as the operation-related information related to the specified binary operation, and the specified operation-related information to output By doing so, the analysis device 10 can accurately identify a highly relevant table even when three or more tables are combined.

In addition, the analysis device 10 associates all the tables related to the binary operation as a table group so that the tables related to the 1:1 join operation that are specified in the query but not actually executed in the database system 1 relationship can be excluded. This allows the user to accurately grasp the usage status and processing status of the database system 1 based on the output result of the analysis device 10 .

In addition, the user can detect frequently combined table groups based on the output result of the analysis device 10 . As a result, in an environment where a plurality of database systems are connected as shown in FIG. , the performance of the multi-database system environment can be improved.

Although the present invention has been described above using the embodiments, the technical scope of the present invention is not limited to the scope described in the above embodiments, and various modifications and changes are possible within the scope of the gist thereof. be. For example, in the above-described embodiment, the analysis device 10 is provided with the operation unit 12 and the display unit 13, but the present invention is not limited to this. The analyzer 10 does not have to include the operation section 12 and the display section 13 . In this case, the execution plan acquisition unit 151 may acquire the execution plan corresponding to the query periodically at a specified time without accepting the query execution period from the user. Also, the execution plan acquisition unit 151 may acquire an execution plan when detecting that the number of query logs accumulated in the database system 1 is increasing. By doing so, the analysis device 10 can automatically acquire the execution plan, and can automatically identify the binary operation based on the execution plan.

Also, all or part of the device can be functionally or physically distributed and integrated in arbitrary units. In addition, new embodiments resulting from arbitrary combinations of multiple embodiments are also included in the embodiments of the present invention. The effect of the new embodiment caused by the combination has the effect of the original embodiment.

DESCRIPTION OF SYMBOLS 1... Database system, 2... Data virtualization system, 10... Analysis apparatus, 11... Communication part, 12... Operation part, 13... Display part, 14... Storage part , 15... control unit, 151... execution plan acquisition unit, 152... analysis unit, 153... statistical information generation unit, 154... output unit

Claims (6)

An analysis device for analyzing relationships between multiple tables stored in a database system,
A node indicating the content of processing generated for a query executed in the database system so that the database system performs an operation corresponding to the query, and an edge indicating the input of data from another node to the node an execution plan acquisition unit that acquires an execution plan having a binary tree data structure consisting of
Based on the nodes included in the execution plan acquired by the execution plan acquisition unit, identify a node corresponding to a binary operation indicating an operation for joining tables performed in the database system in correspondence with the execution plan, A plurality of lower nodes that are included in a subtree that is a lower binary tree that is connected to a specified node via an edge that inputs data to the specified node included in the binary tree indicated by the execution plan . By scanning, a first table group including one or more tables connected by a binary operation corresponding to the specified node and a second table group including one or more tables are specified, and the specified an analysis unit that includes a first group of tables and the second group of tables and generates calculation-related information to be confirmed by a user of the analysis device ;
an output unit that outputs the calculation-related information generated by the analysis unit;
Analyzer with The analysis unit identifies the binary operation by analyzing a data structure defined in the execution plan acquired by the execution plan acquisition unit.
The analyzer according to claim 1. The analysis unit generates the operation-related information including information related to a processing load when combining the first table group and the second table group in the database system,
The analyzer according to claim 1 or 2. The execution plan acquisition unit acquires a plurality of execution plans corresponding to each of the plurality of queries,
The analysis unit identifies the binary operation performed in each of the plurality of execution plans acquired by the execution plan acquisition unit;
further comprising a statistical information generation unit that indicates the statistical value of the processing load as statistical information of the binary operation with the same group of tables to be combined and generates information that is confirmed by the user of the analysis device ;
The output unit outputs the statistical information generated by the statistical information generation unit.
The analyzer according to claim 3. The execution plan acquisition unit acquires a plurality of execution plans corresponding to each of the plurality of queries,
The analysis unit identifies the binary operation performed in each of the plurality of execution plans acquired by the execution plan acquisition unit;
The number of times the binary operation was performed in the database system and the number of data contained in one or more tables included in the first table group as statistical information of the binary operation with the same group of tables to be joined. a statistical information generating unit that generates information that indicates the total number of data included in one or more tables included in the second table group and the number of data included in the second table group and that is confirmed by the user of the analysis device ;
The output unit outputs the statistical information generated by the statistical information generation unit.
The analyzer according to any one of claims 1 to 4. the computer runs
A node indicating the content of processing generated for a query executed in the database system so that the database system performs an operation corresponding to the query, and an edge indicating the input of data from another node to the node obtaining an execution plan having a binary tree data structure consisting of
a step of identifying a node corresponding to a binary operation representing an operation of joining tables performed in the database system corresponding to the execution plan, based on the nodes included in the acquired execution plan;
A plurality of lower nodes that are included in a subtree that is a lower binary tree that is connected to a specified node via an edge that inputs data to the specified node included in the binary tree indicated by the execution plan. identifying, by scanning , a first group of tables including one or more tables joined by the binary operation corresponding to the identified node, and a second group of tables including one or more tables; ,
a step of generating operation-related information including information indicating the identified first table group and the second table group and confirmed by a user of the computer ;
outputting the generated operation-related information;
A method of analysis comprising

Images