CN111522793A - Method for detecting execution plan exception of Oracle database - Google Patents
Method for detecting execution plan exception of Oracle database Download PDFInfo
- Publication number
- CN111522793A CN111522793A CN202010223047.XA CN202010223047A CN111522793A CN 111522793 A CN111522793 A CN 111522793A CN 202010223047 A CN202010223047 A CN 202010223047A CN 111522793 A CN111522793 A CN 111522793A
- Authority
- CN
- China
- Prior art keywords
- database
- execution plan
- health
- value
- current
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Granted
Links
- 238000000034 method Methods 0.000 title claims abstract description 16
- 230000036541 health Effects 0.000 claims abstract description 50
- 230000002159 abnormal effect Effects 0.000 claims abstract description 23
- 238000013210 evaluation model Methods 0.000 claims abstract description 8
- 230000008439 repair process Effects 0.000 claims abstract description 8
- 238000001514 detection method Methods 0.000 claims abstract description 5
- 230000004044 response Effects 0.000 claims description 12
- 230000005856 abnormality Effects 0.000 claims description 5
- 238000004445 quantitative analysis Methods 0.000 abstract 1
- 238000012423 maintenance Methods 0.000 description 5
- 238000012545 processing Methods 0.000 description 3
- 238000011161 development Methods 0.000 description 2
- 238000011156 evaluation Methods 0.000 description 2
- 238000012544 monitoring process Methods 0.000 description 2
- 238000012360 testing method Methods 0.000 description 2
- 238000004458 analytical method Methods 0.000 description 1
- 230000007547 defect Effects 0.000 description 1
- 238000012986 modification Methods 0.000 description 1
- 230000004048 modification Effects 0.000 description 1
- 230000008569 process Effects 0.000 description 1
- 238000003672 processing method Methods 0.000 description 1
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/28—Databases characterised by their database models, e.g. relational or object models
- G06F16/284—Relational databases
Landscapes
- Engineering & Computer Science (AREA)
- Databases & Information Systems (AREA)
- Theoretical Computer Science (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Debugging And Monitoring (AREA)
Abstract
A detection method for the exception of an Oracle database execution plan comprises the following steps of S1, analyzing and summarizing exception data of the execution plan, and constructing a database execution plan health evaluation model; step S2, determining each preset threshold parameter in the model, initializing error times, inputting current service scene and database performance parameters, and calculating current health value; and step S3, if the current health value is less than or equal to the preset health minimum value, adding 1 to the error frequency, and if the current health value is greater than the preset health minimum value, executing correctly at the current time. And step S4, when the accumulated value of the error times is larger than the preset threshold value, the execution plan is abnormal, and alarm information is sent or a repair script is executed. According to the method, system performance after the database execution plan is abnormal is summarized and summarized, quantitative analysis indexes are abstracted, automatic judgment and emergency treatment are performed by the system after the Oracle execution plan is abnormal, so that fault finding time and emergency treatment time are reduced, and service continuity is improved.
Description
Technical Field
The invention belongs to the technical field of database detection, and particularly relates to a method for detecting an Oracle database execution plan exception.
Background
In large financial companies such as securities, banks, insurance and the like, databases of many core systems are Oracle databases, and hundreds of millions of records are stored in many databases. In operation and maintenance, because the Oracle database uses the CBO evaluation model when analyzing SQL statements, in a high concurrency system, when the number of records in the Oracle single table exceeds billion, the database often has abnormal execution plans, which results in long SQL response time, session congestion, and as time accumulates, the influence range is continuously expanded, and finally, all database resources are used for processing the abnormal SQL of the execution plans, so that new requests cannot be responded, and service development is influenced.
In view of the difficulty in discovering the abnormality of the Oracle execution plan, no mature automation tool, processing mode or passive operation and maintenance mode exists in the industry at present, and after the abnormality occurs in the business system, the operation and maintenance personnel intervene in analysis and processing manually.
However, the manual processing method needs a long time to locate the problem, the financial system has high timeliness requirements, and the long duration of the fault causes huge economic loss to the enterprise, and even influences the rating and business development range of the company.
Disclosure of Invention
The invention aims to overcome the defects of the prior art and provides a method for detecting the abnormality of the Oracle database execution plan.
The invention provides a method for detecting the exception of an Oracle database execution plan, which comprises the following steps,
step S1, analyzing and summarizing abnormal data of the execution plan, and constructing a database execution plan health evaluation model;
step S2, determining each preset threshold parameter in the model, initializing error times, inputting current service scene and database performance parameters, and calculating current health value;
and step S3, if the current health value is less than or equal to the preset health minimum value, adding 1 to the error frequency, and if the current health value is greater than the preset health minimum value, executing correctly at the current time.
And step S4, when the accumulated value of the error times is larger than the preset threshold value, the execution plan is abnormal, and alarm information is sent or a repair script is executed.
As a further technical solution of the present invention, in step S1, the formula of the database execution plan health assessment model is
Wherein H is a database execution plan health value, Session is the number of sessions of the current database, Active is the number of Active sessions of the current database, MaxActive is a database Active Session number threshold, QT is the average response time of the current Active SQL, MaxQT is the average response time threshold, RT is the average interface consumption time of the statistical time period, MaxRT is the average interface consumption time threshold,
for the average query volume per second of the service system in the statistical time period,
the query volume per second is averaged for a preset service system.
Further, the specific steps of step S2 are,
step S21, determining the active conversation number threshold MaxActive, average response time threshold MaxQT, average interface time consumption threshold MaxRT and the preset average query volume per second of the service system according to the service scene and the database performance
；
Step S22, determining the detected Time interval Time and stability parameters
；
Step S23, determining the minimum value of the health value according to the performance of the database
；
Step S24, setting the number of abnormal times Error = 0;
step S25, inquiring the Session number Session and the active Session number of the current database
Average response time QT of active SQL;
step S26, inquiring the average consumption QPS of the interface in the previous Time period;
step S27, calculating the health value H according to the execution plan health assessment model.
Further, in step S3, if the health value is not good, the method further includes
，
Setting the abnormal times for the minimum value of the health value determined by the database performance
(ii) a If health value
Then, the number of exceptions Error =0 is set.
Further, step S4 is specifically executed if the number of abnormal times is greater
If the number of abnormal times is less than the preset number, the execution plan is abnormal, alarm information is sent or a repair script is executed
Then the next plan is executed.
According to the invention, a database execution plan health evaluation model is constructed by analyzing a large number of cases of database execution plan abnormity, so that the problem that the database execution plan abnormity is difficult to find is solved, operation and maintenance personnel can combine a monitoring system and a dial-up test system, health evaluation dimensionality is enriched from an application layer and a database layer, automatic finding and repairing of an Oracle execution plan abnormity scene are realized, fault finding and repairing time is reduced, and service continuity is improved.
Detailed Description
The embodiment provides a method for detecting the exception of an Oracle database execution plan, which comprises the following steps,
step S1, analyzing and summarizing abnormal data of the execution plan, and constructing a database execution plan health evaluation model;
step S2, determining each preset threshold parameter in the model, initializing error times, inputting current service scene and database performance parameters, and calculating current health value;
and step S3, if the current health value is less than or equal to the preset health minimum value, adding 1 to the error frequency, and if the current health value is greater than the preset health minimum value, executing correctly at the current time.
And step S4, when the accumulated value of the error times is larger than the preset threshold value, the execution plan is abnormal, and alarm information is sent or a repair script is executed.
The health assessment model of the database execution plan is formulated as
Wherein H is a database execution plan health value, Session is the number of sessions of the current database, Active is the number of Active sessions of the current database, MaxActive is a database Active Session number threshold, QT is the average response time of the current Active SQL, MaxQT is the average response time threshold, RT is the average interface consumption time of the statistical time period, MaxRT is the average interface consumption time threshold,
for the average query volume per second of the service system in the statistical time period,
the query volume per second is averaged for a preset service system.
The detection process of the method is as follows;
step 1, determining threshold values MaxActive, MaxQT, MaxRT and database performance according to service scene and database performance,
。
Step 2, determining the Time interval Time and the stability parameter of the detection
。
Step 3, determining the minimum value of the health value according to the performance of the database
。
And 4, setting the abnormal times Error = 0.
Step 5, inquiring the Session number Session and the active Session number of the current database
Average response of active SQLTime QT.
And 6, inquiring the average consumption QPS of the interface in the previous Time period.
And 7, calculating a health value H according to the execution plan health evaluation model.
Step 8, if
Is provided with
(ii) a If it is not
Error =0 is set.
Step 9, if
And executing the plan exception, sending alarm information or executing the repair script.
Step 10, if
And repeating the operation from the step 4 to the step 10.
The method introduces the concept of database execution plan health indexes, constructs a database execution plan health evaluation model, solves the problem that the database execution plan is difficult to discover due to abnormity, and enriches health evaluation dimensionality from an application layer and a database layer by operation and maintenance personnel by combining a monitoring system and a dial testing system, so that automatic discovery and repair of an Oracle execution plan abnormal scene are realized, the time for discovering and repairing faults is reduced, and the service continuity is improved.
The foregoing illustrates and describes the principles, general features, and advantages of the present invention. It will be understood by those skilled in the art that the present invention is not limited to the embodiments described above, which are intended to further illustrate the principles of the invention, and that various changes and modifications may be made without departing from the spirit and scope of the invention, which is intended to be protected by the appended claims. The scope of the invention is defined by the claims and their equivalents.
Claims (5)
1. A method for detecting the exception of an Oracle database execution plan is characterized by comprising the following steps,
step S1, analyzing and summarizing abnormal data of the execution plan, and constructing a database execution plan health evaluation model;
step S2, determining each preset threshold parameter in the model, initializing error times, inputting current service scene and database performance parameters, and calculating current health value;
step S3, if the current health value is less than or equal to the preset health minimum value, adding 1 to the error frequency, and if the current health value is greater than the preset health minimum value, executing correctly at the current time;
and step S4, when the accumulated value of the error times is larger than the preset threshold value, the execution plan is abnormal, and alarm information is sent or a repair script is executed.
2. The method for detecting the abnormality of the Oracle database execution plan as claimed in claim 1, wherein in step S1, the formula of the database execution plan health assessment model is
H is a database execution plan health value, Session is the number of sessions of the current database, Active is the number of Active sessions of the current database, MaxActive is a database Active Session number threshold, QT is the average response time of the current Active SQL, MaxQT is the average response time threshold, RT is the average consumed time of the interface in the statistical time period, MaxRT is the average consumed time threshold of the interface, QPS is the average query volume per second of the service system in the statistical time period, and QPS is the average query volume per second of the service system in the statistical0The query volume per second is averaged for a preset service system.
3. The method for detecting the abnormality of the Oracle database execution plan according to claim 1, wherein the step S2 includes the following specific steps,
step S21, determining an active session number threshold MaxActive, an average response time threshold MaxQT, an interface average time consumption threshold MaxRT and a preset business system average query per second QPS of the database according to the business scene and the database performance0；
Step S22, determining the Time interval Time and the stability parameter of the detection;
step S23, determining the minimum value H of the health value according to the performance of the database0；
Step S24, setting the abnormal number Error equal to 0;
step S25, inquiring Session number Session, Active Session number Active and average response time QT of Active SQL of the current database;
step S26, inquiring the average consumption QPS of the interface in the previous Time period;
step S27, calculating the health value H according to the execution plan health assessment model.
4. The method as claimed in claim 1, wherein in step S3, if the health value H is less than or equal to H0，H0Setting the abnormal times Error as Error +1 for the minimum value of the health value determined by the database performance; if the health value H > H0If the Error count Error is set to 0.
5. The method as claimed in claim 1, wherein the step S4 is specifically configured to indicate that the execution plan is abnormal if the abnormal number Error is greater, send an alarm message or execute a repair script, and execute the next plan if the abnormal number Error is less than or equal to.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010223047.XA CN111522793B (en) | 2020-03-26 | 2020-03-26 | Method for detecting abnormal execution plan of Oracle database |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010223047.XA CN111522793B (en) | 2020-03-26 | 2020-03-26 | Method for detecting abnormal execution plan of Oracle database |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN111522793A true CN111522793A (en) | 2020-08-11 |
| CN111522793B CN111522793B (en) | 2024-06-18 |
Family
ID=71910591
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202010223047.XA Active CN111522793B (en) | 2020-03-26 | 2020-03-26 | Method for detecting abnormal execution plan of Oracle database |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111522793B (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113297307A (en) * | 2020-08-15 | 2021-08-24 | 阿里巴巴集团控股有限公司 | Database request identification and anomaly detection method, device, equipment and medium |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102081623A (en) * | 2009-11-30 | 2011-06-01 | 中国移动通信集团浙江有限公司 | Method and system for detecting database abnormality |
| CN102081622A (en) * | 2009-11-30 | 2011-06-01 | 中国移动通信集团贵州有限公司 | Method and device for evaluating system health degree |
| CN102982037A (en) * | 2011-09-05 | 2013-03-20 | 中国移动通信集团浙江有限公司 | Database node health condition detection method and detection device |
-
2020
- 2020-03-26 CN CN202010223047.XA patent/CN111522793B/en active Active
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102081623A (en) * | 2009-11-30 | 2011-06-01 | 中国移动通信集团浙江有限公司 | Method and system for detecting database abnormality |
| CN102081622A (en) * | 2009-11-30 | 2011-06-01 | 中国移动通信集团贵州有限公司 | Method and device for evaluating system health degree |
| CN102982037A (en) * | 2011-09-05 | 2013-03-20 | 中国移动通信集团浙江有限公司 | Database node health condition detection method and detection device |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113297307A (en) * | 2020-08-15 | 2021-08-24 | 阿里巴巴集团控股有限公司 | Database request identification and anomaly detection method, device, equipment and medium |
| CN113297307B (en) * | 2020-08-15 | 2024-03-05 | 阿里巴巴集团控股有限公司 | Database request identification and anomaly detection methods, devices, equipment and media |
Also Published As
| Publication number | Publication date |
|---|---|
| CN111522793B (en) | 2024-06-18 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN111309565B (en) | Alarm processing method and device, electronic equipment and computer readable storage medium | |
| Zhao et al. | Understanding and handling alert storm for online service systems | |
| CN101902366B (en) | Method and system for detecting abnormal service behaviors | |
| CN111209131A (en) | Method and system for determining fault of heterogeneous system based on machine learning | |
| Li et al. | Generic and robust localization of multi-dimensional root causes | |
| CN105550100A (en) | Method and system for automatic fault recovery of information system | |
| CN110333995A (en) | The method and device that operation of industrial installation is monitored | |
| CN117035513A (en) | Intelligent monitoring and management system and method for energy operation of intelligent industrial park | |
| CN113360722B (en) | Fault root cause positioning method and system based on multidimensional data map | |
| CN111125056A (en) | Automatic operation and maintenance system and method for information system database | |
| CN115033463B (en) | System exception type determining method, device, equipment and storage medium | |
| CN111258798A (en) | Fault positioning method and device for monitoring data, computer equipment and storage medium | |
| CN115529595A (en) | Method, device, equipment and medium for detecting abnormity of log data | |
| CN110784352B (en) | Data synchronous monitoring and alarming method and device based on Oracle golden gate | |
| CN114924990A (en) | Abnormal scene testing method and electronic equipment | |
| CN112949874A (en) | Power distribution terminal defect characteristic self-diagnosis method and system | |
| CN111522793A (en) | Method for detecting execution plan exception of Oracle database | |
| CN109634808B (en) | Chain monitoring event root cause analysis method based on correlation analysis | |
| CN114826788B (en) | Equipment management and control system based on information security | |
| CN114675789B (en) | Big data analysis and storage system and method based on computer system | |
| CN114531338A (en) | Monitoring alarm and tracing method and system based on call chain data | |
| CN111258788B (en) | Disk failure prediction method, device and computer readable storage medium | |
| CN114760332A (en) | Efficient equipment monitoring and analyzing method | |
| CN112183997A (en) | Monitoring and analyzing system for abnormal state of energy consumption unit | |
| KR20170127876A (en) | System and method for dealing with troubles through fault analysis of log |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant |