JP2007122405A - Performance tuning system for database management system - Google Patents

Abstract

<P>PROBLEM TO BE SOLVED: To select an SQL group including a search system SQL and an update system SQL for finding an effective tuning method in an SQL group unit. <P>SOLUTION: An SQL group to be tuned is selected from statistic information accumulated through actual work operation and from an execution frequency and SQL processing time. Weighting matching priority is carried out among the SQL group, and a score target value is decided for each SQL. Tuning is carried out on the target SQL to calculate a score, and tuning satisfying the target value is decided. <P>COPYRIGHT: (C)2007,JPO&INPIT

Description

  The present invention relates to a SQL performance tuning method that enables optimum tuning of SQL executed in a database management system.

  In the conventional SQL performance tuning, a method of performing performance tuning in SQL units is realized.

  Further, the method for extracting SQL statistical information in units of transactions, analyzing it, and performing tuning by performing tuning described in Japanese Patent Laid-Open No. 2001-175678 enables tuning for a plurality of SQLs.

  Furthermore, the automatic index addition system for a relational database described in Japanese Patent Laid-Open No. 10-1111819 enables effective index tuning for a plurality of SQLs of a search system SQL and an update system SQL.

JP 2001-175678 A JP 10-1111819 A

  In an actual system such as electronic commerce, operation is performed by executing a wide variety of search and update SQL. In such a system, in order to maintain the database access performance, tuning by SQL unit is difficult. In order to improve performance, a target SQL is selected, and a tuning method considering the mutual influence as an SQL group is essential.

  In the above prior art, the method described in Japanese Patent Application Laid-Open No. 2001-175678 is effective for search SQL, but cannot be applied to update SQL.

  Japanese Patent Application Laid-Open No. 10-1111819 has a problem that it cannot be applied to tuning other than tuning for adding an index.

  Accordingly, an object of the present invention is to solve the above-described problems, to select an SQL group including a search system and an update system SQL, and to provide an SQL tuning system for an SQL group unit.

  In order to achieve the above object, a performance tuning system for a database management system according to the present invention uses a database management system to analyze and execute an SQL command for relational database processing operation issued by a business program, and to obtain statistics acquired by the database management system. Various tunings by setting a SQL command selection processing unit that selects a plurality of SQL commands to be tuned among SQL commands issued by a business program based on information, and a target score for the selected plurality of SQL commands The problem is solved by constructing a system including an SQL tuning processing unit that determines an optimal tuning method from a calculation result of a score at the time of execution.

  According to the performance tuning system of the present invention, it is possible to efficiently improve the performance of all SQL commands with respect to a plurality of SQL commands for searching and updating.

  Embodiments of a performance tuning system for a database management system according to the present invention will be described below in detail with reference to the drawings.

  FIG. 1 is a block diagram showing a system configuration example to which an embodiment of a performance tuning system for a database management system according to the present invention is applied. In the figure, 101 is a business program, 102 is an SQL (Structured Query Language) command issued by the business program 101, 103 is a database management system (DBMS) that realizes control according to the SQL command 102, and 104 is a SQL command 102. Is an SQL analysis / execution processing unit that performs execution control of the corresponding search processing and update processing by analyzing the contents of the URL, 105 is a statistical information acquisition unit that obtains statistical information 107 regarding processing in the SQL analysis / execution processing unit 104, and 106 is a statistical A journal for accumulating statistical information 107 acquired by the information acquisition unit 105, 108 a SQL tuning tool for performing SQL tuning, 109 an SQL command selection processing unit for selecting a plurality of target SQL commands based on the statistical information 107, 110 SQL command selection processing unit 109 An SQL tuning processing unit 111 that tunes the SQL command selected in step S1 and makes a determination based on a score.

