TOP SQL data intelligent management method and system based on database

Abstract

The present invention discloses a TOP SQL data intelligent management method and system based on a database, including: monitoring the database and collecting TOP SQL from the application configuration file of the database; clustering the collected TOP SQL according to the accumulation of time; storing the clustered TOP SQL data in a baseline table according to preset indicators; cleaning the data in the baseline table to identify new TOP SQL and decayed TOP SQL. The present invention can quickly identify new TOP SQL and decayed TOP SQL, and can configure different thresholds according to different database services to identify new TOP SQL and decayed TOP SQL, and the data storage structure generated by the system is reasonably designed, and has a perfect data cleaning mechanism to ensure the sustainable and stable operation of the business.

Description

Technical Field

[0001] The present invention belongs to the technical field of databases and relates to a TOP SQL data intelligent management method and system based on a database. Background Art

[0002] Databases are core components of many business systems. During daily operations and maintenance, users are very concerned about changes in TOP SQL. Generally, the TOP SQL at the same time of day should be the same. When the TOP SQL rankings change, it needs to attract the attention of the DBA. However, there are generally a large number of databases in customer systems, and different databases carry different businesses. Because of different load or performance requirements for databases, it is also very important to support different TOP SQL analyses for different databases. At present, the solution for Oracle performance analysis is generally to check the AWR performance report, but the performance report has many data indicators, and it is time-consuming and labor-intensive to search manually, and it is difficult to quickly find problematic SQL. Summary of the invention

[0003] The purpose of the present invention is to solve the problem in the prior art that it is time-consuming and laborious to manually search for performance report data and it is difficult to quickly find problematic SQL, and to provide a database-based TOP SQL data intelligent management method and system.

[0004] In order to achieve the above object, the present invention adopts the following technical solutions:

[0005] The TOP SQL data intelligent management method based on the database includes:

[0006] Monitor the database and collect the top SQL from the database application configuration file;

[0007] Cluster the collected TOP SQLs according to the accumulation of time;

[0008] Store the clustered TOP SQL data in the baseline table according to the preset indicators;

[0009] Clean the data in the baseline table and identify new TOP SQL and decaying TOP SQL.

[0010] A further improvement of the present invention is:

[0011] Furthermore, the database is monitored, and the top SQL is collected from the application configuration file of the database. Specifically, a fixed time frequency is set in the application configuration file to collect the top N top SQLs.

[0012] Furthermore, the collected TOP SQL is clustered according to the accumulation of time. Specifically, the TOP SQL collected according to a fixed time frequency is stored, and the stored TOP SQL is aggregated according to multiples of the time frequency to obtain aggregated data recorded in different time lengths, which are named as: tbl_xxxxx_sqlstats1, tbl_xxxxx_sqlstats10, tbl_xxxxx_sqlstats60, etc., where xxxxx represents the unique identification ID of the database instance; the unique identification ID of the same database instance is stored in the same table.

[0013] Furthermore, the clustered TOP SQL data is stored in a baseline table according to preset indicators, specifically: the preset indicators identified in the TOP SQL of the fixed-duration record aggregated data are stored in the baseline table, and the key indicators include the unique identifier sql_id of the SQL statement, the unique identifier con_id of the session, the cumulative value total, the row number rn, the number of logical reads buffer_gets obtained from the database buffer cache, the number of executions of the SQL statement executions, the total time consumed by the SQL statement execution elapsed_time, the hash value plan_hash_value of the SQL execution plan, and the collection time and mintop; the mintop is the record with the most occurrences within each 10 minutes.

[0014] Furthermore, before cleaning the data in the baseline table, it also includes: setting a baseline historical data days threshold, if the data in the baseline table does not exceed the set baseline historical data days threshold, then the data in the baseline table is not cleaned.

[0015] Furthermore, the data in the baseline table is cleaned, specifically:

[0016] Step 1: Set the total number threshold for SQL execution when querying data with new TOP SQL and the total number threshold for SQL execution when querying data with decaying TOP SQL;

[0017] Step 2: When the data in the baseline table exceeds the set baseline historical data days threshold, determine whether there is a TOP SQL performance indicator data within the specified time period that is greater than the total number threshold of SQL execution when the decay TOP SQL query data is executed. If so, proceed to step 3.

[0018] Step 3: Continue to query the performance indicator data of buffer_gets, executions, elapsed_time, and plan_hash_value according to the sql_id list queried in step 2, process the indicator result data, remove the records with non-existent values, and group the remaining data according to sql_id, plan_hash_value, and con_id. After grouping, calculate the difference between every two records and sum the differences to obtain the TOP SQL performance indicator data after processing in the specified time period;

[0019] Step 4: Get the hour range corresponding to the specified time period, query the baseline data of the same period, and compare;

[0020] Step 5: Based on the sql_id list queried in step 2 and the hour range obtained in step 4 as parameters, query the important performance indicator data of the same period in the baseline, sql_id, con_id, total, rn, buffer_gets, executions, elapsed_time, plan_hash_value, collection time and mintop, buffer_gets_perexe, elapsed_time_perexe. If the baseline data exists, proceed to the next step. Otherwise, there is no new TOP SQL / decayed TOP SQL.

