KR20040027270A - Method for monitoring database system - Google Patents

Abstract

The present invention is a database system monitoring method, which performs a formal and atypical analysis of an Oracle-based database system to diagnose the performance of a database system, derives and analyzes the factors of database load, and improves a database. A database system monitoring method for maintaining a system. The database system monitoring method according to the present invention includes: a) a check box and item for selecting whether or not to perform a formal analysis for each diagnostic item for checking the state of the database system after checking the usage environment of the Oracle-based database system; An item display window including a sub-field, a run and stop icon that can execute the formal analysis or stop the executed formal analysis, a performance status window that displays the performance status of the formal analysis by item, an item Graphical user interface including performance objectives and detailed explanation display window displaying the purpose and detailed description of each analysis, analysis result display window displaying recommended value and analysis result, and tuning guide display window providing information about tuning guide After providing the database, the database Checking the status of the Oracle-based database system by performing a formal analysis comprising collecting and storing information about diagnostic items for checking the status of the system; b) checking the status of the resultant system of step a). If a load is detected, changing the parameters of the database system for the item in which the load occurred; and c) instance monitoring for monitoring the statistics of Oracle instances in real-time graphs, session monitoring for monitoring status per session, and SQL per session. After analyzing the cause of the load through the SQL monitoring, and performing the atypical analysis consisting of the application tuning and managing the object to maintain the optimization of the database system. The above method can be helped to stabilize the system easily by scripts and tuning guides that have been tested and verified in the field, even by the non-experts of the database by formal analysis. Diagnose the state of the system and trends of change. In addition, problem analysis and tracking can be done quickly and easily through an organic user interface that performs related tools according to the monitoring and tuning procedures of atypical analysis. Therefore, the database system monitoring method according to the present invention is an efficient method for monitoring the database system, and is an online inspection service for deriving and analyzing the factors of the database load and improving the problems. You can check the performance.

Description

How to monitor your database system {METHOD FOR MONITORING DATABASE SYSTEM}

The present invention relates to an efficient database system monitoring method. More specifically, the present invention diagnoses the performance of the database system by performing the atypical analysis and formal analysis of the Oracle-based database system, derives and analyzes the factors of the database load, and improves the database system optimized It relates to a database system monitoring method for maintaining the system.

Today, the performance management of a company's computer systems is becoming an important factor in determining the success or failure of a business beyond the customer service. However, even if the system is built by investing huge amount of money and time, even if it works well without any problems in the beginning, the demand increases and the number of users and the amount of data to be processed increases over time. Complaints increase the work done by the database administrator ("DBA"). There are a number of factors that affect performance on systems based on Oracle databases. The architecture of the entire system, the performance of the hardware itself, the RAID level of the disk, the configuration of various environment variables of the operating system, the configuration of various parameters of Oracle, the object type and data storage method, the file distribution method, and the index creation , How to write SQL (structured query language, aka structured query language), how to use program logic, data modeling, job scheduling, etc. Since a system operates in such a way that these various elements are intricately interlocked with each other, it is not easy to manage to ensure that all elements are properly balanced without biasing to either side. There is no definite answer to system performance management, and the database administrator or system administrator must constantly monitor, tune and tune the system to keep up with the ever-changing situation of the system he is managing.

Performance management targets for Oracle-based database systems generally fall into three broad areas:

1) Applications: business rules, modeling, program logic / SQL, etc.

2) Database Management Solution (DBMS): Memory and resource allocation, storage configuration, data partitioning, file I / O distribution, etc.

3) Operating system (OS): CPU, memory, disk I / O, network contention elimination, etc.

When performing performance tuning from the build stage of the system, it is a good idea to go from the upper layer to the lower layer, and it will be much more advantageous in terms of efficiency and efficiency of performance management. do. Because the OS provides information on the most important resources such as CPU, memory, and disk I / O, it is possible to find out whether there is a lack of contention or contention through the OS level monitoring.

The main checkpoints for memory are paging and swapping. In general, swapping should never occur and paging should rarely occur. There are two things you can do if memory contention exists. You can either add memory in hardware or reduce the memory used by Oracle. Reducing the memory used by Oracle means reducing the size of the System Global Area (SGA) or adjusting the "sort_area_size", "hash_area_size", etc., to reduce the memory area allocated by each Oracle process.

