A method and device for detecting the execution efficiency of SQL statements

Abstract

The present disclosure provides a method and device for detecting the execution efficiency of SQL statements, which can obtain the current version execution plan and the previous version execution plan of a target SQL statement; based on the current version execution plan and the previous version execution plan, detect whether the execution efficiency of the target SQL statement has decreased; if it has decreased, obtain a first object set corresponding to the current version execution plan and a second object set corresponding to the previous version execution plan; based on the first object set and the second object set, detect alternative factors that cause the execution efficiency of the target SQL statement to decrease and output the detection result. The present disclosure can efficiently detect alternative factors that cause the execution efficiency of the target SQL statement to decrease by using the first object set corresponding to the current version execution plan of the target SQL statement with decreased execution efficiency and the second object set corresponding to the previous version execution plan, thereby ensuring the normal operation of the database.

Description

Technical Field

[0001] The present disclosure relates to the field of computer technology, and in particular to a method and device for detecting the execution efficiency of SQL statements. Background Art

[0002] During database operation and maintenance, the execution efficiency of SQL statements often decreases, resulting in long execution times, which in turn causes transaction response times to lengthen or a large number of SQL statements to be responded to accumulate, leading to exhaustion of database CPU resources.

[0003] Since the more objects an SQL statement is associated with, the more statistical information and indexes need to be checked, and the more factors that lead to reduced execution efficiency, how to efficiently detect the execution efficiency of SQL statements and determine the factors that reduce the execution efficiency of SQL statements has become a technical problem that technical personnel in this field urgently need to solve. Summary of the Invention

[0004] In view of the above problems, the present disclosure provides a method and device for detecting the execution efficiency of SQL statements to overcome or at least partially solve the above problems. The technical solution is as follows:

[0005] A method for detecting SQL statement execution efficiency, comprising:

[0006] Get the target SQL statement;

[0007] Obtain the current version execution plan and the previous version execution plan of the target SQL statement;

[0008] Based on the current version execution plan and the previous version execution plan, detecting whether the execution efficiency of the target SQL statement is reduced; if reduced, obtaining a first object set corresponding to the current version execution plan and a second object set corresponding to the previous version execution plan, wherein the first object set includes objects associated with the current version execution plan, and the second object set includes objects associated with the previous version execution plan;

[0009] Based on the first object set and the second object set, candidate factors that cause the execution efficiency of the target SQL statement to be reduced are detected and a detection result is output.

[0010] Optionally, obtaining the target SQL statement includes:

[0011] For each SQL statement in the target database: group them according to the SQL plan hash value and sort them in descending order according to the total logical read value, and obtain the first SQL statement with the highest number of orders;

[0012] For each SQL statement in the target database: group the first SQL statements according to the SQL text hash value of the first SQL statement with the largest number of executions and sort them in descending order according to the average single execution logical reads to obtain the second number of second SQL statements that are ranked first;

[0013] Deduplication is performed on the first number of the first SQL statements and the second number of the second SQL statements to obtain a third number of target SQL statements.

[0014] Optionally, detecting whether the execution efficiency of the target SQL statement is reduced based on the current version execution plan and the previous version execution plan includes:

[0015] Obtaining first execution target data when the current version execution plan is executed for the first time, the first execution target data including a first average logical read value;

[0016] Obtaining second execution target data of the previous version execution plan when it is last executed, where the second execution target data includes a second average logical read value;

[0017] The first average logical reading value is compared with the second average logical reading value to determine whether the execution efficiency of the target SQL statement is reduced.

[0018] Optionally, obtaining the first object set corresponding to the current version execution plan and the second object set corresponding to the previous version execution plan includes:

[0019] The SQL plan hash value and the SQL text hash value of the target SQL statement are used to filter out the first object set corresponding to the current version execution plan and the second object set corresponding to the previous version execution plan through the v$sql_plan view.

[0020] Optionally, the first execution target data further includes a first execution usage time, and the second execution target data further includes a second execution usage time.

[0021] Optionally, detecting, based on the first object set and the second object set, candidate factors that reduce the execution efficiency of the target SQL statement and outputting the detection result includes:

[0022] Performing a difference calculation on the first object set and the second object set to obtain a third object set and a fourth object set, wherein the third object set includes objects that exist in the first object set but not in the second object set, and the fourth object set includes objects that exist in the second object set but not in the first object set;

[0023] Performing a union calculation on the first object set and the second object set to obtain a fifth object set, wherein the fifth object set includes objects existing in the first object set or in the second object set;

[0024] Based on the first execution usage time and the second execution usage time, candidate factors for reducing the execution efficiency of the target SQL statement are determined by using the third object set, the fourth object set, and the fifth object set respectively, and a detection result is output.

[0025] Optionally, the determining, based on the first execution usage time and the second execution usage time, candidate factors for reducing the execution efficiency of the target SQL statement by using the third object set, the fourth object set, and the fifth object set respectively and outputting a detection result includes:

[0026] For any object in the third object set: obtaining the first time of creation of the object;

[0027] When the first time is greater than the second execution usage time and less than the first execution usage time, determining that the execution efficiency of the target SQL statement is reduced is an alternative factor including the object being created and outputting a detection result including object information corresponding to the object.

[0028] Optionally, the determining, based on the first execution usage time and the second execution usage time, candidate factors for reducing the execution efficiency of the target SQL statement by using the third object set, the fourth object set, and the fifth object set respectively and outputting a detection result includes:

[0029] For any object in the fourth object set: detecting whether the object exists in the target database; if not, determining a second time when the object was deleted; if so, determining a third time when the last DDL operation was performed on the object;

