Database intelligent risk identification and dynamic health monitoring report generation method and system

Abstract

The application discloses a database intelligent risk identification and dynamic health monitoring report generation method and system, belongs to the technical field of database operation and maintenance monitoring, obtains transaction duration, lock waiting link, log writing rate, master-slave synchronization delay, SQL execution plan change quantity and table statistical information update time of a target database in a monitoring period; generates a risk feature sequence including snapshot retention conduction coefficient and execution plan drift aggregation degree; identifies a business scenario type based on the risk feature sequence and determines a dynamic risk baseline; calculates an implicit risk value when the risk feature sequence deviates from the dynamic risk baseline, determines a risk category and affected instances, business tables and SQL; and then generates and dynamically adjusts a health monitoring report; the application can identify an implicit database risk formed by a single index not exceeding a limit but multiple index links being accumulated.

Description

Technical Field

[0001] This invention relates to the field of database operation and maintenance monitoring technology, specifically to a method and system for intelligent database risk identification and dynamic health monitoring report generation. Background Technology

[0002] In existing technologies, database health monitoring typically uses single-point thresholds such as CPU, memory, connection count, and slow SQL queries as the basis for alerts, and generates fixed template reports periodically. In scenarios involving cross-database settlement, regulatory reporting, or hospital nighttime batch data entry, some anomalies do not manifest as instantaneous resource overruns, but rather as a combination of characteristics such as low-frequency long transactions consuming snapshots, minor accumulation of archived logs, slow increase in standby database application latency, and distortion of index statistics. These anomalies may not reach the alert threshold for any single metric, but they can suddenly trigger lock wait propagation, master-slave failover failure, or core query timeouts during report generation, leading to misjudgments of the database's health by the business, failing to identify hidden risks in advance and generate health monitoring reports that update as risks change. Summary of the Invention

[0003] The purpose of this invention is to provide a database-based intelligent risk identification and dynamic health monitoring report generation method and system to address the shortcomings of the prior art.

[0004] To achieve the above objectives, the present invention provides the following technical solution: a database-based intelligent risk identification and dynamic health monitoring report generation method, comprising:

[0005] Obtain operational sampling data of the target database during the monitoring period, including transaction duration, lock wait chain, log write rate, master-slave synchronization delay, SQL execution plan changes, and table statistics update time;

[0006] A risk feature sequence is generated based on the operational sampling data. The risk feature sequence includes the snapshot retention transmission coefficient and the execution plan drift aggregation degree.

[0007] The business scenario type of the database is identified based on the risk feature sequence, and the corresponding dynamic risk baseline is determined based on the business scenario type.

[0008] When the risk feature sequence deviates from the dynamic risk baseline, a latent risk value is calculated, and the risk category and risk impact range are determined based on the latent risk value. The risk impact range includes affected instances, affected business tables, and affected SQL statements.

[0009] A health monitoring report is generated based on the risk category and the scope of the risk impact, and the risk level, cause explanation and treatment recommendations in the health monitoring report are adjusted in real time according to the changes in the hidden risk value.

[0010] Preferably, generating the snapshot retention conduction coefficient includes:

[0011] The active transaction with the longest transaction duration within the same sampling time is used as the lock wait link backtracking entry point. The lock wait link is used to search in reverse for a session that does not have a previous level blocking session, and this session is determined as the snapshot retention starting point.

[0012] Starting from the snapshot retention point, read the link nodes step by step along the blocking direction of the lock wait link, and append the log write rate and master-slave synchronization delay of adjacent sampling times to the corresponding link nodes;

[0013] When the log write rate remains greater than 0 and the master-slave synchronization delay extends and expands along the lock wait link, the corresponding link segment is identified as the stagnant transmission segment, and a snapshot stagnant transmission coefficient is generated based on the number of consecutive samples of the stagnant transmission segment and the covered lock wait link level.

[0014] Preferably, the aggregation degree of the generated execution plan drift includes:

[0015] Based on the data table names and table statistics update times involved in the business SQL, statistical information time-series markers are generated, and the statistical information time-series markers are bound to the corresponding SQL execution plan changes to form a plan change record;

[0016] Within a continuous sampling period, business SQL statements with execution plan changes greater than 0 and bound to the same statistical information time series marker are identified as drift SQL statements, and drift SQL statements with the same statistical information time series marker are aggregated into plan drift groups.

[0017] The execution plan drift aggregation degree is generated based on the number of different drift SQL statements within the planned drift group, the number of consecutive sampling times in the planned drift group, and the number of data tables commonly involved in the drift SQL statements.

[0018] Preferably, the business scenario type in which the database exists includes:

[0019] The snapshot retention propagation coefficient and execution plan drift aggregation degree within the same monitoring period are aligned according to the sampling time to generate a dual-feature time series segment, and the order of change of the snapshot retention propagation coefficient and the execution plan drift aggregation degree is marked.

[0020] The consecutive sampling times where both the snapshot retention propagation coefficient and the execution plan drift aggregation degree are greater than 0 are determined as the continuous overlapping intervals, and the overlapping interval position identifier is generated according to the position of the continuous overlapping interval in the dual-feature time segment.

[0021] A scenario fingerprint is generated based on the order of changes and the location identifier of the overlapping interval, and the business scenario type is determined according to the scenario fingerprint as a batch warehousing scenario, a centralized reporting scenario, or a master-slave catch-up scenario.

[0022] Preferably, determining a dynamic risk baseline includes:

[0023] Filter historical health sampling records to find matching records that have the same sequence of changes, the same overlapping interval location identifiers, and the same overlapping persistence identifiers as the fingerprint of the current scene;

[0024] Read the snapshot retention propagation coefficient and execution plan drift aggregation degree from the matching records respectively, and determine the distribution range of the snapshot retention propagation coefficient and the distribution range of the execution plan drift aggregation degree according to the numerical order;

[0025] The business scenario type, scenario fingerprint, snapshot retention transmission coefficient distribution range, and execution plan drift aggregation degree distribution range are combined to form the corresponding dynamic risk baseline.

[0026] Preferably, determining whether a risk characteristic sequence deviates from a dynamic risk baseline includes:

[0027] The snapshot retention conduction coefficient at each sampling moment within the current monitoring period is compared with the upper boundary of the snapshot retention conduction coefficient distribution interval, and sampling moments that exceed the upper boundary are marked as snapshot deviation moments.

[0028] The execution plan drift aggregation degree at each sampling time within the current monitoring period is compared with the upper boundary of the execution plan drift aggregation degree distribution interval, and sampling times that exceed the upper boundary are marked as drift deviation times;

[0029] According to the sampling time sequence, sampling times with consecutive snapshot deviation markers or drift deviation markers are merged to generate deviation time sequence segments.

[0030] Preferably, calculating the latent risk value includes:

[0031] Based on the snapshot deviation time in the deviation time sequence segment, read the generation record of the corresponding snapshot retention transmission coefficient, obtain the retention transmission segment that forms the coefficient, and based on the drift deviation time, read the generation record of the corresponding execution plan drift aggregation degree, obtain the plan drift group that forms the aggregation degree;

[0032] Arrange the stagnant conduction segments and planned drift groups in chronological order of deviation time to obtain the deviation source chain;

[0033] Based on the number of sampling moments of consecutive deviations in the deviation source chain, the lock waiting link level covered by the lingering transmission segment, and the number of repeated occurrences of drift SQL in the planned drift group, a hidden risk value is generated.

[0034] Preferably, the risk category and the scope of risk impact are determined, including:

[0035] When the snapshot deviation in the deviation source chain is earlier than the drift deviation or only the snapshot deviation exists, the risk category is determined to be transaction retention synchronization propagation risk, and the instance where the retention propagation segment is located is determined to be the affected instance;

[0036] When the drift deviation in the deviation source chain is earlier than the snapshot deviation or only the drift deviation exists, the risk category is determined as statistical mismatch query aggregation risk, and the common data table and drift SQL in the planned drift group are respectively determined as the affected business table and the affected SQL.

[0037] When both a stagnant transmission segment and a planned drift group exist in the same off-source chain, the instance containing the stagnant transmission segment, the common data table in the planned drift group, and the drift SQL are all written into the risk impact scope.

[0038] Preferably, health monitoring reports are generated and dynamically adjusted, including:

[0039] Select the corresponding report paragraph skeleton according to the risk category, and write the affected instance, affected business table and affected SQL into the affected object position in the report paragraph skeleton to generate an initial health monitoring report;

[0040] The direction of change of latent risk values ​​at adjacent sampling times is linked to the stagnant transmission segment or planned drift group in the deviation source chain to form a risk evolution trajectory, and the risk level is determined as rising, maintaining or falling based on the direction of change at the latest sampling time.

[0041] When the risk level changes, the corresponding handling suggestions are written according to the explanation of the reason for replacing the latest bound delayed transmission segment or planned drift group, and the risk of transaction delay synchronization diffusion or statistical mismatch query aggregation is written, and a dynamic health monitoring report is generated.

[0042] This invention also provides a database-based intelligent risk identification and dynamic health monitoring report generation system, comprising:

[0043] Run the sampling data acquisition module to obtain the target database's running sampling data during the monitoring period, including transaction duration, lock wait chain, log write rate, master-slave synchronization delay, SQL execution plan changes, and table statistics update time;

[0044] The risk feature sequence generation module generates a risk feature sequence based on the running sample data. The risk feature sequence includes the snapshot retention transmission coefficient and the execution plan drift aggregation degree.

[0045] The baseline determination module identifies the business scenario type of the database based on the risk feature sequence, and determines the corresponding dynamic risk baseline based on the business scenario type.

[0046] The implicit risk calculation module calculates the implicit risk value when the risk feature sequence deviates from the dynamic risk baseline, and determines the risk category and risk impact range based on the implicit risk value. The risk impact range includes affected instances, affected business tables, and affected SQL.

[0047] The dynamic adjustment module generates a health monitoring report based on the risk category and the scope of risk impact, and adjusts the risk level, cause explanation, and treatment recommendations in the health monitoring report in real time according to the changes in the latent risk value.

[0048] The technical effects and advantages provided by the present invention in the above technical solution are as follows:

[0049] This invention differs from existing technologies that rely solely on single-indicator thresholds such as CPU usage, connection count, slow SQL queries, and master-slave latency for alerts. Its core lies in correlating transaction duration, lock wait paths, log write rates, and master-slave synchronization latency along the same blocked path to form a snapshot retention propagation coefficient. Simultaneously, it binds SQL execution plan changes to table statistics update times to form execution plan drift aggregation. Through these methods, low-intensity anomalies originally scattered across transactions, locks, log synchronization, and execution plans can be transformed into risk characteristics with temporal propagation relationships. This addresses the problem of existing health monitoring failing to identify hidden risks where individual indicators are within limits but multiple indicators accumulate and deteriorate along the business path, thereby enabling early detection of transaction retention synchronization propagation risks and statistical mismatch query aggregation risks.

[0050] This invention differs from fixed template reports and fixed baseline judgment methods. Its core lies in generating a scenario fingerprint based on the sequential changes and continuous overlap intervals of the snapshot retention propagation coefficient and the execution plan drift aggregation degree. A dynamic risk baseline is then determined based on historical health sampling records under the same scenario fingerprint. When a risk deviates, the affected instances, affected business tables, and affected SQL queries are identified by reverse-linking the retention propagation segment and plan drift group through the deviation source chain. Therefore, the health monitoring report no longer merely displays general alarms but can adjust the risk level, cause explanation, and handling recommendations in real time according to changes in implicit risk values. This allows the report content to directly correspond to specific risk sources and handling targets, improving the accuracy of anomaly location and the efficiency of database operation risk handling. Attached Figure Description

[0051] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments recorded in this invention. For those skilled in the art, other drawings can be obtained based on these drawings.

[0052] Figure 1This is a flowchart of the database intelligent risk identification and dynamic health monitoring report generation method of the present invention.

[0053] Figure 2 The flowchart below shows the module of the database intelligent risk identification and dynamic health monitoring report generation system of the present invention.

[0054] Figure 3 This is a flowchart of the dynamic risk baseline determination method of the present invention. Detailed Implementation

[0055] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, the technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0056] Example 1, please refer to Figure 1 and Figure 3 As shown in this embodiment, the database intelligent risk identification and dynamic health monitoring report generation method includes:

[0057] Obtain operational sampling data of the target database during the monitoring period, including transaction duration, lock wait chain, log write rate, master-slave synchronization delay, SQL execution plan changes, and table statistics update time.

[0058] In one possible embodiment of the present invention, the monitoring service establishes a read-only monitoring connection with the target database in advance, and sets a monitoring period according to business off-peak hours, batch processing windows, or report generation windows. The monitoring period includes multiple continuous sampling moments. At each sampling moment, the monitoring service reads running status data from the database session view, lock management view, log archive view, master-slave replication view, SQL execution statistics view, and data dictionary statistics information view, respectively.

[0059] Specifically, the monitoring service obtains the start time of currently active but uncommitted transactions and determines the transaction duration based on the difference between the current sampling time and the transaction start time. At the same time, it obtains the association between waiting sessions, blocked sessions, and blocked sessions related to the active transaction, generates a lock waiting link according to the transmission path from the blocked session to the last-level waiting session, and records the link length and waiting duration of the lock waiting link.

[0060] Furthermore, the monitoring service obtains the redo log volume or archived log volume between two adjacent sampling times, and calculates the log write rate based on the redo log volume or archived log volume and the sampling time interval; it obtains the difference between the primary database log generation location and the standby database log application location, or the difference between the last log application time of the standby database and the current log time of the primary database, as the primary-standby synchronization delay. For a preset set of business SQL queries, the monitoring service obtains the current execution plan identifier, access path, connection order, and index usage, and compares it with the baseline execution plan of the corresponding SQL query in the previous monitoring period to obtain the SQL execution plan change; the monitoring service also reads the last update time of the statistical information of the data tables and indexes involved in the business SQL query, and uses the last update time as the table statistical information update time.

[0061] In this embodiment, after acquiring the aforementioned data, the monitoring service uses the sampling time as an index to encapsulate the transaction duration, lock wait chain, log write rate, master-slave synchronization delay, SQL execution plan changes, and table statistics update time into the same sampling record. It then performs time alignment and outlier filtering on multiple consecutive sampling records to form the target database's operational sampling data within the monitoring period. This operational sampling data is used to subsequently generate the snapshot retention propagation coefficient and execution plan drift aggregation degree.

[0062] A risk feature sequence is generated based on the operational sampling data. The risk feature sequence includes the snapshot retention transmission coefficient and the execution plan drift aggregation degree.

[0063] In one possible embodiment of the present invention, the running sampling data is arranged in the order of sampling time to form a set of sampling records. Each sampling record includes transaction duration, lock wait chain, log write rate, master-slave synchronization delay, SQL execution plan change amount, and table statistics update time.

[0064] To obtain the snapshot persistence propagation coefficient, at each sampling time, the transaction duration of each active transaction within that sampling time is first read, and the active transaction with the largest transaction duration value is determined as the target transaction. The transaction duration is calculated by subtracting the start time of the active transaction from the sampling time to obtain the duration in seconds. Since the target transaction is the transaction with the longest duration at the same sampling time, it best represents the starting source of snapshot occupancy; therefore, the target transaction is used as the lock wait link backtracking entry point.

[0065] Subsequently, within the lock wait chain corresponding to the same sampling time, waiting sessions or blocked sessions with session association relationships with the target transaction are searched. The lock wait chain is recorded in the direction of "blocked session pointing to blocked session." When the target transaction's session is a blocked session, the upstream blocked session is searched backwards along the lock wait chain until a session without an upstream blocked session is found, and this session is determined as the snapshot retention starting point. When the target transaction's session is a blocked session, the target transaction's session is directly determined as the snapshot retention starting point. This process allows the duration of a single transaction to be correlated with the source blocking position in the lock wait chain, avoiding risk assessment based solely on transaction duration.

[0066] After determining the snapshot retention starting point, the link nodes are read level by level along the blocking direction of the lock waiting link. Let the level where the snapshot retention starting point is located be layer 1, the sessions directly blocked by it be layer 2, and so on. For a link node at layer k, at sampling time t_i, the log write rate R_i,k corresponding to the database instance where the node resides is read, and the master-slave synchronization delay D_i,k at the same sampling time is also read; simultaneously, the log write rate R_i+1,k and the master-slave synchronization delay D_i+1,k corresponding to the next adjacent sampling time t_i+1 are read. The log write rate is obtained by dividing the amount of new logs between two adjacent sampling times by the sampling interval, and the master-slave synchronization delay is obtained by converting the difference between the primary database log generation location and the standby database log application location into time or log volume. Thus, each lock waiting link level carries the log write rate and master-slave synchronization delay under continuous sampling.

[0067] When determining whether the log write rate is sufficient for continued writing, each consecutive sampled record corresponding to the k-th layer link node is compared. If R_i,k is greater than 0 and R_i+1,k is greater than 0, then the layer is determined to meet the continued writing condition between t_i and t_i+1. This condition does not use a manual alarm threshold, but rather uses the existence of new log entries as the criterion. The design principle is that as long as logs continue to be generated, the backup database still needs to continuously apply the logs. If snapshot retention also exists, the latency has an accumulated basis.

[0068] When determining whether the master-slave synchronization delay is increasing synchronously with the extension of the lock-waiting link, the master-slave synchronization delays of adjacent levels within the same sampling time are compared, and the delay changes of adjacent sampling times are also compared. If the master-slave synchronization delay D_i,k+1 of level k+1 is greater than or equal to the master-slave synchronization delay D_i,k of level k, and the master-slave synchronization delay D_i+1,k+1 of the next sampling time is greater than D_i,k+1, then it is determined that the master-slave synchronization delay has increased along the extension of the lock-waiting link. This determination only uses the sequential relationship between adjacent levels and adjacent sampling times within the same link, does not rely on a fixed threshold, and can identify situations where the individual delay value does not exceed the limit but the delay continues to accumulate along the blocking path.