[0021] Step 6: Compare the sql_id plus con_id of the top SQL in the specified time period with the baseline data. If the sql_id plus con_id does not exist in the baseline and the number of times the sql appears is greater than the total number threshold of SQL execution when setting the new top SQL query data, it means that the sql_id plus con_id is a new top SQL. Otherwise, proceed to the next step.

[0022] Step 7: If the decayed TOP SQL is indicated in step 6, then calculate percent90BufferGets and percent90ElapsedTime under the same sql_id plus con_id to obtain the performance of the SQL statement in most cases, including the demand for buffers and the time required for execution; percent90BufferGets is an indicator to measure the buffer acquisition operation of SQL statements in the database; among all sessions that execute the SQL statement, 90% of the sessions obtain no more than the corresponding specific value of the buffer; percent90ElapsedTime is another performance analysis indicator that focuses on the execution time of the SQL statement; this indicator means that among all sessions that execute the SQL statement, 90% of the sessions have an execution time that does not exceed the corresponding specific value;

[0023] Step 8: Compare the execution plan plan_hash_value of the TOP SQL in the specified time period with the execution plan in the baseline data. If the execution plan does not exist in the baseline, it means that the execution plan has changed. Return the list of changed execution plans.

[0024] Furthermore, the new TOP SQL and decaying TOP SQL are identified, including:

[0025] According to the business situation of each database, it supports configuring threshold rules, the configuration is as follows:

[0026] Whether to enable TOP SQL change monitoring. If it is disabled, new TOP SQL / decayed TOP SQL identification will not be performed;

[0027] Set the time period. If the time period is not set, new TOP SQL / decayed TOP SQL will not be identified.

[0028] Set the TOP SQL analysis range and analyze the changes of the TOP SQL within the set ranking;

[0029] Set the TOP SQL analysis period, which indicates the data range of the TOP SQL calculation baseline;

[0030] The TOP SQL change threshold setting includes two conditions:

[0031] Condition 1: Determine whether the database load percentage threshold, Gets per Exec (s) growth percentage threshold, and growth amount threshold are greater than their respective set thresholds;

[0032] The database load percentage threshold means that the database load must be greater than the current setting before the new TOPSQL and decaying TOP SQL are identified. Otherwise, the database is considered to be in good condition and does not need attention.

[0033] The Gets per Exec (s) growth percentage threshold means that if the growth rate of TOP SQL within the specified time range is greater than the set threshold, decay is judged to have occurred;

[0034] The growth threshold means that if the growth is greater than the set threshold, decay is judged to have occurred;

[0035] Condition 2: Determine whether the Elapsed Time per Exec (s) growth percentage threshold, growth value (s) threshold, and execution plan change exceed their respective threshold conditions;

[0036] The Elapsed Time per Exec (s) growth percentage threshold indicates that if the execution time increase of the TOP SQL within the specified time range is greater than the set threshold, decay occurs;

[0037] The growth value (s) threshold indicates that if the growth of the execution time is greater than the set threshold, decay is judged to have occurred;

[0038] The execution plan changes, which means that when the execution plan of the TOP SQL changes, the decay is judged to have occurred.

[0039] Furthermore, the database load percentage is calculated as follows:

[0040] DB Time / ElapsedTime / CPU core number* 100%

[0041] The calculation method of the Gets per Exec growth is:

[0042] buffer_gets_perexe - percent90BufferGets

[0043] The calculation method of the Gets per Exec increase is:

[0044] (buffer_gets_perexe - percent90BufferGets) / percent90BufferGets *100%

[0045] The calculation method of the Elapsed Time per Exec growth is:

[0046] elapsed_time - percent90BufferGets

[0047] The calculation method of the Elapsed Time per Exec increase is:

[0048] (elapsed_time - percent90BufferGets) * 100%

[0049] Among them, DB Time is the database time; Elapsed Time is the total time from the start of the database operation to the completion of the operation; buffer_gets_perexe is the number of buffer gets of the database during each execution.

[0050] Furthermore, after identifying the new TOP SQL and decayed TOP SQL, it also includes: cleaning up expired data; the cleaning up expired data includes: setting the number of days for retaining TOP SQL baseline data, the start time of the cleaning task, the end time of the situation task, the number of concurrent cleaning instances, the number of retries when a single instance fails to clean up, the retry interval, and the retention time of sqlstats, sqltext, and sqlplan data; when the instruction to clean up expired data meets the set conditions, the expired data is cleaned up.

[0051] TOP SQL data intelligent management system based on database, including:

[0052] A collection module, wherein the collection module monitors the database and collects TOP SQL from the application configuration file of the database;

[0053] A clustering module, which clusters the collected TOP SQLs according to the accumulation of time;

[0054] A storage module, wherein the storage module stores the clustered TOP SQL data in a baseline table according to preset indicators;

[0055] A cleaning module cleans the data in the baseline table and identifies new TOP SQL and decayed TOP SQL.

[0056] Compared with the prior art, the present invention has the following beneficial effects:

[0057] The present invention can quickly identify new TOP SQL and decayed TOP SQL by judging whether TOP SQL meets the threshold conditions configured in the baseline table. At the same time, different thresholds can be configured according to different database services to identify new TOP SQL and decayed TOP SQL. The data storage structure generated by the system is reasonably designed and has a complete data cleaning mechanism to ensure sustainable and stable operation of the business. BRIEF DESCRIPTION OF THE DRAWINGS