FIG. 2 is a flowchart showing an example of the overall procedure of the tuning system according to the system of FIG. In the figure, the statistical information accumulated by executing the business program is acquired (201). Then, an SQL command to be an SQL tuning target is selected from the acquired statistical information by the following method (202).
Method 1: Among the SQLs included in the statistical information, the higher ones are sorted in the order of the most frequently used SQLs and are selected as tuning target SQL groups.
Method 2: Among the SQLs included in the statistical information, the higher ones are selected as the tuning target SQL group by sorting in the order of the poorly performing SQL.

  In order to improve the performance according to the importance of each SQL for the SQL in the selected SQL group, weighting is performed for each SQL so that the total score becomes 100% (203). Next, the value of the initial score of each SQL is set to 100, and the target score for each SQL is calculated from the weighted value and the initial value performed for each SQL (204). Thereafter, tuning processing is performed on the target SQL group, and a score is calculated for each SQL in the target SQL group (205). Execution and score calculation are performed until all possible tuning processes and combinations of tuning processes are completed (206 = NO). When score calculation is completed for all tuning patterns (206 = YES), the calculation result is evaluated and the best tuning process is selected (207). Even if all tuning results fall below the target score, the best tuning process is selected.

  FIG. 3 is a diagram showing an example of a tuning table used to calculate a score for tuning performed by the SQL tuning processing unit in FIG. This tuning table is used to calculate a score at the time of tuning execution, and includes a tuning item 301, a score value (basic value) 302, and a fluctuation value presence / absence 303. The score value (basic value) 302 includes a search system 304 and an update system 305. When tuning of the tuning item 301 is executed, the score value of the search system 304 is changed to the update system SQL if tuning is performed for the search system SQL. In the case of tuning, the score value of the update system 305 is applied. The fluctuation value presence / absence 303 indicates whether a detailed fluctuation value table exists, and “present (variation value table name)” or “none” is set. For example, for the tuning of “setting an index”, the score value varies depending on the column to which the index is set, the type of index, etc., so a link is set to the target fluctuation value table to calculate the detailed score value. Then, the score value is calculated from the fluctuation value table.

  FIG. 4 is a diagram showing an example of a variation value table related to “define an index in the target column” in the tuning method of FIG. This fluctuation value table is a detailed score value table used to calculate a score when tuning “define an index for a target column”, and includes a target column 401, a target column number 402, a tuning content 403, and a score. It consists of a variation value 404.

  FIG. 5 is a diagram showing an example of a variation value table related to “delete the index of the target column” in the tuning method of FIG. This variation value table is a detailed score value table used for calculating a score when tuning “delete the index of the target column” is performed similarly to the above-described variation value table. Numeral 502, tuning contents 503, and score fluctuation value 504.

  FIG. 6 is a diagram showing a specific example of an inventory management table in the inventory management system to be tuned. This inventory management table includes a product code 601, a product name 602, a manufacturing year 603, a quantity 604, and a price 605. In the figure, it is assumed that an index is defined only for the product code 601.

FIG. 7 is a diagram showing a specific example of a result when the tuning system of FIG. 2 is applied to the inventory management system of FIG. As a result of selecting the SQL to be tuned with the most frequently used one from the statistical information acquired first, the following two were selected as the corresponding SQL. The value portion corresponding to the column is variable, and the following SQL is a representative example.
SQL1: select Product code from inventory control table where Year of manufacture ≦ 2004 order by Product code, quantity SQL2: update Inventory control table set Price = Price x 0.7 where Year of manufacture ≦ 2004 and Price ≧ 100000
Further, assuming that the ratio of the usage frequencies of SQL1 and SQL2 is 6: 4 from the statistical information, the weights of SQL1 and SQL2 are determined to be 60% and 40%, respectively. As a result, the SQL1 and SQL2 score target values are determined as SQL1 = 160 and SQL2 = 140, with the initial value set to 100.

Next, score values for each tuning item are calculated for all applicable tuning items and all combinations shown in FIGS. An example of the score value when tuning is performed is as follows. The number of updates is assumed to be 10 from the statistical information.
# 1: A single index is defined in the column “Production Year”.

  Since this applies to “define an index in the target column” in the tuning method of FIG. 3, a score value is calculated from the variation value table of FIG.

Regarding SQL1, since the target column 401 is “where search condition”, the target column number 402 is “1”, and the tuning 403 is “define a single column index”, the score value is +20, and the result is as follows: Become.
SQL1 = 100 + 20 = 120
Similarly, the score value of SQL2 is calculated as follows.
SQL2 = 100 + 20 = 120
Similarly, the result of calculating the score value is as follows.
# 2: A single column index is defined for each “year of manufacture” and “price”.
SQL1 = 100 + 20 = 120
SQL2 = 100 + 2 × 20 + 10 × (−5) = 90
#M: A multi-column index is defined in the order of “product code”, “quantity”, and “production year”.
SQL1 = 100 + 40 = 140
SQL2 = 100
#N: A single index is defined in the column “Production Year”. In addition, a multi-column index is defined in the order of “product code”, “quantity”, and “production year”.
SQL1 = 100 + 40 = 140
SQL2 = 100 + 20 = 120
When the last selected tuning result is compared with the target score value and evaluated, a single index is defined in the column “manufacturing year” as the optimum tuning item. In addition, it is determined that the multi-column index is defined in the order of “product code”, “quantity”, and “production year”.

  As for this tuning result, when the index is defined for “year of manufacture” and “price” in the tuning of SQL2, in the search condition of the update statement, the performance is improved with respect to the search for “price”, but the index is defined as “ Since the “Price” column itself is updated, the fact that the performance deteriorates when considered as a whole appears in the score value.

  In addition, by defining a multi-column index in the order of “product code”, “quantity”, “manufacturing year” for SQL1, it becomes possible to perform processing only with the index, and it is not necessary to perform sort processing inside the DBMS. The performance of the order by clause is reflected in the score value. Note that tuning that defines an index causes performance degradation in update processing, but only SQL2 is the tuning target for the update system, and the performance definition does not occur because the column that defines the index is not the update target column. The result is also reflected in the score value.

  FIG. 8 is a diagram showing a specific example of an inquiry response table in an inquiry system to be tuned as another embodiment. This inquiry response table includes an inquiry date 801, an inquirer 802, an OS name 803, a product name 804, a version 805, an inquiry content 806, an answer 807, and an answer date 808. In the figure, it is assumed that indexes are defined in three columns of an inquiry date 801, an OS name 803, and a product name 804, respectively.