[0069] When the same link segment simultaneously satisfies both the log write rate maintenance write requirement and the primary / standby synchronization delay increasing synchronously with the lock wait link extension, this link segment is reserved as a lingering transmission segment. The lingering transmission segment includes a start level, an end level, the sampling time at which the conditions are first met, and the sampling time at which the conditions are last consecutively met. If the above two conditions are no longer met between two adjacent sampling times, the recording of the current lingering transmission segment ends, and recording resumes from the next sampling time when the conditions are met.

[0070] Finally, a snapshot retention conduction coefficient is generated based on the number of consecutive samples in the retention conduction segment and the number of lock-waiting link levels covered. The number of consecutive samples is denoted as C, representing the number of sampling moments from the first fulfillment of the condition to the last consecutive fulfillment of the condition in the retention conduction segment; the number of lock-waiting link levels covered is denoted as L, representing the number of link levels contained in the retention conduction segment. The snapshot retention conduction coefficient P is calculated as: P = C × L. C characterizes the duration of retention conduction over time, and L characterizes the spread of retention conduction along the blocked path. The product of these two values, P, reflects both the duration of time and the spread of links.

[0071] To obtain the aggregation degree of execution plan drift, a time-series marker for statistical information is first generated for the data tables involved in the business SQL based on the table statistics update time. For any business SQL, the names of the data tables involved and the corresponding table statistics update time are read. The time-series marker for statistical information is composed of the data table name and the table statistics update time, represented as "data table name - statistics update time". For example, if the business SQL involves data table A, and the table statistics update time is January 1, 2026, 02:00:00, then the time-series marker for the statistics of this data table is "A-20260101020000". Through this marker, the same data table is distinguished as different objects under different statistics update times, avoiding the mixed judgment of execution plan changes before and after the statistics have been updated.

[0072] After generating the statistical information time-series markers, these markers are bound to the corresponding SQL execution plan changes. Specifically, for each business SQL statement, at sampling time t_i, the SQL execution plan change Q_i is read, and the statistical information time-series marker M_i corresponding to the data table involved in the business SQL statement is read. The business SQL identifier, Q_i, and M_i are written into the same plan change record. If a business SQL statement involves multiple data tables, multiple plan change records are generated, with each record corresponding to the statistical information time-series marker for one data table. Thus, the specific data table corresponding to each SQL execution plan change and its statistical information update time can be determined.

[0073] Within consecutive sampling timeframes, SQL statements whose execution plan changes point to the same statistical time series marker are identified as drifting SQL statements. The scope of consecutive sampling timeframes uses the sampling records already formed within the current monitoring period, without introducing additional time window parameters. For the same statistical time series marker M, the plan change records pointing to M are grouped according to the business SQL identifier; if the same business SQL statement has a greater than 0 SQL execution plan change in more than two consecutive sampling timeframes, and the statistical time series marker bound to this change is the same, then the business SQL statement is identified as a drifting SQL statement.

[0074] It should be noted that a value greater than 0 here indicates that the current execution plan differs from the baseline execution plan in terms of nodes, access paths, connection order, or index usage. No manual threshold is used, and slight but persistent execution path offsets are preserved.

[0075] After identifying the drifting SQL statements, they are aggregated according to the data tables involved to obtain planned drift groups. Specifically, drifting SQL statements with the same statistical information time series markers are grouped into the same planned drift group; when the same drifting SQL statement involves multiple data tables, it is placed into the planned drift group for each corresponding data table. Each planned drift group includes at least the statistical information time series marker, a set of drifting SQL statement identifiers, the sampling time of each drifting SQL statement, and the name of the data table commonly involved. This process ensures that execution plan changes are no longer expressed in isolation as individual SQL statements, but rather form an aggregated result centered on the update time of the data table statistical information.

[0076] Finally, the execution plan drift aggregation degree is generated based on the number of recurrences of drift SQL statements in the plan drift group, the duration of continuous sampling, and the number of data tables involved. For any plan drift group, the number of different drift SQL statements within the plan drift group is denoted as N; the duration of continuous sampling is denoted as T, where T is the number of sampling times covered by the plan drift group from the first occurrence of a drift SQL statement to the last consecutive occurrence of a drift SQL statement; and the number of data tables involved is denoted as B, where B is the number of data tables involved by the drift SQL statements in the plan drift group. The calculation expression for the execution plan drift aggregation degree G is: G = N × T × B. G is used to characterize the degree of aggregation of execution plan offsets in business queries under the statistical information time-series markers.

[0077] The business scenario type of the database is identified based on the risk feature sequence, and the corresponding dynamic risk baseline is determined based on the business scenario type.

[0078] In one possible embodiment of the present invention, firstly, the snapshot retention propagation coefficient and execution plan drift aggregation degree generated within the same monitoring period are aligned according to the sampling time. For any sampling time, the snapshot retention propagation coefficient and execution plan drift aggregation degree corresponding to that sampling time are written into the same dual-feature record; all dual-feature records are arranged in chronological order of sampling time to obtain a dual-feature time sequence segment. If only one feature parameter exists at a certain sampling time, the missing feature parameter is recorded as 0, to ensure that only one dual-feature record corresponds to the same sampling time.

[0079] The sequential changes and persistent overlapping intervals of the dual-feature time-series segments are marked. Specifically, the sampling moment when the snapshot retention propagation coefficient first changes from 0 to greater than 0 is recorded as the snapshot start time, and the sampling moment when the execution plan drift aggregation degree first changes from 0 to greater than 0 is recorded as the drift start time. If the snapshot start time is earlier than the drift start time, it is marked as snapshot first; if the drift start time is earlier than the snapshot start time, it is marked as drift first; if they correspond to the same sampling moment, they are marked as synchronous. The persistent overlapping interval is a set of consecutive sampling moments where both the snapshot retention propagation coefficient and the execution plan drift aggregation degree are greater than 0, and its start sampling moment, end sampling moment, and the number of sampling moments included are recorded.