[0058] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings required for use in the embodiments are briefly introduced below. It should be understood that the following drawings only show certain embodiments of the present invention and therefore should not be regarded as limiting the scope. For ordinary technicians in this field, other related drawings can be obtained based on these drawings without creative work.

[0059] Figure 1 It is a flow chart of the TOP SQL data intelligent management method based on database of the present invention;

[0060] Figure 2It is a structural diagram of the TOP SQL data intelligent management system based on database of the present invention;

[0061] Figure 3 This is a schematic diagram of the judgment process of the new TOP SQL / decayed TOP SQL;

[0062] Figure 4 A schematic diagram for setting the TOP SQL change threshold. DETAILED DESCRIPTION

[0063] In order to make the purpose, technical solutions and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Generally, the components of the embodiments of the present invention described and shown in the drawings here can be arranged and designed in various different configurations.

[0064] Therefore, the following detailed description of the embodiments of the present invention provided in the accompanying drawings is not intended to limit the scope of the invention claimed for protection, but merely represents selected embodiments of the present invention. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0065] It should be noted that similar reference numerals and letters denote similar items in the following drawings, and therefore, once an item is defined in one drawing, further definition and explanation thereof is not required in subsequent drawings.

[0066] In the description of the embodiments of the present invention, it should be noted that if the terms "upper", "lower", "horizontal", "inner", etc. indicate an orientation or positional relationship based on the orientation or positional relationship shown in the drawings, or the orientation or positional relationship in which the product of the invention is usually placed when in use, it is only for the convenience of describing the present invention and simplifying the description, and does not indicate or imply that the device or element referred to must have a specific orientation, be constructed and operated in a specific orientation, and therefore cannot be understood as a limitation on the present invention. In addition, the terms "first", "second", etc. are only used to distinguish the description, and cannot be understood as indicating or implying relative importance.

[0067] In addition, if the term "horizontal" appears, it does not mean that the component must be absolutely horizontal, but can be slightly tilted. For example, "horizontal" only means that its direction is more horizontal than "vertical", which does not mean that the structure must be completely horizontal, but can be slightly tilted.

[0068] In the description of the embodiments of the present invention, it is also necessary to explain that, unless otherwise clearly specified and limited, the terms "set", "install", "connect", and "connect" should be understood in a broad sense, for example, it can be a fixed connection, a detachable connection, or an integral connection; it can be a mechanical connection or an electrical connection; it can be a direct connection, or it can be indirectly connected through an intermediate medium, or it can be the internal connection of two components. For ordinary technicians in this field, the specific meanings of the above terms in the present invention can be understood according to specific circumstances.

[0069] The present invention is further described in detail below in conjunction with the accompanying drawings:

[0070] See also Figure 1 The present invention discloses a TOP SQL data intelligent management method based on a database, comprising:

[0071] S101: monitor the database and collect the top SQL from the application configuration file of the database;

[0072] Set a fixed time frequency in the application configuration file to collect the top N TOP SQLs.

[0073] S102: clustering the collected TOP SQLs according to the accumulation of time;

[0074] The TOP SQL collected at a fixed time frequency is stored, and the stored TOP SQL is aggregated according to multiples of the time frequency to obtain aggregated data recorded in different time lengths, which are named as: tbl_xxxxx_sqlstats1, tbl_xxxxx_sqlstats10, tbl_xxxxx_sqlstats60, etc., where xxxxx represents the unique identification ID of the database instance; the unique identification ID of the same database instance is stored in the same table.

[0075] S103: storing the clustered TOP SQL data in a baseline table according to preset indicators;

[0076] The preset indicators identified in the TOP SQL of the fixed-duration record aggregation data are stored in the baseline table. The key indicators include the unique identifier of the SQL statement sql_id, the unique identifier of the session con_id, the cumulative value total, the row number rn, the number of logical reads obtained from the database buffer cache buffer_gets, the number of SQL statement executions executions, the total execution time of the SQL statement elapsed_time, the hash value of the SQL execution plan plan_hash_value, the collection time and mintop.

[0077] S104: Clean the data in the baseline table and identify new TOP SQL and decayed TOP SQL.

[0078] Before cleaning the data in the baseline table, the method further includes: setting a baseline historical data days threshold; if the data in the baseline table does not exceed the set baseline historical data days threshold, the data in the baseline table is not cleaned.

[0079] Clean the data in the baseline table, specifically:

[0080] Step 1: Set the total number threshold for SQL execution when querying data with new TOP SQL and the total number threshold for SQL execution when querying data with decaying TOP SQL;

[0081] Step 2: When the data in the baseline table exceeds the set baseline historical data days threshold, determine whether there is a TOP SQL performance indicator data within the specified time period that is greater than the total number threshold of SQL execution when the decay TOP SQL query data is executed. If so, proceed to step 3.

[0082] Step 3: Continue to query the performance indicator data of buffer_gets, executions, elapsed_time, and plan_hash_value according to the sql_id list queried in step 2, process the indicator result data, remove the records with non-existent values, and group the remaining data according to sql_id, plan_hash_value, and con_id. After grouping, calculate the difference between every two records and sum the differences to obtain the TOP SQL performance indicator data after processing in the specified time period;