FIG. 9 is a diagram showing a specific example of a result when the tuning system of FIG. 2 is applied to the inquiry system of FIG. As a result of selecting the SQL to be tuned with the most frequently used one from the statistical information acquired first, the following two were selected as the corresponding SQL. The value portion corresponding to the column is variable, and the following SQL is a representative example.
SQL1: select Query contents, answer from query answer table where OS name = "OS2" and product name = "product 3" and version = "5.0"
SQL2: insert into Query Response Table values ('2005/02/05', "tanaka", "OS4", "Product 3", "4.0", "Question Content 10", "Response Content 10", '2005/02 / 06 ')
Furthermore, assuming that the ratio of the usage frequencies of SQL1 and SQL2 is 4: 6 from the statistical information, the respective weights are determined to be 40% for SQL1 and 60% for SQL2. From this, SQL1 and SQL2 score target values are set to 100 as the initial value.
SQL1 = 140 and SQL2 = 160 are determined.

Next, as with the inventory management system described above, score values for each tuning item are calculated for all applicable tuning items and all combinations shown in FIGS. It is assumed that the number of additional inquiry contents is 10 from the statistical information.
# 1: A single column index is defined for “version”.
SQL1 = 100 + 20 = 120
SQL2 = 100 + 10 × (−5) = 50
# 2: “OS name”, “product name”, and “version” are redefined with a multi-column index.
SQL1 = 100 + 30 = 130
SQL2 = 100 + 20 = 120
#M: Delete the index defined in “inquiry date”.
SQL1 = 100
SQL2 = 100 + 1 × 30 = 130
#N: “OS name”, “product name”, and “version” are redefined with a multi-column index. In addition, the index defined in the “query date and time” is deleted.
SQL1 = 100 + 30 = 130
SQL2 = 100 + 20 + 1 × 30 = 150
When the last selected tuning result is evaluated by comparing with the target score value, “OS name”, “product name”, and “version” are redefined as multi-column indexes as the optimum tuning items. In addition, it is determined that the index defined in “inquiry date” is to be deleted.

  As for the tuning result, since the index is not defined in the “version” used in the search condition for the tuning of the SQL1, the performance of the SQL1 is improved by defining the index in the “version”, but the SQL2 of the update system is improved. It is clear from the score value that the performance deteriorates. Therefore, considering the performance of SQL2, by changing the definition from the current single column index to the multi-column index of “OS name”, “product name”, and “version”, the number of indexes is reduced, and SQL2 is also used. It can be confirmed through the score that performance can be improved. Also, since SQL2 is an SQL that performs insertion, the performance degrades when the number of index definitions increases. In the SQL 1 of the search system to be tuned, “query date” is not used in the selection formula or search condition, so that deleting the “query date” index does not affect the performance, which appears in the score value. .

  According to the present embodiment, since the target SQL is determined in units of groups, it is possible to obtain a tuning method that is optimal for a SQL group while considering the influence of actual tuning on each SQL.

It is a block diagram which shows the system configuration example to which one Embodiment of the performance tuning system of the database management system in this invention is applied. It is a flowchart which shows an example of the whole procedure of the tuning system by the system of FIG. It is a figure which shows an example of the tuning table used in order to calculate the score of tuning implemented in the SQL tuning process part in FIG. FIG. 4 is a diagram illustrating an example of a variation value table related to “define an index in a target column” in the tuning method of FIG. 3. FIG. 4 is a diagram illustrating an example of a variation value table related to “delete target column index” in the tuning method of FIG. 3. It is a figure which shows one specific example of the inventory management table | surface in the inventory management system used as tuning object. FIG. 7 is a diagram illustrating a specific example of a result when the tuning system of FIG. 2 is applied to the inventory management system of FIG. 6. It is a figure which shows one specific example of the inquiry reply table in the inquiry system used as tuning object. FIG. 9 is a diagram illustrating a specific example of a result when the tuning system of FIG. 2 is applied to the inquiry system of FIG. 8.

Explanation of symbols

DESCRIPTION OF SYMBOLS 101 ... Business program, 102 ... SQL command, 103 ... Database management system (DBMS), 104 ... SQL analysis / execution processing unit, 105 ... Statistical information acquisition unit, 106 ... Journal, 107 ... Statistical information, 108 ... SQL tuning tool, 109: SQL command selection processing unit, 110: SQL tuning processing unit, 111: Relational database (RDB).

Claims (3)

  A SQL command selection processing unit that selects a plurality of SQL commands to be tuned from among SQL commands issued by the business program, and a tuning method is determined by score values calculated by a plurality of tuning methods for the selected SQL commands. A tuning system comprising an SQL tuning processing unit.   The tuning system according to claim 1, wherein a plurality of SQL commands to be tuned are selected based on statistical information. 3. The system according to claim 1, wherein a target value for performance tuning is determined based on priority for a plurality of SQL commands to be tuned.

Images