[0080] It is understandable that the above judgment uses whether the feature parameter is 0 as the boundary. 0 indicates that no corresponding risk transmission or plan drift has been formed from the running sample data. Therefore, there is no need to set a separate judgment parameter, which can avoid the identification distortion under different business windows due to fixed numerical boundaries.

[0081] Furthermore, based on the sequence of changes and the continuous overlapping intervals, the dual-feature time series segments are mapped to scene fingerprints. The scene fingerprint consists of a sequence of changes identifier, an overlapping interval position identifier, and an overlapping duration identifier. The overlapping interval position identifier is determined based on the relative position of the continuous overlapping interval and the dual-feature time series segment: if the starting sampling time of the continuous overlapping interval coincides with the first sampling time of the dual-feature time series segment, it is recorded as a pre-segment overlap; if the ending sampling time of the continuous overlapping interval coincides with the last sampling time of the dual-feature time series segment, it is recorded as a post-segment overlap; if neither of the above two conditions is met, it is recorded as a mid-segment overlap. The overlapping duration identifier is determined based on the number of sampling times contained in the continuous overlapping interval; this number is directly used without conversion to a level.

[0082] After generating the scenario fingerprint, the business scenario type is determined according to a fixed mapping relationship: when the scenario fingerprint is snapshot-first and the overlapping interval is marked as later overlap, it is determined to be a batch ingestion scenario, indicating that the write process first causes snapshot retention propagation, which then affects the execution plan performance; when the scenario fingerprint is drift-first and there is a continuous overlapping interval, it is determined to be a centralized reporting scenario, indicating that the query execution plan first drifts, and then overlaps with lock waiting and synchronization delay; when the scenario fingerprint is snapshot-first and there is no continuous overlapping interval, or it is synchronous and the overlapping interval is marked as earlier overlap, it is determined to be a master-slave catch-up scenario, indicating that the master-slave synchronization delay mainly changes with the snapshot retention propagation. Through the above mapping, the business scenario type is obtained only by the snapshot retention propagation coefficient, the execution plan drift aggregation degree, and their temporal relationship, ensuring the consistency of the data source before and after.

[0083] Subsequently, records matching the current scenario fingerprint are extracted from historical health sampling records. Historical health sampling records are those that have been monitored but have not generated risk impact ranges. During extraction, the order of changes in the historical sampling records is first compared to see if they match the current scenario fingerprint; then, the overlapping interval position markers are compared; and finally, the overlapping duration markers are compared. Historical sampling records that match all three are considered matching records. For all matching records, the snapshot retention transmission coefficient and execution plan drift aggregation degree are read and sorted in ascending order of value. The minimum to maximum value of the snapshot retention transmission coefficient is determined as the snapshot retention transmission coefficient distribution interval, and the minimum to maximum value of the execution plan drift aggregation degree is determined as the execution plan drift aggregation degree distribution interval.

[0084] The two extracted distribution intervals are combined to form the dynamic risk baseline corresponding to the business scenario type. The dynamic risk baseline includes the business scenario type, scenario fingerprint, snapshot retention propagation coefficient distribution interval, and execution plan drift aggregation degree distribution interval. Whenever a new health sampling record is formed, if its scenario fingerprint matches an existing scenario fingerprint, the health sampling record is added to the corresponding historical health sampling record, and the two distribution intervals are redefined; if its scenario fingerprint is different, a new dynamic risk baseline is generated. Thus, the dynamic risk baseline updates with changes in the business scenario type and health sampling records, enabling subsequent risk identification to use the health distribution under the same business scenario as a comparison basis.

[0085] When the risk feature sequence deviates from the dynamic risk baseline, a latent risk value is calculated, and the risk category and risk impact range are determined based on the latent risk value. The risk impact range includes affected instances, affected business tables, and affected SQL statements.

[0086] In one possible embodiment of the present invention, firstly, the snapshot retention transmission coefficient and the execution plan drift aggregation degree within the current monitoring period are compared on a sample-by-sample basis. The dynamic risk baseline includes the distribution interval of the snapshot retention transmission coefficient and the distribution interval of the execution plan drift aggregation degree, denoted as the snapshot baseline interval and the drift baseline interval, respectively. The lower boundary of the snapshot baseline interval is the minimum value of the snapshot retention transmission coefficient in the historical health sampling records under the same business scenario type, and the upper boundary is its maximum value; the lower boundary of the drift baseline interval is the minimum value of the execution plan drift aggregation degree in the historical health sampling records under the same business scenario type, and the upper boundary is its maximum value. Since both the snapshot retention transmission coefficient and the execution plan drift aggregation degree are used to represent the degree of risk accumulation, exceeding the corresponding upper boundary is considered a risk deviation, and cases below the lower boundary are not included in the risk deviation.

[0087] For any sampling moment in the current monitoring period, the snapshot retention transmission coefficient and execution plan drift aggregation degree of that sampling moment are read. If the snapshot retention transmission coefficient is greater than the upper boundary of the snapshot baseline interval, the sampling moment is marked as a snapshot deviation moment; if the execution plan drift aggregation degree is greater than the upper boundary of the drift baseline interval, the sampling moment is marked as a drift deviation moment; if both exceed their respective upper boundaries, the sampling moment has both a snapshot deviation mark and a drift deviation mark. Sampling moments with at least one consecutive deviation mark are merged according to the order of sampling moments to form a deviation time series segment. "Consecutive" means that there are no sampling records without deviation marks between two adjacent sampling moments; once a sampling record without a deviation mark appears, the current deviation time series segment ends, and a new deviation time series segment is formed when a deviation mark appears again. Therefore, the deviation time series segment can accurately represent the continuous spillover process of the risk characteristic sequence relative to the dynamic risk baseline.

