Nothing Special   »   [go: up one dir, main page]

WO2019148713A1 - Sql语句处理方法、装置、计算机设备和存储介质 - Google Patents

Sql语句处理方法、装置、计算机设备和存储介质 Download PDF

Info

Publication number
WO2019148713A1
WO2019148713A1 PCT/CN2018/088974 CN2018088974W WO2019148713A1 WO 2019148713 A1 WO2019148713 A1 WO 2019148713A1 CN 2018088974 W CN2018088974 W CN 2018088974W WO 2019148713 A1 WO2019148713 A1 WO 2019148713A1
Authority
WO
WIPO (PCT)
Prior art keywords
performance
sql statement
sql
version environment
low
Prior art date
Application number
PCT/CN2018/088974
Other languages
English (en)
French (fr)
Inventor
王瑾玲
高金芳
Original Assignee
平安科技（深圳）有限公司
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by 平安科技（深圳）有限公司 filed Critical 平安科技（深圳）有限公司
Publication of WO2019148713A1 publication Critical patent/WO2019148713A1/zh

Links

Images

Classifications

    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/245—Query processing
    • G06F16/2453—Query optimisation
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/242—Query formulation
    • G06F16/2433—Query languages

Definitions

  • the present application relates to a SQL statement processing method, apparatus, computer device, and storage medium.
  • SQL Structured Query Language
  • the purpose of the optimizer is to get the most efficient execution path of the target SQL statement in the current situation according to certain judgment principles, that is, to get the best execution plan of the target SQL statement. Therefore, the execution plan of the same SQL statement in different versions of the database environment will be different, and the execution plan will change the performance of the SQL statement.
  • the SQL SQL Performance Analyze, SQL performance analysis
  • the relational database system version upgrade can be performed to ensure that the performance of the SQL statement after the relational database system version is upgraded is not degraded.
  • a SQL sentence processing method According to various embodiments disclosed herein, a SQL sentence processing method, apparatus, computer device, and storage medium are provided.
  • a SQL statement processing method includes: obtaining a database sub-parameter of each initial SQL statement; performing calculation according to the database sub-parameter to obtain a performance consumption ratio of each initial SQL statement; and extracting performance consumption ratio is greater than a preset threshold
  • the initial SQL statement forms a core SQL statement set; the core SQL statement set is placed in the old version environment and the new version environment for performance analysis to obtain a low performance SQL statement; the low performance SQL statement is in the new version environment
  • the performance of the low-performance SQL statement is lower than the initial SQL statement in the core SQL statement set in the performance of the old version environment; and performance improvement processing on the low-performance SQL statement.
  • An SQL sentence processing device includes: a database sub-parameter acquisition module, configured to acquire a database sub-parameter of each initial SQL statement; a performance consumption ratio calculation module, configured to perform calculation according to the database sub-parameter, and obtain each initial The performance of the SQL statement is consumed; the core SQL statement set forming module is configured to extract an initial SQL statement whose performance consumption ratio is greater than a preset threshold, forming a core SQL statement set; and a low performance SQL statement extraction module for using the core
  • the SQL statement set is placed in the old version environment and the new version environment for performance analysis to obtain a low performance SQL statement; the low performance SQL statement is in the performance of the new version environment is lower than the performance in the old version environment, The initial SQL statement in the core SQL statement set; and the low performance SQL statement processing module perform performance improvement processing on the low performance SQL statement.
  • a computer device comprising a memory and one or more processors having stored therein computer readable instructions, the computer readable instructions being executable by the processor to cause the one or more processors to execute The following steps: obtaining database sub-parameters of each initial SQL statement; performing calculation according to the database sub-parameters, obtaining performance consumption ratio of each initial SQL statement; extracting initial SQL statements whose performance consumption ratio is greater than a preset threshold, forming a set of core SQL statements; the core SQL statement set is placed in the old version environment and the new version environment for performance analysis to obtain a low performance SQL statement; the low performance SQL statement is lower in the performance of the new version environment than in the old The initial SQL statement in the core SQL statement set of the performance of the version environment; and performance improvement processing on the low performance SQL statement.
  • One or more non-transitory computer readable storage mediums storing computer readable instructions, when executed by one or more processors, cause one or more processors to perform the steps of: acquiring each initial The database sub-parameter of the SQL statement; calculating according to the database sub-parameter, obtaining the performance consumption ratio of each initial SQL statement; extracting the initial SQL statement whose performance consumption ratio is greater than a preset threshold, forming a core SQL statement set;
  • the core SQL statement set is placed in the old version environment and the new version environment for performance analysis, and the low performance SQL statement is obtained; the low performance SQL statement is lower in the performance of the new version environment than in the old version environment.
  • the initial SQL statement in the core SQL statement set and performance improvement processing on the low performance SQL statement.
  • FIG. 1 is an application scenario diagram of a SQL statement processing method in accordance with one or more embodiments.
  • FIG. 2 is a flow diagram of a method of processing a SQL statement in accordance with one or more embodiments.
  • FIG. 3 is a flow diagram of a method for processing a SQL statement in accordance with another or more embodiments.
  • FIG. 4 is a block diagram showing the structure of an SQL sentence processing apparatus in accordance with one or more embodiments.
  • FIG. 5 is an internal structural diagram of a computer device in accordance with one or more embodiments.
  • first may be referred to as a second analysis parameter without departing from the scope of the present application, and similarly, the second analysis parameter may be referred to as a first analysis parameter.
  • first analysis parameter may be referred to as a second analysis parameter without departing from the scope of the present application, and similarly, the second analysis parameter may be referred to as a first analysis parameter.
  • second analysis parameter may be referred to as a first analysis parameter.
  • Both the first analysis parameter and the second analysis parameter are analytical parameters, but they are not the same analysis parameters.
  • Terminal 102 communicates with server 104 over a network over a network.
  • the server 104 can receive the initial SQL statement sent by the terminal 102, and perform SQL statement processing on the received initial SQL statement, and can also send the processed SQL statement to the terminal 102.
  • the terminal 102 can be, but is not limited to, various personal computers, notebook computers, smart phones, tablets, and portable wearable devices, and the server 104 can be implemented with a stand-alone server or a server cluster composed of a plurality of servers.
  • a SQL statement processing method is provided, which is applied to the server 102 in FIG. 1 as an example, and includes the following steps:
  • Step 202 Obtain a database sub-parameter of each initial SQL statement.
  • the initial SQL statement refers to the SQL statement that needs to be processed by the SQL statement before upgrading to the new version of the relational database system.
  • the SQL statement processing of the initial SQL statement is to ensure that the performance of the SQL statement does not decrease after the relational database system version is upgraded.
  • a relational database system refers to a database system used for data management and association management between data and fields. Relational database systems include, but are not limited to, Oracle database systems, SQL Server database systems, and MySQL database systems. Each relational database can have multiple versions. For example, the Oracle database system has multiple versions, including Oracle 10g, Oracle 11g, and Oracle 12c.
  • the database sub-parameter refers to the parameters obtained after an initial SQL statement is run in a relational database system.
  • the database sub-parameters include a combination of one or more of logical reads, number of executions, and elapsed time.
  • the logical read (buffer_gets) refers to the number of pages read from the data cache in the initial SQL statement.
  • the execution number (execution_count) refers to the number of times the initial SQL statement is executed in one run, and the elapsed time (cpu_time) It refers to the time when the initial SQL statement consumes the CPU (Central Processing Unit) in one run.
  • the database sub-parameters of each initial SQL statement can be obtained by querying the V$SQLAREA view or the V$SQL view.
  • Both the V$SQLAREA view and the V$SQL view are system views used to monitor the consumption of SQL statement resources in the Oracle database system.
  • the V$SQL view records the database sub-parameters of each SQL statement.
  • the V$SQLAREA view also aggregates SQL statements and counts the sum of database sub-parameters of multiple SQL statements. For example, in the V$SQLAREA view, statistics can be performed according to sql_text, for example, the consumption time of the first 1000 initial SQL statements.
  • Step 204 Perform calculation according to the database sub-parameters to obtain a performance consumption ratio of each initial SQL statement.
  • Performance consumption ratio refers to the overall proportion of a SQL statement consumed in one or more performances for all SQL statements.
  • the performance of the SQL statement can be evaluated by a combination of one or more of the database sub-parameters, such as logical reads, number of executions, and elapsed time. For example, querying the same data, the longer the initial SQL statement is consumed, the lower the execution efficiency of the initial SQL, and the performance of the initial SQL can be considered lower. Similarly, querying the same data, the more executions of the initial SQL statement, the lower the execution efficiency of the initial SQL, and the performance of the initial SQL can be considered lower.
  • the database sub-parameters are multiple; before calculating the performance consumption of each initial SQL statement according to the database sub-parameters, the method further includes: obtaining the total database parameters of all the initial SQL statements; The sub-parameters are calculated to obtain the performance consumption ratio of each initial SQL statement, including: calculating the single performance proportion of the database sub-parameters of each initial SQL statement in the corresponding database total parameters; obtaining the pre-corresponding to the database sub-parameters The reference weight is set; the single performance ratio corresponding to the plurality of database sub-parameters and the corresponding preset reference weight are calculated, and the performance consumption ratio of each initial SQL statement is obtained.
  • the database total parameter refers to the total parameters obtained after all the initial SQL statements are run in the relational database system.
  • the single performance ratio refers to the proportion of the individual database sub-parameters in the corresponding database total parameters.
  • the initial performance of each initial SQL statement logical read r in the total logical read R The single performance ratio of the number of executions c in the total number of executions C Or consumption time t in the total consumption time T of the proportion of single performance
  • the reference weights w1, w2, and w3 may be preset for three database sub-parameters of logical read, execution count, and consumption time, respectively, according to the formula Calculate the performance consumption ratio P of each initial SQL statement.
  • the maximum of the multiple single performance ratios may be used as the performance consumption ratio of the initial SQL statement, or may be more
  • the average value of the single performance ratio is taken as the performance consumption of the initial SQL statement, but is not limited thereto.
  • Step 206 Extract an initial SQL statement whose performance consumption ratio is greater than a preset threshold, and form a core SQL statement set.
  • the core SQL statement set refers to a set formed by a plurality of initial SQL statements whose performance consumption ratio is greater than a preset threshold. For example, when the performance consumption of the initial SQL statement is greater than 99%, it indicates that the initial SQL statement has a great influence on one running process, and multiple initial SQL statements with a performance consumption ratio greater than a preset threshold may be used from all initials.
  • the SQL statement is extracted and formed into a core SQL statement set for further processing.
  • the initial SQL statement may be sorted according to the performance consumption ratio of the initial SQL statement, and the initial SQL statement with the performance consumption ratio greater than the preset threshold may be extracted to form a core SQL statement set, and may also be extracted.
  • the initial SQL statement with a preset percentage before sorting forms a core SQL statement set.
  • Step 208 The core SQL statement set is respectively placed in the old version environment and the new version environment for performance analysis, and the low performance SQL statement is obtained.
  • the old version environment refers to the relational database system environment before the relational database system version is upgraded.
  • the new version environment refers to the relational database system environment after the relational database system version is upgraded.
  • the Oracle database system needs to be upgraded from the Oracle 10g version to the Oracle 12c version.
  • the Oracle 10g version of the Oracle database system environment is the old version environment
  • the Oracle 12c version of the Oracle database system environment is the new version environment.
  • the initial SQL statements in the core SQL statement set are placed in the old version environment and the new version environment respectively, and low performance SQL statements can be obtained through performance analysis.
  • a low-performance SQL statement is an initial SQL statement in the core SQL statement set that is less powerful in the new version environment than in the old version environment.
  • the initial SQL statements in the set of core SQL statements can be characterized from one or more of the logical reads, the number of executions, and the elapsed time. For example, comparing the initial SQL statements in the core SQL statement set to logical reads in the old version environment and the new version environment, the logical read in the new version environment is larger than the initial SQL corresponding to the logical read in the old version environment.
  • the statement acts as a low-performance SQL statement.
  • Step 210 Perform performance improvement processing on the low performance SQL statement.
  • Performance improvement processing refers to the processing of optimizing the performance of low-performance SQL statements.
  • the low performance SQL statement itself can be optimized. For example, optimizing the operators of low-performance SQL statements, including replacing the IN operator with the EXISTS operator in the business-intensive low-performance SQL statement, and using the IS NULL operator or the IS NOT NULL operator with other operations of the same function. Operation instead. For another example, adjusting the order of conditions after WHERE, so that the amount of calculation is reduced. It is also possible to filter low-performance SQL statements of the same function but different ways of writing, reducing duplicate operations.
  • the low performance SQL statement can be further filtered when the number of low performance SQL statements is detected to exceed a predetermined number. For example, when it is detected that the low-performance SQL statement is greater than 100,000, the performance cost of each low-performance SQL statement in all low-performance SQL statements can be calculated according to the database sub-parameter of each low-performance SQL statement, and the performance is selected. A low-performance SQL statement that consumes more than a preset threshold. Repeat the above filtering process until the number of low-performance SQL statements is less than the preset number. Perform performance improvement on less than the preset number of low-performance SQL statements after filtering.
  • the performance consumption ratio of each initial SQL statement is obtained. Therefore, it is possible to filter out a set of core SQL statements that have a great influence on the execution efficiency of all SQL statements from all the initial SQL statements according to the performance consumption ratio.
  • the low-performance SQL statement in the core SQL statement set is filtered out again in the new version environment with lower performance than the old version environment. .
  • the core SQL statement set is placed in the old version environment and the new version environment for performance analysis, and the low performance SQL statement is obtained, including: obtaining the core SQL statement set in the old version environment; and the old version environment
  • the core SQL statement collection is sent to the new version environment; the SQL performance analysis task of the old version environment and the SQL performance analysis task of the new version environment are created and executed in the new version environment, and the first analysis parameter and the second analysis parameter are respectively obtained.
  • the first analysis parameter is an analysis parameter obtained by executing an SQL performance analysis task of the old version environment;
  • the second analysis parameter is an analysis parameter obtained by executing a SQL performance analysis task of the new version environment. Comparing the first analysis parameter with the second analysis parameter to obtain a low performance SQL statement.
  • the SQL performance analysis task refers to the task in the system database for analyzing the performance of SQL statements.
  • the first analysis parameter and the second analysis parameter refer to parameters reflecting the performance of the SQL statement, including but not limited to database read parameters, execution times, consumption time, and the like, and may also be parameters related to the execution plan.
  • the core SQL statement set in the old version environment needs to be sent to the new version environment for performance analysis.
  • SPA SQL Performance Analyze, SQL Performance Analysis
  • SQL Performance Analysis is a new feature that is only available in the Oracle database system after the Oracle 11g version. Therefore, before the Oracle database system version is upgraded, it is necessary to obtain the core SQL statement set in the Oracle 10g version database system, and send the core SQL statement set in the Oracle 10g version database system to the Oracle 12c version database system for testing.
  • the first SPA task in the Oracle 10g version database system environment and the second SPA task in the Oracle 12c version database system environment are created and executed in the Oracle 12c version database system, and the first analysis parameter and the second analysis parameter are respectively obtained.
  • the first analysis parameter is an analysis parameter obtained by executing the first SPA task in the Oracle 10g version database system environment;
  • the second analysis parameter is an analysis parameter obtained by executing the second SPA task in the Oracle 12c version database system environment. Comparing the first analysis parameter with the second analysis parameter to obtain a low performance SQL statement.
  • the set of SQL statements in the old version environment is sent to the new version environment, including: creating a transit table in the old version environment; importing the core SQL statement set in the old version environment into the transit table; The relay table with the core SQL statement set is sent to the new version environment for decompression, and the core SQL statement set in the new version environment is obtained.
  • a transit table is a table that copies or transfers data from one relational database system to another relational database system.
  • the initial SQL statement is in the old version environment, so the core SQL statement collection is also in the old version environment. It is necessary to create a transit table in the old version environment, and import the initial SQL statement in the core SQL statement set in the old version environment into the relay table; send the relay table storing the core SQL statement set to the new version environment for decompression, A collection of core SQL statements obtained in a new version of the environment.
  • the version number of the transfer table can also be preset, so that the subsequent core SQL statement set corresponding to each performance analysis process can be quickly found.
  • the SQL statement processing method further includes processing steps for the newly added SQL statement, and specifically includes the following steps:
  • step 302 an incremental SQL statement is obtained.
  • the incremental SQL statement refers to the new SQL statement added during the upgrade of the relational database system version to the relational database system version upgrade.
  • the incremental SQL statement can be filtered out, that is, the duplicate incremental SQL statement is removed, the incremental SQL statement that performs the error is reported, and the incremental SQL statement that does not capture the bound variable value but contains the bound variable. Further processing of incremental SQL statements after successful execution and screening.
  • Step 304 Detect whether the incremental SQL statement is preset with the SQL execution plan management SPM.
  • SQL execution plan management SPM SQL plan management refers to the Oracle database system automatically determines whether a new execution plan of a SQL is more reasonable, only if the new execution plan is better than the original execution plan. In this case, the new execution plan will be used.
  • step 306 is performed; when the incremental SQL statement is detected, the SQL execution plan management is not preset.
  • step 310 is performed.
  • step 306 it is determined whether the execution plan of the incremental SQL statement preset with the SPM in the old version environment is the same as the execution plan in the new version environment.
  • step 314 is executed, and no processing is performed; if the incremental SQL statement of the SPM is preset in the old version The execution plan in the environment is not the same as the execution plan in the new version environment, and step 308 is performed.
  • the specific execution plan is determined by the optimizer of the Oracle database system. Since the optimizers between different versions of the relational database system will be different, if the same SQL statement is bound to a fixed execution plan, the execution plan of the SQL statement in the old version environment is in the new version environment. The execution plan may be different.
  • Step 308 removing the SPM of the incremental SQL statement.
  • the SPM of the incremental SQL statement is removed.
  • the incremental SQL statement with the SPM removed can be run again in the old version environment and the new version environment, further judging the execution plan of the incremental SQL statement with the SPM removed in the old version environment and Is there a difference in the new version of the environment?
  • Step 310 Determine whether the execution plan of the incremental SQL statement without the preset SPM in the old version environment is the same as the execution plan in the new version environment.
  • step 314 is performed. If no, it indicates that the incremental SQL statement has different performance in the new version environment and the old version environment, and needs further processing, and step 312 is performed.
  • Step 312 performing performance improvement processing on the incremental SQL statement loop until the performance of the incremental SQL statement in the old version environment is greater than or equal to the performance in the new version environment.
  • the performance of the incremental SQL statement loop can be improved according to the above method of processing the initial SQL statement until the performance of the processed incremental SQL statement in the old version environment is greater than or equal to the performance in the new version environment.
  • performance analysis is performed on the incremental incremental SQL statement added during the process of upgrading the relational database system version to the relational database system version upgrade.
  • the incremental SQL statement is preset with the SQL execution plan management SPM, and whether the incremental SQL statement is executed in the old version environment with the same execution plan as in the new version environment, and based on the judgment result, the incremental SQL statement Corresponding processing has been carried out to ensure that the performance of the incremental SQL statement after the upgrade of the relational database system is not reduced.
  • the performance improvement process for the low performance SQL statement includes: screening the low performance SQL statement; and performing the split processing on the low performance SQL statement according to the preset number to form a split processing.
  • Screening processing refers to the process of culling low-performance SQL statements that do not affect the execution result. It can screen out low-performance SQL statements with repeated functions. For example, a globally unique identifier SQL_ID can be preset for each initial SQL statement, which can be generated by a hash algorithm. Remove low-performance SQL statements from low-performance SQL statements based on SQL_ID. For another example, the database sub-parameters and low-performance SQL statements with the same execution result can be extracted for screening. Low-performance SQL statements that can remove unbound variables, and low-performance SQL statements for unbound variables can be viewed through the FORCE_MATCHING_SIGNATURE field.
  • Low-performance SQL statements that are executed only once can be removed, such as removing low-performance SQL statements corresponding to real-name users such as DEVSUP, DBMON, SOLIX, and APPMGR.
  • Non-application-initiated low-performance SQL statements can be removed, such as user-initiated SQL statements for "SQL*Plus” or "PL/SQL Developer”.
  • Low-performance SQL statements initiated by the database itself can be removed, for example, low-performance SQL statements with the optimizer instruction "/*OPT_DYN_SAMP*/" removed. It also removes useless functions, such as removing the nextval function used to generate the sequence number associated with the sort. It also removes static data that does not change, such as removing MIG-related configuration tables.
  • a low performance SQL statement containing a bind variable that does not catch the corresponding bind variable value can also be removed.
  • Binding variables is a variable that replaces the constants in the sql statement.
  • Split processing refers to the process of splitting all low-performance SQL statements into multiple sub-sets of low-performance SQL statements.
  • the remaining performance cost may be filtered after all low-performance SQL statements whose performance consumption is greater than a preset threshold are filtered out by one or more database sub-parameters such as logical read, execution count, and consumption time.
  • a low-performance SQL statement that accounts for less than a preset threshold is split. For example, according to a preset number, for example, every 10,000 low-performance SQL statements can be split into a low-performance SQL sentence sub-set.
  • the low-performance SQL statements in the low-performance SQL sentence sub-set can be obtained by performing playback processing and performance analysis on the low-performance SQL sentence sub-sets obtained after the screening processing and the split processing.
  • Playback processing refers to the execution of low-performance SQL statements in a low-performance SQL statement sub-set in the old version environment and the new version environment. It is possible to extract a low-performance SQL statement in which the logical read differs between the old version environment and the new version environment, that is, the front-to-back comparison difference is greater than zero. It is also possible to remove the playback error, that is, the number of playback executions is zero. It is also possible to remove non-playback SQL statements, ie low-performance SQL statements that do not capture bind variable values but contain bind variables.
  • the low-performance SQL sentence sub-set after the playback processing may be further filtered according to one or more database sub-parameters such as logical read, execution count, and consumption time, to obtain a core SQL statement set in the low-performance SQL sentence sub-set.
  • Performance analysis of the core SQL statement set in the low-performance SQL sentence sub-set which may be performed for the execution plan change, extracting the execution times greater than the preset execution times, such as 50, and taking out a single logical read is greater than the preset logical read , such as 100, and meet low-performance SQL statements with a rate of change of more than 20%. Filtering again based on database sub-parameters results in low-performance SQL statements in a low-performance SQL statement sub-set.
  • the performance of the low-performance SQL statement after the loop processing in the new version environment is greater than or equal to that in the old version environment.
  • the performance ensures that the performance of the SQL statement after the relational database system version is upgraded is not degraded.
  • a SQL sentence processing apparatus 400 including: a database sub-parameter acquisition module 402, configured to acquire database sub-parameters of each initial SQL statement;
  • the module 404 is configured to perform calculation according to the database sub-parameters to obtain a performance consumption ratio of each initial SQL statement.
  • the core SQL statement set forming module 406 is configured to extract an initial SQL statement whose performance consumption ratio is greater than a preset threshold, and form a core.
  • the SQL statement collection module 408 is configured to place the core SQL statement set in the old version environment and the new version environment for performance analysis to obtain a low performance SQL statement; the low performance SQL statement is in the new version environment.
  • the initial SQL statement in the core SQL statement set is lower in performance than in the old version environment; the low performance SQL statement processing module 410 performs performance improvement on the low performance SQL statement.
  • the database sub-parameters are multiple, and the performance consumption ratio calculation module 404 is further configured to obtain the total database parameters of all the initial SQL statements; and calculate the database sub-parameters of each initial SQL statement in the corresponding database total parameters.
  • the single performance ratio in the database; the preset reference weight corresponding to the database sub-parameter is obtained; the single performance ratio corresponding to the plurality of database sub-parameters and the corresponding preset reference weight are calculated, and the performance consumption of each initial SQL statement is obtained. Proportion.
  • the low performance SQL statement extraction module 408 is further configured to obtain a core SQL statement set in an old version environment; send the core SQL statement set in the old version environment to the new version environment; in the new version environment Create and execute the SQL performance analysis task of the old version environment and the SQL performance analysis task of the new version environment, respectively obtain the first analysis parameter and the second analysis parameter; the first analysis parameter is obtained by executing the SQL performance analysis task of the old version environment. Analyze parameters; the second analysis parameter is the analysis parameter obtained by executing the SQL performance analysis task of the new version environment. Comparing the first analysis parameter with the second analysis parameter to obtain a low performance SQL statement.
  • the low performance SQL statement extraction module 408 is further configured to create a relay table in the old version environment; import the core SQL statement set in the old version environment into the relay table; and the relay table storing the core SQL statement set Send it to the new version environment for decompression to get the core SQL statement collection in the new version environment.
  • the low-performance SQL sentence processing module 410 is further configured to perform filtering processing on the low-performance SQL statement; and the low-performance SQL statement after the screening process is split according to a preset number to form a low Performance SQL statement sub-collection; low-performance SQL statement sub-sets are placed in the old version environment and the new version environment for performance analysis, to obtain low-performance SQL statements in the low-performance SQL sentence sub-set; the resulting low-performance SQL statement loop Screening, split processing, and performance analysis are performed until the performance of the low-performance SQL statement in the new version environment is greater than or equal to that in the old version environment.
  • the apparatus further includes an incremental SQL statement processing module for obtaining the incremental SQL statement; when the incremental SQL statement is detected, the SQL execution plan management SPM is preset, and the incremental SQL statement is in the old version.
  • the SPM of the incremental SQL statement is removed; whether the execution plan of the incremental SQL statement without the preset SPM in the old version environment is in the new version The execution plan in the environment is the same; if not, the incremental SQL statement loop is performance-enhanced until the performance of the incremental SQL statement in the old version environment is greater than or equal to the performance in the new version environment.
  • the various modules in the above SQL statement processing apparatus may be implemented in whole or in part by software, hardware, and combinations thereof.
  • Each of the above modules may be embedded in or independent of the processor in the computer device, or may be stored in a memory in the computer device in a software form, so that the processor invokes the operations corresponding to the above modules.
  • a computer device which may be a server, and its internal structure diagram may be as shown in FIG. 5.
  • the computer device includes a processor, memory, network interface, and database connected by a system bus.
  • the processor of the computer device is used to provide computing and control capabilities.
  • the memory of the computer device includes a non-volatile storage medium, an internal memory.
  • the non-volatile storage medium stores an operating system, computer readable instructions, and a database.
  • the non-volatile storage medium can be a non-transitory computer readable storage medium.
  • the internal memory provides an environment for operation of an operating system and computer readable instructions in a non-volatile storage medium.
  • the database of the computer device is used to store data such as initial SQL statements.
  • the network interface of the computer device is used to communicate with an external terminal via a network connection.
  • the computer readable instructions are executed by a processor to implement a SQL statement processing method.
  • FIG. 5 is only a block diagram of a part of the structure related to the solution of the present application, and does not constitute a limitation of the computer device to which the solution of the present application is applied.
  • the specific computer device may It includes more or fewer components than those shown in the figures, or some components are combined, or have different component arrangements.
  • a computer device comprising a memory and one or more processors, the memory storing computer readable instructions, the computer readable instructions being executed by the processor to: obtain a database sub-parameter of each initial SQL statement; The sub-parameters are calculated to obtain the performance consumption ratio of each initial SQL statement; the initial SQL statements with the ratio of performance consumption greater than the preset threshold are extracted to form a core SQL statement set; the core SQL statement sets are respectively placed in the old version environment and new Perform performance analysis in the version environment to get low-performance SQL statements; low-performance SQL statements are initial SQL statements in the core SQL statement set that are lower in performance in the new version environment than in the old version environment; low-performance SQL statements Perform performance improvement processing.
  • the database sub-parameters are plural, and when the processor executes the computer readable instructions, before performing the calculation according to the database sub-parameters to obtain the performance consumption ratio of each initial SQL statement, The following steps are performed: obtaining the total database parameters of all the initial SQL statements; performing the calculation according to the database sub-parameters to obtain the performance consumption ratio of each initial SQL statement, including the following steps: calculating the database of each initial SQL statement The single-performance ratio of the sub-parameters in the corresponding total parameters of the database; obtaining the preset reference weight corresponding to the database sub-parameters; calculating according to the single-performance ratio corresponding to the plurality of database sub-parameters and the corresponding preset reference weights, The performance consumption of each initial SQL statement.
  • the executed core SQL statement set is respectively placed in the old version environment and the new version environment for performance analysis
  • the steps of obtaining the low performance SQL statement include the following steps. : Get the core SQL statement collection in the old version environment; send the core SQL statement collection in the old version environment to the new version environment; create and execute the SQL performance analysis task and the new version environment of the old version environment in the new version environment
  • the SQL performance analysis task obtains the first analysis parameter and the second analysis parameter respectively;
  • the first analysis parameter is an analysis parameter obtained by executing an SQL performance analysis task of the old version environment;
  • the second analysis parameter is a SQL performance by executing a new version environment Analyze the analysis parameters obtained by the task. Comparing the first analysis parameter with the second analysis parameter to obtain a low performance SQL statement.
  • the step of transmitting the set of SQL statements in the old version environment to the new version environment includes the steps of: creating a relay table in the old version environment; The core SQL statement set in the old version environment is imported into the transit table; the transit table storing the core SQL statement set is sent to the new version environment for decompression, and the core SQL statement set in the new version environment is obtained.
  • the step of performing performance improvement processing on the low performance SQL statement includes the following steps: screening the low performance SQL statement; The low-performance SQL statement is split according to the preset number to form a low-performance SQL sentence sub-set; the low-performance SQL sentence sub-set is placed in the old version environment and the new version environment for performance analysis, and low-performance SQL is obtained.
  • the processor executes the computer readable instructions
  • the following steps are further performed: obtaining an incremental SQL statement; when the incremental SQL statement is detected, the SQL execution plan management SPM is preset, and the incremental SQL statement is old
  • the SPM of the incremental SQL statement is removed; whether the execution plan of the incremental SQL statement without the preset SPM in the old version environment is new or not
  • the execution plan in the version environment is the same; if not, the performance of the incremental SQL statement loop is improved until the performance of the incremental SQL statement in the old version environment is greater than or equal to the performance in the new version environment.
  • the database sub-parameters include logical reads, number of executions, and elapsed time.
  • One or more non-transitory computer readable storage mediums storing computer readable instructions, when executed by one or more processors, cause one or more processors to perform the steps of: acquiring each The database sub-parameter of the initial SQL statement; calculating according to the database sub-parameters, obtaining the performance consumption ratio of each initial SQL statement; extracting the initial SQL statement whose performance consumption ratio is greater than the preset threshold, forming a core SQL statement set;
  • the statement set is placed in the old version environment and the new version environment for performance analysis to obtain low-performance SQL statements; the low-performance SQL statement is in the core SQL statement set in which the performance of the new version environment is lower than that of the old version environment.
  • the initial SQL statement performance improvement processing for low-performance SQL statements.
  • the database sub-parameters are multiple, and when the computer readable instructions are executed by the processor, before the step of performing the calculation based on the database sub-parameters to obtain the performance consumption ratio of each initial SQL statement, The following steps are also performed: obtaining the total database parameters of all the initial SQL statements; performing the calculation according to the database sub-parameters to obtain the performance consumption ratio of each initial SQL statement, including the following steps: calculating each initial SQL statement The single performance ratio of the database sub-parameters in the corresponding total parameters of the database; the preset reference weights corresponding to the database sub-parameters are obtained; and the single-performance ratio corresponding to the plurality of database sub-parameters and the corresponding preset reference weights are calculated, Get the performance penalty of each initial SQL statement.
  • the computer readable instructions when executed by the processor, performing the performance analysis of the core SQL statement set in the old version environment and the new version environment respectively, obtaining the steps of the low performance SQL statement, including the following Step: Obtain the core SQL statement collection in the old version environment; send the core SQL statement collection in the old version environment to the new version environment; create and execute the SQL performance analysis task and the new version environment of the old version environment in the new version environment
  • the SQL performance analysis task obtains the first analysis parameter and the second analysis parameter respectively;
  • the first analysis parameter is an analysis parameter obtained by executing an SQL performance analysis task of the old version environment;
  • the second analysis parameter is a SQL by executing a new version environment Analysis parameters obtained from the performance analysis task. Comparing the first analysis parameter with the second analysis parameter to obtain a low performance SQL statement.
  • the step of transmitting the set of SQL statements in the old version environment to the new version environment is performed, comprising the steps of: creating a transit table in the old version environment Import the core SQL statement collection in the old version environment into the relay table; send the relay table storing the core SQL statement collection to the new version environment for decompression, and obtain the core SQL statement set in the new version environment.
  • the step of performing a performance improvement process on the low performance SQL statement when the computer readable instructions are executed by the processor comprises the steps of: screening the low performance SQL statement; The processed low-performance SQL statement is split according to the preset number to form a low-performance SQL sentence sub-set; the low-performance SQL sentence sub-set is placed in the old version environment and the new version environment for performance analysis, and low performance is obtained.
  • Low-performance SQL statements in a subset of SQL statements; screening, splitting, and performance analysis of the resulting low-performance SQL statement loop until the performance of the low-performance SQL statement in the new version environment is greater than or equal to that in the old version environment Performance.
  • the following steps are further performed: obtaining an incremental SQL statement; when detecting the incremental SQL statement, the SQL execution plan management SPM is preset, and the incremental SQL statement is When the execution plan in the old version environment is different from the execution plan in the new version environment, the SPM of the incremental SQL statement is removed; whether the execution plan of the incremental SQL statement without the preset SPM in the old version environment is determined The execution plan in the new version environment is the same; if not, the performance of the incremental SQL statement loop is improved until the performance of the incremental SQL statement in the old version environment is greater than or equal to the performance in the new version environment.
  • the database sub-parameters include logical reads, number of executions, and elapsed time.
  • Non-volatile memory can include read only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM), or flash memory.
  • Volatile memory can include random access memory (RAM) or external cache memory.
  • RAM is available in a variety of formats, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronization chain.
  • SRAM static RAM
  • DRAM dynamic RAM
  • SDRAM synchronous DRAM
  • DDRSDRAM double data rate SDRAM
  • ESDRAM enhanced SDRAM
  • Synchlink DRAM SLDRAM
  • Memory Bus Radbus
  • RDRAM Direct RAM
  • DRAM Direct Memory Bus Dynamic RAM
  • RDRAM Memory Bus Dynamic RAM