[0083] Step 4: Get the hour range corresponding to the specified time period, query the baseline data of the same period, and compare;

[0084] Step 5: Based on the sql_id list queried in step 2 and the hour range obtained in step 4 as parameters, query the important performance indicator data of the same period in the baseline, sql_id, con_id, total, rn, buffer_gets, executions, elapsed_time, plan_hash_value, collection time and mintop, buffer_gets_perexe, elapsed_time_perexe. If the baseline data exists, proceed to the next step. Otherwise, there is no new TOP SQL / decayed TOP SQL.

[0085] Step 6: Compare the sql_id plus con_id of the top SQL in the specified time period with the baseline data. If the sql_id plus con_id does not exist in the baseline and the number of times the sql appears is greater than the total number threshold of SQL execution when setting the new top SQL query data, it means that the sql_id plus con_id is a new top SQL. Otherwise, proceed to the next step.

[0086] Step 7: If the decayed TOP SQL is indicated in step 6, then calculate percent90BufferGets and percent90ElapsedTime under the same sql_id plus con_id to obtain the performance of the SQL statement in most cases, including the demand for buffers and the time required for execution; percent90BufferGets is an indicator to measure the buffer acquisition operation of SQL statements in the database; among all sessions that execute the SQL statement, 90% of the sessions obtain no more than the corresponding specific value of the buffer; percent90ElapsedTime is another performance analysis indicator that focuses on the execution time of the SQL statement; this indicator means that among all sessions that execute the SQL statement, 90% of the sessions have an execution time that does not exceed the corresponding specific value;

[0087] Step 8: Compare the execution plan plan_hash_value of the TOP SQL in the specified time period with the execution plan in the baseline data. If the execution plan does not exist in the baseline, it means that the execution plan has changed. Return the list of changed execution plans.

[0088] Identify new TOP SQL and decaying TOP SQL, including:

[0089] According to the business situation of each database, it supports configuring threshold rules, the configuration is as follows:

[0090] Whether to enable TOP SQL change monitoring. If it is disabled, new TOP SQL / decayed TOP SQL identification will not be performed;

[0091] Set the time period. If the time period is not set, new TOP SQL / decayed TOP SQL will not be identified.

[0092] Set the TOP SQL analysis range and analyze the changes of the TOP SQL within the set ranking;

[0093] Set the TOP SQL analysis period, which indicates the data range of the TOP SQL calculation baseline;

[0094] The TOP SQL change threshold setting includes two conditions:

[0095] Condition 1: determine whether the database load percentage threshold, Gets per Exec (s) growth percentage threshold, and growth amount threshold are greater than their respective set thresholds;

[0096] The database load percentage threshold means that the database load must be greater than the current setting before the new TOPSQL and decaying TOP SQL are identified. Otherwise, the database is considered to be in good condition and does not need attention.

[0097] The Gets per Exec (s) growth percentage threshold means that if the growth rate of TOP SQL within the specified time range is greater than the set threshold, decay is judged to have occurred;

[0098] The growth threshold means that if the growth is greater than the set threshold, decay is judged to have occurred;

[0099] Condition 2: Determine whether the Elapsed Time per Exec (s) growth percentage threshold, growth value (s) threshold, and execution plan change exceed their respective threshold conditions;

[0100] The Elapsed Time per Exec (s) growth percentage threshold indicates that if the execution time increase of the TOP SQL within the specified time range is greater than the set threshold, decay occurs;

[0101] The growth value (s) threshold indicates that if the growth of the execution time is greater than the set threshold, decay is judged to have occurred;

[0102] The execution plan changes, which means that when the execution plan of the TOP SQL changes, the decay is judged to have occurred.

[0103] The database load percentage is calculated as:

[0104] DB Time / ElapsedTime / CPU core number* 100%

[0105] The calculation method of the Gets per Exec growth is:

[0106] buffer_gets_perexe - percent90BufferGets

[0107] The calculation method of the Gets per Exec increase is:

[0108] (buffer_gets_perexe - percent90BufferGets) / percent90BufferGets *100%

[0109] The calculation method of the Elapsed Time per Exec growth is:

[0110] elapsed_time - percent90BufferGets

[0111] The calculation method of the Elapsed Time per Exec increase is:

[0112] (elapsed_time - percent90BufferGets) * 100%

[0113] Among them, DB Time is the database time; Elapsed Time is the total time from the start of the database operation to the completion of the operation; buffer_gets_perexe is the number of buffer gets of the database during each execution.

[0114] After identifying new TOP SQL and decayed TOP SQL, it also includes: cleaning up expired data; the cleaning up expired data includes: setting the number of days to retain TOP SQL baseline data, the start time of the cleaning task, the end time of the situation task, the number of concurrent cleaning instances, the number of retries when a single instance fails to clean up, the retry interval, and the retention time of sqlstats, sqltext, and sqlplan data; when the instruction to clean up expired data meets the set conditions, the expired data is cleaned up.

[0115] New Top SQL refers to SQL statements that appear for the first time in a performance analysis period and show high resource consumption, which may be caused by improper code or data access before optimization. Decaying Top SQL usually refers to a SQL statement whose SQL performance decays over time, such as an increase in execution time or average logical read, or a deterioration in execution plan.

[0116] See also Figure 2 The present invention discloses a TOP SQL data intelligent management system based on a database, comprising:

