JPH09305461A - System and method for supporting transaction analysis - Google Patents

Abstract

PROBLEM TO BE SOLVED: To show a user information for analyzing the relation between queries and transactions, and tables of a database according to the use state of the database. SOLUTION: The issue of various database operation requests such as queries and transactions are monitored by a transaction analysis support tool 13 and frequency information showing generation frequencies as to the respective database operation requests is checked by the tables constituting the data base. A transaction analysis support screen 14 is generated by making the generation frequencies of the respective data base operation requests and specification conditions of rows of respective tables used by the operation requests correspond to each other by the tables and shown to the user 10. consequently, information on various queries and transactions based upon the tables as viewpoints can be shown to the user 10 to provide a clue to what index is most preferably added to which column of a table.

Description

Detailed Description of the Invention

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a transaction analysis support system and a transaction analysis support method, and more particularly to a transaction analysis support system and a transaction for analyzing the relation between a query and a table issued by a query or transaction issued in a relational database system. Regarding analysis support method.

[0002]

2. Description of the Related Art Generally, in a relational database system, various database operations are performed on a query or a query designated by a transaction. In this case, creating a proper index reduces the processing such as table scan and sort.
The system performance can be improved. In order to attach an appropriate index, it is necessary to fully consider the database usage by users. In order to do so, the relationship between queries and transactions and database tables must be analyzed in detail.

Generally, indexes supported by a relational database system include a clustered index and a non-clustered index. Both of these indexes are configured as a B-tree structure.

In a clustered index, the data is
It is physically stored in the order of the index sort key.
The actual data is stored at the bottom level of the index. Because of this structure, it is convenient for operations such as retrieval of columns sorted in ascending order and range search. Also, in a clustered index, new data is inserted in physical order based on the index key, which is also effective in avoiding serialization of insertion. Therefore, the following are suitable for the clustered index.

Columns in which data is frequently accessed sequentially Columns used in ORDER BY and GROUP BY Columns used in joins Columns that are not updated frequently On the other hand, in a non-clustered index, data physical The target order is independent of the index sort key. The bottom level of the index contains pointers to rows of data, not the actual data. Non-clustered index is SELECT list and WHE
A query is said to be covered if it contains all the columns referenced in the RE clause. Since all the requested information is contained in the index page, the database management system does not need to read the actual data page, which can save I / O. If the nonclustered index covers the query, the nonclustered index will be faster than the clustered index, especially for queries with low selectivity.

The following are suitable as the non-clustered index. Columns referenced in highly selective queries Columns used in aggregate functions Columns used in joins, ORDER BY, or GROUP BY Unique or nearly unique selected by consolidation in WHERE clause Here, the selectivity means the ratio of access from the table, and the smaller the number of rows accessed in the number of rows forming the table, the higher the selectivity.

A composite index is also available as an index form. A composite index is an index that has multiple column sort keys, and both clustered and non-clustered indexes can have composite keys. A good choice for a composite index is when two or more columns are searched as one column. And, composite indexes can reduce overhead during data manipulation over multiple single column indexes. Not suitable are indexes that are too wide, for example
Only the second or third part is an index used in the WHERE clause.

[0008]

However, in the conventional relational database system, a tool for analyzing the relationship between the query or transaction and the database table is not prepared, and the above-mentioned database usage situation is It was very difficult to grasp. For this reason, it is easy for the user or the database administrator to judge the matters related to the system performance, such as what kind of index is best attached to which column of the table.

The present invention has been made in view of the above circumstances, and is capable of presenting various kinds of information necessary for analyzing the relationship between a query or a transaction and a database table based on the database usage status to the user. Another object of the present invention is to provide a transaction analysis support system and a transaction analysis support method that can improve the performance of a database system.

[0010]

A transaction analysis support system according to the present invention is a frequency information management in which the frequency of occurrence of various database operation requests issued to a database system is counted and held for each table constituting the database. Means, condition holding means for holding the specified condition for each table column used in the operation request for each database operation request, occurrence frequency information held by the frequency information management means, and the condition holding means It is characterized by comprising means for displaying a transaction analysis support screen showing, for each table, the correspondence relationship between the occurrence frequency of each database operation request and the column designation condition by using the held designated condition.