In the case of disk I / O, it checks the disk's% busy or disk queue length. In general, if% busy is over 50, the disk bottleneck is considered. If the queue is long but the disk is not busy, This is a problem with the controller, not the disk. When it is determined that a bottleneck exists on a particular disk, the problem is usually solved by distributing I / O-intensive files on the database side. Especially in an Oracle system, write and archive content are easily caused by write contention. Be careful with the distribution of files, rollbacks, and temporal table spaces.

Memory or disk I / O problems are not usually the cause of sudden performance deterioration unless the system is built early, and the main problem is the lack of CPU resources due to the increase of data volume or the increase of users. Thus, in general, the main concern for OS monitoring is CPU utilization.

If the CPU utilization is low when the system's performance is very slow, it means that there is a bottleneck that causes standby somewhere in the system, and if the percentage of% wio occupies most of the time, it means that the disk I / O is a bottleneck. . In the case of Oracle system, it may be caused by I / O problem or contention on internal resources such as lock or latch. In this case, the "V $ SESSION_WAIT" view is used to generate the database. Wait Event should be inquired and cause should be analyzed and action taken according to the type of waiting event.

If the CPU usage is very high when monitoring from the OS side when the system performance is very slow, first check the ratio of "% sys" and "% usr". If% sys is high (usually over 15%), you need to find out what causes the OS overhead. If% usr is high and some processes occupy most of the CPU, you should check and take action on what the process is doing. In the case of an Oracle process, the process identity number (PID) of the process should be used to query the V $ PROCESS (SPID column) and the V $ SESSION views to find the corresponding database session to check the current work being performed and take appropriate action. In an Oracle system, processes that consume excessive CPU are often caused by inefficient SQL. For example, if the execution plan is so bad that SQL is executed that accesses a large number of blocks unnecessarily, this will cause a large amount of memory operations (Logical IO), which will put a heavy load on the CPU. The sorting and parsing tasks performed during SQL processing are also typical CPU tasks, so any application that unnecessarily causes excessive sorting or repetitive analysis can be the main culprit of system performance degradation.

When there is a performance problem with a system, it is generally difficult to troubleshoot the problem, and there are no malicious processes that can be pointed out as the main culprit. The performance of the system is generally slow or somehow a bottleneck exists. This is when the cause is not visible. When monitoring on the OS side, users will often experience a situation where there is little idle when the CPU utilization is not occupied by a specific process while compromising performance. The bottleneck caused by the misallocation and allocation of resources may be the root cause, or an unnecessary consumption of resources in an application may be the root cause, and perhaps an application is consuming as much resources as it needs, but the entire system resource covers it. If you have the symptoms caused by the lack of checks, but the symptoms are not clear, where should I solve the problem. It may be a species problem here, or it may be a problem caused by a complex entanglement of all problems. Controversy arises between database administrators and developers in terms of database management solutions. Database tuning first or application tuning first. Depending on the situation, you may prefer to perform database tuning first to get more effect, but if you want to prioritize it, it is often more effective and efficient to check and tune your application first.

The first is that database tuning can only be done manually depending on the amount of work and type of work required from the outside. You alone cannot have a complete database on your own. However, there are only databases that do their best to achieve the maximum efficiency in a given situation according to the work contents and workload required by users through SQL. However, if there is a fundamental problem with the database itself, then the effort to tune the database is trivial.

Second, the bottlenecks perceived in the database are often magnified by higher-level applications, so it is often difficult to conclude by itself what to do with database management solutions. For example, the Buffer Cache Hit Ratio by itself is no basis for judgment. Even if the hit rate is low, it is not a problem if it is due to a nightly batch job, and the reason for the high hit rate may be that the same block is repeatedly accessed due to inefficient SQL using correlated subqueries. After all, various statistical indicators of the database management solution reveal the symptoms externally, but treatment is not about the symptoms but about the cause of the disease. Therefore, the root cause must be identified first. It can not be.

