KR100852323B1 - Apparatus and method for monitoring data base - Google Patents

Abstract

The present invention relates to an apparatus for monitoring a database, and in particular, an apparatus capable of collecting and monitoring only state data of a database required under commercialized conditions in consideration of an operating environment of a database management system (DBMS) provided in an information processing system. And to a method. To this end, the present invention provides a database monitoring apparatus for managing a database using a database management system and monitoring the state of the database, comprising: a database which is a collection of operational data integrated and managed for the purpose of being shared and used by multiple users; ; And a database management system that selectively collects state data from the database based on the monitoring environment setting information that is set, and monitors the database.

Database, Monitoring, SQL, Database Management System (DBMS)

Description

Database monitoring apparatus and method {APPARATUS AND METHOD FOR MONITORING DATA BASE}

1 is a block diagram showing the configuration of a database monitoring apparatus according to an embodiment of the present invention.

2 is a configuration diagram showing an internal configuration of a database management system in FIG.

Figure 3 is an exemplary view for explaining the setting screen data of the monitoring tool of the database management system according to an embodiment of the present invention.

4 is an exemplary diagram for explaining a monitoring tool of a database management system according to an exemplary embodiment of the present invention.

5 is an exemplary view showing screen data of an individual session according to an embodiment of the present invention.

6 is a flowchart illustrating a database monitoring method according to an embodiment of the present invention.

7 is a flowchart illustrating an environment setting process for database monitoring according to an embodiment of the present invention.

<Description of main reference numerals in the drawings>

10: database 20a-20n: user

30: Log file 40: Administrator

100: database management system 101: configuration unit

102: data collection unit 103: data analysis unit

104: user interface (UI) 105: data input and output (I / O)

106: memory 107: control unit

The present invention relates to an apparatus for monitoring a database, and in particular, an apparatus capable of collecting and monitoring only state data of a database required under commercialized conditions in consideration of an operating environment of a database management system (DBMS) provided in an information processing system. And to a method.

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 DBMS (Database Management System), which is a mediator of applications and data that allows data to be shared by multiple applications so that all applications can share data. Software that collects in place and maintains comprehensively. In other words, the database management system is a comprehensive data management system that provides applications with consistent results at all times by retrieving or changing the data desired by the applications.

The database management system manages user requirements or the requirements of other programs so that users or other programs can use the data even if they do not know where the particular data is actually stored on the storage medium. The database management system ensures the integrity and security of the data in handling user requirements. Relational Database Management System (RDBMS) is the most common type of database management system, and the standardized user and program interface of the relational database management system is called Structured Query Language (SQL).

On the other hand, the technology to effectively build and operate the database as a component of the information processing system is an important problem that determines the success or failure of the information processing system. Accordingly, a database management system has been established, and various methods for managing the same have been performed.

Such a database management system has not been able to perform smooth database monitoring because the database management systems have different business characteristics and operating environments for monitoring the state of the database using a commercially available database management system tool. . Here, since the database management system tool is a pre-made tool, only the type of data provided exists, and there is a problem in that it cannot be reprocessed or collect new data. For example, in an Oracle database, various information about the operation (for example, views starting with v $) are stored in internal tables and managed in real time. However, there is a problem in commercially available tools that only provide information that appears in the screen design of the tool in the view starting with v $.

In addition, although the conventional database management system also has a tool for providing session level information, it is difficult to provide detailed information of the database session because it is difficult to collect when there are more than several thousand sessions. In other words, it is impossible to collect data by selecting and considering the performance of the database management system.

SUMMARY OF THE INVENTION The present invention has been made in view of the above problems, and provides an apparatus and method for monitoring a database that can collect and monitor only state data of a required database under commercialized conditions of a database management system provided in an information processing system. There is this.

Another object of the present invention is to provide an apparatus and method for monitoring a database that can selectively collect state data (including raw information) of a database in consideration of an operating environment of a database management system.

Another object of the present invention is to provide an apparatus and method for monitoring a database that can collect state data of a database at a desired interval in consideration of load of a database management system.

It is still another object of the present invention to provide an apparatus and method for monitoring a database that can analyze and analyze the corresponding database state data collected selectively.