[0088] Secondly, based on the order of occurrence of the snapshot retention transmission coefficient deviation and the execution plan drift aggregation degree deviation in the deviation time sequence segment, the corresponding retention transmission segment and plan drift group are reverse-associated to obtain the deviation source chain. Specifically, for each snapshot deviation time in the deviation time sequence segment, the snapshot retention transmission coefficient generation record corresponding to that sampling time is read, and the retention transmission segment identifier that forms the coefficient is obtained from the generation record; for each drift deviation time, the execution plan drift aggregation degree generation record corresponding to that sampling time is read, and the plan drift group identifier that forms the aggregation degree is obtained from the generation record. Subsequently, according to the order of the deviation time, the retention transmission segment identifier and the plan drift group identifier are arranged into a chain record to obtain the deviation source chain.

[0089] In this embodiment, the deviation source chain includes at least the deviation start sampling time, the deviation end sampling time, the deviation type order, the lingering transmission segment, and the planned drift group. The deviation type order indicates whether the snapshot deviation occurs first, the drift deviation occurs first, or both occur simultaneously. The lingering transmission segment retains its start level, end level, number of consecutive samples, and the instance it belongs to; the planned drift group retains its statistical information time sequence marker, drift SQL identifier set, number of recurrences of drift SQL, and the data tables involved. Through this reverse association, the deviation judgment result no longer remains at the numerical level, but is restored to the lock wait link level and business SQL aggregation source that caused the deviation.

[0090] The implicit risk value is generated based on the deviation from the source chain. First, determine the number of consecutive deviation sampling moments in the deviation from the source chain, denoted as 'a', where 'a' is the total number of sampling moments within the deviation time segment. Next, determine the lock wait link levels covered by the stagnant transmission segment in the deviation from the source chain, denoted as 'b', where 'b' is the number of levels contained in the stagnant transmission segment from the start level to the end level; when there is no stagnant transmission segment in the deviation from the source chain, 'b' is 0. Then, determine the number of times the drift SQL recurs within the planned drift group, denoted as 'c', where 'c' is the sum of the number of times each drift SQL in the planned drift group occurs within the deviation time segment; when there is no planned drift group in the deviation from the source chain, 'c' is 0. The implicit risk value is denoted as H, and its calculation expression is: H = a × (b + c). Where 'a' represents the persistence of risk deviation, 'b' represents the range of transaction stagnation spreading to the lock wait link, and 'c' represents the degree of repetitive aggregation of execution plan drift in the business SQL. This calculation method ensures that the same single deviation is not amplified, while continuous deviation, link level expansion, and repeated occurrence of drifting SQL will collectively increase the hidden risk value.

[0091] Finally, the risk category and scope of impact are determined based on the implicit risk value and the order of deviation types. When the snapshot deviation in the deviation source chain is earlier than the drift deviation, or when only the snapshot deviation exists, the risk category is determined as transaction stagnation synchronization propagation risk, and the instance where the corresponding stagnation propagation segment is located is determined as the affected instance; if a planned drift group also exists in the deviation source chain, the data tables commonly involved in the planned drift group are determined as the affected business tables, and the drift SQL in them is determined as the affected SQL.

[0092] When the drift deviation in the source chain occurs earlier than the snapshot deviation, or when only the drift deviation exists, the risk category is determined as statistical mismatch query aggregation risk, and the data tables commonly involved in the planned drift group are determined as affected business tables, and the drift SQL in the planned drift group is determined as affected SQL; if there is also a stagnant transmission segment in the source chain, the instance where the stagnant transmission segment is located is determined as the affected instance.

[0093] Therefore, while generating risk categories, the affected instances, affected business tables, and affected SQL statements can be output, so that subsequent health monitoring reports can be directly linked to specific objects requiring action.

[0094] A health monitoring report is generated based on the risk category and the scope of the risk impact, and the risk level, cause explanation and treatment recommendations in the health monitoring report are adjusted in real time according to the changes in the hidden risk value.

[0095] In one possible implementation of the present invention, firstly, the corresponding report paragraph skeleton is invoked according to the risk category. The report paragraph skeleton pre-sets field positions according to the risk category, including at least the risk category position, risk level position, affected object position, cause description position, handling suggestion position, and update time position. When the risk category is transaction delay and synchronous spread risk, the report paragraph skeleton corresponding to transaction delay and synchronous spread risk is selected; when the risk category is statistical mismatch query aggregation risk, the report paragraph skeleton corresponding to statistical mismatch query aggregation risk is selected. Subsequently, the affected instances obtained in the previous step are written to the instance field in the affected object position, the affected business tables are written to the business table field in the affected object position, and the affected SQL is written to the SQL field in the affected object position. If a field does not have a corresponding object in the previous step, the field is written as "No corresponding affected object" to ensure the report fields are complete and to avoid introducing manually entered content. After completing the above writing, an initial health monitoring report is obtained.

[0096] The direction of change of latent risk values ​​at consecutive sampling times is bound to the deviation from the source chain to obtain the risk evolution trajectory. For the i-th sampling time in the current deviation time segment, the latent risk value H_i at that sampling time is read, and the latent risk value H_i-1 at the previous sampling time is read, and the difference ΔH_i=H_i-H_i-1 is calculated.

[0097] If ΔH_i is greater than 0, the direction of change of the latent risk value at that sampling time is recorded as increasing; if ΔH_i is equal to 0, it is recorded as unchanged.

[0098] If ΔH_i is less than 0, it is recorded as a decrease.

[0099] The judgment is based solely on the relationship between the latent risk values ​​at adjacent sampling times, without setting any additional thresholds. The design principle is that the latent risk value is generated by the number of consecutive deviations from the sampling time, the lock waiting link level, and the number of repeated occurrences of drift SQL. The difference between adjacent values ​​can directly reflect whether the risk accumulation continues to expand.

[0100] Then, the direction of change of the latent risk value at each sampling time is linked to the corresponding stagnant transmission segment or planned drift group in the deviation source chain at that sampling time, forming a risk evolution trajectory. The risk evolution trajectory includes the sampling time, latent risk value, direction of change, stagnant transmission segment identifier, planned drift group identifier, and deviation type order. The risk level is determined based on the risk evolution trajectory: when the direction of change at the latest sampling time is increasing, the risk level is determined to be rising; when the direction of change at the latest sampling time is unchanged, the risk level is determined to be maintaining; when the direction of change at the latest sampling time is decreasing, the risk level is determined to be falling. This risk level represents the real-time change of the risk state under the same deviation source chain and does not change the aforementioned risk category.

