KR101136457B1 - Method and apparatus for analyzing SQL Trace in DataBase Management System - Google Patents

Abstract

The present invention relates to a tuning operation for improving quality and performance in a database management system.

SQL Trace analysis method according to the present invention, generated by collecting the execution statistics of the SQL statement for each session (Session) to grasp the problem of the structured query (SQL) statement in the tuning process for improving the quality of the database management system (DBMS) A method of analyzing a SQL trace file, which is a raw data file that contains traced execution information, (a) separating a plurality of SQL statements included in each of the plurality of SQL trace files to be analyzed and Extracting; And (b) sorting the extracted plurality of SQL statements according to a predetermined criterion.

Description

Method and apparatus for analyzing SQL Trace in DataBase Management System for Structured Query Language (SRl) execution path analysis in database management system

The following drawings attached to this specification are illustrative of preferred embodiments of the present invention, and together with the detailed description of the invention to serve to further understand the technical spirit of the present invention, the present invention is a matter described in such drawings It should not be construed as limited to.

1 is a diagram illustrating a configuration of a database system in which a SQL Trace analysis apparatus according to an embodiment of the present invention is implemented.

2 is a block diagram showing the main configuration of the SQL Trace analysis apparatus according to an embodiment of the present invention.

3 is a flowchart illustrating a procedure of an SQL trace analysis method according to an embodiment of the present invention.

4 is a diagram capturing a screen of the SQL statement and execution statistics information extracted from the SQL Trace file.

<Explanation of symbols for the main parts of the drawings>

100: analysis device 200: DBMS

210: database 300: administrator

401,402,403: user

The present invention relates to a database management system, and more particularly, to a tuning operation for improving quality and performance of a database management system.

In general, a database stores data related to each other. The data stored in the database needs to be updated, inserted, or deleted as necessary. For this purpose, the database is managed by a software system called Database Management System (DBMS), which is a mediator of applications and data, which allows data to be shared by multiple applications in one place so that all applications can share data. It is software that collects and maintains comprehensively. In other words, DBMS is a comprehensive data management system that provides applications with consistent results by searching or changing the data they want.

These DBMSs use Structrer Query Language (SQL) to allow multiple users to retrieve or manage data in the database. Structured query language is a type of query language for databases, including data definition words (DDL) and data manipulation words (DML). This SQL is widely used because it is not limited to a specific database system. It was initially used only on systems that were IBM's relational database, but is now widely used in other databases.

SQL is a representative language based on relational mapping and is a language for mapping desired output relations from input relations (tables). In 1973, structured queries as relational expressions (SQUARE) were published in 1973. Many mathematical expressions were difficult for beginners to use. Based on this, structured english as query language (SEQUEL) was created at San Jose Institute in IBM in 1974, and this was again improved and SEQUEL 2 was released in 1976. The system, based on SEQUEL 2, called System R and Oracle, became known as SQL.

SQL has not only simple query capabilities, but also complete data definition and manipulation capabilities. It can also be used interactively through an online terminal, or embedded in a program in a host language such as COBOL, PL / I, or C. SQL is device independent and does not make any reference to access paths. It performs operations on tables, which are sets of records rather than individual records. In addition, SQL is a syntax similar to English sentences, so even beginners can use it relatively easily.

Recently, with the development of the information and communication field, a huge amount of information is pouring out, and accordingly, it is necessary to manage a huge amount of data. To this end, a database consisting of a large amount of data has been built, and the DBMS for the management of such a database is gradually developing. This vast amount of databases and the many users who want to use them make DBMSs more complex and more complex for user convenience. In addition, as many users want to use such a large database, the DBMS also has a large load due to increased SQL processing. This load on the DBMS slows down the processing speed of the system, which leads to a decrease in the performance of the DBMS.

As such, according to the development of information and communication technology, there is a need to improve the quality of a large database and a DBMS managing the same. To this end, tuning has been carried out to improve the performance and processing speed of the DBMS. In addition, fast tuning is urgently needed to reflect rapidly changing information, data, and user demands. For this tuning, it is necessary to analyze the SQL statements that are the main targets in the DBMS and extract the problematic SQL statements quickly and accurately. In order to analyze the already processed SQL statement, the relevant information when the SQL statement is processed should be recorded as a record, which is left in the SQL Trace file. This SQL Trace file is a raw data file that contains the SQL processing information at the time of processing. Therefore, tuning starts by analyzing these SQL trace files and finding problems.