A database monitoring apparatus for managing a database using the database management system according to the present invention and monitoring the state of the database for achieving the above object comprises: a database in which a large amount of data and data state data are stored and managed; A session for selecting data collection items for selectively collecting instance level statistics and waiting event information input from an administrator, and at least one session for selectively collecting session level statistics and waiting event information. Environment setting means for setting monitoring environment setting information including selection information and a data collection period; And collecting means for selectively collecting only raw data corresponding to the configuration information from state data provided in real time from the database.

In addition, the database monitoring apparatus according to the present invention receives an inquiry request for the raw data from an administrator, and receives an analysis request for the raw data from a service means or an administrator for providing the raw data for the monitoring, It further includes an analysis means for comparing, analyzing and providing trends on the raw data.
In this case, the session selection information is at least one selected from a background session, a parallel slave session, and an active session.
Further, the raw data may include instance level statistical information and wait event information; And session level detail information, session level statistical information, session level waiting event information, and session level SQL information.

delete

delete

According to another aspect of the present invention, there is provided a method for monitoring a state of a database for storing and managing a large amount of data and state data, the method comprising: receiving segment user and database link setting information for collecting data from an administrator; Session selection information and data collection for selecting data collection items for selectively collecting instance-level statistics information and waiting event information, and selecting at least one session for selectively collecting session-level statistics information and waiting event information. An environment setting step of setting monitoring environment setting information including a cycle; A data collection step of selectively collecting raw data corresponding to the environment setting information from state data provided in real time from the database; And a data service step of providing the collected raw data to an administrator.

The data service step may further include: receiving an inquiry request for the raw data from the manager; And providing the collected raw data to an administrator according to the inquiry request or receiving a request for analyzing the raw data from the administrator. And comparing and analyzing trends of the collected raw data according to the analysis request and providing the manager with the result data.

delete

It should be noted that in the following description, only parts necessary for understanding the operation and operation according to the present invention will be described, and descriptions of other parts will be omitted so as not to distract from the gist of the present invention.

Also, in the following description, specific details of the database monitoring apparatus and method of the present invention are presented to provide a more general understanding of the present invention. It will be apparent to those skilled in the art that the present invention may be readily practiced without these specific details and also by their modifications.

In the following description, the database state data is a view starting with "V $" and will be used immediately to mean data corresponding to various various information about the operation of the database. In addition, in the following description, the codeability table information refers to setting information that varies according to the administrator's setting, and is a view starting with "CT $", and means information consisting of information previously defined in the data provided by the database. Will be used.

On the other hand, the database monitoring apparatus and method of the present invention, even if the operating environment, that is, have a different operating environment for the task by setting the database monitoring configuration information in consideration of this, the state data of the database corresponding to the environment setting information We propose a technical configuration to collect the data. In addition, a technical configuration for collecting raw information constituting the state information of the database is proposed. In addition, we propose a technical configuration that allows you to query the state data of the collected database.

Hereinafter, exemplary embodiments of the present invention will be described in detail with reference to the accompanying drawings.

1 is a block diagram showing the configuration of a database monitoring apparatus according to an embodiment of the present invention.

Referring to FIG. 1, first, a database monitoring apparatus includes a database 10 in which a large amount of data and data state data are selectively constructed, users 20a to 20n using the database 10, and the database ( 10), the database management system 100 and the database management system 100 to control to monitor the database by selectively collecting and analyzing the corresponding data in the database 10 in response to the set monitoring environment information ), A manager 40 for operation management, and a log file storage unit 30 in which execution information of the database management system 100 is recorded.

The database 10 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 20a to 20n are people, applications, clients, or user terminals using the database 10. The users 20a to 20n perform operations such as defining, registering, and inserting data into the database 10 or searching, updating, deleting, changing, referencing, applying, and manipulating data of the database 10. do.

The manager 40 is an individual or a group responsible for maintaining the database 10 and the database management system 100 in the best condition. In addition, the manager 40 sets monitoring environment setting information for monitoring the database 10.