In this transaction analysis support system, frequency information indicating the frequency of occurrence of various database operation requests such as queries and transactions is checked for each table constituting the database.
Then, for each table, the transaction analysis support screen is created by associating the occurrence frequency of each database operation request with the specified condition for the column of each table used in the operation request, and presents it to the user. This makes it possible to present various query and transaction information to the user from the viewpoint of the table, and to provide a clue as to which column of the table should be indexed and which index is most preferable. . Addition, deletion, and modification of indexes have no effect on the database system other than its performance. Therefore, it is important to review the index design from various angles, not only before the database system is operated, but also after the database is started. This can be achieved by using the transaction analysis support system of the present invention.

Further, the means for displaying the transaction analysis support screen displays the database operation requests related to a predetermined table on the transaction analysis support screen for each type of operation request such as selection, insertion, deletion, and update. It is preferable to provide means for rearranging. This makes it possible to easily analyze whether the table is used more frequently by the database operation request of the selection type or the update type.

Further, the means for displaying the transaction analysis support screen displays database operation requests related to a predetermined table on the transaction analysis support screen, and assigns the database operation requests with priorities assigned in advance. It is preferable to provide means for rearranging in order. This makes it possible to analyze information by focusing on high-priority queries and transactions, and the user can obtain clues to take performance measures.

Further, the means for displaying the transaction analysis support screen is provided with means for narrowing down database operation requests to be displayed on the transaction analysis support screen in accordance with the narrowing-down condition specified on the transaction analysis support screen. , Information can be classified and displayed according to the conditions specified by the query or transaction condition clauses, and it can give a clue to take measures for performance under a specific condition.

Further, the frequency information management means incorporates a frequency information collection instruction individually for each function when the database operation request is converted into a function, and responds to the instruction from the incorporated frequency information collection instruction. It can be configured by means for updating the information indicating the occurrence frequency of the function. In this case, when the execution of the functionalized database operation request is requested, the frequency information collection instruction is automatically executed, and the information indicating the occurrence frequency of the function is updated by +1. By thus providing the device for collecting the frequency information, the usage frequency can be correctly acquired.

[0016]

Embodiments of the present invention will be described below with reference to the drawings. FIG. 1 shows the configuration of a relational database system using a transaction analysis support system according to an embodiment of the present invention. This database system is, for example, operated in an individual computer or on a client / server system composed of a client computer and a server computer connected via a network, and is a relational database management system (RD).
BMS) 11 and transaction analysis support tool 13 are used.

The relational database management system (RDBMS) 11 is a program executed in an individual computer or on a server computer of a client / server system, and is a relational data model (Relational Data Mode).
Management of relational database based on l). Relational database is a computer 2
A plurality of tables T1 and T stored in the next storage device 12
2, T3, T4, ... The relational database management system (RDBMS) 11 uses the table T based on a database operation request given as a query or a transaction from the user 10.
Various relational operations between the data items 1, T2, T3, T4, ... Are executed and the results are returned to the user 10.

Here, the query is an inquiry request to a relational database expressed in a database operation language such as SQL, and the transaction is a series of operation procedure requests corresponding to such a combination of queries.

The relational database management system (RDBMS) 11 also stores a stored procedure function for functionalizing a database operation request and registering it in the stored procedure library 111, and holds the created index in the index table 112, and refers to the index. And the data operation function etc. are prepared.

The transaction analysis support tool 13 is
Relational database management system (RDBM
S) The relational database management system (RDBMS) as software for expanding the functions of 11.
11 is a program that is installed and used in a computer together with 11, and is used to analyze the relationships between queries and transactions and tables T1, T2, T3, T4, ... From various viewpoints.

Specifically, the transaction analysis support tool 13 monitors the issuance of various database operation requests such as queries and transactions, acquires the occurrence frequency of each database operation request, and registers it in the usage frequency management table 131. The column condition management table 132 manages the function and the specified condition for the column of each table used in each database operation request, and the transaction analysis support screen 14 is created and displayed by associating the occurrence frequency with the column condition for each table. It has a function to do. The transaction analysis support screen 14 displays transaction information (type of each query or transaction, frequency of occurrence thereof, priority, specified conditions for columns of tables related to the queries or transactions, etc.) in table units.

Further, the transaction analysis support tool 13 has a function of performing transaction information input, change, and deletion functions, a table row number (record number) acquisition function, a unique value calculation function, and a table configuration as functions performed through the transaction analysis support screen 14. It has an information / index information acquisition function and a stored procedure creation function.

Further, the transaction analysis support screen 14
In the above, transaction information is used for query and transaction type (SELECT, INSERT, UPDA).
TE, DELETE, etc.), priority, and frequency of use. Also, the information to be displayed can be narrowed down according to the conditions in the columns.