Landscapes

  • Engineering & Computer Science (AREA)
  • Theoretical Computer Science (AREA)
  • Physics & Mathematics (AREA)
  • Computational Linguistics (AREA)
  • Data Mining & Analysis (AREA)
  • Databases & Information Systems (AREA)
  • General Engineering & Computer Science (AREA)
  • General Physics & Mathematics (AREA)
  • Mathematical Physics (AREA)
  • Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
  • Stored Programmes (AREA)

Abstract

一种SQL语句处理方法，包括：获取每个初始SQL语句的数据库子参数；根据数据库子参数进行计算，得到每个初始SQL语句的性能消耗占比；提取性能消耗占比大于预设阈值的初始SQL语句，形成核心SQL语句集合；将核心SQL语句集合分别置于旧版本环境和新版本环境中进行性能分析，得到低性能SQL语句；低性能SQL语句为在新版本环境的性能低于在旧版本环境的性能的、处于核心SQL语句集合中的初始SQL语句；及对低性能SQL语句进行性能提升处理。

Description

SQL语句处理方法、装置、计算机设备和存储介质
本申请要求于2018年01月30日提交中国专利局，申请号为2018100912405，申请名称为“SQL语句处理方法、装置、计算机设备和存储介质”的中国专利申请的优先权，其全部内容通过引用结合在本申请中。
技术领域
本申请涉及一种SQL语句处理方法、装置、计算机设备和存储介质。
背景技术
SQL(Structured Query Language，结构化查询语言)是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。不同版本关系数据库系统之间的优化器之间会有所差异。优化器的目的是按照一定的判断原则来得到它认为的目标SQL语句在当前的情形下的最高效的执行路径，也就是为了得到目标SQL语句的最佳执行计划。因此相同的SQL语句在不同版本的数据库环境中的执行计划会有所差异，且执行计划会改变SQL语句的性能。通常会采用SPA(SQL Performance Analyze，SQL性能分析)技术对SQL语句进行分析，得到SQL语句分别在关系数据库系统的新版本环境和旧版本环境中的执行计划及性能。对在新版本环境中性能降低的SQL语句进行优化后才能进行关系数据库系统版本升级，以保证关系数据库系统版本升级后SQL语句的性能不降低。
然而，当SQL语句量十分庞大时，传统的SQL语句处理方式需要耗费大量的时间分析定位在新版本环境中性能降低的SQL语句，从而导致关系数据库系统版本升级周期较长。
发明内容
根据本申请公开的各种实施例，提供一种SQL语句处理方法、装置、计算机设备和存储介质。
一种SQL语句处理方法，包括：获取每个初始SQL语句的数据库子参数；根据所述数据库子参数进行计算，得到每个初始SQL语句的性能消耗占比；提取性能消耗占比大于预设阈值的初始SQL语句，形成核心SQL语句集合；将所述核心SQL语句集合分别置于旧版本环境和新版本环境中进行性能分析，得到低性能SQL语句；所述低性能SQL语句为在新版本环境的性能低于在旧版本环境的性能的、处于所述核心SQL语句集合中的初始SQL语句；及对所述低性能SQL语句进行性能提升处理。
一种SQL语句处理装置，包括：数据库子参数获取模块，用于获取每个初始SQL语句的数据库子参数；性能消耗占比计算模块，用于根据所述数据库子参数进行计算，得到每个初始SQL语句的性能消耗占比；核心SQL语句集合形成模块，用于提取性能消耗占 比大于预设阈值的初始SQL语句，形成核心SQL语句集合；低性能SQL语句提取模块，用于将所述核心SQL语句集合分别置于旧版本环境和新版本环境中进行性能分析，得到低性能SQL语句；所述低性能SQL语句为在新版本环境的性能低于在旧版本环境的性能的、处于所述核心SQL语句集合中的初始SQL语句；及低性能SQL语句处理模块，对所述低性能SQL语句进行性能提升处理。
一种计算机设备，包括存储器和一个或多个处理器，所述存储器中储存有计算机可读指令，所述计算机可读指令被所述处理器执行时，使得所述一个或多个处理器执行以下步骤：获取每个初始SQL语句的数据库子参数；根据所述数据库子参数进行计算，得到每个初始SQL语句的性能消耗占比；提取性能消耗占比大于预设阈值的初始SQL语句，形成核心SQL语句集合；将所述核心SQL语句集合分别置于旧版本环境和新版本环境中进行性能分析，得到低性能SQL语句；所述低性能SQL语句为在新版本环境的性能低于在旧版本环境的性能的、处于所述核心SQL语句集合中的初始SQL语句；及对所述低性能SQL语句进行性能提升处理。
一个或多个存储有计算机可读指令的非易失性计算机可读存储介质，计算机可读指令被一个或多个处理器执行时，使得一个或多个处理器执行以下步骤：获取每个初始SQL语句的数据库子参数；根据所述数据库子参数进行计算，得到每个初始SQL语句的性能消耗占比；提取性能消耗占比大于预设阈值的初始SQL语句，形成核心SQL语句集合；将所述核心SQL语句集合分别置于旧版本环境和新版本环境中进行性能分析，得到低性能SQL语句；所述低性能SQL语句为在新版本环境的性能低于在旧版本环境的性能的、处于所述核心SQL语句集合中的初始SQL语句；及对所述低性能SQL语句进行性能提升处理。
本申请的一个或多个实施例的细节在下面的附图和描述中提出。本申请的其它特征和优点将从说明书、附图以及权利要求书变得明显。
附图说明
为了更清楚地说明本申请实施例或现有技术中的技术方案，下面将对实施例或现有技术描述中所需要使用的附图作简单地介绍，显而易见地，下面描述中的附图仅仅是本申请的一些实施例，对于本领域普通技术人员来讲，在不付出创造性劳动的前提下，还可以根据这些附图获得其他的附图。
图1为根据一个或多个实施例中SQL语句处理方法的应用场景图。
图2为根据一个或多个实施例中SQL语句处理方法的流程示意图。
图3为根据另一个或多个实施例中SQL语句处理方法的流程示意图。
图4为根据一个或多个实施例中SQL语句处理装置的结构框图。
图5为根据一个或多个实施例中计算机设备的内部结构图。
具体实施方式
为了使本申请的技术方案及优点更加清楚明白，以下结合附图及实施例，对本申请进行进一步详细说明。应当理解，此处描述的具体实施例仅仅用以解释本申请，并不用于限定本申请。
可以理解，本申请所使用的术语“第一”、“第二”等可在本文中用于描述各种元件，但这些元件不受这些术语的限制。这些术语仅用于将第一个元件与另一个元件区分。举例来说，在不脱离本申请的范围的情况下，可以将第一分析参数称为第二分析参数，且类似地，可将第二分析参数称为第一分析参数。第一分析参数和第二分析参数两者都是分析参数，但其不是同一分析参数。
本申请提供的SQL语句处理方法，可以应用于如图1所示的应用环境中。终端102通过网络与服务器104通过网络进行通信。比如说服务器104可接收终端102发送的初始SQL语句，并对接收的初始SQL语句进行SQL语句处理，还可以将处理后的SQL语句发送至终端102。终端102可以但不限于是各种个人计算机、笔记本电脑、智能手机、平板电脑和便携式可穿戴设备，服务器104可以用独立的服务器或者是多个服务器组成的服务器集群来实现。
在其中一个实施例中，如图2所示，提供了一种SQL语句处理方法，以该方法应用于图1中的服务器102为例进行说明，包括以下步骤：
步骤202，获取每个初始SQL语句的数据库子参数。
初始SQL语句是指是指在升级到新版本关系数据库系统之前，需要进行SQL语句处理的SQL语句。对初始SQL语句进行SQL语句处理是为了保证关系数据库系统版本升级后SQL语句的性能不降低。关系数据库系统是指用于数据管理及数据与字段之间关联关系管理的数据库系统。关系数据库系统包括但不限于Oracle数据库系统、SQL Server数据库系统及MySQL数据库系统等。每种关系数据库可具有多种版本。比如Oracle数据库系统具有多种版本，包括Oracle 10g、Oracle 11g及Oracle 12c等。数据库子参数是指一个初始SQL语句在关系数据库系统中运行之后得到的参数。数据库子参数包括逻辑读、执行次数和消耗时间等其中一种或多种的组合。逻辑读(buffer_gets)是指初始SQL语句在一次运行过程中从数据缓存读取的页数，执行次数(execution_count)是指初始SQL语句在一次运行过程中所被执行的次数，消耗时间(cpu_time)是指初始SQL语句在一次运行过程中消耗CPU(Central Processing Unit，中央处理器)的时间。
在其中一个实施例中，在Oracle数据库系统中，可通过查询V$SQLAREA视图或V$SQL视图中获取每个初始SQL语句的数据库子参数。V$SQLAREA视图和V$SQL视图都是Oracle数据库系统中用于监控SQL语句资源消耗的系统视图。V$SQL视图可记录每个SQL语句的数据库子参数，V$SQLAREA视图还可对SQL语句进行聚合，统计多个SQL语句的数据库子参数之和。比如说，V$SQLAREA视图中，可根据sql_text进行统计，比如说对前1000条初始SQL语句的消耗时间进行统计。
步骤204，根据数据库子参数进行计算，得到每个初始SQL语句的性能消耗占比。
性能消耗占比是指一个SQL语句针对所有SQL语句在一种或多种性能方面消耗的综合占比。可通过逻辑读、执行次数和消耗时间等其中一种或多种在数据库子参数的组合来评估SQL语句的性能。比如说，查询相同的数据，初始SQL语句的消耗时间越久，则说明该初始SQL的执行效率越低，可认为该初始SQL的性能较低。同样的，查询相同的数据，初始SQL语句的执行次数越多，则说明该初始SQL的执行效率越低，可认为该初始SQL的性能较低。
在其中一个实施例中，数据库子参数为多个；在根据数据库子参数进行计算，得到每个初始SQL语句的性能消耗占比之前，还包括：获取所有初始SQL语句的数据库总参数；根据数据库子参数进行计算，得到每个初始SQL语句的性能消耗占比，包括：计算每个初始SQL语句的数据库子参数在相应的数据库总参数中的单性能占比；获取与数据库子参数对应的预设参考权重；根据多个数据库子参数对应的单性能占比和相应的预设参考权重进行计算，得到每个初始SQL语句的性能消耗占比。
数据库总参数是指所有初始SQL语句在关系数据库系统中运行之后得到的总参数。单性能占比是指单项数据库子参数在相应数据库总参数中的占比。比如说，每个初始SQL语句逻辑读r在总逻辑读R的单性能占比
Figure PCTCN2018088974-appb-000001
执行次数c在总执行次数C的单性能占比
Figure PCTCN2018088974-appb-000002
或消耗时间t在总消耗时间T的占比单性能
Figure PCTCN2018088974-appb-000003
可针对逻辑读、执行次数、消耗时间三个数据库子参数分别预设参考权重w1、w2、w3，则可根据公式
Figure PCTCN2018088974-appb-000004
计算得到每个初始SQL语句的性能消耗占比P。
在其中一个实施例中，计算出每个初始SQL语句的多个单性能占比之后，还可将多个单性能占比中的最大值作为初始SQL语句的性能消耗占比，或者可将多个单性能占比的平均值作为初始SQL语句的性能消耗占比，但不限于此。
步骤206，提取性能消耗占比大于预设阈值的初始SQL语句，形成核心SQL语句集合。
核心SQL语句集合是指由性能消耗占比大于预设阈值的多个初始SQL语句所形成的集合。比如说，初始SQL语句的性能消耗占比大于99％时，说明该初始SQL语句对一次运行过程影响很大，则可将性能消耗占比大于预设阈值的多个初始SQL语句从所有的初始SQL语句中提取出来，形成核心SQL语句集合进行进一步处理。
在其中一个实施例中，还可以根据初始SQL语句的性能消耗占比对初始SQL语句进行排序，可以提取排序后性能消耗占比大于预设阈值的初始SQL语句形成核心SQL语句集合，还可以提取排序后前预设百分比的初始SQL语句形成核心SQL语句集合。
步骤208，将核心SQL语句集合分别置于旧版本环境和新版本环境中进行性能分析，得到低性能SQL语句。
旧版本环境是指关系数据库系统版本升级前的关系数据库系统环境。新版本环境是指关系数据库系统版本升级后的关系数据库系统环境。比如说，Oracle数据库系统需要从Oracle 10g版本升级至Oracle 12c版本，则Oracle 10g版本的Oracle数据库系统环境为旧版本环境，Oracle 12c版本的Oracle数据库系统环境为新版本环境。将核心SQL语句集合中的初始SQL语句分别置于旧版本环境和新版本环境中运行，通过性能分析可得到低性能SQL语句。低性能SQL语句为在新版本环境的性能低于在旧版本环境的性能的、处于核心SQL语句集合中的初始SQL语句。
在其中一个实施例中，可从逻辑读、执行次数和消耗时间等其中一种或多种维度来对核心SQL语句集合中的初始SQL语句进行性能分析。比如说，比较核心SQL语句集合中的初始SQL语句分别在旧版本环境和新版本环境中的逻辑读，将在新版本环境中的逻辑读大于在旧版本环境中的逻辑读所对应的初始SQL语句作为低性能SQL语句。
步骤210，对低性能SQL语句进行性能提升处理。
性能提升处理是指对低性能SQL语句的性能进行优化的处理。可以对低性能SQL语句本身进行优化。比如说，对低性能SQL语句的操作符进行优化，包括在业务密集的低性能SQL语句中以EXISTS操作符代替IN操作符，将IS NULL操作符或IS NOT NULL操作符用其它相同功能的操作运算代替等。再比如，调整WHERE之后的条件顺序，使得减少运算量。还可以对相同功能但不同写法的低性能SQL语句进行过滤，减少重复运算。
在其中一个实施例中，当检测到低性能SQL语句的数量超过预设数量时，还可以对低性能SQL语句做进一步筛选。比如说，当检测到低性能SQL语句大于十万个时，可根据每个低性能SQL语句的数据库子参数计算每个低性能SQL语句在所有低性能SQL语句的性能消耗占比，筛选出性能消耗占比大于预设阈值的低性能SQL语句。重复上述筛选过程，直至低性能SQL语句的数量小于预设数量时。对筛选后小于预设数量的低性能SQL语句进行性能提升处理。
上述SQL语句处理方法中，通过对每个初始SQL语句的数据库子参数进行计算，得到每个初始SQL语句的性能消耗占比。从而能够根据性能消耗占比从所有的初始SQL语句中，筛选出对所有SQL语句的执行效率有较大影响的核心SQL语句集合。通过将核心SQL语句集合置于旧版本环境和新版本环境中进行性能分析，再次筛选出在新版本环境中的性能低于在旧版本环境的性能的处于核心SQL语句集合中的低性能SQL语句。使得只需要对少量的低性能SQL语句进行性能提升处理即可保证关系数据库系统版本升级后SQL语句的性能不降低，节约了分析定位在新版本环境中性能降低的SQL语句的时间，从而缩短关系数据库系统版本升级周期。
在其中一个实施例中，将核心SQL语句集合分别置于旧版本环境和新版本环境中进行性能分析，得到低性能SQL语句，包括：获取旧版本环境中的核心SQL语句集合；将旧版本环境中的核心SQL语句集合发送至新版本环境中；在新版本环境中创建并执行旧版本环境的SQL性能分析任务和新版本环境的SQL性能分析任务，分别得到第一分析参 数和第二分析参数；第一分析参数是通过执行旧版本环境的SQL性能分析任务得到的分析参数；第二分析参数是通过执行新版本环境的SQL性能分析任务得到的分析参数。比较第一分析参数和第二分析参数，得到低性能SQL语句。
SQL性能分析任务是指系统数据库中用于分析SQL语句的性能的任务。第一分析参数和第二分析参数是指反映SQL语句的性能的参数，包括但不限于逻辑读、执行次数、消耗时间等数据库子参数，还可以是与执行计划相关的参数等。
举例来说，当旧版本环境中没有SQL性能分析功能而新版本环境中有SQL性能分析功能时，则需要将旧版本环境中的核心SQL语句集合发送至新版本环境中进行性能分析。比如在Oracle数据库系统需要从Oracle 10g版本升级至Oracle 12c版本时，由于SPA(SQL Performance Analyze，SQL性能分析)是Oracle 11g版本之后的Oracle数据库系统才有的新功能。所以在Oracle数据库系统版本升级之前，需要将获取Oracle 10g版本数据库系统中的核心SQL语句集合，将Oracle 10g版本数据库系统中的核心SQL语句集合发送至Oracle 12c版本数据库系统中进行测试。在Oracle 12c版本数据库系统中创建并执行Oracle 10g版本数据库系统环境下的第一SPA任务和Oracle 12c版本数据库系统环境下的第二SPA任务，分别得到第一分析参数和第二分析参数。第一分析参数是通过执行Oracle 10g版本数据库系统环境下的第一SPA任务得到的分析参数；第二分析参数是通过执行Oracle 12c版本数据库系统环境下的第二SPA任务得到的分析参数。比较第一分析参数和第二分析参数，得到低性能SQL语句。
在其中一个实施例中，将旧版本环境中的SQL语句集合发送至新版本环境中，包括：在旧版本环境中创建中转表；将旧版本环境中的核心SQL语句集合导入中转表；将存储有核心SQL语句集合的中转表发送至新版本环境中进行解压，得到新版本环境中的核心SQL语句集合。
中转表是指将一个关系数据库系统中的数据复制或转移到另一个关系数据库系统中的表。在系统数据库版本升级前，初始SQL语句是处于旧版本环境中的，因此核心SQL语句集合也是出于旧版本环境中。需要在旧版本环境中创建中转表，并将旧版本环境中的核心SQL语句集合中的初始SQL语句导入中转表；将存储有核心SQL语句集合的中转表发送至新版本环境中进行解压，在新版本环境中得到的核心SQL语句集合。还可对中转表预设版本号，使得后续能够快速查找到每一次性能分析过程中所对应的核心SQL语句集合。通过将旧版本环境中的核心SQL语句集合通过中转表转移到新版本环境中，还能够提高性能分析对象的精准性。
在其中一个实施例中，对于关系数据库系统版本准备升级至关系数据库系统版本升级的过程中，还可存在新增的SQL语句。如图3所示，SQL语句处理方法还包括对新增的SQL语句的处理步骤，具体包括以下步骤：
步骤302，获取增量SQL语句。
增量SQL语句是指在关系数据库系统版本准备升级至关系数据库系统版本升级的过 程中新增的SQL语句。同样的，可对增量SQL语句进行筛除处理，即去除重复的增量SQL语句、执行报错的增量SQL语句和没有抓到绑定变量值但包含绑定变量的增量SQL语句等。对执行成功及筛除后的增量SQL语句做进一步处理。
步骤304，检测增量SQL语句是否预置了SQL执行计划管理SPM。
SQL执行计划管理SPM(SQL plan Management，Oracle执行计划管理)是指Oracle数据库系统自动去判断某个SQL的新的执行计划是否更加合理的功能，只有在新的执行计划比原来的执行计划更好的情况下，该新的执行计划才会被使用。当检测到增量SQL语句预置了SQL执行计划管理SPM时，则说明该增量SQL语句绑定了固定的执行计划，执行步骤306；当检测到增量SQL语句未预置SQL执行计划管理SPM时，执行步骤310。
步骤306，判断预置了SPM的增量SQL语句在旧版本环境中的执行计划是否与在新版本环境中的执行计划相同。
若预置了SPM的增量SQL语句在旧版本环境中的执行计划与在新版本环境中的执行计划相同，则执行步骤314，不作处理；若预置了SPM的增量SQL语句在旧版本环境中的执行计划与在新版本环境中的执行计划不相同，则执行步骤308。
举例来说，要获取表中的某一行数据，可以采取通过索引快速访问表的执行计划，也可以采取读取整个表并根据关键词进行筛选过滤得到数据的执行计划。具体选择哪个执行计划，是由Oracle数据库系统的优化器决定的。由于不同版本关系数据库系统之间的优化器之间会有所差异，所以相同的SQL语句若绑定了固定的执行计划，则该SQL语句在旧版本环境中的执行计划与在新版本环境中的执行计划可能出现不相同的情况。
步骤308，去除增量SQL语句的SPM。
当检测到增量SQL语句预置了SQL执行计划管理SPM，且增量SQL语句在旧版本环境中的执行计划与在新版本环境中的执行计划不相同时，则去除增量SQL语句的SPM。在去除增量SQL语句的SPM之后，可将去除了SPM的增量SQL语句再次在旧版本环境和新版本环境中运行，进一步判断去除了SPM的增量SQL语句的执行计划在旧版本环境和新版本环境中是否存在差异。
步骤310，判断未预置SPM的增量SQL语句在旧版本环境中的执行计划是否与在新版本环境中的执行计划相同。
若未预置SPM的增量SQL语句在旧版本环境中的执行计划与在新版本环境中的执行计划相同，则说明该增量SQL语句在新版本环境和旧版本环境性能相同，执行步骤314，不作处理；若否，则说明该增量SQL语句在新版本环境和旧版本环境性能不相同，需要进一步处理，执行步骤312。
步骤312，对增量SQL语句循环进行性能提升处理，直至增量SQL语句在旧版本环境中的性能大于等于在新版本环境中的性能。
可根据上述对初始SQL语句进行处理的方式，对增量SQL语句循环进行性能提升处理，直至处理后的增量SQL语句在旧版本环境中的性能大于等于在新版本环境中的性能。
步骤314，不作处理。
上述实施例中，对关系数据库系统版本准备升级至关系数据库系统版本升级的过程中新增的增量SQL语句进行了性能分析。通过判断增量SQL语句是否预置了SQL执行计划管理SPM，以及增量SQL语句是否在旧版本环境中的执行计划与在新版本环境中的执行计划相同，并根据判断结果对增量SQL语句进行了相应的处理，保证了增量SQL语句在关系数据库系统版本升级后的性能也不降低。
在其中一个实施例中，对低性能SQL语句进行性能提升处理包括：对低性能SQL语句进行筛除处理；对进行筛除处理后的低性能SQL语句按照预设条数进行拆分处理，形成低性能SQL语句子集合；对低性能SQL语句子集合分别置于旧版本环境和新版本环境中进行性能分析，得到低性能SQL语句子集合中的低性能SQL语句；对得到的低性能SQL语句循环进行筛除处理、拆分处理和性能分析，直至低性能SQL语句在新版本环境中的性能大于等于在旧版本环境中的性能。
筛除处理是指将不影响执行结果的低性能SQL语句进行剔除的处理。可筛除功能重复的低性能SQL语句。比如说，可对每个初始SQL语句都预设全局唯一的标识SQL_ID，该标识可通过hash算法生成。根据SQL_ID去除低性能SQL语句中重复的低性能SQL语句。再比如，可以提取出数据库子参数及执行结果相同的低性能SQL语句进行筛除。可以去除非绑定变量的低性能SQL语句，可通过FORCE_MATCHING_SIGNATURE字段查看非绑定变量的低性能SQL语句。可将只执行一次的低性能SQL语句去除，比如去除DEVSUP、DBMON、SOLIX、APPMGR等实名用户所对应的低性能SQL语句。可去除非应用发起的低性能SQL语句，比如用户发起的模块为“SQL*Plus”或“PL/SQL Developer”的SQL语句。可去除数据库本身发起的低性能SQL语句，比如说，去除优化器指令为“/*OPT_DYN_SAMP*/”的低性能SQL语句。还可去除无用的函数，比如说，去除与排序相关的用于生成序号的nextval函数。还可去除不会改变的静态数据，比如去除MIG相关配置表。
在其中一个实施例中，还可以去除没有抓到对应绑定变量值的包含绑定变量的低性能SQL语句。绑定变量的是来代替sql语句中的常量的变量，通过绑定变量可以减少SQL的解析,节约共享池的空间。提高并行度，并行度是指指令或数据并行执行的最大数目。比如有个低性能SQL语句通过改变eee这个谓词赋值来查询：select aaa，bbb from ccc where ddd＝fff；select aaa，bbb from ccc where ddd＝ggg；select aaa，bbb from ccc where ddd＝hhh。每条语句都要被数据库解析一次，这样比较浪费资源，如果把eee换成“:1”这样的绑定变量形式，无论ddd后面是什么值，都不需要重复解析。
拆分处理是指将所有的低性能SQL语句拆分成多个低性能SQL语句子集合的处理。可以在通过逻辑读、执行次数、消耗时间等其中一种或多种数据库子参数筛选出所有低性能SQL语句中，性能消耗占比大于预设阈值的低性能SQL语句之后，对剩余的性能消耗占比小于预设阈值的低性能SQL语句进行拆分处理。比如说，按照预设数量，比如可将 每10000个低性能SQL语句，拆分一个低性能SQL语句子集合。
对经过筛除处理和拆分处理之后得到的低性能SQL语句子集合分别进行回放处理及性能分析，可得到低性能SQL语句子集合中的低性能SQL语句。回放处理是指对低性能SQL语句子集合中的低性能SQL语句再次置于旧版本环境和新版本环境中执行。可提取出逻辑读在旧版本环境和新版本环境中存在差异的，即前后对比差值大于0的低性能SQL语句。还可去除回放有报错的，即回放执行次数为0。还可去除无回放SQL语句，即没有抓到绑定变量值但包含绑定变量的低性能SQL语句。还可将回放处理之后的低性能SQL语句子集合再次根据逻辑读、执行次数、消耗时间等其中一种或多种数据库子参数进行筛选，得到低性能SQL语句子集合中的核心SQL语句集合。对低性能SQL语句子集合中的核心SQL语句集合进行性能分析，可针对执行计划发生变化的，提取出执行次数大于预设执行次数，比如50，并且取出单次的逻辑读大于预设逻辑读，比如100，，并且满足变化率超过20％的低性能SQL语句。再次根据数据库子参数进行筛选，得到低性能SQL语句子集合中的低性能SQL语句。
上述实施例中，通过对低性能SQL语句不断循环进行上述筛除处理、拆分处理和性能分析，能够使得循环处理后的低性能SQL语句在新版本环境中的性能大于等于在旧版本环境中的性能，从而保证了关系数据库系统版本升级后SQL语句的性能不降低。
应该理解的是，虽然图2和3的流程图中的各个步骤按照箭头的指示依次显示，但是这些步骤并不是必然按照箭头指示的顺序依次执行。除非本文中有明确的说明，这些步骤的执行并没有严格的顺序限制，这些步骤可以以其它的顺序执行。而且，图2和3中的至少一部分步骤可以包括多个子步骤或者多个阶段，这些子步骤或者阶段并不必然是在同一时刻执行完成，而是可以在不同的时刻执行，这些子步骤或者阶段的执行顺序也不必然是依次进行，而是可以与其它步骤或者其它步骤的子步骤或者阶段的至少一部分轮流或者交替地执行。
在其中一个实施例中，如图4所示，提供了一种SQL语句处理装置400，包括：数据库子参数获取模块402，用于获取每个初始SQL语句的数据库子参数；性能消耗占比计算模块404，用于根据数据库子参数进行计算，得到每个初始SQL语句的性能消耗占比；核心SQL语句集合形成模块406，用于提取性能消耗占比大于预设阈值的初始SQL语句，形成核心SQL语句集合；低性能SQL语句提取模块408，用于将核心SQL语句集合分别置于旧版本环境和新版本环境中进行性能分析，得到低性能SQL语句；低性能SQL语句为在新版本环境的性能低于在旧版本环境的性能的、处于核心SQL语句集合中的初始SQL语句；低性能SQL语句处理模块410，对低性能SQL语句进行性能提升处理。
在其中一个实施例中，数据库子参数为多个，性能消耗占比计算模块404还用于获取所有初始SQL语句的数据库总参数；计算每个初始SQL语句的数据库子参数在相应的数据库总参数中的单性能占比；获取与数据库子参数对应的预设参考权重；根据多个数据库子参数对应的单性能占比和相应的预设参考权重进行计算，得到每个初始SQL语句的 性能消耗占比。
在其中一个实施例中，低性能SQL语句提取模块408还用于获取旧版本环境中的核心SQL语句集合；将旧版本环境中的核心SQL语句集合发送至新版本环境中；在新版本环境中创建并执行旧版本环境的SQL性能分析任务和新版本环境的SQL性能分析任务，分别得到第一分析参数和第二分析参数；第一分析参数是通过执行旧版本环境的SQL性能分析任务得到的分析参数；第二分析参数是通过执行新版本环境的SQL性能分析任务得到的分析参数。比较第一分析参数和第二分析参数，得到低性能SQL语句。
在其中一个实施例中，低性能SQL语句提取模块408还用于在旧版本环境中创建中转表；将旧版本环境中的核心SQL语句集合导入中转表；将存储有核心SQL语句集合的中转表发送至新版本环境中进行解压，得到新版本环境中的核心SQL语句集合。
在其中一个实施例中，低性能SQL语句处理模块410还用于对低性能SQL语句进行筛除处理；对进行筛除处理后的低性能SQL语句按照预设条数进行拆分处理，形成低性能SQL语句子集合；对低性能SQL语句子集合分别置于旧版本环境和新版本环境中进行性能分析，得到低性能SQL语句子集合中的低性能SQL语句；对得到的低性能SQL语句循环进行筛除处理、拆分处理和性能分析，直至低性能SQL语句在新版本环境中的性能大于等于在旧版本环境中的性能。
在其中一个实施例中，该装置还包括增量SQL语句处理模块，用于获取增量SQL语句；当检测到增量SQL语句预置了SQL执行计划管理SPM，且增量SQL语句在旧版本环境中的执行计划与在新版本环境中的执行计划不相同时，则去除增量SQL语句的SPM；判断未预置SPM的增量SQL语句在旧版本环境中的执行计划是否与在新版本环境中的执行计划相同；若否，则对增量SQL语句循环进行性能提升处理，直至增量SQL语句在旧版本环境中的性能大于等于在新版本环境中的性能。
关于SQL语句处理装置的具体限定可以参见上文中对于SQL语句处理方法的限定，在此不再赘述。上述SQL语句处理装置中的各个模块可全部或部分通过软件、硬件及其组合来实现。上述各模块可以硬件形式内嵌于或独立于计算机设备中的处理器中，也可以以软件形式存储于计算机设备中的存储器中，以便于处理器调用执行以上各个模块对应的操作。
在其中一个实施例中，提供了一种计算机设备，该计算机设备可以是服务器，其内部结构图可以如图5所示。该计算机设备包括通过系统总线连接的处理器、存储器、网络接口和数据库。该计算机设备的处理器用于提供计算和控制能力。该计算机设备的存储器包括非易失性存储介质、内存储器。该非易失性存储介质存储有操作系统、计算机可读指令和数据库。该非易失性存储介质可以是非易失性计算机可读存储介质。该内存储器为非易失性存储介质中的操作系统和计算机可读指令的运行提供环境。该计算机设备的数据库用于存储初始SQL语句等数据。该计算机设备的网络接口用于与外部的终端通过网络连接通信。该计算机可读指令被处理器执行时以实现一种SQL语句处理方法。
本领域技术人员可以理解，图5中示出的结构，仅仅是与本申请方案相关的部分结构的框图，并不构成对本申请方案所应用于其上的计算机设备的限定，具体的计算机设备可以包括比图中所示更多或更少的部件，或者组合某些部件，或者具有不同的部件布置。
一种计算机设备，包括存储器和一个或多个处理器，存储器中存储有计算机可读指令，计算机可读指令被处理器执行时实现以下步骤：获取每个初始SQL语句的数据库子参数；根据数据库子参数进行计算，得到每个初始SQL语句的性能消耗占比；提取性能消耗占比大于预设阈值的初始SQL语句，形成核心SQL语句集合；将核心SQL语句集合分别置于旧版本环境和新版本环境中进行性能分析，得到低性能SQL语句；低性能SQL语句为在新版本环境的性能低于在旧版本环境的性能的、处于核心SQL语句集合中的初始SQL语句；对低性能SQL语句进行性能提升处理。
在其中一个实施例中，数据库子参数为多个，处理器执行计算机可读指令时，在所执行的根据数据库子参数进行计算，得到每个初始SQL语句的性能消耗占比的步骤之前，还实现以下步骤：获取所有初始SQL语句的数据库总参数；所执行的根据数据库子参数进行计算，得到每个初始SQL语句的性能消耗占比的步骤，包括以下步骤：计算每个初始SQL语句的数据库子参数在相应的数据库总参数中的单性能占比；获取与数据库子参数对应的预设参考权重；根据多个数据库子参数对应的单性能占比和相应的预设参考权重进行计算，得到每个初始SQL语句的性能消耗占比。
在其中一个实施例中，处理器执行计算机可读指令时，所执行的将核心SQL语句集合分别置于旧版本环境和新版本环境中进行性能分析，得到低性能SQL语句的步骤，包括以下步骤：获取旧版本环境中的核心SQL语句集合；将旧版本环境中的核心SQL语句集合发送至新版本环境中；在新版本环境中创建并执行旧版本环境的SQL性能分析任务和新版本环境的SQL性能分析任务，分别得到第一分析参数和第二分析参数；第一分析参数是通过执行旧版本环境的SQL性能分析任务得到的分析参数；第二分析参数是通过执行新版本环境的SQL性能分析任务得到的分析参数。比较第一分析参数和第二分析参数，得到低性能SQL语句。
在其中一个实施例中，处理器执行计算机可读指令时，所执行的将旧版本环境中的SQL语句集合发送至新版本环境中的步骤，包括以下步骤：在旧版本环境中创建中转表；将旧版本环境中的核心SQL语句集合导入中转表；将存储有核心SQL语句集合的中转表发送至新版本环境中进行解压，得到新版本环境中的核心SQL语句集合。
在其中一个实施例中，处理器执行计算机可读指令时，所执行的对低性能SQL语句进行性能提升处理的步骤，包括以下步骤：对低性能SQL语句进行筛除处理；对进行筛除处理后的低性能SQL语句按照预设条数进行拆分处理，形成低性能SQL语句子集合；对低性能SQL语句子集合分别置于旧版本环境和新版本环境中进行性能分析，得到低性能SQL语句子集合中的低性能SQL语句；对得到的低性能SQL语句循环进行筛除处理、拆分处理和性能分析，直至低性能SQL语句在新版本环境中的性能大于等于在旧版本环 境中的性能。
在其中一个实施例中，处理器执行计算机可读指令时，还实现以下步骤：获取增量SQL语句；当检测到增量SQL语句预置了SQL执行计划管理SPM，且增量SQL语句在旧版本环境中的执行计划与在新版本环境中的执行计划不相同时，则去除增量SQL语句的SPM；判断未预置SPM的增量SQL语句在旧版本环境中的执行计划是否与在新版本环境中的执行计划相同；若否，则对增量SQL语句循环进行性能提升处理，直至增量SQL语句在旧版本环境中的性能大于等于在新版本环境中的性能。
在其中一个实施例中，数据库子参数包括逻辑读、执行次数和消耗时间。
一个或多个存储有计算机可读指令的非易失性计算机可读存储介质，计算机可读指令被一个或多个处理器执行时，使得一个或多个处理器执行以下步骤：：获取每个初始SQL语句的数据库子参数；根据数据库子参数进行计算，得到每个初始SQL语句的性能消耗占比；提取性能消耗占比大于预设阈值的初始SQL语句，形成核心SQL语句集合；将核心SQL语句集合分别置于旧版本环境和新版本环境中进行性能分析，得到低性能SQL语句；低性能SQL语句为在新版本环境的性能低于在旧版本环境的性能的、处于核心SQL语句集合中的初始SQL语句；对低性能SQL语句进行性能提升处理。
在其中一个实施例中，数据库子参数为多个，计算机可读指令被处理器执行时，在所执行的根据数据库子参数进行计算，得到每个初始SQL语句的性能消耗占比的步骤之前，还实现以下步骤：获取所有初始SQL语句的数据库总参数；所执行的根据数据库子参数进行计算，得到每个初始SQL语句的性能消耗占比的步骤，包括以下步骤：计算每个初始SQL语句的数据库子参数在相应的数据库总参数中的单性能占比；获取与数据库子参数对应的预设参考权重；根据多个数据库子参数对应的单性能占比和相应的预设参考权重进行计算，得到每个初始SQL语句的性能消耗占比。
在其中一个实施例中，计算机可读指令被处理器执行时，所执行的将核心SQL语句集合分别置于旧版本环境和新版本环境中进行性能分析，得到低性能SQL语句的步骤，包括以下步骤：获取旧版本环境中的核心SQL语句集合；将旧版本环境中的核心SQL语句集合发送至新版本环境中；在新版本环境中创建并执行旧版本环境的SQL性能分析任务和新版本环境的SQL性能分析任务，分别得到第一分析参数和第二分析参数；第一分析参数是通过执行旧版本环境的SQL性能分析任务得到的分析参数；第二分析参数是通过执行新版本环境的SQL性能分析任务得到的分析参数。比较第一分析参数和第二分析参数，得到低性能SQL语句。
在其中一个实施例中，计算机可读指令被处理器执行时，所执行的将旧版本环境中的SQL语句集合发送至新版本环境中的步骤，包括以下步骤：在旧版本环境中创建中转表；将旧版本环境中的核心SQL语句集合导入中转表；将存储有核心SQL语句集合的中转表发送至新版本环境中进行解压，得到新版本环境中的核心SQL语句集合。
在其中一个实施例中，计算机可读指令被处理器执行时，所执行的对低性能SQL语 句进行性能提升处理的步骤，包括以下步骤：对低性能SQL语句进行筛除处理；对进行筛除处理后的低性能SQL语句按照预设条数进行拆分处理，形成低性能SQL语句子集合；对低性能SQL语句子集合分别置于旧版本环境和新版本环境中进行性能分析，得到低性能SQL语句子集合中的低性能SQL语句；对得到的低性能SQL语句循环进行筛除处理、拆分处理和性能分析，直至低性能SQL语句在新版本环境中的性能大于等于在旧版本环境中的性能。
在其中一个实施例中，计算机可读指令被处理器执行时，还实现以下步骤：获取增量SQL语句；当检测到增量SQL语句预置了SQL执行计划管理SPM，且增量SQL语句在旧版本环境中的执行计划与在新版本环境中的执行计划不相同时，则去除增量SQL语句的SPM；判断未预置SPM的增量SQL语句在旧版本环境中的执行计划是否与在新版本环境中的执行计划相同；若否，则对增量SQL语句循环进行性能提升处理，直至增量SQL语句在旧版本环境中的性能大于等于在新版本环境中的性能。
在其中一个实施例中，数据库子参数包括逻辑读、执行次数和消耗时间。
本领域普通技术人员可以理解实现上述实施例方法中的全部或部分流程，是可以通过计算机可读指令来指令相关的硬件来完成，所述的计算机可读指令可存储于一非易失性计算机可读取存储介质中，该计算机可读指令在执行时，可包括如上述各方法的实施例的流程。本申请所提供的各实施例中所使用的对存储器、存储、数据库或其它介质的任何引用，均可包括非易失性和/或易失性存储器。非易失性存储器可包括只读存储器(ROM)、可编程ROM(PROM)、电可编程ROM(EPROM)、电可擦除可编程ROM(EEPROM)或闪存。易失性存储器可包括随机存取存储器(RAM)或者外部高速缓冲存储器。作为说明而非局限，RAM以多种形式可得，诸如静态RAM(SRAM)、动态RAM(DRAM)、同步DRAM(SDRAM)、双数据率SDRAM(DDRSDRAM)、增强型SDRAM(ESDRAM)、同步链路(Synchlink)DRAM(SLDRAM)、存储器总线(Rambus)直接RAM(RDRAM)、直接存储器总线动态RAM(DRDRAM)、以及存储器总线动态RAM(RDRAM)等。
以上实施例的各技术特征可以进行任意的组合，为使描述简洁，未对上述实施例中的各个技术特征所有可能的组合都进行描述，然而，只要这些技术特征的组合不存在矛盾，都应当认为是本说明书记载的范围。
以上所述实施例仅表达了本申请的几种实施方式，其描述较为具体和详细，但并不能因此而理解为对发明专利范围的限制。应当指出的是，对于本领域的普通技术人员来说，在不脱离本申请构思的前提下，还可以做出若干变形和改进，这些都属于本申请的保护范围。因此，本申请专利的保护范围应以所附权利要求为准。