The database management system 100 is a system that maintains, maintains, and manages the database 10 and helps the users 20a to 20n use them. More specifically, the database management system 100 is a software system configured to configure and apply the database 10. In particular, it includes a function that allows a user or an application to easily use the database 10. The functions can be broadly divided into configuration functions, operation functions, and control functions. The configuration function defines the structure of the data to be stored in the database 10 and the manner in which the application program uses it, and includes the definition of the record structure, the definition of the data model, the definition of the physical structure, and the like. In addition, the operation function is a function to search, update, insert, delete the data stored in the database according to the user's request. In addition, the control function is a function to protect the data by keeping the contents of the database 10 consistent and not duplicated, and restricts unauthorized access. In addition, the database management system 100 may selectively update the database state data to the database 10 or the database management system in response to the monitoring environment setting information set by the manager 40 for database monitoring according to an embodiment of the present invention. It is stored in the internal memory of 100) and managed. At this time, the selectively collected state data to be stored in a table. For example, when the database 10 is an Oracle database, a view starting with "V $" is state data. In addition, the database management system 100 allows the collected data to be directly inquired and analyzed in various forms.

An internal configuration of the database management system 100 will be described in detail with reference to the accompanying drawings.

FIG. 2 is a diagram illustrating an internal configuration of the database management system 100 of FIG. 1.

Referring to FIG. 2, the database management system 100 may include an environment setting unit 101 configured to set monitoring environment setting information according to setting information for monitoring the database 10 input from the manager 40. Data collection unit 102 for selectively collecting data corresponding to the environment setting unit 101, when the manager 40 requests data analysis, the data collected by the data collection unit 102 is analyzed A data analysis unit 103, a user interface 104 for receiving setting information from the manager 40, a data input / output unit 105 for inputting and outputting data relating to a database, and a memory 106 for storing a program for database monitoring. And a control unit 107 for controlling the overall operation for monitoring the database.

The environment setting unit 101 receives the setting information by the user interface 104 so that the monitoring environment setting information can be set according to the setting information. Thus, it is possible to set the collection target item, the execution interval, and the detailed property of the collection item. Detailed description thereof will be described later.

The data collection unit 102 selectively collects data corresponding to the environment setting information of the environment setting unit 101 from the database 10. The data collected by the data collector 102 selectively collects instance level statistics and information on waiting events. In this case, the collected data may be stored in a table.

The data analyzer 103 compares and analyzes the trends of the data collected by the data collector 102.

The user interface 104 interfaces so that the setting information of the manager 40 or a request signal of the users 20a-20n can be input to the database management system 100.

The data input / output unit 105 receives data regarding the database 10, and outputs corresponding output data of the data collector 102 and the data analyzer 103.

The memory 106 stores programs for database monitoring described above. In addition, the memory 106 may store database state data, etc. selectively collected by the data collector 102.

Next, the screen data for setting the monitoring environment setting information will be described with reference to FIG. 3.

In the screen data of FIG. 3, the manager 40 sets a collection target item (eg, all selections) and an execution interval (eg, once a day) in a region 'a'. That is, the statistical information and the waiting event information provided by the database 10 are selectively selected. At the same time, the manager 40 sets up the codeability table information (or master information) together. The codeability table information is, for example, a view starting with "CT $" and refers to setting information that varies according to a setting of an administrator. Here, when the database management system 100 has a heavy load, the manager 40 may set a cycle for collecting statistical information for each segment as once a day or once a week. In addition, the manager 40 sets the collection item detailed attribute in the region 'b' of the screen data of FIG. 3. That is, in session collection, it is selected whether to collect a background, a parallel slave, or an active. For example, in the case of a Data Warehouse (DW) system, it is appropriate to select a parallel slave, and in the case of an Online Transaction Processing (OLTP) system, it is generally preferable to exclude the parallel slave. Here, the OLTP refers to a method in which a plurality of users on a network process unit operations such as updating or inquiring data in a database in real time.

Through the above-described process, since the database state data is selectively tabled and stored in the database 10 and managed, the collected data can be directly inquired and analyzed in various forms.

For the detailed description of the database monitoring of the present invention will be described with respect to the process of selectively obtaining the state data through the accompanying FIG.