[0030] When the second time is greater than the second execution usage time and less than the first execution usage time, determining that the candidate factor for the reduction in execution efficiency of the target SQL statement includes deletion of the object and outputting a detection result including object information corresponding to the object;

[0031] When the third time is greater than the second execution usage time and less than the first execution usage time, determining that the candidate factor for the reduction in execution efficiency of the target SQL statement includes invalidation of the object and outputting a detection result including object information corresponding to the object.

[0032] Optionally, the determining, based on the first execution usage time and the second execution usage time, candidate factors for reducing the execution efficiency of the target SQL statement by using the third object set, the fourth object set, and the fifth object set respectively and outputting a detection result includes:

[0033] For any object in the fifth object set: obtaining a fourth time for collecting statistical information of the object;

[0034] When the fourth time, the first execution usage time, and the second execution usage time satisfy a preset size relationship condition, obtaining a statistical information change rate of the object;

[0035] Calculating a target change value corresponding to the object based on the statistical information change rate;

[0036] When the target change value corresponding to the object is greater than a first preset change threshold, determining that the candidate factor for reducing the execution efficiency of the target SQL statement includes an unstable statistical information change rate of the object and outputting a detection result including an unstable statistical information change rate of the object;

[0037] When the target change values corresponding to each object in the fifth object set are all less than the second preset change threshold, the alternative factors for determining that the execution efficiency of the target SQL statement is reduced include dynamic sampling inaccuracy and outputting the detection results of the statistical information change rate of each object in the fifth object set.

[0038] A device for detecting the execution efficiency of SQL statements, comprising: a target SQL statement obtaining unit, an execution plan obtaining unit, an execution efficiency detecting unit, an object set obtaining unit, and an efficiency reduction alternative factor detecting unit.

[0039] The target SQL statement obtaining unit is used to obtain the target SQL statement;

[0040] The execution plan obtaining unit is used to obtain the current version execution plan and the previous version execution plan of the target SQL statement;

[0041] The execution efficiency detection unit is configured to detect whether the execution efficiency of the target SQL statement is reduced based on the current version execution plan and the previous version execution plan, and if so, trigger the object set obtaining unit;

[0042] The object set obtaining unit is configured to obtain a first object set corresponding to the current version execution plan and a second object set corresponding to the previous version execution plan, wherein the first object set includes objects associated with the current version execution plan, and the second object set includes objects associated with the previous version execution plan;

[0043] The efficiency reduction candidate factor detection unit is used to detect the candidate factors that cause the execution efficiency of the target SQL statement to be reduced based on the first object set and the second object set, and output the detection result.

[0044] By means of the above technical solution, the present disclosure provides a method and device for detecting the execution efficiency of SQL statements, which can obtain a target SQL statement; obtain the current version execution plan and the previous version execution plan of the target SQL statement; based on the current version execution plan and the previous version execution plan, detect whether the execution efficiency of the target SQL statement is reduced, and if it is reduced, obtain a first object set corresponding to the current version execution plan and a second object set corresponding to the previous version execution plan, wherein the first object set includes each object associated with the current version execution plan, and the second object set includes each object associated with the previous version execution plan; based on the first object set and the second object set, detect alternative factors that cause the execution efficiency of the target SQL statement to be reduced and output the detection result. The present disclosure can efficiently detect alternative factors that cause the execution efficiency of the target SQL statement to be reduced by using the first object set corresponding to the current version execution plan of the target SQL statement with reduced execution efficiency and the second object set corresponding to the previous version execution plan, thereby facilitating timely optimization by operation and maintenance personnel to ensure the normal operation of the database.

[0045] The above description is only an overview of the technical solution of the present disclosure. In order to more clearly understand the technical means of the present disclosure, it can be implemented in accordance with the contents of the specification. In order to make the above and other purposes, features and advantages of the present disclosure more obvious and easy to understand, the specific implementation methods of the present disclosure are listed below. BRIEF DESCRIPTION OF THE DRAWINGS

[0046] Various other advantages and benefits will become apparent to those skilled in the art upon reading the detailed description of the preferred embodiment below. The accompanying drawings are for illustration purposes only and are not to be considered as limiting the present disclosure. The same reference symbols are used throughout the drawings to represent the same components. In the drawings:

[0047] Figure 1 A flow chart showing an implementation of a method for detecting SQL statement execution efficiency provided by an embodiment of the present disclosure is shown;

[0048] Figure 2A flow chart showing another implementation of the method for detecting the execution efficiency of SQL statements provided in an embodiment of the present disclosure is shown;

[0049] Figure 3 A flow chart showing another implementation of the method for detecting the execution efficiency of SQL statements provided in an embodiment of the present disclosure is shown;

[0050] Figure 4 A flowchart illustrating a specific implementation of step S500 in the SQL statement execution efficiency detection method provided by an embodiment of the present disclosure is shown;

[0051] Figure 5 A flowchart illustrating another specific implementation of step S500 in the SQL statement execution efficiency detection method provided by an embodiment of the present disclosure is shown;

[0052] Figure 6 A flowchart illustrating another specific implementation of step S500 in the SQL statement execution efficiency detection method provided by an embodiment of the present disclosure is shown;

[0053] Figure 7 A flowchart illustrating another specific implementation of step S500 in the SQL statement execution efficiency detection method provided by an embodiment of the present disclosure is shown;

[0054] Figure 8 A structural diagram of an SQL statement execution efficiency detection device provided by a disclosed embodiment is shown. DETAILED DESCRIPTION