[0101] Furthermore, when the risk level changes, the reason explanation in the initial health monitoring report is replaced according to the latest occurrence of the stagnant transmission segment or planned drift group in the deviation from the source chain. If the latest sampling time is bound to a stagnant transmission segment, the instance where the stagnant transmission segment is located, the starting level, the ending level, the number of consecutive samplings, and the corresponding log write rate are read to maintain the write status and the primary / standby synchronization delay expansion status, and a reason explanation is generated: "The affected instance has a stagnant transmission segment from the first starting level to the second ending level, and the log write is maintained within the consecutive sampling time, accompanied by the primary / standby synchronization delay expansion." If the latest sampling time is bound to a planned drift group, the statistical information time series marker of the planned drift group, the data tables involved, the drift SQL, and the number of times the drift SQL is repeated are read, and a reason explanation is generated: "The affected business table is associated with multiple drift SQL under the statistical information time series marker, and the drift SQL is repeated within the deviation time series segment." The risk category and affected object in the initial health monitoring report are retained during replacement to avoid inconsistencies between the reported object and the reason explanation.

[0102] Finally, based on the replaced cause description, select appropriate handling recommendations and generate a dynamic health monitoring report. When the risk category is transaction stagnation and synchronization propagation risk, and the cause description includes a stagnation propagation segment, the handling recommendations are written as transaction release and master-slave catch-up; where transaction release corresponds to verifying the session where the snapshot stagnation started and its uncommitted transactions, and master-slave catch-up corresponds to verifying the application status of the standby database of the affected instance. When the risk category is statistical mismatch query aggregation risk, and the cause description includes a plan drift group, the handling recommendations are written as statistical information update and SQL execution plan verification; where statistical information update corresponds to the common data tables involved in the plan drift group, and SQL execution plan verification corresponds to the drifted SQL in the plan drift group. After completing the writing of the risk level, cause description, and handling recommendations, the latest sampling time is written to the update time position to obtain the dynamic health monitoring report.

[0103] Example 2, please refer to Figure 2As shown in this embodiment, the database-based intelligent risk identification and dynamic health monitoring report generation system includes:

[0104] Run the sampling data acquisition module to obtain the target database's running sampling data during the monitoring period, including transaction duration, lock wait chain, log write rate, master-slave synchronization delay, SQL execution plan changes, and table statistics update time;

[0105] The risk feature sequence generation module generates a risk feature sequence based on the running sample data. The risk feature sequence includes the snapshot retention transmission coefficient and the execution plan drift aggregation degree.

[0106] The baseline determination module identifies the business scenario type of the database based on the risk feature sequence, and determines the corresponding dynamic risk baseline based on the business scenario type.

[0107] The implicit risk calculation module calculates the implicit risk value when the risk feature sequence deviates from the dynamic risk baseline, and determines the risk category and risk impact range based on the implicit risk value. The risk impact range includes affected instances, affected business tables, and affected SQL.

[0108] The dynamic adjustment module generates a health monitoring report based on the risk category and the scope of risk impact, and adjusts the risk level, cause explanation, and treatment recommendations in the health monitoring report in real time according to the changes in the latent risk value.

[0109] The above description is merely a specific embodiment of this application, but the scope of protection of this application is not limited thereto. Any changes or substitutions that can be easily conceived by those skilled in the art within the scope of the technology disclosed in this application should be included within the scope of protection of this application.

Claims

1. A method for intelligent risk identification and dynamic health monitoring report generation based on a database, characterized in that, include: Obtain operational sampling data of the target database during the monitoring period, including transaction duration, lock wait chain, log write rate, master-slave synchronization delay, SQL execution plan changes, and table statistics update time; A risk feature sequence is generated based on the operational sampling data. The risk feature sequence includes the snapshot retention transmission coefficient and the execution plan drift aggregation degree. The business scenario type of the database is identified based on the risk feature sequence, and the corresponding dynamic risk baseline is determined based on the business scenario type. When the risk feature sequence deviates from the dynamic risk baseline, a latent risk value is calculated, and the risk category and risk impact range are determined based on the latent risk value. The risk impact range includes affected instances, affected business tables, and affected SQL statements. A health monitoring report is generated based on the risk category and the scope of the risk impact, and the risk level, cause explanation and treatment recommendations in the health monitoring report are adjusted in real time according to the changes in the hidden risk value.

2. The database-based intelligent risk identification and dynamic health monitoring report generation method according to claim 1, characterized in that, Generate snapshot retention conduction coefficients, including: The active transaction with the longest transaction duration within the same sampling time is used as the lock wait link backtracking entry point. The lock wait link is used to search in reverse for a session that does not have a previous level blocking session, and this session is determined as the snapshot retention starting point. Starting from the snapshot retention point, read the link nodes step by step along the blocking direction of the lock wait link, and append the log write rate and master-slave synchronization delay of adjacent sampling times to the corresponding link nodes; When the log write rate remains greater than 0 and the master-slave synchronization delay extends and expands along the lock wait link, the corresponding link segment is identified as the stagnant transmission segment, and a snapshot stagnant transmission coefficient is generated based on the number of consecutive samples of the stagnant transmission segment and the covered lock wait link level.

3. The database intelligent risk identification and dynamic health monitoring report generation method according to claim 1, characterized in that, Generate execution plan drift aggregation degree, including: Based on the data table names and table statistics update times involved in the business SQL, statistical information time-series markers are generated, and the statistical information time-series markers are bound to the corresponding SQL execution plan changes to form a plan change record; Within a continuous sampling period, business SQL statements with execution plan changes greater than 0 and bound to the same statistical information time series marker are identified as drift SQL statements, and drift SQL statements with the same statistical information time series marker are aggregated into plan drift groups. The execution plan drift aggregation degree is generated based on the number of different drift SQL statements within the planned drift group, the number of consecutive sampling times in the planned drift group, and the number of data tables commonly involved in the drift SQL statements.