[0117] A collection module, wherein the collection module monitors the database and collects TOP SQL from the application configuration file of the database;

[0118] A clustering module, which clusters the collected TOP SQLs according to the accumulation of time;

[0119] A storage module, wherein the storage module stores the clustered TOP SQL data in a baseline table according to preset indicators;

[0120] A cleaning module cleans the data in the baseline table and identifies new TOP SQL and decayed TOP SQL.

[0121] Example:

[0122] The specific technical solution adopted by the present invention is as follows:

[0123] 1. Collect TOP SQL regularly and store it

[0124] The application configuration file configures the timing collection of the top N top SQL and the timing frequency. The timing frequency and N are configurable. The default collection time is to collect the top 50 top SQL every 30 seconds. The main indicators collected include ash, sqlstats, execution plan, etc. Since the amount of database performance data to be collected is large and the indicators are numerous, the data storage solution is:

[0125] A database instance and a metric type are stored in a table, for example, tbl_xxxxx_sqlstats, where xxxxx indicates the unique ID of the database instance.

[0126] The raw data collected in 30 seconds is stored in the database, and the data is converged every minute. It is aggregated into one record per minute, one record per 10 minutes, and one record per hour. Tables such as tbl_xxxxx_sqlstats1, tbl_xxxxx_sqlstats10, and tbl_xxxxx_sqlstats60 are generated respectively.

[0127] 2. TOP SQL preprocessing

[0128] 2.1. TOP SQL baseline data synchronization

[0129] Synchronizing baseline data means identifying some key indicators from the TOP SQL indicators of 10-minute aggregated data and storing them in the baseline table at regular intervals every day. The key indicators include the unique identifier of the SQL statement sql_id, the unique identifier of the session con_id, the cumulative value total, the row number rn, the number of logical reads obtained from the database buffer cache buffer_gets, the number of SQL statement executions executions, the total execution time of the SQL statement elapsed_time, the hash value of the SQL execution plan plan_hash_value, the collection time, and mintop.

[0130] The number of logical reads buffer_gets obtained from the database buffer cache, the number of SQL statement executions executions, the total time consumed by the SQL statement execution elapsed_time, and the cumulative value total are the sum of the unique identifier sql Id of the SQL statement aggregated within one hour.

[0131] Calculate the new indicator based on the key indicators buffer_gets, executions, and elapsed_time. Specifically, buffer_gets_perexe = buffer_gets / executions, rounded to the nearest integer.

[0132] elapsed_time_perexe=elapsed_time / executions, rounded to the nearest integer.

[0133] Mintop is the minimum ranking of the TOP SQL in one hour. The TOP SQL in one hour is counted in 6 periods of 10 minutes each. The sql_id is ranked from large to small according to the number of times it appears, and the minimum ranking of the six ranking results in the current hour is taken. Since the baseline data is aggregated into one hour at a granularity of 10 minutes, it is necessary to find the record with the most occurrences in each 10 minutes as the mintop of this SQL.

[0134] The baseline data is also stored in one table per database instance, and the table name is tbl_xxxxx_top_sql_base60.

[0135] 2.2. TOP SQL data cleaning

[0136] TOP SQL data cleaning mainly pre-processes the data and identifies whether it is a new TOP SQL / decayed TOP SQL according to the configured rules. The identification here cannot be considered as the final new TOP SQL / decayed TOP SQL, because each database instance will have some other constraint rules to filter these new TOP SQL / decayed TOP SQL according to different business loads.

[0137] The configuration rules are mainly as follows:

[0138] When querying data with the new top sql, the total number threshold of sql execution is 60; when querying data with the decaying top sql, the total number threshold of sql execution is 10; the baseline historical data day threshold requirement indicates that the baseline data must contain at least 7 days of data before data cleaning can be performed.

[0139] See also Figure 3 The method to determine whether it is the privileged TOP SQL / decayed TOP SQL is as follows:

[0140] Step 1: Determine whether there is data for more than 7 days in the baseline table. If so, proceed to the next step. Otherwise, there is no new TOP SQL / decayed TOP SQL. The purpose of this step is mainly to prevent the system from running for the first time without sufficient baseline data for comparison, which may suddenly generate multiple new TOP SQL or decayed TOP SQL.

[0141] Step 2: Check the TOP SQL performance indicator data within the specified time period to see whether there is a top SQL with a value greater than the total number threshold of SQL execution when the decaying top SQL query data is used. If so, proceed to the next step. Otherwise, there is no new TOP SQL / decaying TOP SQL.

[0142] Step 3: Continue to query performance indicator data such as buffer_gets, executions, elapsed_time, plan_hash_value, etc. according to the sql_id list queried in step 2, process the indicator result data, remove the records with non-existent values, and group the remaining data according to sql_id, plan_hash_value and con_id. After grouping, calculate the difference between every two records and sum the differences to obtain the TOP SQL performance indicator data after processing in the specified time period.

[0143] Step 4: Get the hour range corresponding to the specified time period. For example, if you query 9:10-9:20, the hour is 9. If you query 9:50-10:00, the hours are 9 and 10. The purpose is to query the baseline data of the same period for comparison.