[0055] Exemplary embodiments of the present disclosure will be described in more detail below with reference to the accompanying drawings. Although exemplary embodiments of the present disclosure are shown in the accompanying drawings, it should be understood that the present disclosure can be implemented in various forms and should not be limited by the embodiments set forth herein. Rather, these embodiments are provided to enable a more thorough understanding of the present disclosure and to fully convey the scope of the present disclosure to those skilled in the art.

[0056] like Figure 1 As shown, a flow chart of an implementation of a method for detecting the execution efficiency of SQL statements provided in an embodiment of the present disclosure is provided. The method for detecting the execution efficiency of SQL statements may include:

[0057] S100: Obtain a target SQL statement.

[0058] The target SQL statement is also called VIP SQL (very important SQL). The embodiment of the present disclosure can obtain the target SQL statement in the target database. Optionally, the target database can be an Oracle database.

[0059] Optionally, the embodiment of the present disclosure may determine an SQL statement with a high total execution overhead in the target database as a target SQL statement based on the SQL plan hash value (sql_plan_hash_value) and the total logical read value.

[0060] Optionally, the embodiment of the present disclosure may determine an SQL statement with a high single execution overhead in the target database as a target SQL statement based on the SQL text hash value (sql_id) and the average single execution logical read (buffer_gets_delta).

[0061] Optional, based on Figure 1 The method shown, such as Figure 2 As shown, a flowchart of another implementation of the method for detecting SQL statement execution efficiency provided by an embodiment of the present disclosure is shown, where step S100 may include:

[0062] S110 , for each SQL statement in the target database: group them according to the SQL plan hash value and sort them in descending order according to the total logical read value, to obtain the first SQL statement with the highest number of sorted statements.

[0063] It is understandable that the first number can be set according to needs. Optionally, the first number can be set to 10.

[0064] S120 . For each SQL statement in the target database, group the SQL statements according to the SQL text hash value of the first SQL statement with the largest number of executions and sort them in descending order according to the average single execution logical reads to obtain the second largest number of second SQL statements that are ranked first.

[0065] In the embodiment of the present disclosure, the number of executions (EXECUTIONS_TOTAL) of each first SQL statement can be obtained from dba_hist_sqlstat, and the SQL text hash values of the first SQL statements with the largest number of executions can be grouped.

[0066] It is understandable that the second number can be set according to needs. Optionally, the second number can be set to 10.

[0067] S130: De-duplicate the first number of first SQL statements and the second number of second SQL statements to obtain a third number of target SQL statements.

[0068] Optionally, the embodiment of the present disclosure may obtain the target SQL statement according to a preset SQL statement execution efficiency detection time. For example, the preset SQL statement execution efficiency detection time may be 9:00 and 15:00 every day.

[0069] The embodiment of the present disclosure can reduce the detection amount, improve detection efficiency and ensure detection accuracy by screening out SQL statements with high total execution overhead and high single execution overhead in the target database as target SQL statements.

[0070] S200: Obtain the current version execution plan and the previous version execution plan of the target SQL statement.

[0071] In the embodiment of the present disclosure, the execution plan of the target SQL statement can be obtained by using the explain keyword in MySQL.

[0072] Optionally, in the embodiment of the present disclosure, when it is detected that the execution plan of the target SQL statement has not changed, step S200 may be executed on the next target SQL statement until all the third number of target SQL statements are detected.

[0073] S300 , based on the current version execution plan and the previous version execution plan, detect whether the execution efficiency of the target SQL statement is reduced. If it is reduced, execute step S400 .

[0074] Optionally, the embodiment of the present disclosure may compare the current version execution plan with the previous version execution plan to determine whether the current version execution plan has changed relative to the previous version execution plan. If changed, further detect whether the execution efficiency of the target SQL statement has decreased based on the current version execution plan and the previous version execution plan.

[0075] Optionally, the disclosed embodiment can compare the COST value, distribution key, execution plan time, push-down function, and statistical information of the current version execution plan with that of the previous version execution plan to determine whether the current version execution plan has changed relative to the previous version execution plan, and detect whether the execution efficiency of the target SQL statement has decreased.

[0076] The disclosed embodiment can determine whether the execution efficiency of the target SQL statement under the current version execution plan is lower than that under the previous version execution plan based on the performance of the target SQL statement when the current version execution plan and the previous version execution plan are respectively executed in the target database.

[0077] Optional, based on Figure 1 The method shown, such as Figure 3 As shown, a flow chart of another implementation of the method for detecting the execution efficiency of SQL statements provided in an embodiment of the present disclosure is provided. Step S300 may include:

[0078] S310 : Obtain first execution target data when the current version execution plan is executed for the first time, where the first execution target data includes a first average logical read value.

[0079] S320: Obtain second execution target data of the last version execution plan when it is last executed, where the second execution target data includes a second average logical read value.

[0080] S330 : Compare the first average logical read value and the second average logical read value to determine whether the execution efficiency of the target SQL statement is reduced.

[0081] Optionally, in an embodiment of the present disclosure, when the first average logical read value is greater than 1.5 times the second average logical read value, it may be determined that the execution efficiency of the target SQL statement is reduced; otherwise, it may be determined that the execution efficiency of the target SQL statement is not reduced.

[0082] Optionally, in the embodiment of the present disclosure, when it is determined that the execution efficiency of the target SQL statement has not decreased, step S200 may be executed on the next target SQL statement until all the third number of target SQL statements are detected.