Last but not least, SQL tuning is relatively easy compared to database tuning. Database tuning is actually a very repetitive and exhausting task. If you try to tune the database, you will first have to stop the service to apply the new parameter values, and there is no guarantee that it will end at once. Since the source of the problem is not obvious, monitoring should be repeated after applying a specific parameter value and monitoring after adjustment. In addition, there are many tuning items that affect the performance of the system, and it is not easy to consider them one by one, and each item is influenced by each other. Since the case is trivial, it is not as easy as balancing the dice to keep it optimal.

In the past, however, most of the tasks related to database system performance diagnostics were handled manually using administrator-written or collected SQL scripts. Therefore, it is difficult to stabilize the system and train experts about different systems by using different analysis methods and using different tuning methods because standardized information collection and guidance are not provided, and a user interface that provides various detailed information required for the system quickly and easily. Was lacking. For this reason, an efficient and systematic database system performance diagnosis method and a user-friendly interface that can be checked periodically are needed.

Accordingly, it is an object of the present invention to provide a method for performing performance diagnosis on a database system in real time, thereby reducing the effort of a developer or a database administrator, and identifying and correcting a problem more quickly and easily.

Another object of the present invention is to provide a computer-readable recording medium storing a program that can perform performance diagnosis of a database system in real time, thereby reducing the effort of a developer or a database administrator, and identifying and correcting a problem more quickly and easily.

1 shows a flowchart of a formal analysis according to a preferred embodiment according to the present invention.

2 shows a preferred implementation of a graphical user interface (GUI) provided to a user for performing the formal analysis.

3 is a block diagram summarizing the flow chart of the amorphous stone analysis and the necessary functions for each step.

4 is a graphical user interface showing an example of the use of an instance monitor.

FIG. 5 shows a statistical view required to find basic information of a corresponding database session, work status, and related SQL information using OS PID information.

Figure 6 is a user interface for performing session monitoring after finding the PID of the top process using the "Top" command.

7 is a screen transfer of SQL from the session monitor to the plan tool.

FIG. 8 is a graphical user interface for selecting a statistical item to be queried from options of a session monitor and sorting by corresponding column to retrieve session information having the largest logical reads.

9 is a graphical user interface for tracing a particular session in a session monitor and then importing a trace file through a trace tool, formatting the elapsed time in order from a large SQL, and displaying it with various statistical information.

10 is a graphical user interface for checking the locking and blocking status of a session using a lock / latch monitor.

11 is a user interface showing an example of extracting the top SQL through the SQL monitor.

12 is a flowchart showing an algorithm for automatically determining an analysis method according to the present invention.

FIG. 13 is a user interface illustrating that statistics information on segment objects such as tables, indexes, and clusters can be easily managed through a menu screen in a cost-based optimizer operating environment.

The database system monitoring method according to the present invention for achieving the above object is to perform the formal and atypical analysis of the Oracle-based database system to diagnose the performance of the database system, to derive the factors of the database load and to analyze the problems The present invention relates to a database system monitoring method for improving and maintaining an optimized database system.

In this specification, "formal analysis" refers to an analysis for diagnosing the performance of an Oracle-based database system, and "atypical analysis" refers to a case where a load is detected in the state of the system as a result of the formal analysis. Analyze and analyze to solve a problem.

The database system monitoring method according to the present invention includes the following steps:

a) an item display window including a check box for selecting whether or not to perform a formal analysis for each diagnostic item for checking the state of the database system after checking the usage environment of the Oracle-based database system, Displays the execution and stop icons that can execute the formal analysis or stop the executed formal analysis, the execution status window that displays the execution status of the formal analysis item by item, the purpose of performing the analysis by item, and a detailed description. According to the user's execution command, after providing the graphical user interface including the execution purpose and detailed explanation display window, the analysis result display window displaying the recommended value and the analysis result value, and the tuning guide display window providing information on the tuning guide. Information about diagnostic items for checking the state of the database system Steps to the house and perform a formal analysis consists of storing them to check the status of Oracle-based database system,

b) if the load of the system is detected as a result of step a), changing the parameters of the database system for the item in which the load occurred; and

c) Analyze the cause of the load through instance monitoring that monitors statistics of Oracle instances in real time graphs, session monitoring that monitors the status of each session, and SQL monitoring that monitors the SQL of each session, and then perform application tuning and perform object tuning. Performing atypical analysis consisting of managing steps to maintain optimization of the database system.