[0144] Step 5: Based on the sql_id list queried in step 2 and the hour range obtained in step 4 as parameters, query the important performance indicator data of the same period in the baseline, sql_id, con_id, total, rn, buffer_gets, executions, elapsed_time, plan_hash_value, collection time and mintop, buffer_gets_perexe, elapsed_time_perexe. If the baseline data exists, proceed to the next step. Otherwise, there is no new TOP SQL / decayed TOP SQL.

[0145] Step 6: Compare the sql_id plus con_id of the top SQL in the specified time period with the baseline data. If the sql_id plus con_id does not exist in the baseline and the number of times the sql appears is greater than the total number threshold of SQL execution when setting the new top SQL query data, it means that the sql_id plus con_id is a new top SQL. Otherwise, proceed to the next step.

[0146] Step 7: If the decayed TOP SQL is indicated in step 6, percent90BufferGets and percent90ElapsedTime under the same sql_id plus con_id are calculated to obtain the performance of the SQL statement in most cases, including the demand for buffers and the time required for execution; percent90BufferGets is an indicator to measure the buffer acquisition operation of SQL statements in the database; among all sessions that execute the SQL statement, 90% of the sessions obtain the number of buffers that does not exceed the corresponding specific value; percent90ElapsedTime is another performance analysis indicator that focuses on the execution time of the SQL statement; this indicator indicates that among all sessions that execute the SQL statement, 90% of the sessions have an execution time that does not exceed the corresponding specific value; the majority of cases refers to 90% of the cases.

[0147] Step 8: Compare the execution plan (plan_hash_value) of the TOP SQL in the specified time period with the execution plan in the baseline data. If the execution plan does not exist in the baseline, it means that the execution plan has changed. Return the list of changed execution plans.

[0148] 3. Identification of new TOP SQL and decaying TOP SQL

[0149] According to the business situation of each database, some threshold rules can be configured. The configuration is as follows:

[0150] Determine whether to enable TOP SQL change monitoring. If it is disabled, new TOP SQL / decayed TOP SQL identification will not be performed;

[0151] Change monitoring period: Supports setting time periods, such as 00:00-08:00 or other time periods. New TOP SQL / decayed TOP SQL identification will not be performed for unset time periods.

[0152] The TOP SQL analysis range supports TOP 5-TOP 50, which means that only the TOP SQL within the set ranking will be analyzed for changes;

[0153] TOP SQL analysis period: The default range is 2-100 days, which indicates the data range of the TOP SQL calculation baseline;

[0154] See also Figure 4 , TOP SQL change threshold setting, including 2 conditions:

[0155] Condition 1: Determine whether the database load percentage threshold, Gets per Exec(s) growth percentage threshold, and growth amount threshold are greater than their respective set thresholds.

[0156] 1) The database load percentage threshold means that the database load must be greater than the current setting before the new TOP SQL and decaying TOP SQL are identified. Otherwise, the database is considered to be in good condition and does not need attention.

[0157] 2) The Gets per Exec (s) growth percentage threshold means that if the growth rate of TOP SQL within the specified time range is greater than the set threshold, decay is judged to have occurred;

[0158] 3) The growth threshold means that if the growth is greater than the set threshold, decay is judged to have occurred;

[0159] Condition 2: Determine whether the Elapsed Time per Exec (s) growth percentage threshold, growth value (s) threshold, and execution plan change exceed their respective threshold conditions

[0160] 1) The Elapsed Time per Exec (s) growth percentage threshold indicates that if the execution time increase of the TOP SQL within the specified time range is greater than the set threshold, decay occurs;

[0161] 2) Growth value (s) threshold means that if the growth of execution time is greater than the set threshold, decay is judged to have occurred;

[0162] 3) Change in execution plan means that when the execution plan of TOP SQL changes, decay is judged to have occurred.

[0163] Data cleaning is a method for identifying and calling new TOP SQL and decayed TOP SQL. The TOP SQL analysis range and TOP SQL analysis period of the above configuration information will be used as calling parameters, and other configurations are the threshold conditions for the current module to reprocess data. The cleaned data must meet the above two conditional configurations to be considered as new TOP SQL or decayed TOP SQL.

[0164] The calculation method of the configuration involved in the above configuration information is as follows

[0165] The database load percentage is calculated as:

[0166] DB Time / ElapsedTime / CPU core number* 100%

[0167] The calculation method of the Gets per Exec growth is:

[0168] buffer_gets_perexe - percent90BufferGets

[0169] The calculation method of the Gets per Exec increase is:

[0170] (buffer_gets_perexe - percent90BufferGets) / percent90BufferGets *100%

[0171] The calculation method of the Elapsed Time per Exec growth is:

[0172] elapsed_time - percent90BufferGets

[0173] The calculation method of the Elapsed Time per Exec increase is:

[0174] (elapsed_time - percent90BufferGets) * 100%

[0175] Among them, DB Time is the database time; Elapsed Time is the total time from the start of the database operation to the completion of the operation; buffer_gets_perexe is the number of buffer gets of the database during each execution.

[0176] 4. Expired data cleaning

[0177] In order to prevent the database from continuing to expand and the premature historical data from becoming less and less meaningful for performance analysis, the application configuration file configures the time for scheduled cleanup of different types of indicator data and the data retention period. After the specified time is reached, the expired historical data will be cleaned up.