[0083] The average logical read value is an important indicator for judging the execution efficiency of SQL statements. The embodiment of the present disclosure can accurately judge whether the execution efficiency of the target SQL statement has decreased by the average logical read value of the target SQL statement under different versions of the execution plan, that is, whether the current version of the execution plan has deteriorated compared to the previous version of the execution plan.

[0084] Optionally, the first execution target data may further include a first execution usage time, and the second execution target data may further include a second execution usage time.

[0085] S400: Obtain a first object set corresponding to the current version execution plan and a second object set corresponding to the previous version execution plan, wherein the first object set includes objects associated with the current version execution plan, and the second object set includes objects associated with the previous version execution plan.

[0086] Optionally, the embodiment of the present disclosure may utilize the SQL plan hash value and the SQL text hash value of the target SQL statement to filter out the first object set corresponding to the current version execution plan and the second object set corresponding to the previous version execution plan through the v$sql_plan view.

[0087] Specifically, the embodiment of the present disclosure can use the SQL plan hash value and SQL text hash value of the target SQL statement as filtering conditions, and obtain the object names (object_name) involved in the current version execution plan and the previous version execution plan from the v$sql_plan view, thereby obtaining the first object set and the second object set.

[0088] S500: Detect candidate factors that reduce the execution efficiency of a target SQL statement based on the first object set and the second object set, and output a detection result.

[0089] Optional, based on Figure 3 The method shown, such as Figure 4 As shown, a flowchart of a specific implementation of step S500 in the SQL statement execution efficiency detection method provided by an embodiment of the present disclosure is shown. Step S500 may include:

[0090] S510: Perform a difference calculation on the first object set and the second object set to obtain a third object set and a fourth object set, wherein the third object set includes objects that exist in the first object set but not in the second object set, and the fourth object set includes objects that exist in the second object set but not in the first object set.

[0091] S520: Perform a union calculation on the first object set and the second object set to obtain a fifth object set, where the fifth object set includes objects that exist in the first object set or in the second object set.

[0092] S530 : Based on the first execution usage time and the second execution usage time, determine candidate factors for reducing the execution efficiency of the target SQL statement using the third object set, the fourth object set, and the fifth object set respectively, and output a detection result.

[0093] Optionally, the embodiment of the present disclosure may pre-set the parameter enable_ddl_logging of the target database to true so that the alert log records all DDL operations of the object. Wherein, DDL operations are operations such as creating, deleting, and modifying objects within the database.

[0094] Optional, based on Figure 4 The method shown, such as Figure 5 As shown, a flowchart of another specific implementation of step S500 in the SQL statement execution efficiency detection method provided by an embodiment of the present disclosure is shown, and step S530 may include:

[0095] S531a: For any object in the third object set: obtain the first time of creation of the object.

[0096] Specifically, the embodiment of the present disclosure can obtain the first creation time (CREATED) of the object in the dba_objects view.

[0097] S531b: When the first time is greater than the second execution usage time and less than the first execution usage time, determine that the candidate factor for reducing the execution efficiency of the target SQL statement includes the object being created and output a detection result including object information corresponding to the object.

[0098] The embodiment of the present disclosure detects a newly associated object between the current version execution plan and the previous version execution plan, and identifies the creation of the object as an alternative factor causing the reduction in the execution efficiency of the target SQL statement, which helps operation and maintenance personnel determine the cause of the reduction in the execution efficiency of the target SQL statement.

[0099] Optional, based on Figure 4 The method shown, such as Figure 6 As shown, a flowchart of another specific implementation of step S500 in the SQL statement execution efficiency detection method provided by an embodiment of the present disclosure is shown, and step S530 may include:

[0100] S532a. For any object in the fourth object set: check whether the object exists in the target database; if not, determine the second time when the object was deleted; if so, determine the third time of the last DDL operation on the object.

[0101] Specifically, the embodiment of the present disclosure can query the second time when the object was deleted through the alert log of the target database. Optionally, the embodiment of the present disclosure can determine the available state of the object if the object exists in the target database. If the available state is invalid, the third time (LAST_DDL_TIME) of the last DDL operation on the object can be determined in the dba_objects view.

[0102] S532b: When the second time is greater than the second execution usage time and less than the first execution usage time, determine that the candidate factor for the reduction in execution efficiency of the target SQL statement includes deletion of the object and output a detection result including object information corresponding to the object.

[0103] The embodiment of the present disclosure detects objects that are deleted from the current version of the execution plan compared to the previous version of the execution plan, and identifies the deletion of the object as an alternative factor causing the reduction in the execution efficiency of the target SQL statement, which helps operation and maintenance personnel determine the cause of the reduction in the execution efficiency of the target SQL statement.

[0104] S532c: When the third time is greater than the second execution usage time and less than the first execution usage time, determine that the candidate factor for reducing the execution efficiency of the target SQL statement includes the invalidation of the object and output a detection result including object information corresponding to the object.

[0105] The embodiment of the present disclosure detects objects that are invalid in the current version execution plan compared with the previous version execution plan, and determines the invalidation of the object as an alternative factor causing the reduction in the execution efficiency of the target SQL statement, which helps operation and maintenance personnel determine the reason for the reduction in the execution efficiency of the target SQL statement.

[0106] Optional, based on Figure 4 The method shown, such as Figure 7 As shown, a flowchart of another specific implementation of step S500 in the SQL statement execution efficiency detection method provided by an embodiment of the present disclosure is shown, and step S530 may include:

[0107] S533a. For any object in the fifth object set: obtain a fourth time for collecting statistical information of the object.