Hereinafter, with reference to the accompanying drawings to describe the present invention in more detail.

1 shows a flowchart of a formal analysis according to a preferred embodiment according to the present invention.

As shown in FIG. 1, the formal analysis includes an information collecting step S101, a problem identification step S102, a guide and an advice step S103, and a result storing step S104. When collecting information, the user environment (eg language and version) is first identified to actively cope with the use environment. Specifically, by checking the language used, an interface corresponding to the user language can be provided, and by checking the version used, the user version can effectively cope with the change in the version used. The diagnostic information for checking the status of the database includes basic information, shared global area ("SGA"), standby events, I / O, space, access type, and Oracle Multi-Thread Server. "MTS"), Oracle parallel server ("OPS"), including eight categories, including the sub-category. The formal analysis performs information collection on the diagnostic information in real time. If there is an item that has a load as a result of the collection, it is provided to the user in a form that can be identified by the user (eg, a mark or a color), and the user can easily check detailed information on the item having the load. After that, the purpose of the diagnosis and the detailed description of the item-specific diagnosis are presented along with the recommended value and guidance on tuning is provided. Formal analysis items are defined as follows.

-Define formal analysis items-

@ SQL Definition

@Format:

@ {

@ {groupid},-> integer format (start with 0)

@ {groupname},-> string format

@ {itemid},-> integer format (start with 0)

@ {itemname},-> string format

@ {itemSQL_7},-> string format (SQL for version 7)

@ {itemSQL_8},-> string format (SQL for Version 8)

@ {itemSQL_8i},-> string format (SQL for version 8i)

@ {itemSQL_9i},-> string format (SQL for version 9)

@ {itemNote},-> string format (Analytics purpose: English)

@ {itemNote_KOR},-> string format (Analysis Item Purpose: Korean)

@ {itemDescription},-> string format

@ {itemDescription_KOR},-> string format

@ {itemGuide},-> string format (Tuning Instructions)

@ {itemGuide_EN},-> string format (Tuning Guide: Korean)

@ {itemType},-> integer format (0: No Ref, 1: <, 2:>)

@ {itemInitRef},-> real format

@ {itemUnit}-> string format

@}

Table 1 below summarizes the details of the formal analysis items.

Table 1 continued

Table 1 continued

Table 1 continued

Table 1 continued

_

Table 1 continued

As shown in Table 1, the formal analysis items for checking the state of the database system include eight categories including basic information, SGA, wait event, I / O, space, access type, MTS, and OPS and 45 It is divided into three details.

2 shows a preferred implementation of a graphical user interface (GUI) provided to a user for performing the formal analysis. The user interface is written in HTML (HyperText Markup Language) format. As can be seen in FIG. 1, the user interface includes a check box 211 for selecting whether or not to perform a formal analysis for each diagnostic item for checking a state of a database system and a load display field 212 for each item. Item display window 201 including a, execution and stop icon 202 that can execute a formal analysis or stop the executed formal analysis, performance status display window for displaying the performance status of the formal analysis by item (203), the performance purpose and detailed explanation display window (204) for displaying the purpose and detailed description of the analysis of each item, the analysis result display window (205) for displaying the recommended value and the analysis result, information for tuning guide A tuning guide display window 206, and a summary summary window 207 summarizing the analysis purpose and recommended values. By providing a check box 211 for selecting whether or not to perform a formal analysis for each item, it is possible to perform a formal analysis only on an item in which a load often occurs without performing a formal analysis on all items. In addition, by providing a load availability display field 212 that can display the load for each item, the user can easily identify the item in which the load has occurred. In addition, when the user wants to confirm the results of the formal analysis in more detail, the user may obtain detailed information on the above analysis result by clicking each item. For example, if the user wants to check the result of a more detailed analysis of the "buffer cache hit ratio", the buffer cache hit ratio is performed by clicking the "buffer cache hit ratio" item in the item display window 201 on the left. Status, purpose of execution, detailed explanation, recommended value and result value can be easily checked in one window. In the above screen, the dictionary cache hit ratio indicates that an item having a load is generated as a result of performing a formal analysis by displaying a loading section display field 202 for each item. Therefore, after clicking on the above item, the result of the atypical analysis is checked, and then the parameter is changed according to the tuning guide. The display of the load may be expressed in various ways, for example, it may be displayed in red so as to be clearly recognized by the user.