[0178] The configuration for cleaning expired data is as follows: TOP SQL baseline data is retained for 100 days; sqlstats, sqltext, and sqlplan data is retained for 60 days; cleaning task start time: starts at 10 pm; situation task end time: ends at 2 am; the number of concurrent cleaning instances is 4; the number of retries when a single instance fails to clean is 3 times; the retry interval is 30 seconds; 1 minute data is retained for 1 month; 10 minutes data is retained for 2 months; 60 minutes data is retained for 6 months.

[0179] A terminal device is provided in one embodiment of the present invention. The terminal device of this embodiment includes: a processor, a memory, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, the steps in the above-mentioned method embodiments are implemented. Alternatively, when the processor executes the computer program, the functions of the modules / units in the above-mentioned device embodiments are implemented.

[0180] The computer program may be divided into one or more modules / units, and the one or more modules / units are stored in the memory and executed by the processor to accomplish the present invention.

[0181] The terminal device may be a computing device such as a desktop computer, a notebook, a PDA, a cloud server, etc. The terminal device may include, but is not limited to, a processor and a memory.

[0182] The processor can be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field-programmable gate arrays (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc.

[0183] The memory may be used to store the computer programs and / or modules, and the processor implements various functions of the terminal device by running or executing the computer programs and / or modules stored in the memory and calling the data stored in the memory.

[0184] If the module / unit integrated in the terminal device is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the present invention implements all or part of the processes in the above-mentioned embodiment method, and can also be completed by instructing the relevant hardware through a computer program. The computer program can be stored in a computer-readable storage medium. When the computer program is executed by the processor, the steps of the above-mentioned various method embodiments can be implemented. Among them, the computer program includes computer program code, and the computer program code can be in source code form, object code form, executable file or some intermediate form. The computer-readable medium may include: any entity or device capable of carrying the computer program code, recording medium, U disk, mobile hard disk, disk, optical disk, computer memory, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), electric carrier signal, telecommunication signal and software distribution medium. It should be noted that the content contained in the computer-readable medium can be appropriately increased or decreased according to the requirements of legislation and patent practice in the jurisdiction. For example, in some jurisdictions, according to legislation and patent practice, computer-readable media do not include electric carrier signals and telecommunication signals.

[0185] The above are only preferred embodiments of the present invention and are not intended to limit the present invention. For those skilled in the art, the present invention may have various modifications and variations. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present invention shall be included in the protection scope of the present invention.

Claims

1. A TOP SQL data intelligent management method based on a database, characterized in that: include: Monitor the database and collect the top SQL from the database application configuration file; Cluster the collected TOP SQLs according to the accumulation of time; Store the clustered TOP SQL data in the baseline table according to the preset indicators; Clean the data in the baseline table to identify the new TOP SQL and decayed TOP SQL; the data in the baseline table is cleaned as follows: Step 1: Set the total number threshold for SQL execution when querying data with new TOP SQL and the total number threshold for SQL execution when querying data with decaying TOP SQL; Step 2: When the data in the baseline table exceeds the set baseline historical data days threshold, determine whether there is a TOP SQL performance indicator data within the specified time period that is greater than the total number threshold of SQL execution when the decay TOP SQL query data is executed. If so, proceed to step 3. Step 3: Continue to query the performance indicator data of buffer_gets, executions, elapsed_time, and plan_hash_value according to the sql_id list queried in step 2, process the indicator result data, remove the records with non-existent values, and group the remaining data according to sql_id, plan_hash_value, and con_id. After grouping, calculate the difference between every two records and sum the differences to obtain the TOP SQL performance indicator data after processing in the specified time period; Step 4: Get the hour range corresponding to the specified time period, query the baseline data of the same period, and compare; Step 5: Based on the sql_id list queried in step 2 and the hour range obtained in step 4 as parameters, query the important performance indicator data of the same period in the baseline, sql_id, con_id, total, rn, buffer_gets, executions, elapsed_time, plan_hash_value, collection time and mintop, buffer_gets_perexe, elapsed_time_perexe. If the baseline data exists, proceed to the next step. Otherwise, there is no new TOP SQL / decayed TOP SQL. Step 6: Compare the sql_id plus con_id of the top SQL in the specified time period with the baseline data. If the sql_id plus con_id does not exist in the baseline and the number of times the sql appears is greater than the total number threshold of SQL execution when setting the new top SQL query data, it means that the sql_id plus con_id is a new top SQL. Otherwise, proceed to the next step. Step 7: If the decayed TOP SQL is indicated in step 6, then calculate percent90BufferGets and percent90ElapsedTime under the same sql_id plus con_id to obtain the performance of the SQL statement in most cases, including the demand for buffers and the time required for execution; percent90BufferGets is an indicator to measure the buffer acquisition operation of SQL statements in the database; among all sessions that execute the SQL statement, 90% of the sessions obtain no more than the corresponding specific value of the buffer; percent90ElapsedTime is another performance analysis indicator that focuses on the execution time of the SQL statement; this indicator means that among all sessions that execute the SQL statement, 90% of the sessions have an execution time that does not exceed the corresponding specific value; Step 8: Compare the execution plan plan_hash_value of the TOP SQL in the specified time period with the execution plan in the baseline data. If the execution plan does not exist in the baseline, it means that the execution plan has changed. Return the list of changed execution plans.

