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

Abstract

A device and a method for monitoring a database are provided to collect and monitor database status data needed as a usual condition of a DBMS(DataBase Management System) included in an information processing system and collect the status data of the database selectively by considering an OS(Operating System) of the DBMS. A database(10), which is a repository of operation data, is integrally organized and managed to be shared and used by a plurality of users(20a-20n). A DBMS(100) sets monitoring environment setting information for collecting the status data of the database selectively and monitors the database by collecting status data selectively from the database based on the monitoring environment setting information. The monitoring environment setting information comprises setting information for selectively collecting instance level statistics information and wait event information provided from the database. A manager(40) sets up monitoring environment setup information for monitoring the database.

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.

The present invention for achieving the above object, in the database monitoring apparatus for managing a database using a database management system and monitoring the state of the database, integrated and managed for the purpose of being shared and used by multiple users A database of aggregated operational data; And a database management system that selectively collects state data from the database according to the monitoring environment setting information that is set, and monitors the database.

The database management system may further include: an environment setting unit configured to set monitoring environment setting information according to setting information for monitoring a database input from an administrator; A data collecting unit for selectively collecting data corresponding to the environment setting unit; A data analysis unit for analyzing data collected by the data collection unit when the manager requests data analysis; A user interface for receiving monitoring environment setting information from the administrator; A data input / output unit for inputting / outputting data relating to a database; A memory in which a program for monitoring the database is stored; It is configured to include a control unit for controlling the overall operation for the monitoring.

In addition, the database management system is configured to selectively collect state data of the database corresponding to the monitoring environment setting information set to at least one of a specific period, condition and information set by the administrator.

In addition, the database management system collects state data corresponding to the monitoring configuration information among data of the database, and delta information for displaying raw information when the collected state data is raw information. And obtains the database state data to the administrator in combination with the codeability table information set by the environment setting information.

In addition, the present invention for achieving the above object, in the method for managing a database using a database management system and monitoring the state of the database, the state data from the database is selectively selected by the monitoring environment setting information is set The process of collecting; When the collected state data inquiry request, characterized in that it comprises the step of transmitting the collected state data.

The process of selectively collecting the state data may include selectively collecting instance level statistics and wait event information provided by a database according to monitoring configuration information. It is done by

In addition, the process of servicing the collected state data to the administrator, the service to compare the trends of the collected statistical information according to the configuration information according to the analysis request of the administrator; Servicing the pending event information trends; Serve at least one or more of the steps of servicing statistical information and waiting event information from start to end of individual sessions.

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 selectively data state data are 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 managing the operation, and a log file storage unit 30 in which execution information of the database system 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 210. The users 20a to 20n may define, register, or insert data into the database 210, or search, update, delete, change, reference, apply, or manipulate data of the database 210. do.

The manager 40 is an individual or a group responsible for managing 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 state data collected by the wall is 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.

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

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 monitoring environment setting will be described with reference to FIG. 3 as an example for describing screen data when setting the monitoring environment setting information.

In the screen data of FIG. 3, the manager 40 sets a collection target item (for example, all selections) and an execution interval (for example, once a day) in the 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 area '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. 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.

Here, the detailed information for each session level means sort information, undo information, transaction information, access object name, slave process information for the PQ master, etc. for each session level. 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 is 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). $ View information is collected and managed separately according to the setting cycle / condition / information so that it can be used to analyze and improve the data 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 considering 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 ring 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, 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 for selectively collecting state data from the database according to the monitoring environment setting information that is set and monitoring the database. According to claim 1, Monitoring of the database management system is performed in an Oracle database. According to claim 1, The database management system, And a monitoring environment setting information configured to selectively collect database state data. According to claim 1, The monitoring configuration information, A database monitoring apparatus, comprising configuration information for selectively collecting instance level statistics information and wait event information provided by a database. The method of claim 4, wherein The monitoring configuration information, Database monitoring apparatus comprising the configuration information according to the session (session) collection. The method of claim 5, Setting information according to the session collection, And at least one of a background session, a parallel slave session, and an active session desired to be collected, to set session collection information. According to claim 1, The monitoring configuration information, And a codeability table information for collecting specific raw information among state data of the database and serving the administrator. According to claim 1, The database management system, An environment setting unit configured to set monitoring environment setting information according to setting information for monitoring a database input from an administrator; A data collecting unit for selectively collecting data corresponding to the environment setting unit; A data analysis unit for analyzing data collected by the data collection unit when the manager requests data analysis; A user interface for receiving monitoring environment setting information from the administrator; A data input / output unit for inputting / outputting data relating to a database; A memory in which a program for monitoring the database is stored; And a control unit for controlling the overall operation for the monitoring. According to claim 1, The database management system, And collecting state data of the database corresponding to the monitoring configuration information set to at least one of a specific period, condition, and information set by an administrator. According to claim 1, The database management system, Collects state data corresponding to the monitoring configuration information among the data in the database, Acquire delta information for displaying raw information when the collected state data is raw information, and provide database state data to an administrator in combination with codeability table information set by environment setting information. Database monitoring device characterized in that. The method of claim 10, The codeability table information, Means setting information that is variable according to the setting of the administrator, characterized in that the database (view) starting with "CT $". According to claim 1, The database state data, A database monitoring device, characterized in that the view starts with "V $". In a method of managing a database using a database management system and monitoring the state of the database, Selectively collecting state data from a database by setting monitoring environment setting information; And transmitting the collected state data when the collected state data inquiry request is made. The method of claim 13, The method of claim 1, further comprising the step of setting the environment setting information for the database monitoring. The method of claim 14, The process of pre-setting the monitoring environment setting information, Receiving segment user and database link configuration information for data collection; And receiving a data collection function to selectively collect statistics information of the instance level and selectively collect information on the wait event. The method of claim 15, Receiving the data collection function, Setting a statistical information collection period for each segment; Setting to store the SQL log information together; Setting codeability table information for serving the raw information by combining the raw information collected by a delta value of the state information of the database; And at least one of setting session collection information according to session collection as a data collection function. The method of claim 16, Setting session collection information according to the session collection, And selecting at least one of a background session, a parallel slave session, and an active session desired to be collected, to set session collection information. The method of claim 13, The process of selectively collecting the state data, And selectively collecting instance level statistics and wait event information provided by the database according to the monitoring configuration information. The method of claim 13, The process of servicing the collected state data to an administrator, A service for comparing trends of statistical information collected according to the environment setting information according to an analysis request of an administrator; Servicing the pending event information trends; And providing at least one of the steps of servicing statistical information and waiting event information from start to end of individual sessions to the administrator. The method of claim 13, Monitoring of the database management system is performed in an Oracle database.

Images