The formal analysis is a useful tool that can be used to easily determine the system status during the diagnosis of a database system, and a database administrator can use it as a tool for checking, recording, and reporting the system status on a regular basis.

3 is a block diagram summarizing the flow chart 310 of the amorphous stone analysis and the necessary functions 320 for each step. Atypical analysis according to the present invention analyzes the problem via instance monitoring 301, session monitoring 302, SQL monitoring 304 (304), then performs application tuning (305) and manages objects (306). This analysis allows the database system to remain in an optimized state.

Hereinafter, the steps will be described in more detail.

Instance Monitoring (or Instance & OS Monitor) 301 includes nine predefined categories for Oracle instances, buffer cache hit ratio, library / dictionary hit ratio, and correlated sessions. connected sessions, active transactions, shared pool size, I / O blocks per second, redo size / writes per second, analytics per second This tool allows you to monitor the statistics of parsing / execution count per second and wait event occurrence per second in real time graph.You can set the refresh interval and You can also zoom in on the graph. The above nine categories are the most important and frequently inquired statistical information among the information provided by the instance monitor, and are useful for intuitively determining and acting on the trend of the system situation. For example, if the instance monitor's IO block graph increases from a certain time and continues to show high numbers, open the session monitor to see the most IO sessions in the current session and find out the SQL and other related information. Can be. 4 is a graphical user interface showing an example of the use of an instance monitor.

If you find that some Oracle processes occupy most of the CPU while performing instance monitoring using the Unix "Top" or "Ps" commands, check what state and what they are doing. I need to see. In this case, we use V $ PROCESS and V $ SESSION among Oracle's views, and the PID of the process on the OS corresponds to the SPID column of V $ PROCESS. FIG. 5 shows a statistical view required to find basic information of a corresponding database session, work status, and related SQL information using OS PID information.

Figure 6 is a user interface for performing session monitoring after finding the PID of the top process using the "Top" command. For example, if you use the "Top" command to determine the PID of the top process and find that the Oracle process with the current PID of 157596 is high in CPU consumption, run session monitoring, sort by SPID column, and find the session with SPID 157596. do. Referring to FIG. 6, the Oracle process with the current PID of 157596 is a session with the SID of 565 on the current database, and it is confirmed that the user is executing the "SELECT" statement by connecting as the SYS user using Orange.exe. The syntax of the SQL statement is shown at the bottom. If you want to see which resource this session is waiting for, or if you want to see detailed information and various statistics of this session, just double click on the session. The small screen shows the details of a specific session. This information is internally queried for views such as V $ PROCESS, V $ SESSION, V $ SESSION_WAIT, V $ SESSION_EVENT, V $ SESSTAT, V $ OPEN_CURSORS, V $ SQL. Get it.

You can see that this session is constantly waiting for the 'db file scattered read' event, which is a wait event that occurs when you perform a full table scan. If you want to check the execution plan for the SQL currently running in this session and perform tuning, you can send the SQL statement directly from the session monitor to the Plan Tool. 7 is a screen transfer of SQL from the session monitor to the plan tool. The execution plan is automatically displayed for the transferred SQL, and if the SQL syntax is not readable, it can be easily read. That is, the plan tool sets the real-time trace information of the SQL statement for each level and expresses the hierarchical level to easily analyze the execution plan and establish the optimal execution plan while immediately checking related object information.

The biggest advantage of the plan tool according to Fig. 7 is that the trace can be performed and formatted in real time. With a single button, trace the session, execute SQL, and then import the trace file from the server to format "TKPROF". Format to complete. In addition, you can select from 1, 4, 8, or 12 trace levels, and test easily by changing the array size, execution schema, and optimizer mode.

Lastly, one of the important functions of the plan tool is to separate the original SQL and the tuning SQL into separate tabs so that you can work by comparing execution plans, schema information, and trace results for each, and save all the tuning results. It is very useful in practice because it can be recalled later to retrieve all information including execution plan, statistical information and object status at the time of work.