2. The TOP SQL data intelligent management method based on database according to claim 1 is characterized in that: The monitoring of the database and collecting the TOP SQL from the application configuration file of the database specifically includes: setting a fixed time frequency in the application configuration file to collect the top N TOP SQLs.

3. The TOP SQL data intelligent management method based on database according to claim 2 is characterized in that: The collected TOP SQL is clustered according to the accumulation of time, specifically: the TOP SQL collected according to a fixed time frequency is stored, and the stored TOP SQL is aggregated according to multiples of the time frequency to obtain aggregated data recorded in different time lengths, which are named as: tbl_xxxxx_sqlstats1, tbl_xxxxx_sqlstats10, tbl_xxxxx_sqlstats60, where xxxxx represents the unique identification ID of the database instance; the unique identification ID of the same database instance is stored in the same table.

4. The TOP SQL data intelligent management method based on database according to claim 3 is characterized in that: The clustered TOP SQL data is stored in the baseline table according to preset indicators, specifically: the preset indicators identified in the TOP SQL of the fixed time record aggregation data are stored in the baseline table, and the key indicators include the unique identifier sql_id of the SQL statement, the unique identifier con_id of the session, the cumulative value total, the row number rn, the number of logical reads buffer_gets obtained from the database buffer cache, the number of executions of the SQL statement executions, the total time consumed by the SQL statement execution elapsed_time, the hash value plan_hash_value of the SQL execution plan, and the collection time mintop; the mintop is the record with the most occurrences within each 10 minutes.

5. The TOP SQL data intelligent management method based on database according to claim 4 is characterized in that: Before cleaning the data in the baseline table, the method further includes: setting a baseline historical data days threshold; if the data in the baseline table does not exceed the set baseline historical data days threshold, the data in the baseline table is not cleaned.

6. The TOP SQL data intelligent management method based on database according to claim 5 is characterized in that: After identifying the new TOP SQL and the decayed TOP SQL, it also includes: cleaning up expired data; the cleaning up expired data includes: setting the number of days to retain the TOP SQL baseline data, the start time of the cleaning task, the end time of the situation task, the number of concurrent cleaning instances, the number of retries when a single instance fails to clean up, the retry interval, and the retention time of sqlstats, sqltext, and sqlplan data; when the instruction to clean up expired data meets the set conditions, the expired data is cleaned up.

7. The TOP SQL data intelligent management system based on database is characterized by: include: A collection module, wherein the collection module monitors the database and collects TOP SQL from the application configuration file of the database; A clustering module, which clusters the collected TOP SQLs according to the accumulation of time; A storage module, wherein the storage module stores the clustered TOP SQL data in a baseline table according to preset indicators; A cleaning module cleans the data in the baseline table and identifies new TOP SQL and decayed TOP SQL. The data in the baseline table is cleaned as follows: Step 1: Set the total number threshold for SQL execution when querying data with new TOP SQL and the total number threshold for SQL execution when querying data with decaying TOP SQL; Step 2: When the data in the baseline table exceeds the set baseline historical data days threshold, determine whether there is a TOP SQL performance indicator data within the specified time period that is greater than the total number threshold of SQL execution when the decay TOP SQL query data is executed. If so, proceed to step 3. Step 3: Continue to query the performance indicator data of buffer_gets, executions, elapsed_time, and plan_hash_value according to the sql_id list queried in step 2, process the indicator result data, remove the records with non-existent values, and group the remaining data according to sql_id, plan_hash_value, and con_id. After grouping, calculate the difference between every two records and sum the differences to obtain the TOP SQL performance indicator data after processing in the specified time period; Step 4: Get the hour range corresponding to the specified time period, query the baseline data of the same period, and compare; Step 5: Based on the sql_id list queried in step 2 and the hour range obtained in step 4 as parameters, query the important performance indicator data of the same period in the baseline, sql_id, con_id, total, rn, buffer_gets, executions, elapsed_time, plan_hash_value, collection time and mintop, buffer_gets_perexe, elapsed_time_perexe. If the baseline data exists, proceed to the next step. Otherwise, there is no new TOP SQL / decayed TOP SQL. Step 6: Compare the sql_id plus con_id of the top SQL in the specified time period with the baseline data. If the sql_id plus con_id does not exist in the baseline and the number of times the sql appears is greater than the total number threshold of SQL execution when setting the new top SQL query data, it means that the sql_id plus con_id is a new top SQL. Otherwise, proceed to the next step. Step 7: If the decayed TOP SQL is indicated in step 6, then calculate percent90BufferGets and percent90ElapsedTime under the same sql_id plus con_id to obtain the performance of the SQL statement in most cases, including the demand for buffers and the time required for execution; percent90BufferGets is an indicator to measure the buffer acquisition operation of SQL statements in the database; among all sessions that execute the SQL statement, 90% of the sessions obtain no more than the corresponding specific value of the buffer; percent90ElapsedTime is another performance analysis indicator that focuses on the execution time of the SQL statement; this indicator means that among all sessions that execute the SQL statement, 90% of the sessions have an execution time that does not exceed the corresponding specific value; Step 8: Compare the execution plan plan_hash_value of the TOP SQL in the specified time period with the execution plan in the baseline data. If the execution plan does not exist in the baseline, it means that the execution plan has changed. Return the list of changed execution plans.

Images