[0108] Specifically, the embodiment of the present disclosure can obtain the fourth time when the object collects statistical information (LAST_ANALYZED) through the dba_tab_statistics view or the dba_ind_statistics view.

[0109] S533b: When the fourth time, the first execution usage time, and the second execution usage time satisfy a preset size relationship condition, obtain a statistical information change rate of the object.

[0110] It is understood that the preset size relationship condition can be set according to actual needs. Optionally, the preset size relationship condition can be that the fourth time is greater than the value of the second execution usage time minus the preset invalidation time and less than the first execution usage time. Optionally, the preset invalidation time can be 5 hours from the optimizer_invalidation_period parameter.

[0111] The disclosed embodiment can obtain the statistical information change rate of an object through wri$_optstat_tab_history or wri$_optstat_ind_history. Specifically, the disclosed embodiment can obtain the current NUM_ROWS and the NUM_ROWS four times ago, and determine the statistical information change rate of the object based on the ratio of the current NUM_ROWS to the NUM_ROWS four times ago.

[0112] S533c. Calculate the target change value corresponding to the object based on the statistical information change rate.

[0113] Specifically, in the embodiment of the present disclosure, a preset value may be divided by the statistical information change rate to calculate the target change value. Optionally, the preset value may be 1.

[0114] S533d. When the target change value corresponding to the object is greater than the first preset change threshold, determine that the alternative factors for reducing the execution efficiency of the target SQL statement include the unstable statistical information change rate of the object and output a detection result including the unstable statistical information change rate of the object.

[0115] S533e. When the target change values corresponding to each object in the fifth object set are all less than the second preset change threshold, determine that the alternative factors for reducing the execution efficiency of the target SQL statement include dynamic sampling inaccuracy and output the detection results of the statistical information change rate of each object in the fifth object set.

[0116] Optionally, the first preset change rate value may be 1.5. Optionally, the second preset change threshold value may be 1.

[0117] Optionally, the detection result may also include optimization suggestions corresponding to each candidate factor.

[0118] Optionally, the embodiment of the present disclosure can output the test results after testing the execution efficiency of each target SQL statement to a specific file, so that the test results can be displayed through the test result viewing interface provided by the web platform, which is convenient for the database administrator (Database Administrator, DBA) to view.

[0119] The present disclosure provides a method for detecting the execution efficiency of SQL statements, which can obtain a target SQL statement; obtain a current version execution plan and a previous version execution plan of the target SQL statement; based on the current version execution plan and the previous version execution plan, detect whether the execution efficiency of the target SQL statement is reduced, and if it is reduced, obtain a first object set corresponding to the current version execution plan and a second object set corresponding to the previous version execution plan, wherein the first object set includes each object associated with the current version execution plan, and the second object set includes each object associated with the previous version execution plan; based on the first object set and the second object set, detect alternative factors that cause the execution efficiency of the target SQL statement to be reduced and output a detection result. The present disclosure can efficiently detect alternative factors that cause the execution efficiency of the target SQL statement to be reduced by using the first object set corresponding to the current version execution plan of the target SQL statement with reduced execution efficiency and the second object set corresponding to the previous version execution plan, thereby facilitating timely optimization by operation and maintenance personnel to ensure the normal operation of the database.

[0120] Although the operations are depicted in a particular order, this should not be understood as requiring that the operations be performed in the particular order shown or in a sequential order.Multitasking and parallel processing may be advantageous under certain circumstances.

[0121] It should be understood that the various steps described in the method embodiments of the present disclosure may be performed in different orders and / or in parallel. In addition, the method embodiments may include additional steps and / or omit the steps shown. The scope of the present disclosure is not limited in this respect.

[0122] Corresponding to the above method embodiment, the embodiment of the present disclosure also provides a SQL statement execution efficiency detection device, whose structure is as follows: Figure 8 As shown, it may include: a target SQL statement obtaining unit 100 , an execution plan obtaining unit 200 , an execution efficiency detecting unit 300 , an object set obtaining unit 400 , and an efficiency reduction alternative factor detecting unit 500 .

[0123] The target SQL statement obtaining unit 100 is used to obtain a target SQL statement.

[0124] The execution plan obtaining unit 200 is used to obtain the current version execution plan and the previous version execution plan of the target SQL statement.

[0125] The execution efficiency detection unit 300 is used to detect whether the execution efficiency of the target SQL statement is reduced based on the current version execution plan and the previous version execution plan. If it is reduced, the object set acquisition unit 400 is triggered.

[0126] The object set obtaining unit 400 is used to obtain a first object set corresponding to the current version execution plan and a second object set corresponding to the previous version execution plan, wherein the first object set includes each object associated with the current version execution plan, and the second object set includes each object associated with the previous version execution plan.

[0127] The efficiency reduction candidate factor detection unit 500 is configured to detect candidate factors that reduce the execution efficiency of the target SQL statement based on the first object set and the second object set, and output a detection result.

[0128] Optionally, the target SQL statement obtaining unit 100 may include: a first SQL statement obtaining sub-unit, a second SQL statement obtaining sub-unit, and a target SQL statement obtaining sub-unit.

[0129] The first SQL statement obtaining subunit is used to group the SQL statements in the target database according to the SQL plan hash value and sort them in descending order according to the total logical read value to obtain the first number of first SQL statements that are ranked first.

[0130] The second SQL statement obtaining subunit is used to group the SQL statements in the target database according to the SQL text hash value of the first SQL statement with the largest number of executions and sort them in descending order according to the average single execution logical read, to obtain the second largest number of second SQL statements with the highest order.

