CN103793526A

CN103793526A - Method for monitoring SQL statement performance

Info

Publication number: CN103793526A
Application number: CN201410061517.1A
Authority: CN
Inventors: 初贤哲
Original assignee: Inspur Electronic Information Industry Co Ltd
Current assignee: Inspur Electronic Information Industry Co Ltd
Priority date: 2014-02-24
Filing date: 2014-02-24
Publication date: 2014-05-14

Abstract

The invention provides a method for monitoring SQL statement performance, and belongs to the field of computer system design and the field of databases. The performance of related SQL statements is monitored by reading a data dictionary of oracle and performance data of the operating system-level, and the dependence on a graphic interface tool is reduced. The method solves the problems that a traditional imaging management tool is high in cost and hard to manage, and the processing time of database maintenance workers is shortened.

Description

A kind of method of monitoring SQL statement performance

technical field

The present invention relates to Computer System Design field and database field, be specifically related to the method for a kind of monitor control mechanism query language (Structured Query Language, SQL) SQL statement performance.

Background technology

In the information age of current high speed development, IT system performance issue is the mountain that is difficult to go beyond in face of technician that lies across always.The method of various performance optimizations is constantly explored out.In the various systems based on database, for guaranteeing the search efficiency of statement, IT company has developed many technology one after another, provides support in all its bearings.Aspect the optimization and tuning of SQL, the instrument such as gridcontrol, OEM of Oracle, and other third party's graphical tools are all done relatively goodly.But these graphical tools are under some extreme cases, and for example database does not fill data bank or the charge of third party's instrument, and how monitor database SQL statement performance becomes a problem.

Summary of the invention

The method that this kind monitored SQL statement performance under textual interface be the performance index that obtain SQL statement by data query dictionary monitor SQL statement performance this

The present invention utilizes the performance data of the MMON of oracle and the collection of MMNL process as source data, by the analysis of source data different aspects being drawn to corresponding sql performance data, send early warning mail or note according to the threshold values of definition to related personnel, thereby increase work efficiency.Method for designing of the present invention is after certain measured value (metrics) has exceeded default limit value (threshold value), to have submitted warning to, and create new MMON and be subordinate to process (MMON slave process) and carry out snapshot (snapshot), catch the statistical information of the nearest SQL object of revising, complete and give MMNL process for various tasks relevant with manageability lightweight and that frequency is higher, these 2 processes are written in the table of WRM $ _ * and WRH $ _ * beginning obtaining data, obtain the essential information of corresponding sql-the comprise execution number of times of sql by reading data in these tables, execution time, variate-value, cpu consumes, io consumes, memory consumption and various waiting events, reach the object of monitoring sql statement performance by the monitoring class to these basic indexs.

This project organization comprises: oracle background process module (1), problem process module (2), problem sql module (3), problem sql resource consumption and executive plan module (4), interface module (5);

Oracle background process module, background process module is to be integrated in oracle inside, has oracle automatically to safeguard, is responsible for collecting the related data of the sql statement that client sends, and carrys out timing operation according to related definition;

Problem process module, navigates to problem process by system level, is mainly that the resources such as the use, File Open number of consumption by the monitoring of system resource being comprised to consumption to cpu, io, internal memory, the process number of opening obtain problem process;

Problem sql module, by the id of process module acquisition problem process, then obtains by dictionary view the sql_id that this process is carried out, and can obtain by sql_id the sql statement text that this process is carried out;

Problem sql resource consumption and executive plan module, obtain sql_id by sql module and obtain the executive plan of sql statement, then obtains the reason of the consumption of natural resource of this this sql statement by analyzing this executive plan;

Interface module, by calling of mail or note system interface sent to related maintenance personnel by problem sql, to pinpoint the problems early.

The invention has the beneficial effects as follows: by occurring that the performance data that performance issue period MMON and MMNL process are collected is foundation, first find the process of consumption of natural resource, comprising consuming most cpu, consume internal memory most, consume io most, carry out number of times at most, firmly resolve at most, version too much, consistent read is too much, segment is maximum etc., by finding the id of this process to obtain the sql statement that this process is carried out, then obtain the executive plan of this sql statement by methods such as autotrace, explain, dbms.By the deciphering of sql statement execution plan being obtained to resource that this sql statement mainly consumes and his waiting event, maximum such as finding that the io of this statement consumes, when checking his executive plan, find it is the full table scan at a large table, whether so just should consider increase index in corresponding field, whether need subregion, whether need to set up the overall situation or local index, whether need some historical datas of large table to clear up, this sql statement need to be adjusted etc. and to avoid full table scan, advise the managerial personnel that are prompted to of robotization more by thereby corresponding statement adjustment being made to this class, and because be the inquiry of carrying out respective resources by sql statement, so more easily realize and customizing, can be according to oneself need to be to send corresponding warning going wrong, and the interface of sql statement is the standardized workload that reduces exploitation.

The method of monitoring sql statement under this textual interface has above-mentioned advantage, make it make up the deficiency that cannot monitor sql statement performance without graphical interfaces in the situation that, work efficiency and the work quality of DBA are greatly improved, alleviate the workload of DBA daily servicing, improved DBA to the corresponding speed of problem with the time, and in patterned situation, have very high technological value because customizable makes the method cannot use.

Accompanying drawing explanation

Accompanying drawing 1 is building-block of logic of the present invention.

Embodiment

With reference to the accompanying drawings, content of the present invention, to monitor single sql performance as example, is described to the implementation procedure of this structure.

As described in summary of the invention, logic realization construction design method of the present invention mainly comprises: oracle background process module (1), problem process module (2), problem sql module (3), problem sql resource consumption and executive plan module (4), interface module (5).

A kind of logical organization of the method for monitoring SQL statement performance comprises, the relevant information that oracle background process module is responsible for collecting sql statement, comprise the execution number of times of relevant sql statement at the appointed time, execution time, resolve number of times, sub-vernier number etc., then by these deposit data in corresponding dictionary table, for follow-up monitoring provides the most original data; Problem process module according to process resource consumption (comprise process consume cpu, internal memory, number, language handle number, io open file) situation judge whether this process is problem process, thereby determine whether this process id is sent to sql module, this module judges from system-level, thereby this carries out performance monitoring from 2 aspects and obtains comprehensive, complete performance data, if there are multiple process consume resources to reach threshold line, can sort and obtain resource consumption process list according to the index of being concerned about so; The process that problem sql module provides according to process module is obtained the sql_id of this process for No. id, then obtain according to this sql_id the sql text that this process is carried out, thereby the variate-value that can obtain this sql statement according to data dictionary information judges whether it is because histogram or binding variable are spied out the problem causing, and follows readable result of maintainer to developer; Problem sql resource consumption and executive plan module, the sql_id providing by sql module obtains the executive plan of this sql and obtains the Expenditure Levels of this sql to resource, thereby obtain this sql and which walks consumption of natural resource on earth, and then judge that whether the executive plan of this sql is correct; Interface module, thus sql text, resource consumption and the executive plan this process carried out by mailing system or note system send to related personnel to allow related personnel obtain in time relevant information, improve the corresponding time of processing problem.

Claims

1. monitor a method for SQL statement performance, it is characterized in that this project organization comprises: oracle background process module (1), problem process module (2), problem sql module (3), problem sql resource consumption and executive plan module (4), interface module (5);