However, due to the large database and the DBMS complicated by many users, there are many areas to control and manage, thus creating various SQL trace files. Each SQL Trace file contains a large amount of SQL statements. Therefore, in the past, in order to analyze SQL trace files and to tune SQL, it was inconvenient to analyze several SQL trace files one by one. In other words, to tune the existing SQL, all the SQL Trace files had to be opened and checked one by one. In addition, since several SQLs exist in one SQL Trace file, it is inconvenient to find, extract, and re-edit the SQL that has a problem.

The first object of the present invention is to improve the problem that the overall analysis is delayed and the tuning becomes difficult and complicated by analyzing several SQL Trace files separately during the existing SQL tuning. .

The second purpose is to improve the faster and more accurate SQL analysis by improving the work that has been doubled to extract the SQL statement of the problem that needs tuning from a large SQL Trace file.

In order to achieve the above object, SQL Trace analysis method according to the present invention, SQL statement to grasp the problem of structured query (SQL) statement in the tuning (Tuning) process for improving the quality of the database management system (DBMS) A method of analyzing a SQL trace file, which is a raw data file containing trace information generated by collecting execution statistics by session, (a) a plurality of SQL trace files to be analyzed Separating and extracting a plurality of SQL statements included in each; And (b) sorting the extracted plurality of SQL statements according to a predetermined criterion.

Preferably, step (a) comprises: (1) changing a plurality of SQL Trace files to be analyzed into a format that can be analyzed in a standardized format; And (2) analyzing the changed plurality of files to separate and extract a plurality of SQL statements included in each file.

According to another aspect of the present invention, an execution path generated by collecting execution statistics of SQL statements for each session in order to identify a problem of SQL statements in a tuning process for improving the quality of a database management system (DBMS). A device that analyzes a SQL trace file, a raw data file containing information, and refers to a plurality of SQL trace files to be analyzed and means for separating and extracting a plurality of SQL statements included in each file. ; And a means for sorting the extracted plurality of SQL statements according to a predetermined criterion.