4 is an exemplary diagram for describing a monitoring tool of a database management system according to an exemplary embodiment of the present invention.

Referring to FIG. 4, first, the manager 40 sets a monitoring environment for monitoring on a screen as shown in FIG. 4A. The detailed description of such an environment setting has already been described and thus will be omitted.

Thereafter, the database management system 100 is provided with real-time state data corresponding to the monitoring configuration information set on the screen data of (a) in the database 10.

Then, the database management system 100 corresponds to the state data corresponding to the monitoring configuration information set by the manager 40 among the information starting with V $ provided in real time from the database 10 as shown in FIG. Get raw data for. That is, for this purpose, the database management system 100 collects a delta value for obtaining raw information from the database 10 by performing a TPA_RUN package. As a result, the database management system 100 may obtain status information starting with "GT $".

Thus, FIG. 4D of the monitoring result screen data by FIG. 4C showing the delta values collected through the above-described FIG. 4B and the codeability table information set by the administrator. ) Is displayed.

5 is an exemplary view showing screen data of an individual session according to an exemplary embodiment of the present invention.

Referring to FIG. 5, detailed information about individual sessions of the database management system 100 is collected, and statistics and waiting event information of the individual sessions can be viewed.

Among the screen data of the individual session, reference numeral 'a' is an area for providing detailed information for each session level, reference numeral 'b' is an area for providing statistics and waiting event information of each session, and reference numeral 'c' Is the area that provides the SQL information running in the session.

Herein, the detailed information for each session level means sort information, session information, undo information, transaction information, access object name, slave process information for the PQ master, and the like. In addition, the statistics and waiting event information of the individual session means the statistical information, the waiting event information of the collection period unit time of each session, through which the history of the statistics of the specific process, waiting event information can be analyzed. In addition, SQL information executed in the individual session means SQL executed in the corresponding session.

Next, a method for monitoring a database by a database monitoring device that performs such an operation will be described in detail.

6 is a flowchart illustrating a database monitoring method according to an embodiment of the present invention, and FIG. 7 is a flowchart illustrating an environment setting process for database monitoring according to an embodiment of the present invention.

6 and 7, in step 601, the database management system 100 receives monitoring environment setting information for collecting database state data from the manager 40. Here, the environment setting information is set by the following process.

First, in step 701, the database management system 100 receives a segment user and database link setting information for data collection when environment setting is executed.

In step 703, the database management system 100 receives basic environment setting information for database monitoring from the manager 40.

At this time, the database management system 100 receives the following basic configuration information. In the following, the setting information input as the environment setting information may be set irrespective of the input order.

It receives data collection function to collect statistics on instance level and selectively collect information on waiting events. At this time, the manager 40 may set a cycle for collecting statistical information for each segment. In addition, it can be set to save SQL log information together. In addition, it is also possible to set up the codeability table information to serve the source information in combination with the source information collected by the delta value of the state information of the database 10. In addition, session collection information can be set in session collection. That is, it can collect the desired session among the background session, parallel slave session, and active session. In this case, at least one session to be collected can be selected. In addition, when excessive load is applied to the database management system 100, the period for collecting the statistical information for each segment may be set by the manager 40 as once a day or once a week.

After the monitoring environment setting information is set, in step 603, the database management system 100 selectively collects the state data provided from the database 10 in real time and the corresponding raw data corresponding to the environment setting information. Proceed to Accordingly, the database management system 100 collects a view starting with "V $" in real time, and then performs a specific package (TPA_RUN package) process to service the corresponding raw information to the administrator. Collect raw information according to Here, the source information is collected in combination with the codeability table information set by the monitoring configuration information. At this time, the database management system 100 collects and stores the original information at regular intervals according to the environment setting information. Here, the state data corresponding to the raw information collected according to the environment setting information may be tabled and stored in the database 10 or the data collection unit 102 inside the database management system 100.

Thereafter, in step 605, the database management system 100 manages the state data of the database collected in step 603 by table according to the monitoring configuration information. In this way, detailed information about individual sessions is collected, which is then the basis for providing statistics and waiting event information for individual sessions to the administrator.

Thus, in step 607, the database management system 100 determines whether the manager 40 requests the collected state data inquiry.

