Oracle database execution plan anomaly detection method

Abstract

An Oracle database execution plan anomaly detection method comprises the following steps of: S1, analyzing and concluding execution plan anomaly data, and constructing a database execution plan healthassessment model; S2, determining each preset threshold parameter in the model, initializing error times, inputting a current business scene and database performance parameters, and calculating a current health value; S3, if the current health value is smaller than or equal to a preset minimum health value, adding 1 to the error times, and if the current health value is larger than the preset minimum health value, determining that the current execution is correct; and S4, when a cumulative value of the error times is greater than a preset threshold, indicating that the execution plan is abnormal, and transmitting alarm information or executing a repair script. According to the Oracle database execution plan anomaly detection method, the system performance after the database execution planis abnormal is concluded and summarized, quantitative analysis indexes are abstracted, and automatic judgment and emergency processing of the system are realized after the Oracle execution plan is abnormal, so that the fault discovery time and the emergency processing time are reduced, and the service continuity is improved.

Description

technical field [0001] The invention belongs to the technical field of database detection, and in particular relates to a method for detecting abnormality of an Oracle database execution plan. Background technique [0002] In large financial companies such as securities, banks, and insurance companies, the databases of many core systems use Oracle databases, and many databases store hundreds of millions of records. In operation and maintenance, because the Oracle database uses the CBO evaluation model when parsing SQL statements, in a high-concurrency system, when the number of records in a single Oracle table exceeds tens of billions, the database often has execution plan exceptions, resulting in The sql response time becomes longer, the session is blocked, and as time accumulates, the scope of influence continues to expand, eventually causing all database resources to be used to process sql with abnormal execution plans, unable to respond to new requests, and affecting bus...

AI Technical Summary

Problems solved by technology

In operation and maintenance, because the Oracle database uses the CBO evaluation model when parsing SQL statements, in a high-concurrency system, when the number of records in a single Oracle table exceeds tens of billions, the database often has execution plan exceptions, resulting in The sql response time becomes longer, the session is blocked, and as time accumulates, the scope of influence continues to expand, eventually causing all database resources to be used to process sql with abnormal execution plans, unable to respond to new requests, and affecting business development

[0003] Given that Oracle execution plan exceptions are difficult to find, the industry currently does not have mature automation tools, and the processing method is still a passive operation and maintenance mode. When an abnormality occurs in the business system, the operation and maintenance personnel manually intervene in the analysis and processing

[0004] However, the manual processing method takes a long time to locate the problem, and the financial system has high timeliness requirements. If the fault lasts for too long, it will cause huge economic losses to the company, and even affect the company's rating and business development. scope

Images