According to another aspect of the present invention, in order to identify the problem of the SQL statement in the tuning process for improving the quality of the DBMS (DBMS) execution path generated by collecting the execution statistics of the SQL statement by session (Session) ( A recording medium that records a program that analyzes a SQL trace file, which is a raw data file containing trace information, and includes a procedure for changing a plurality of SQL trace files to be analyzed into a format that can be analyzed in a standardized format. A computer-readable program for analyzing the changed plurality of files, separating and extracting the plurality of SQL statements included in each file, and for sorting the extracted plurality of SQL statements according to a predetermined criterion; A recording medium recording the above is provided.

Hereinafter, exemplary embodiments of the present invention will be described in detail with reference to the accompanying drawings. Prior to this, terms or words used in the specification and claims should not be construed as having a conventional or dictionary meaning, and the inventors should properly explain the concept of terms in order to best explain their own invention. Based on the principle that can be defined, it should be interpreted as meaning and concept corresponding to the technical idea of the present invention. Therefore, the embodiments described in the specification and the drawings shown in the drawings are only the most preferred embodiment of the present invention and do not represent all of the technical idea of the present invention, various modifications that can be replaced at the time of the present application It should be understood that there may be equivalents and variations.

1 is a diagram illustrating a configuration of a database system in which a SQL Trace analysis apparatus according to an embodiment of the present invention is implemented.

Referring to FIG. 1, a database system includes a database 210 in which a large amount of data are constructed, users 401, 402, and 403 using the database 210, a database management system 200 managing the database 210, and the database. A manager 300 for operating and managing the management system 200, a log file 220 in which execution information of the database system is recorded, and an analysis device 100 for analyzing the log file 220.

The database 210 is a collection of one or more logically related data, which is highly structured to improve the efficiency of searching and updating. In other words, it is a collection of data that systematically integrates several data files to eliminate duplication of data items and to structure and remember the data.

The users 401, 402, 403 are people, applications, clients, user terminals, etc. that use the database 210. The user defines, registers, and inserts data into the database 210, and searches, updates, deletes, changes, references, applications, and manipulates the data of the database 210.

The database management system 200 is a system that maintains, maintains, and manages the database 210, and assists the users 401, 402, and 403. More specifically, the database management system 200 (hereinafter referred to as DBMS) is a software system configured to configure the database 210 and apply it. In particular, it includes features that make the database easier for users or applications to use. The functions can be broadly divided into configuration functions, operation functions, and control functions. Configuration functions define the structure of the data to be stored in the database and how the application uses it, including the definition of record structures, the definition of data models, and the definition of physical structures. Manipulation functions allow users to search, update, insert, and delete data stored in the database according to user requirements. The control function is to protect the data by keeping the contents of the database consistent and non-duplicated and restricting unauthorized access.

The manager 300 is an individual or a group responsible for managing the database 210 and the DBMS 200 in the best condition. The database 210 and the DBMS 200 determine the accuracy or integrity of the information of the database 210, determine the internal storage structure and access management measures of the database 210, establish and check the security measures of the data, and the like. Monitor your performance and take responsibility for responding to changing needs.

The log file 220 is a raw data file in which various information is recorded when the database 210 is manipulated through the DBMS 200. Information related to structured query (SQL) statement is generated as SQL Trace file. In most DBMSs, a large number of users (401, 402, 403) and sessions generate a large amount of SQL Trace files. Each of these SQL Trace files contains a number of SQL statements.

The analyzing apparatus 100 is used to analyze the SQL statement using the log file 220 at the time of tuning the manager 300. In order to improve the performance of the database 210 and the DBMS 200, the manager 300 performs a system tuning operation. In this case, an SQL trace file of the log file 220 is analyzed in order to analyze a problem SQL statement. Will be analyzed. The analyzing apparatus 100 performs such a task as generating, analyzing, sorting, and outputting the SQL trace file, and provides the analyzed information to the manager 300.

Then, the SQL trace analysis apparatus according to an embodiment of the present invention will be described in detail with reference to the drawings.

2 is a block diagram showing the main configuration of the SQL Trace analysis apparatus according to an embodiment of the present invention.

2, the SQL Trace analysis apparatus 100 according to the present invention is a trace file generation module 110, format conversion module 120, SQL extraction module 130, SQL sorting module 140, SQL output module 150 and the peripherals that drive them.

The trace file generation module 110 collects execution statistics of SQL statements processed by the DBMS 200 for each session and generates a trace file. The trace file is a raw data file and includes a plurality of SQL information therein. The trace file generation module 110 generates a plurality of trace files due to the number of users using the DBMS 200 and the complicated DBMS 200 structure. In addition, when a trace generation function is supported in the DBMS 200, only a trace generation instruction may be transmitted to the DBMS 200 without directly generating a trace file. In this case, the overall performance of the DBMS 200 is slightly deteriorated. Therefore, in the present invention, the trace file generation module 110 may be omitted.

The format conversion module 120 converts a plurality of SQL Trace files into a format of a format that can be analyzed. Alternatively, analyze a plurality of SQL Trace files and create a file in a text format that can be analyzed for each file. Since the SQL Trace file is a raw data file, it is difficult to directly grasp the internal information, so it is easily converted into a text format and a formatted format. An example of such a format is the textual SQL prf format. This conversion can be done using an existing utility tool, for example using the TKPROF tool. The format conversion module 120 converts a plurality of SQL Trace files in a batch to generate a file in a format of a standard form that can be analyzed.

The SQL extraction module 130 separates the SQLs from the converted SQL trace files and extracts the separated SQLs. A single SQL Trace file contains a lot of SQL information, and all of these SQL trace files are analyzed to extract all the SQL. The extracted SQL includes SQL statements and statistical information when the SQL is processed. In addition, the SQL extraction module 130 may separate and extract SQLs directly from a SQL trace file which is a raw data file.

The SQL sort module 140 sorts all the SQL extracted by the SQL extraction module 130. Each of the plurality of SQL trace files to be analyzed again contains a large number of SQLs. Sorting is performed on all SQLs extracted from all SQL Trace files of the analysis target, and the sorting criteria is based on one or a combination of items of SQL information included in the extracted SQL. The sorting criteria may be set as basic items that are input from a user or preset. As the sorting method, an ascending sorting method, a descending sorting method, or a sorting according to a rule set by a user is used. In particular, sorting may be performed based on a plurality of items. The item used as a reference when sorting the SQL exists in the SQL execution statistics. The SQL Execution Account Report is included with the SQL statements in the SQL extracted from the SQL Trace file. For example, the extracted SQL includes an SQL statement A and SQL execution statistics information B as shown in FIG. SQL statement (A) is a statement executed in the DBMS, SQL execution statistics (B) is a variety of information when the SQL statement (A) is executed. For example, assuming that the sort criterion item is set to CPU, the operation of the SQL sort module 140 compares all CPU items of SQL execution statistics information in all of the extracted SQLs. Sorted in SQL order.

Here, referring to FIG. 4, the items that are the sort criteria in the SQL sorting module 140 are parsed, executed, and fetched items, which are operations of the DBMS by SQL. . Parsing is a statistic about the stage at which an SQL statement is parsed, including new parsing or retrieval from a shared pool. However, if Hold cursor is specified in Looped SQL or PRO * SQL, it is parsed only once. Execute is a statistic about the execution phase of an SQL statement. Update, insert, and delete statements are the result of doing this. Fetch is the statistics called when the SQL statement is executed. In case of extracting one case after SELECT statements processed in partial range method or full range processing, many values appear mainly here. Next, the statistical value items for the above three items will be described. count is the number of times the SQL statement was parsed, executed, and executed. CPU is the CPU time actually used by parsing, execution, and invocation. elapsed is the total time actually spent from start to finish. disk is the number of data blocks read from the disk. query is the number of blocks read in the memory that have not been changed or read snapshot blocks that have been modified by another session but have not been checked yet. current is the number of blocks that only access blocks that are valid only for you because you have not checked what you have done in the current session. rows is the number of rows finally accessed by the result of executing the SQL statement.

The SQL output module 150 outputs through the output means to provide the SQL sorted by the SQL sort module 140 to the user. The output can be on screen, display means, or saved to a file. In addition, you can set the number of SQL to be output as you want.

Next, the peripheral device for driving the modules includes a user interface 160, a control module 170, an input / output device 180, a memory 190, and the like. The user interface 160 is a means for receiving information required by each module from a user. The control module 170 is a means for controlling the operation between each module, or between the module and the peripheral device. The input / output device 180 is a means for communicating data with a DBMS. The memory 190 is a means for temporarily storing sorted SQLs and storing information of modules and devices.

Next, an SQL trace analysis method according to an embodiment of the present invention will be described. 3 is a flowchart illustrating a procedure of an SQL trace analysis method according to an embodiment of the present invention.

Referring to FIG. 3, the SQL Trace analysis method according to the present invention first records execution information such as SQL or transactions performed in a DBMS. Among them, SQL trace file is created by recording execution statistics information about SQL executed in DBMS. This process can be omitted if the DBMS provides a trace generation function (step S10).

When the administrator tries to tune or check the DBMS, the SQL Trace file is analyzed. When the analysis start signal is received from the administrator, the analysis process is performed. If there is no analysis start signal, the trace file generation process of step S10 is repeated and proceeds (step S20).

When the analysis begins, the first step is to select the object to be analyzed. In this case, a file to be analyzed is selected from a plurality of SQL trace files generated in step S10 or prepared in advance by an administrator or a user. All SQL Trace files included in a certain folder or directory may be selected, or files may be individually selected (step S30).

After the SQL trace files to be analyzed are selected, the files are converted into a form that is easy to analyze. Since the SQL trace file is a raw data file, an intermediate process of converting the SQL trace file into an easy form (for example, a text file) is performed. At this time, all the files selected in step S30 are converted sequentially or simultaneously. The raw data file, the SQL Trace file, can be converted directly to another format, or the SQL Trace file can be maintained and referenced to create a new file in the converted format. The format that can be analyzed is, for example, the SQL prf format. In addition, the conversion operation may be performed using the TKPROF utility tool (step S40).

The files generated after the process of step S40 undergo an internal analysis process to separate and extract SQLs. In this case, the plurality of analysis files are analyzed to extract a plurality of SQLs included therein. All extracted SQLs are stored in temporary storage for the following procedure. In another embodiment, SQL may be extracted by directly analyzing the row data files selected after step S30. In this case, step S40 may be omitted (step S50).

All of the extracted SQLs are sorted according to a predetermined criterion in descending or ascending order or by a rule input from a user. In this case, the sorted SQLs are listed in total in a plurality of SQLs included in each of the plurality of SQL trace files selected for analysis. In addition, before the sorting operation, a step of setting an item to be a reference for sorting is performed, and the setting of the reference item may be directly set by the user or may be preset. In this case, the user may input a reference item to be sorted from the user or provide a list of selectable reference items, and then select the user to set the item. Items that may be the basis of the alignment have been described above and will be omitted. (Steps S60 and S70)

The sorted SQLs are outputted through display means or generated as analysis report files and provided to the user. In this case, the output can also be controlled by setting the number or output amounts of SQL to be output from the top. The provided SQL analysis information helps the user to use it usefully during DBMS tuning work or check work (step S80).

The method of the present invention as described above may be implemented as a program and stored in a recording medium (CD-ROM, RAM, ROM, floppy disk, hard disk, magneto-optical disk, etc.) in a computer-readable form. Since this process can be easily implemented by those skilled in the art will not be described in more detail.

The terms or words used in this specification and claims are not to be construed as limiting in their usual or dictionary meanings, and the inventors may appropriately define the concept of terms in order to best explain their invention in the best way possible. It should be interpreted as meaning and concept corresponding to the technical idea of the present invention based on the principle that the present invention.

Therefore, the embodiments described in the specification and the drawings shown in the drawings are only the most preferred embodiment of the present invention and do not represent all of the technical idea of the present invention, various modifications that can be replaced at the time of the present application It should be understood that there may be equivalents and variations.

The apparatus and method for analyzing SQL traces according to the present invention provides an effect of analyzing a large number of SQL Trace files that need to be analyzed for actual DBMS tuning at once and analyzing them more quickly than existing analysis tasks. This allows for fast and fast tuning, providing a higher quality database service.

In addition, all the SQLs included in the multiple SQL Trace files can be extracted and all the SQLs can be integratedly analyzed, thus providing a more accurate and faster analysis than analyzing them one by one.

Claims (16)

Execution path information generated by collecting execution statistics of SQL statements by session to identify problems with structured query (hereinafter, SQL) statements during the tuning process for improving the quality of database management systems (DBMS) A method of analyzing a SQL Trace file, which is a raw data file containing (a) separating and extracting a plurality of SQL statements each included in the plurality of SQL Trace files to be analyzed; And and (b) sorting the extracted plurality of SQL statements according to a predetermined criterion. The method of claim 1, Step (a) is, (1) changing a plurality of SQL Trace files to be analyzed into a format that can be analyzed in a standardized format; And (2) analyzing and analyzing the plurality of modified files to separate and extract a plurality of SQL statements included in each file. The method according to claim 1 or 2, Before the step (a) further comprises the step of generating a SQL trace file for each of a plurality of sessions. The method according to claim 1 or 2, And outputting the sorted SQL statement after step (b). The method according to claim 1 or 2, The step (b) is a large amount of SQL trace analysis method, characterized in that to sort the SQL statements based on the desired item among the execution statistics of the extracted plurality of SQL statements according to the user's request. The method of claim 5, A large SQL Trace analysis method characterized by sorting in ascending or descending order or a rule desired by the user based on the value of a reference item. The method of claim 5, The criteria items include the number of executions related to SQL Parsing, Execute SQL, Fetch command, CPU time, total elapsed time, the number of times a disk (physical) or memory (logical) has been read, A method of analyzing a large amount of SQL Traces, characterized in that it is one of the number of extracted rows, the number of library cache misses, and a combination thereof. The method of claim 4, wherein A method for analyzing a large amount of SQL traces, characterized in that for outputting the sorted SQL statements in order of priority by the number set by the user. The method of claim 2, Step (1) is a SQL Trace analysis method, characterized in that for changing the SQL trace file of the raw data file to the SQL prf format of the text that can be analyzed. The method of claim 9, A large SQL Trace analysis method characterized by using the TKPROF utility tool when changing to the SQL prf format. Raw containing execution information generated by collecting execution statistics of SQL statements for each session in order to identify problems with SQL statements in the tuning process for improving the quality of the database management system (DBMS). ) A device that analyzes a SQL trace file, a data file. Means for separating and extracting a plurality of SQL statements included in each file by referring to the plurality of SQL Trace files to be analyzed; And And a means for sorting the extracted plurality of SQL statements according to a predetermined criterion. The method of claim 11, The means for extracting, Means for converting a plurality of SQL Trace files to be analyzed into a format that can be analyzed in a standardized format; And And a means for separating and extracting a plurality of SQL statements included in each file by analyzing the changed plurality of files. 13. The method according to claim 11 or 12, And a means for generating a SQL trace file for each of a plurality of sessions in the database management system. 13. The method according to claim 11 or 12, And a means for outputting the sorted SQL statements in the sorting means. 13. The method according to claim 11 or 12, And the sorting means sorts the SQL statements based on a desired item among execution statistics of the extracted plurality of SQL statements according to a user's request. Raw containing execution information generated by collecting execution statistics of SQL statements for each session in order to identify problems with SQL statements in the tuning process for improving the quality of the database management system (DBMS). ) A recording medium that records a program that analyzes a SQL trace file, a data file. A process of changing a plurality of SQL Trace files to be analyzed into a format that can be analyzed in a standard format; Analyzing a plurality of changed files to separate and extract a plurality of SQL statements included in each file, and And a computer-readable program for recording a computer-readable program for executing the procedure of sorting the extracted plurality of SQL statements according to predetermined criteria.

Images