[0131] The target SQL statement obtaining subunit is configured to perform deduplication processing on the first number of first SQL statements and the second number of second SQL statements to obtain a third number of target SQL statements.

[0132] Optionally, the execution efficiency detection unit 300 may include: a first execution target data obtaining subunit, a second execution target data obtaining subunit, and an average logic reading value comparison subunit.

[0133] The first execution target data obtaining subunit is configured to obtain first execution target data when the current version execution plan is executed for the first time, where the first execution target data includes a first average logical read value.

[0134] The second execution target data obtaining subunit is configured to obtain the second execution target data of the previous version execution plan when the previous version execution plan is last executed, where the second execution target data includes a second average logical read value.

[0135] The average logic reading value comparison subunit is used to compare the first average logic reading value with the second average logic reading value to determine whether the execution efficiency of the target SQL statement is reduced.

[0136] Optionally, the object set obtaining unit 400 can be specifically configured to use the SQL plan hash value and the SQL text hash value of the target SQL statement to filter out the first object set corresponding to the current version execution plan and the second object set corresponding to the previous version execution plan through the v$sql_plan view.

[0137] Optionally, the first execution target data further includes a first execution usage time, and the second execution target data further includes a second execution usage time.

[0138] Optionally, the efficiency reduction candidate factor detection unit 500 may include: a difference calculation subunit, a union calculation subunit, and an efficiency reduction candidate factor determination subunit.

[0139] A difference calculation subunit is used to perform difference calculation on the first object set and the second object set to obtain a third object set and a fourth object set, wherein the third object set includes objects that exist in the first object set but not in the second object set, and the fourth object set includes objects that exist in the second object set but not in the first object set.

[0140] The union calculation subunit is configured to perform a union calculation on the first object set and the second object set to obtain a fifth object set, wherein the fifth object set includes objects that exist in the first object set or in the second object set.

[0141] The efficiency reduction candidate factor determination subunit is used to determine the candidate factors for reducing the execution efficiency of the target SQL statement based on the first execution usage time and the second execution usage time, using the third object set, the fourth object set and the fifth object set respectively, and output the detection result.

[0142] Optionally, the efficiency reduction candidate factor determination subunit may include: a first time obtaining subunit and a first candidate factor determination subunit.

[0143] The first time obtaining subunit is used for obtaining the first time when the object is created for any object in the third object set.

[0144] The first alternative factor determination subunit is used to determine, when the first time is greater than the second execution usage time and less than the first execution usage time, that the alternative factor for reducing the execution efficiency of the target SQL statement includes the object being created and output a detection result including object information corresponding to the object.

[0145] Optionally, the efficiency reduction candidate factor determination subunit may include: an object detection subunit, a second time determination subunit, a third time determination subunit, a second candidate factor determination subunit, and a third candidate factor determination subunit.

[0146] The object detection subunit is used to detect whether any object in the fourth object set exists in the target database, and if not, trigger the second time determination subunit; if so, trigger the third time determination subunit.

[0147] The second time determination subunit is used to determine the second time when the object is deleted.

[0148] The third time determination subunit is configured to determine the third time of the last DDL operation on the object.

[0149] a second candidate factor determination subunit configured to, when the second time is greater than the second execution usage time and less than the first execution usage time, determine that the candidate factor for reducing the execution efficiency of the target SQL statement includes deletion of the object and output a detection result including object information corresponding to the object;

[0150] The third alternative factor determination subunit is used to determine that the alternative factor for reducing the execution efficiency of the target SQL statement includes the failure of the object and output a detection result including object information corresponding to the object when the third time is greater than the second execution usage time and less than the first execution usage time.

[0151] Optionally, the efficiency reduction candidate factor determination subunit may include: a fourth time acquisition subunit, a statistical information change rate acquisition subunit, a target change value calculation subunit, a fourth candidate factor determination subunit, and a second candidate factor determination subunit.

[0152] The fourth time obtaining subunit is configured to obtain, for any object in the fifth object set, a fourth time for collecting statistical information of the object.

[0153] The statistical information change rate obtaining subunit is configured to obtain the statistical information change rate of the object when the fourth time satisfies a preset size relationship condition with the first execution usage time and the second execution usage time.

[0154] The target change value calculation subunit is used to calculate the target change value corresponding to the object based on the statistical information change rate.

[0155] The fourth alternative factor determination subunit is used to determine that the alternative factors for reducing the execution efficiency of the target SQL statement include the unstable statistical information change rate of the object and output the detection result including the unstable statistical information change rate of the object when the target change value corresponding to the object is greater than the first preset change threshold.

[0156] The second alternative factor determination sub-unit is used to determine that the alternative factors for reducing the execution efficiency of the target SQL statement include dynamic sampling misaccuracy and output the detection results of the statistical information change rate of each object in the fifth object set when the target change values corresponding to each object in the fifth object set are less than the second preset change threshold.

[0157] The present disclosure provides a device for detecting the execution efficiency of SQL statements, which can obtain a target SQL statement; obtain a current version execution plan and a previous version execution plan of the target SQL statement; based on the current version execution plan and the previous version execution plan, detect whether the execution efficiency of the target SQL statement is reduced, and if it is reduced, obtain a first object set corresponding to the current version execution plan and a second object set corresponding to the previous version execution plan, wherein the first object set includes each object associated with the current version execution plan, and the second object set includes each object associated with the previous version execution plan; based on the first object set and the second object set, detect alternative factors that cause the execution efficiency of the target SQL statement to be reduced and output the detection result. The present disclosure can efficiently detect alternative factors that cause the execution efficiency of the target SQL statement to be reduced by using the first object set corresponding to the current version execution plan of the target SQL statement with reduced execution efficiency and the second object set corresponding to the previous version execution plan, thereby facilitating timely optimization by operation and maintenance personnel to ensure the normal operation of the database.