Claims (20)

  1. 一种SQL语句处理方法，包括：
    获取每个初始SQL语句的数据库子参数；
    根据所述数据库子参数进行计算，得到每个初始SQL语句的性能消耗占比；
    提取性能消耗占比大于预设阈值的初始SQL语句，形成核心SQL语句集合；
    将所述核心SQL语句集合分别置于旧版本环境和新版本环境中进行性能分析，得到低性能SQL语句；所述低性能SQL语句为在新版本环境的性能低于在旧版本环境的性能的、处于所述核心SQL语句集合中的初始SQL语句；及
    对所述低性能SQL语句进行性能提升处理。
  2. 根据权利要求1所述的方法，其特征在于，所述数据库子参数为多个；在所述根据所述数据库子参数进行计算，得到每个初始SQL语句的性能消耗占比之前，还包括：
    获取所有初始SQL语句的数据库总参数；
    所述根据所述数据库子参数进行计算，得到每个初始SQL语句的性能消耗占比，包括：
    计算每个初始SQL语句的数据库子参数在相应的数据库总参数中的单性能占比；
    获取与数据库子参数对应的预设参考权重；及
    根据多个数据库子参数对应的单性能占比和相应的预设参考权重进行计算，得到每个初始SQL语句的性能消耗占比。
  3. 根据权利要求1所述的方法，其特征在于，所述将所述核心SQL语句集合分别置于旧版本环境和新版本环境中进行性能分析，得到低性能SQL语句，包括：
    获取旧版本环境中的核心SQL语句集合；
    将旧版本环境中的核心SQL语句集合发送至新版本环境中；
    在新版本环境中创建并执行旧版本环境的SQL性能分析任务和新版本环境的SQL性能分析任务，分别得到第一分析参数和第二分析参数；所述第一分析参数是通过执行所述旧版本环境的SQL性能分析任务得到的分析参数；所述第二分析参数是通过执行所述新版本环境的SQL性能分析任务得到的分析参数；及
    比较第一分析参数和第二分析参数，得到低性能SQL语句。
  4. 根据权利要求3所述的方法，其特征在于，所述将旧版本环境中的SQL语句集合发送至新版本环境中，包括：
    在所述旧版本环境中创建中转表；
    将所述旧版本环境中的核心SQL语句集合导入所述中转表；及
    将存储有核心SQL语句集合的中转表发送至新版本环境中进行解压，得到所述新版本环境中的核心SQL语句集合。
  5. 根据权利要求1至4任意一项所述的方法，其特征在于，所述对所述低性能SQL语句进行性能提升处理，包括：
    对所述低性能SQL语句进行筛除处理；
    对进行筛除处理后的低性能SQL语句按照预设条数进行拆分处理，形成低性能SQL语句子集合；
    对低性能SQL语句子集合分别置于旧版本环境和新版本环境中进行性能分析，得到低性能SQL语句子集合中的低性能SQL语句；及
    对得到的低性能SQL语句循环进行筛除处理、拆分处理和性能分析，直至低性能SQL语句在新版本环境中的性能大于等于在旧版本环境中的性能。
  6. 根据权利要求1至4任意一项所述的方法，其特征在于，还包括：
    获取增量SQL语句；
    当检测到所述增量SQL语句预置了SQL执行计划管理SPM，且所述增量SQL语句在旧版本环境中的执行计划与在新版本环境中的执行计划不相同时，则去除所述增量SQL语句的SPM；及
    判断未预置SPM的增量SQL语句在旧版本环境中的执行计划是否与在新版本环境中的执行计划相同；
    若否，则对所述增量SQL语句循环进行性能提升处理，直至增量SQL语句在旧版本环境中的性能大于等于在新版本环境中的性能。
  7. 根据权利要求1至4任意一项所述的方法，其特征在于，所述数据库子参数包括逻辑读、执行次数和消耗时间。
  8. 一种SQL语句处理装置，包括：
    数据库子参数获取模块，用于获取每个初始SQL语句的数据库子参数；
    性能消耗占比计算模块，用于根据所述数据库子参数进行计算，得到每个初始SQL语句的性能消耗占比；
    核心SQL语句集合形成模块，用于提取性能消耗占比大于预设阈值的初始SQL语句，形成核心SQL语句集合；
    低性能SQL语句提取模块，用于将所述核心SQL语句集合分别置于旧版本环境和新版本环境中进行性能分析，得到低性能SQL语句；所述低性能SQL语句为在新版本环境的性能低于在旧版本环境的性能的、处于所述核心SQL语句集合中的初始SQL语句；及低性能SQL语句处理模块，对所述低性能SQL语句进行性能提升处理。
  9. 一种计算机设备，包括存储器及一个或多个处理器，所述存储器中储存有计算机可读指令，所述计算机可读指令被所述一个或多个处理器执行时，使得所述一个或多个处理器执行以下步骤：获取每个初始SQL语句的数据库子参数；
    根据所述数据库子参数进行计算，得到每个初始SQL语句的性能消耗占比；
    提取性能消耗占比大于预设阈值的初始SQL语句，形成核心SQL语句集合；
    将所述核心SQL语句集合分别置于旧版本环境和新版本环境中进行性能分析，得到低性能SQL语句；所述低性能SQL语句为在新版本环境的性能低于在旧版本环境的性能 的、处于所述核心SQL语句集合中的初始SQL语句；及
    对所述低性能SQL语句进行性能提升处理。
  10. 根据权利要求9所述的计算机设备，其特征在于，所述处理器执行所述计算机可读指令时，在所执行的所述根据所述数据库子参数进行计算，得到每个初始SQL语句的性能消耗占比的步骤之前，还执行以下步骤：
    获取所有初始SQL语句的数据库总参数；
    所执行的所述根据所述数据库子参数进行计算，得到每个初始SQL语句的性能消耗占比的步骤，包括以下步骤：
    计算每个初始SQL语句的数据库子参数在相应的数据库总参数中的单性能占比；
    获取与数据库子参数对应的预设参考权重；及
    根据多个数据库子参数对应的单性能占比和相应的预设参考权重进行计算，得到每个初始SQL语句的性能消耗占比。
  11. 根据权利要求9所述的计算机设备，其特征在于，所述处理器执行所述计算机可读指令时，所执行的所述将所述核心SQL语句集合分别置于旧版本环境和新版本环境中进行性能分析，得到低性能SQL语句的步骤，包括以下步骤：
    获取旧版本环境中的核心SQL语句集合；
    将旧版本环境中的核心SQL语句集合发送至新版本环境中；
    在新版本环境中创建并执行旧版本环境的SQL性能分析任务和新版本环境的SQL性能分析任务，分别得到第一分析参数和第二分析参数；所述第一分析参数是通过执行所述旧版本环境的SQL性能分析任务得到的分析参数；所述第二分析参数是通过执行所述新版本环境的SQL性能分析任务得到的分析参数；及
    比较第一分析参数和第二分析参数，得到低性能SQL语句。
  12. 根据权利要求11所述的计算机设备，其特征在于，所述处理器执行所述计算机可读指令时，所执行的所述将旧版本环境中的SQL语句集合发送至新版本环境中的步骤，包括以下步骤：
    在所述旧版本环境中创建中转表；
    将所述旧版本环境中的核心SQL语句集合导入所述中转表；及
    将存储有核心SQL语句集合的中转表发送至新版本环境中进行解压，得到所述新版本环境中的核心SQL语句集合。
  13. 根据权利要求9至12任意一项所述的计算机设备，其特征在于，所述处理器执行所述计算机可读指令时，所执行的所述对所述低性能SQL语句进行性能提升处理的步骤，包括以下步骤：
    对所述低性能SQL语句进行筛除处理；
    对进行筛除处理后的低性能SQL语句按照预设条数进行拆分处理，形成低性能SQL语句子集合；
    对低性能SQL语句子集合分别置于旧版本环境和新版本环境中进行性能分析，得到低性能SQL语句子集合中的低性能SQL语句；及
    对得到的低性能SQL语句循环进行筛除处理、拆分处理和性能分析，直至低性能SQL语句在新版本环境中的性能大于等于在旧版本环境中的性能。
  14. 根据权利要求9至12任意一项所述的计算机设备，其特征在于，所述处理器执行所述计算机可读指令时，还执行以下步骤：
    获取增量SQL语句；
    当检测到所述增量SQL语句预置了SQL执行计划管理SPM，且所述增量SQL语句在旧版本环境中的执行计划与在新版本环境中的执行计划不相同时，则去除所述增量SQL语句的SPM；及
    判断未预置SPM的增量SQL语句在旧版本环境中的执行计划是否与在新版本环境中的执行计划相同；
    若否，则对所述增量SQL语句循环进行性能提升处理，直至增量SQL语句在旧版本环境中的性能大于等于在新版本环境中的性能。
  15. 一个或多个存储有计算机可读指令的非易失性计算机可读存储介质，所述计算机可读指令被一个或多个处理器执行时，使得所述一个或多个处理器执行以下步骤：
    根据所述数据库子参数进行计算，得到每个初始SQL语句的性能消耗占比；
    提取性能消耗占比大于预设阈值的初始SQL语句，形成核心SQL语句集合；
    将所述核心SQL语句集合分别置于旧版本环境和新版本环境中进行性能分析，得到低性能SQL语句；所述低性能SQL语句为在新版本环境的性能低于在旧版本环境的性能的、处于所述核心SQL语句集合中的初始SQL语句；及
    对所述低性能SQL语句进行性能提升处理。
  16. 根据权利要求15所述的存储介质，其特征在于，所述计算机可读指令被一个或多个处理器执行时，在所执行的所述根据所述数据库子参数进行计算，得到每个初始SQL语句的性能消耗占比的步骤之前，还执行以下步骤：
    获取所有初始SQL语句的数据库总参数；
    所执行的所述根据所述数据库子参数进行计算，得到每个初始SQL语句的性能消耗占比的步骤，包括以下步骤：
    计算每个初始SQL语句的数据库子参数在相应的数据库总参数中的单性能占比；
    获取与数据库子参数对应的预设参考权重；及
    根据多个数据库子参数对应的单性能占比和相应的预设参考权重进行计算，得到每个初始SQL语句的性能消耗占比。
  17. 根据权利要求15所述的存储介质，其特征在于，所述计算机可读指令被一个或多个处理器执行时，所执行的所述将所述核心SQL语句集合分别置于旧版本环境和新版本环境中进行性能分析，得到低性能SQL语句的步骤，包括以下步骤：
    获取旧版本环境中的核心SQL语句集合；
    将旧版本环境中的核心SQL语句集合发送至新版本环境中；
    在新版本环境中创建并执行旧版本环境的SQL性能分析任务和新版本环境的SQL性能分析任务，分别得到第一分析参数和第二分析参数；所述第一分析参数是通过执行所述旧版本环境的SQL性能分析任务得到的分析参数；所述第二分析参数是通过执行所述新版本环境的SQL性能分析任务得到的分析参数；及
    比较第一分析参数和第二分析参数，得到低性能SQL语句。
  18. 根据权利要求17所述的存储介质，其特征在于，所述计算机可读指令被一个或多个处理器执行时，所执行的所述将旧版本环境中的SQL语句集合发送至新版本环境中的步骤，包括以下步骤：
    在所述旧版本环境中创建中转表；
    将所述旧版本环境中的核心SQL语句集合导入所述中转表；及
    将存储有核心SQL语句集合的中转表发送至新版本环境中进行解压，得到所述新版本环境中的核心SQL语句集合。
  19. 根据权利要求15至18任意一项所述的存储介质，其特征在于，所述计算机可读指令被一个或多个处理器执行时，所执行的所述对所述低性能SQL语句进行性能提升处理的步骤，包括以下步骤：
    对所述低性能SQL语句进行筛除处理；
    对进行筛除处理后的低性能SQL语句按照预设条数进行拆分处理，形成低性能SQL语句子集合；
    对低性能SQL语句子集合分别置于旧版本环境和新版本环境中进行性能分析，得到低性能SQL语句子集合中的低性能SQL语句；及
    对得到的低性能SQL语句循环进行筛除处理、拆分处理和性能分析，直至低性能SQL语句在新版本环境中的性能大于等于在旧版本环境中的性能。
  20. 根据权利要求15至18任意一项所述的存储介质，其特征在于，所述计算机可读指令被一个或多个处理器执行时，还执行以下步骤：
    获取增量SQL语句；
    当检测到所述增量SQL语句预置了SQL执行计划管理SPM，且所述增量SQL语句在旧版本环境中的执行计划与在新版本环境中的执行计划不相同时，则去除所述增量SQL语句的SPM；及
    判断未预置SPM的增量SQL语句在旧版本环境中的执行计划是否与在新版本环境中的执行计划相同；
    若否，则对所述增量SQL语句循环进行性能提升处理，直至增量SQL语句在旧版本环境中的性能大于等于在新版本环境中的性能。