4. The database intelligent risk identification and dynamic health monitoring report generation method according to claim 3, characterized in that, Identify the type of business scenario in which the database exists, including: The snapshot retention propagation coefficient and execution plan drift aggregation degree within the same monitoring period are aligned according to the sampling time to generate a dual-feature time series segment, and the order of change of the snapshot retention propagation coefficient and the execution plan drift aggregation degree is marked. The consecutive sampling times where both the snapshot retention propagation coefficient and the execution plan drift aggregation degree are greater than 0 are determined as the continuous overlapping intervals, and the overlapping interval position identifier is generated according to the position of the continuous overlapping interval in the dual-feature time segment. A scenario fingerprint is generated based on the order of changes and the location identifier of the overlapping interval, and the business scenario type is determined according to the scenario fingerprint as a batch warehousing scenario, a centralized reporting scenario, or a master-slave catch-up scenario.

5. The database intelligent risk identification and dynamic health monitoring report generation method according to claim 4, characterized in that, Determine the dynamic risk baseline, including: Filter historical health sampling records to find matching records that have the same sequence of changes, the same overlapping interval location identifiers, and the same overlapping persistence identifiers as the fingerprint of the current scene; Read the snapshot retention propagation coefficient and execution plan drift aggregation degree from the matching records respectively, and determine the distribution range of the snapshot retention propagation coefficient and the distribution range of the execution plan drift aggregation degree according to the numerical order; The business scenario type, scenario fingerprint, snapshot retention transmission coefficient distribution range, and execution plan drift aggregation degree distribution range are combined to form the corresponding dynamic risk baseline.

6. The database intelligent risk identification and dynamic health monitoring report generation method according to claim 5, characterized in that, Determining whether a risk characteristic sequence deviates from the dynamic risk baseline includes: The snapshot retention conduction coefficient at each sampling moment within the current monitoring period is compared with the upper boundary of the snapshot retention conduction coefficient distribution interval, and sampling moments that exceed the upper boundary are marked as snapshot deviation moments. The execution plan drift aggregation degree at each sampling time within the current monitoring period is compared with the upper boundary of the execution plan drift aggregation degree distribution interval, and sampling times that exceed the upper boundary are marked as drift deviation times; According to the sampling time sequence, sampling times with consecutive snapshot deviation markers or drift deviation markers are merged to generate deviation time sequence segments.

7. The database intelligent risk identification and dynamic health monitoring report generation method according to claim 6, characterized in that, Calculating the implicit risk value includes: Based on the snapshot deviation time in the deviation time sequence segment, read the generation record of the corresponding snapshot retention transmission coefficient, obtain the retention transmission segment that forms the coefficient, and based on the drift deviation time, read the generation record of the corresponding execution plan drift aggregation degree, obtain the plan drift group that forms the aggregation degree; Arrange the stagnant conduction segments and planned drift groups in chronological order of deviation time to obtain the deviation source chain; Based on the number of sampling moments of consecutive deviations in the deviation source chain, the lock waiting link level covered by the lingering transmission segment, and the number of repeated occurrences of drift SQL in the planned drift group, a hidden risk value is generated.

8. The database intelligent risk identification and dynamic health monitoring report generation method according to claim 7, characterized in that, Determine the risk categories and the scope of their impact, including: When the snapshot deviation in the deviation source chain is earlier than the drift deviation or only the snapshot deviation exists, the risk category is determined to be transaction retention synchronization propagation risk, and the instance where the retention propagation segment is located is determined to be the affected instance; When the drift deviation in the deviation source chain is earlier than the snapshot deviation or only the drift deviation exists, the risk category is determined as statistical mismatch query aggregation risk, and the common data table and drift SQL in the planned drift group are respectively determined as the affected business table and the affected SQL. When both a stagnant transmission segment and a planned drift group exist in the same off-source chain, the instance containing the stagnant transmission segment, the common data table in the planned drift group, and the drift SQL are all written into the risk impact scope.

9. The database intelligent risk identification and dynamic health monitoring report generation method according to claim 8, characterized in that, Generate and dynamically adjust health monitoring reports, including: Select the corresponding report paragraph skeleton according to the risk category, and write the affected instance, affected business table and affected SQL into the affected object position in the report paragraph skeleton to generate an initial health monitoring report; The direction of change of latent risk values ​​at adjacent sampling times is linked to the stagnant transmission segment or planned drift group in the deviation source chain to form a risk evolution trajectory, and the risk level is determined as rising, maintaining or falling based on the direction of change at the latest sampling time. When the risk level changes, the corresponding handling suggestions are written according to the explanation of the reason for replacing the latest bound delayed transmission segment or planned drift group, and the risk of transaction delay synchronization diffusion or statistical mismatch query aggregation is written, and a dynamic health monitoring report is generated.

10. A database-based intelligent risk identification and dynamic health monitoring report generation system, used to implement the database-based intelligent risk identification and dynamic health monitoring report generation method according to any one of claims 1-9, characterized in that, include: Run the sampling data acquisition module to obtain the target database's running sampling data during the monitoring period, including transaction duration, lock wait chain, log write rate, master-slave synchronization delay, SQL execution plan changes, and table statistics update time; The risk feature sequence generation module generates a risk feature sequence based on the running sample data. The risk feature sequence includes the snapshot retention transmission coefficient and the execution plan drift aggregation degree. The baseline determination module identifies the business scenario type of the database based on the risk feature sequence, and determines the corresponding dynamic risk baseline based on the business scenario type. The implicit risk calculation module calculates the implicit risk value when the risk feature sequence deviates from the dynamic risk baseline, and determines the risk category and risk impact range based on the implicit risk value. The risk impact range includes affected instances, affected business tables, and affected SQL. The dynamic adjustment module generates a health monitoring report based on the risk category and the scope of risk impact, and adjusts the risk level, cause explanation, and treatment recommendations in the health monitoring report in real time according to the changes in the latent risk value.

Images