Instance monitoring can find sessions that use a lot of system resources, but you can also find sessions that use a lot of resources directly from database session statistics. At this time, the view to be used is V $ SESSTAT and you can check session-specific information on more than 200 statistics by joining with V $ STATNAME. The most frequently used items for "Top" session extraction are 'session logical reads', 'physical reads', 'CPU used by this session', and 'parse count'. You can get information.

FIG. 8 is a graphical user interface for selecting a statistical item to be queried from options of a session monitor and sorting by corresponding column to retrieve session information having the largest logical reads.

If the session information is checked through the session monitor and it is determined that the session is problematic, the session can be directly removed by clicking the "Kill" session menu and traced to levels 1, 4, 8, and 12 for a specific session. . In particular, in the case of a system that accesses a database through a middleware such as a tuxedo, it is not meaningful to determine whether the resource is excessively consumed in a unit of session since a session for the tuxedo server and various services are performed in the same session. It is often efficient to trace malicious sessions and then extract malicious SQL.

9 is a graphical user interface for tracing a specific session in a session monitor, and then importing a trace file through a trace tool, formatting the elapsed time in order from a large SQL, and displaying it with various statistical information. Since the above trace tool cannot interpret the trace file existing on another computer with "TKPROF", the trace file on the local or other server can be retrieved via FTP and interpreted. By providing analysis information and execution plan in real time at once, it shows excellent performance in diagnosing faulty access paths, large disk I / Os, and resource (CPU, memory) usage. Also, by providing more detailed information (client time, bind variables, and wait events) than "TKPROF", you can pinpoint the problem in the session by viewing the raw trace file and linking with the plan tool.

Occasionally, when a system exhibits sudden performance degradation, other applications that access the same data without waiting or committing for long periods of time after the application has been structurally miswritten can cause transactions to wait forever. . In this case, find the session holding the lock and the waiting session quickly, check whether the situation is caused by the structural problem of the program, and remove the blocking session.

10 is a graphical user interface for checking the locking and blocking status of a session using a lock / latch monitor. Through the lock tree, a session with SID 11 is currently waiting for a request for an update on the same row with session SID 9 without committing after updating a row in the SCOTT.EMP table. You can check it. You can check and kill or trace the session details for the blocking session, and send it to the currently executing SQL plan tool for tuning.

An example of extracting Top SQL through the SQL monitor is shown in FIG. 11. The user interface illustrated in FIG. 11 is an example of extracting a high resource consumption SQL. SQL with a large number of access blocks is the first check target, and the criteria are set to extract about 50 SQLs from the test system in combination with the condition of the number of access blocks per unit execution. Proceed with tuning.

Similarly, SQL that does not have a one-time execution, large number of full scans, and literal SQL that are not shared once with many access blocks per unit execution is not shared.

12 is a flowchart illustrating an algorithm for automatically determining an analysis method. It is determined whether a segment criterion is satisfied, and when it is satisfied, it is determined whether an analysis range is satisfied. If the segment criterion is not satisfied, the analysis is performed according to the row number criterion and the analysis range accordingly. The analysis method according to the above method can reduce the workload of the database administrator by simplifying the work without having to specify the analysis method for each table as in the existing tools.

By conveniently providing the various options (analysis range, parallel analysis, histogram generation, analysis script generation, providing all statistical information) for determining analysis criteria and performing analysis according to the method illustrated in FIG. 12, As described above, in the cost-based optimizer operating environment, statistical information about segment objects such as tables, indexes, and clusters can be easily managed through a menu screen.

The present invention also relates to a computer readable recording medium having stored thereon a program for effectively performing the method.

a) an item display window including a check box for selecting whether or not to perform a formal analysis for each diagnostic item for checking the state of the database system after checking the usage environment of the Oracle-based database system, Displays the execution and stop icons that can execute the formal analysis or stop the executed formal analysis, the execution status window that displays the execution status of the formal analysis item by item, the purpose of performing the analysis by item, and a detailed description. According to the user's execution command, after providing the graphical user interface including the execution purpose and detailed explanation display window, the analysis result display window displaying the recommended value and the analysis result value, and the tuning guide display window providing information on the tuning guide. Information about diagnostic items for checking the state of the database system Home and perform a formal analysis consists of storing this step to check the status of Oracle-based database systems, and