PCT/CN2018/088974 2018-01-30 2018-05-30 Sql语句处理方法、装置、计算机设备和存储介质 WO2019148713A1 (zh)

Applications Claiming Priority (2)

Application Number Priority Date Filing Date Title
CN201810091240.5A CN108197306B (zh) 2018-01-30 2018-01-30 Sql语句处理方法、装置、计算机设备和存储介质
CN201810091240.5 2018-01-30

Publications (1)

Publication Number Publication Date
WO2019148713A1 true WO2019148713A1 (zh) 2019-08-08

Family

ID=62591958

Family Applications (1)

Application Number Title Priority Date Filing Date
PCT/CN2018/088974 WO2019148713A1 (zh) 2018-01-30 2018-05-30 Sql语句处理方法、装置、计算机设备和存储介质

Country Status (2)

Country Link
CN (1) CN108197306B (zh)
WO (1) WO2019148713A1 (zh)

Cited By (1)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
WO2022127866A1 (zh) * 2020-12-17 2022-06-23 中兴通讯股份有限公司 数据处理方法、装置、电子设备、存储介质

Families Citing this family (11)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN109213664A (zh) * 2018-08-23 2019-01-15 北京小度信息科技有限公司 Sql语句的性能分析方法、装置、存储介质和电子设备
CN109240901B (zh) * 2018-08-28 2023-01-17 北京星选科技有限公司 性能分析方法、性能分析装置、存储介质和电子设备
CN109542868A (zh) * 2018-09-28 2019-03-29 中国平安人寿保险股份有限公司 定位异常sql语句的方法、装置、电子设备及存储介质
CN109871372B (zh) * 2019-01-29 2021-01-19 公安部第一研究所 一种结合数据库调优和程序调优的方法
CN110134706A (zh) * 2019-04-01 2019-08-16 平安科技（深圳）有限公司 Sql语句自动优化方法、装置、计算机设备以及存储介质
CN111309581B (zh) * 2020-02-28 2023-09-12 中国工商银行股份有限公司 一种数据库升级场景下的应用性能检测方法及装置
CN111797112B (zh) * 2020-06-05 2022-04-01 武汉大学 一种PostgreSQL预备语句执行优化方法
CN112035513A (zh) * 2020-09-02 2020-12-04 中国平安人寿保险股份有限公司 Sql语句性能优化方法、装置、终端及存储介质
CN112181840B (zh) * 2020-09-30 2023-09-26 深圳前海微众银行股份有限公司 一种数据库状态的确定方法及装置、设备、存储介质
CN112395305B (zh) * 2020-11-11 2024-04-16 中国人寿保险股份有限公司 一种sql语句分析方法、装置、电子设备及存储介质
EP4148581B1 (en) * 2021-09-10 2023-08-30 Axis AB Verification of updated analytical procedures in monitoring systems