Further, when calculating the number of rows and unique values of the table, there is a function of using the information held by the index table 112 of the relational database management system (RDBMS) 11 to quickly obtain the information. are doing.

FIG. 2 shows an example of a specific configuration of the usage frequency management table 131. Here, the case where the occurrence frequency is managed for each transaction stored in the usage frequency management table 131 is illustrated.

The usage frequency management table 131 is provided corresponding to each of the tables T1, T2, T3, T4, ... In the database, and in each usage frequency management table, "day" is set for each transaction name (Tr1 to Trm). The occurrence frequency of the transaction is managed in units of ",""week" and "month."

That is, when a certain transaction occurs, the value of the item column of "day" corresponding to the transaction is updated by +1 at each occurrence, and when the day changes, the item column of "day" at that time. The value of is added to the value of the corresponding "week" item column. When the week changes, the value of the item column of "week" at that time is added to the value of the item column of "month". In this way, "day", "week",
The transaction occurrence frequency is counted in units of "month".

The number of entries in each table of the usage frequency management table 131 is added every time a transaction is registered as a stored procedure. FIG. 3 shows an example of a specific configuration of the column condition management table 132.

Here, a case is illustrated in which the conditions for each column of each table used by the transaction are managed for each stored procedure transaction.

For example, transaction Tr1 uses two tables T1 and T2, and table T1
If there is a condition (J: Join) that joins column 1 of table T2 and column 2 of table T2, transaction T
The column condition information corresponding to r1 is as shown in the figure.

Next, a configuration example of the transaction analysis support screen 14 provided by the transaction analysis support tool 13 will be described with reference to FIGS. 4 and 5. In the title bar 101 of the transaction analysis support screen 14,
The name of the database currently used is displayed. Which table is to be displayed with information can be selected from the table names listed in the table selection pull-down menu 102. The number of records in the selected table is displayed on the line number item. When the table information button 104 is selected, information on transactions used in the table displayed in the table name is displayed. The transaction information displayed here is
By specifying the display method from the "Analysis" menu,
The display form can be changed. By grasping the relationship between the table and the transaction by using this transaction information, the user can identify a place that may be a problem such as excessive join, and can obtain information for index design.

FIG. 5 shows an example of the actual display form of transaction information. Here, "jush
The table "o100" is selected, and the transaction names related to the table are "direct mail sending process", "delinquent customer extraction process", "customer list creation",
"Usage details extraction process", "Customer extraction process", "Regional customer extraction process", "New customer information input", "Withdrawal member process", Customer information change process ", etc. are displayed. Then, corresponding to each transaction name, information indicating the type of the transaction (S, I, D, U, etc.), priority information (01, 10, etc.), frequency information (once a week, once a month, every day) 50 times etc.) is displayed.

In the type information, S indicates a select transaction, I indicates an insert transaction, D indicates a delete transaction, and U indicates an update transaction. Here, since the display by type is selected in the “Analysis” menu, the transaction information is displayed by being sorted by the type of transaction. In this type-based display, selection, insertion, deletion, and update are displayed in this order. For the same type, the display order is determined in consideration of priority and frequency.

In this state, if the priority order display is selected,
Sorting is performed in order of transaction priority. When the display by frequency is selected, the transactions are sorted in order of frequency.

When the transaction extraction designation is selected, a condition input by the user (a transaction using the ORDER BY clause, a transaction using the GROUP BY clause, a WHERE EQU
WHER, a transaction that uses ALITY
Transactions using E-RANGE, WHE
Transactions using RE LIKE, JO
Only the transactions that match the transaction using the IN key, all transactions, etc. are extracted and displayed.

Further, the column names (sid, name, tel, zip, addr) that make up the selected table and the conditions (O: O) for each transaction for that column.
RDER BY, G: GROUP BY, E: WHER
E EQUALITY, R: WHERE RANGE,
L: WHERE LIKE, J: JOIN) is displayed.

Next, with reference to FIG. 6, a procedure of transaction usage frequency information acquisition processing by the transaction analysis support tool 13 will be described. Here, when making a query or transaction a stored procedure,
A case will be described as an example in which a frequency information acquisition command is embedded in the created stored procedure and the usage frequency management table 131 is updated in response to the frequency information acquisition command.

The user 10 issues a stored procedure corresponding to the operation desired to be executed (step S1).
1). When this stored procedure is received by the relational database management system (RDBMS) 11 (step S12), the event occurrence is notified from the relational database management system (RDBMS) 11 to the transaction analysis support tool 13 by its trigger function ( Step S13). This notification of event occurrence can be performed by the above-mentioned frequency information acquisition command.