[0158] Regarding the apparatus in the above embodiment, the specific manner in which each module performs operations has been described in detail in the embodiment of the method, and will not be elaborated here.

[0159] The SQL statement execution efficiency detection device includes a processor and a memory. The above-mentioned target SQL statement acquisition unit 100, execution plan acquisition unit 200, execution efficiency detection unit 300, object set acquisition unit 400 and efficiency reduction alternative factor detection unit 500 are all stored in the memory as program units, and the processor executes the above-mentioned program units stored in the memory to realize corresponding functions.

[0160] The processor includes a kernel, which retrieves the corresponding program unit from memory. One or more kernels can be configured. By adjusting kernel parameters, the first object set corresponding to the current version execution plan of the target SQL statement with reduced execution efficiency and the second object set corresponding to the previous version execution plan can be used to efficiently detect the potential factors that reduce the execution efficiency of the target SQL statement, thereby facilitating timely optimization by operations and maintenance personnel and ensuring the normal operation of the database.

[0161] An embodiment of the present disclosure provides a computer-readable storage medium having a program stored thereon, which implements the SQL statement execution efficiency detection method when executed by a processor.

[0162] An embodiment of the present disclosure provides a processor, which is used to run a program, wherein the SQL statement execution efficiency detection method is executed when the program is running.

[0163] An embodiment of the present disclosure provides an electronic device comprising at least one processor, at least one memory device connected to the processor, and a bus; the processor and the memory device communicate with each other via the bus; and the processor is configured to invoke program instructions stored in the memory device to execute the aforementioned SQL statement execution efficiency detection method. The electronic device herein may be a server, a PC, a PAD, a mobile phone, or the like.

[0164] The present disclosure also provides a computer program product, which, when executed on an electronic device, is suitable for executing a program initialized with the steps of the SQL statement execution efficiency detection method.

[0165] The present disclosure is described with reference to the flowcharts and / or block diagrams of the methods, apparatuses, electronic devices (systems), and computer program products according to the embodiments of the present disclosure. It should be understood that each process and / or block in the flowchart and / or block diagram, as well as the combination of processes and / or blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable device to produce a machine, so that the instructions executed by the processor of the computer or other programmable device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 a process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0166] In a typical configuration, an electronic device includes one or more processors (CPUs), a memory, and a bus. The electronic device may also include an input / output interface, a network interface, and the like.

[0167] Memory may include non-permanent memory in a computer-readable medium, random access memory (RAM), and / or non-volatile memory, such as read-only memory (ROM) or flash RAM. Memory includes at least one memory chip. Memory is an example of a computer-readable medium.

[0168] Computer-readable media includes permanent and non-permanent, removable and non-removable media that can be implemented by any method or technology to store information. The information can be computer-readable instructions, data structures, program modules or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technology, compact disc read-only memory (CD-ROM), digital versatile disc (DVD) or other optical storage, magnetic cassettes, magnetic tape, magnetic disk storage or other magnetic storage devices or any other non-transmission media that can be used to store information that can be accessed by a computing device. As defined herein, computer-readable media does not include transitory computer-readable media (transitory media), such as modulated data signals and carrier waves.

[0169] It should be noted that, in this document, relational terms such as first and second, etc., are used only to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any actual relationship or order between these entities or operations. It should also be noted that the terms "comprises," "comprising," or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, commodity, or device comprising a series of elements includes not only those elements, but also other elements not explicitly listed, or elements inherent to such process, method, commodity, or device. In the absence of further limitations, an element defined by the phrase "comprising a ..." does not exclude the presence of other identical elements in the process, method, commodity, or device comprising the element.

[0170] Those skilled in the art will appreciate that embodiments of the present disclosure may be provided as methods, systems, or computer program products. Thus, the present disclosure may take the form of a fully hardware embodiment, a fully software embodiment, or an embodiment combining software and hardware. Furthermore, the present disclosure may take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to magnetic disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0171] The above are merely examples of the present disclosure and are not intended to limit the present disclosure. Various modifications and variations are possible for those skilled in the art. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present disclosure are intended to be included within the scope of the claims of the present disclosure.

Claims

1. A method for detecting the execution efficiency of SQL statements, characterized in that: include: Get the target SQL statement; Obtain the current version execution plan and the previous version execution plan of the target SQL statement; Obtaining first execution target data of the current version execution plan when the execution plan is first executed, the first execution target data including a first average logical read value and a first execution usage time; Obtaining second execution target data of the previous version execution plan when it is last executed, the second execution target data including a second average logical read value and a second execution usage time; Comparing the first average logical read value with the second average logical read value to determine whether the execution efficiency of the target SQL statement is reduced; if so, obtaining a first object set corresponding to the current version execution plan and a second object set corresponding to the previous version execution plan, wherein the first object set includes objects associated with the current version execution plan, and the second object set includes objects associated with the previous version execution plan; Performing a difference calculation on the first object set and the second object set to obtain a third object set and a fourth object set, wherein the third object set includes objects that exist in the first object set but not in the second object set, and the fourth object set includes objects that exist in the second object set but not in the first object set; performing a union calculation on the first object set and the second object set to obtain a fifth object set, wherein the fifth object set includes objects that exist in the first object set or in the second object set; based on the first execution usage time and the second execution usage time, using the third object set, the fourth object set, and the fifth object set respectively, determining alternative factors that reduce the execution efficiency of the target SQL statement and outputting a detection result.