b) Instance monitoring that monitors statistics of Oracle instance in real time graph, session monitoring that monitors session status, SQL monitoring that monitors SQL per session, analyzes the cause of the load, and then performs application tuning and objects. A step of maintaining an optimization of the database system by performing an atypical analysis consisting of managing the. A computer readable recording medium storing a program for conveniently supporting the above-mentioned formal analysis and atypical analysis enables to easily and quickly increase the efficiency and productivity of development and optimize the performance in a database operating environment.

As described above, according to the efficient database system monitoring method according to the present invention, even a non-expert of the database by formal analysis can be helped to stabilize the system easily by scripts and tuning guides tested and verified in the field. It can generate the periodic tuning guide and save the result to diagnose the status and change of the database system. In addition, problem analysis and tracking can be done quickly and easily through an organic user interface that performs related tools according to the monitoring and tuning procedures of atypical analysis. Therefore, the database system monitoring method according to the present invention is an efficient method for monitoring the database system, and is an online inspection service for deriving and analyzing the factors of the database load and improving the problems. You can check the performance.

Claims (5)

a) an item display window including a check box for selecting whether or not to perform a formal analysis for each diagnostic item for checking the state of the database system after checking the usage environment of the Oracle-based database system, Displays the execution and stop icons that can execute the formal analysis or stop the executed formal analysis, the execution status window that displays the execution status of the formal analysis item by item, the purpose of performing the analysis by item, and a detailed description. According to the user's execution command, after providing the graphical user interface including the execution purpose and detailed explanation display window, the analysis result display window displaying the recommended value and the analysis result value, and the tuning guide display window providing information on the tuning guide. Information about diagnostic items for checking the state of the database system Steps to the house and perform a formal analysis consists of storing them to check the status of Oracle-based database system, b) if the load of the system is detected as a result of step a), changing the parameters of the database system for the item in which the load occurred; and c) Instance monitoring that monitors statistics of Oracle instance in real time graph, session monitoring that monitors session status, SQL monitoring that monitors SQL per session, analyzes the cause of the load, and then performs application tuning and objects. Oracle-based database system monitoring method comprising the step of maintaining an optimization of the database system by performing atypical analysis consisting of managing the. The method of claim 1, wherein the diagnostic item for checking the state of the database system comprises basic information, a shared global area, a standby event, an input / output, a space, an access type, an Oracle multi-thread server. Oracle-based database system monitoring method comprising the eight categories, including the Oracle parallel server (or Oracle parallel server) and its sub-items. The method of claim 1, wherein the instance monitoring comprises: buffer cache hit ratio, library / dictionary hit ratio, correlated sessions, active transaction, shared pool size. (shared pool size), I / O blocks per second, Redo size / writes per second, parse / execution count per second, standbys per second A method for monitoring an Oracle based database system, characterized by nine categories including wait event occurrence per second. The method of claim 1, wherein the analysis is performed by a plan tool which sets the real-time trace information of the SQL statement for each level and expresses the hierarchical level of the SQL statement, and a trace tool linked thereto. How to monitor your database system. a) an item display window including a check box for selecting whether or not to perform a formal analysis for each diagnostic item for checking the state of the database system after checking the usage environment of the Oracle-based database system, Displays the execution and stop icons that can execute the formal analysis or stop the executed formal analysis, the execution status window that displays the execution status of the formal analysis item by item, the purpose of performing the analysis by item, and a detailed description. According to the user's execution command, after providing the graphical user interface including the execution purpose and detailed explanation display window, the analysis result display window displaying the recommended value and the analysis result value, and the tuning guide display window providing information on the tuning guide. Information about diagnostic items for checking the state of the database system Home and perform a formal analysis consists of storing this step to check the status of Oracle-based database systems, and b) Instance monitoring that monitors statistics of Oracle instance in real time graph, session monitoring that monitors session status, SQL monitoring that monitors SQL per session, analyzes the cause of the load, and then performs application tuning and objects. A computer-readable recording medium storing a program implementing the Oracle-based database system monitoring method according to claim 1, comprising the step of maintaining an optimization of the database system by performing an atypical analysis comprising managing the data.