In response to requesting the state data inquiry, the database management system 100 services the collected state data to the manager 40 in step 609. As a result, the manager 40 is provided with statistics and waiting event information for individual sessions. In addition, detailed information of the individual sessions and SQLs performed in the individual sessions can be provided. Here, the individual session details are sort level sort information, undo information, transaction information, access object name, slave process information for PQ master, and the like. . The statistics of the individual sessions and the wait event information enables the historical analysis of the statistics and wait events of a specific process.

However, if it is determined that the state data inquiry is not requested, in step 611, the database management system 100 determines whether to analyze the collected state data.

If the determination result requires the analysis of the state data, in step 613, the database management system 100 analyzes the state data collected in various forms and provides the analysis result to the manager 40.

Accordingly, the apparatus and method for monitoring the database of the present invention are constantly changing in real time from the database by commercializing desired information despite different operating environments such as a database operated in OLTP and a database operated in a data warehouse (DW). It collects and manages state data separately according to setting cycles / conditions / information so that it can be analyzed and improved later.

Meanwhile, in the detailed description of the present invention, specific embodiments have been described, but various modifications are possible without departing from the scope of the present invention. Therefore, the scope of the present invention should not be limited to the described embodiments, but should be determined not only by the scope of the following claims, but also by the equivalents of the claims.

As described in detail above, the present invention has an effect of collecting and monitoring only state data of a database required for commercialization of a database management system provided in an information processing system.

In addition, by taking into account the operating environment of the database management system to selectively collect the state data of the database, it is effective to operate the database management system efficiently.

In addition, by taking into account the load of the database management system to collect the state data of the database at a desired interval, it is effective to operate the database management system efficiently.

In addition, by selectively comparing and analyzing the corresponding database state data collected selectively, the administrator can check the state of the database to manage the database normally.

In addition, there is an effect that can service specific information related to the raw information constituting the state information of the database provided on the screen.

Claims (20)

A database monitoring apparatus for managing a database by using a database management system and monitoring the state of the database, A database in which a large amount of data and data state data are stored and managed; A session for selecting data collection items for selectively collecting instance level statistics and waiting event information input from an administrator, and at least one session for selectively collecting session level statistics and waiting event information. Environment setting means for setting monitoring environment setting information including selection information and a data collection period; And And collecting means for selectively collecting only raw data corresponding to the configuration information from state data provided in real time from the database. The method of claim 1, And a service means for receiving an inquiry request for the raw data from an administrator and providing the raw data to the administrator. The method of claim 1, And analyzing means for receiving an analysis request for the raw data from an administrator and comparing, analyzing, and providing a trend for the raw data. The method of claim 2 or 3, wherein the session selection information, And at least one session information selected from a background session, a parallel slave session, and an active session. The method of claim 4, wherein the raw data is Instance level statistics and wait event information; And And session level detail information, session level statistical information, session level waiting event information, and session level SQL information. The method of claim 5, wherein The session level detailed information may include session level sort information, undo information, transaction information, an access object name, and slave process information for a PQ master. A method of monitoring the status of a database that stores and manages large amounts of data and status data. Receiving a segment user and database link setting information for collecting data from an administrator; Session selection information and data collection for selecting data collection items for selectively collecting instance-level statistics information and waiting event information, and selecting at least one session for selectively collecting session-level statistics information and waiting event information. An environment setting step of setting monitoring environment setting information including a cycle; A data collection step of selectively collecting raw data corresponding to the environment setting information from state data provided in real time from the database; And And a data service step of providing the collected raw data to an administrator. The method of claim 7, wherein the data service step, again Accepting a request for inquiry of the raw data from the manager; And And providing the collected raw data to an administrator according to the inquiry request. The method of claim 7, wherein the data service step, again Receiving a request for analyzing the raw data from the manager; And And comparing and analyzing trends of the collected raw data according to the analysis request, and providing the administrator with the result data. The method of claim 8 or 9, wherein the environment setting step is And setting the codeability table information variable according to the setting of the administrator as one of the monitoring configuration information. delete delete delete delete delete delete delete delete delete delete

Images