2. The method according to claim 1, characterized in that The obtaining of the target SQL statement includes: For each SQL statement in the target database: group them according to the SQL plan hash value and sort them in descending order according to the total logical read value, and obtain the first SQL statement with the highest number of orders; For each SQL statement in the target database: group the first SQL statements according to the SQL text hash value of the first SQL statement with the largest number of executions and sort them in descending order according to the average single execution logical reads to obtain the second number of second SQL statements with the highest number of executions; Deduplication is performed on the first number of the first SQL statements and the second number of the second SQL statements to obtain a third number of target SQL statements.

3. The method according to claim 1, characterized in that The obtaining of the first object set corresponding to the current version execution plan and the second object set corresponding to the previous version execution plan includes: The SQL plan hash value and the SQL text hash value of the target SQL statement are used to filter out the first object set corresponding to the current version execution plan and the second object set corresponding to the previous version execution plan through the v$sql_plan view.

4. The method according to claim 1, wherein The determining, based on the first execution usage time and the second execution usage time, candidate factors for reducing the execution efficiency of the target SQL statement by using the third object set, the fourth object set, and the fifth object set respectively and outputting a detection result includes: For any object in the third object set: obtaining the first time of creation of the object; When the first time is greater than the second execution usage time and less than the first execution usage time, determining that the execution efficiency of the target SQL statement is reduced is an alternative factor including the object being created and outputting a detection result including object information corresponding to the object.

5. The method according to claim 1, characterized in that The determining, based on the first execution usage time and the second execution usage time, candidate factors for reducing the execution efficiency of the target SQL statement by using the third object set, the fourth object set, and the fifth object set respectively and outputting a detection result includes: For any object in the fourth object set: detecting whether the object exists in the target database; if not, determining a second time when the object was deleted; if so, determining a third time when the last DDL operation was performed on the object; When the second time is greater than the second execution usage time and less than the first execution usage time, determining that the candidate factor for the reduction in execution efficiency of the target SQL statement includes deletion of the object and outputting a detection result including object information corresponding to the object; When the third time is greater than the second execution usage time and less than the first execution usage time, determining that the candidate factor for the reduction in execution efficiency of the target SQL statement includes invalidation of the object and outputting a detection result including object information corresponding to the object.

6. The method according to claim 1, characterized in that The determining, based on the first execution usage time and the second execution usage time, candidate factors for reducing the execution efficiency of the target SQL statement by using the third object set, the fourth object set, and the fifth object set respectively and outputting a detection result includes: For any object in the fifth object set: obtaining a fourth time for collecting statistical information of the object; When the fourth time, the first execution usage time, and the second execution usage time satisfy a preset size relationship condition, obtaining a statistical information change rate of the object; Calculating a target change value corresponding to the object based on the statistical information change rate; When the target change value corresponding to the object is greater than a first preset change threshold, determining that the candidate factor for reducing the execution efficiency of the target SQL statement includes an unstable statistical information change rate of the object and outputting a detection result including an unstable statistical information change rate of the object; When the target change values corresponding to each object in the fifth object set are all less than the second preset change threshold, the alternative factors for determining that the execution efficiency of the target SQL statement is reduced include dynamic sampling inaccuracy and outputting the detection results of the statistical information change rate of each object in the fifth object set.

7. A device for detecting the execution efficiency of SQL statements, characterized in that: include: Target SQL statement acquisition unit, execution plan acquisition unit, execution efficiency detection unit, object collection acquisition unit and efficiency reduction alternative factor detection unit, The target SQL statement obtaining unit is used to obtain the target SQL statement; The execution plan obtaining unit is used to obtain the current version execution plan and the previous version execution plan of the target SQL statement; The execution efficiency detection unit includes: a first execution target data obtaining subunit, a second execution target data obtaining subunit, and an average logic reading value comparison subunit; The first execution target data obtaining subunit is configured to obtain first execution target data of the current version execution plan when the current version execution plan is executed for the first time, wherein the first execution target data includes a first average logical read value and a first execution usage time; The second execution target data obtaining subunit is configured to obtain second execution target data of the previous version execution plan when the previous version execution plan is last executed, wherein the second execution target data includes a second average logical read value and a second execution usage time; The average logical reading value comparison subunit is configured to compare the first average logical reading value with the second average logical reading value to determine whether the execution efficiency of the target SQL statement is reduced, and if so, trigger the object set acquisition unit; The object set obtaining unit is configured to obtain a first object set corresponding to the current version execution plan and a second object set corresponding to the previous version execution plan, wherein the first object set includes objects associated with the current version execution plan, and the second object set includes objects associated with the previous version execution plan; An efficiency reduction candidate factor detection unit, comprising: a difference calculation subunit, a union calculation subunit, and an efficiency reduction candidate factor determination subunit; The difference calculation subunit is configured to perform a difference calculation on the first object set and the second object set to obtain a third object set and a fourth object set, wherein the third object set includes objects that exist in the first object set but not in the second object set, and the fourth object set includes objects that exist in the second object set but not in the first object set; The union calculation subunit is configured to perform a union calculation on the first object set and the second object set to obtain a fifth object set, wherein the fifth object set includes objects that exist in the first object set or in the second object set; The efficiency reduction alternative factor determination subunit is used to determine the alternative factors for reducing the execution efficiency of the target SQL statement based on the first execution usage time and the second execution usage time, respectively using the third object set, the fourth object set and the fifth object set, and output a detection result.

Images