Citations (3)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN102541884A (zh) * 2010-12-10 2012-07-04 中国移动通信集团贵州有限公司 数据库优化方法和装置
CN103399851A (zh) * 2013-06-25 2013-11-20 携程计算机技术（上海）有限公司 一种结构化查询语言（sql）脚本的性能分析与预测方法与系统
CN106445795A (zh) * 2016-09-26 2017-02-22 中国工商银行股份有限公司 一种数据库sql效率检测方法及装置

Family Cites Families (1)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US9146946B2 (en) * 2013-05-09 2015-09-29 International Business Machines Corporation Comparing database performance without benchmark workloads

Patent Citations (3)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN102541884A (zh) * 2010-12-10 2012-07-04 中国移动通信集团贵州有限公司 数据库优化方法和装置
CN103399851A (zh) * 2013-06-25 2013-11-20 携程计算机技术（上海）有限公司 一种结构化查询语言（sql）脚本的性能分析与预测方法与系统
CN106445795A (zh) * 2016-09-26 2017-02-22 中国工商银行股份有限公司 一种数据库sql效率检测方法及装置

Cited By (1)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
WO2022127866A1 (zh) * 2020-12-17 2022-06-23 中兴通讯股份有限公司 数据处理方法、装置、电子设备、存储介质

Also Published As