The transaction analysis support tool 13 updates the value of the usage frequency information of "day" in response to the frequency information acquisition command (step S14). Then, the use frequency information of "week" and the use frequency information of "month" are updated as needed (steps S15 and S16). After this,
Relational database management system (RDBM
In (S) 11, the stored procedure is executed, and the result is sent to the user 10 (steps S17, S1).
8).

It should be noted that the frequency information acquisition command itself has a step S
It is also possible to configure to execute the frequency information collection processing of 14 to S16. As described above, in this embodiment, the issuance of various database operation requests such as queries and transactions is monitored by the transaction analysis support tool 13, and the frequency information indicating the occurrence frequency of each database operation request is stored in the database. It is checked for each table that constitutes it. Then, for each table, the transaction analysis support screen 14 is created by associating the occurrence frequency of each database operation request with the specified condition for the column of each table used in the operation request.
Is created and presented to the user 10. This makes it possible to present various queries and transaction information to the user 10 from the viewpoint of the table, and provides the user 10 with clues as to which column of the table should be indexed and which index is most preferable. Can be provided.

[0041]

As described above, according to the present invention, various kinds of information necessary for analyzing the relationship between a query or transaction and a database table can be presented to the user based on the database utilization status. It is possible to improve the performance of the database system.

[Brief description of drawings]

FIG. 1 is a block diagram showing a functional configuration of a relational database system using a transaction analysis support system according to an embodiment of the present invention.

FIG. 2 is a diagram showing an example of a specific configuration of a usage frequency management table used in the system of the embodiment.

FIG. 3 is a diagram showing an example of a specific configuration of a column condition management table used in the system of the embodiment.

FIG. 4 is an exemplary view showing an example of a transaction analysis support screen presented to a user in the system of the embodiment.

FIG. 5 is a view showing an example of a specific display form of a transaction analysis support screen presented to the user in the system of the embodiment.

FIG. 6 is an exemplary flowchart illustrating a procedure of transaction usage frequency information acquisition processing in the system of the embodiment.

[Explanation of symbols]

11 ... relational database management system, 13
... transaction analysis support tool, 14 ... transaction analysis support screen, T1 to T4 ... table, 111 ...
Stored procedure library, 112 ... Index table, 131 ... Usage frequency management table, 132
… Column condition management table.

Claims (7)

[Claims] 1. A frequency information management unit that counts and holds the frequency of occurrence of various database operation requests issued to a database system for each table constituting a database, and the operation request for each database operation request. Using condition holding means for holding designated conditions for the columns of each table used, occurrence frequency information held by the frequency information management means and designated conditions held by the condition holding means, A transaction analysis support system, comprising: a means for displaying a transaction analysis support screen showing a correspondence relationship between a database operation request occurrence frequency and a column designation condition for each table. 2. The means for displaying the transaction analysis support screen arranges database operation requests related to a predetermined table on the transaction analysis support screen according to types of operation requests such as selection, insertion, deletion, and update. The transaction analysis support system according to claim 1, further comprising a changing unit. 3. A means for displaying the transaction analysis support screen, wherein the database operation requests related to a predetermined table are displayed on the transaction analysis support screen in the order of priorities assigned to the respective database operation requests. 2. A means for rearranging, comprising:
Transaction analysis support system described. 4. The means for displaying the transaction analysis support screen includes means for narrowing down database operation requests to be displayed on the transaction analysis support screen according to a narrowing condition specified on the transaction analysis support screen. The transaction analysis support system according to claim 1, which is characterized in that. 5. The frequency information management means indicates the frequency of occurrence of the function in response to an instruction from a frequency information collection instruction individually incorporated for each function when the database operation request is converted into a function. The transaction analysis support system according to claim 1, further comprising means for updating information. 6. A transaction analysis support method for presenting a relationship between each table of a database used in a database system and operated and managed by the database system and various database operation requests, which is issued to the database system. Each database operation request is monitored, the occurrence frequency of each database operation request is counted for each table constituting the database to create frequency information, and each database operation request is used in the operation request. The transaction analysis support screen is displayed by associating the occurrence frequency of each database operation request with the column designation condition for each table using the designated condition for the column of the table and the created frequency information. Transaction analysis support method. 7. When the database operation request is converted into a function, the information indicating the frequency of occurrence of the function is updated in response to an instruction from a frequency information collection instruction individually incorporated for each function, 7. The transaction analysis support method according to claim 6, wherein the frequency information is created.