Publication number Publication date
CN108197306B (zh) 2020-08-25
CN108197306A (zh) 2018-06-22

Similar Documents

Publication Publication Date Title
WO2019148713A1 (zh) Sql语句处理方法、装置、计算机设备和存储介质
US11544623B2 (en) Consistent filtering of machine learning data
Marcu et al. Spark versus flink: Understanding performance in big data analytics frameworks
US10713589B1 (en) Consistent sort-based record-level shuffling of machine learning data
US11182691B1 (en) Category-based sampling of machine learning data
US10366053B1 (en) Consistent randomized record-level splitting of machine learning data
Shi et al. Mrtuner: a toolkit to enable holistic optimization for mapreduce jobs
US10339465B2 (en) Optimized decision tree based models
Tao et al. Minimal mapreduce algorithms
Sun et al. Skipping-oriented partitioning for columnar layouts
JP5298117B2 (ja) 分散コンピューティングにおけるデータマージング
US11100420B2 (en) Input processing for machine learning
US9367601B2 (en) Cost-based optimization of configuration parameters and cluster sizing for hadoop
US20150227521A1 (en) System and method for analysis and management of data distribution in a distributed database environment
US20080140627A1 (en) Method and apparatus for aggregating database runtime information and analyzing application performance
US11429629B1 (en) Data driven indexing in a spreadsheet based data store
CN109885642B (zh) 面向全文检索的分级存储方法及装置
Shi et al. A case study of tuning MapReduce for efficient Bioinformatics in the cloud
US11514236B1 (en) Indexing in a spreadsheet based data store using hybrid datatypes
CN103530369A (zh) 一种去重方法及系统
US11500839B1 (en) Multi-table indexing in a spreadsheet based data store
US10474651B2 (en) Analysis of system information
Dai et al. The Hadoop stack: new paradigm for big data storage and processing
CN113553320B (zh) 数据质量监控方法及装置
US11874799B1 (en) Systems and methods for memory management in source agnostic content staging

Legal Events

Date Code Title Description
NENP Non-entry into the national phase

Ref country code: DE

32PN Ep: public notification in the ep bulletin as address of the adressee cannot be established

Free format text: NOTING OF LOSS OF RIGHTS PURSUANT TO RULE 112(1) EPC (EPO FORM 1205 DATED 22.09.2020)

122 Ep: pct application non-entry in european phase

Ref document number: 18903313

Country of ref document: EP

Kind code of ref document: A1