A method for dynamically adjusting the execution frequency of SQL statements

Abstract

The invention discloses an SQL statement execution frequency dynamic regulation method. The SQL statement execution frequency dynamic regulation method comprises the following steps of a, monitoring sending requests of a client side, and obtaining SQL statements of an operation database; b, performing parsing and regularization treatment on the obtained SQL statements, and binding variables uniformly; c, regularly gathering the execution frequencies of the regulated SQL statements in a database server side; d, if the execution frequency of one SQL statement exceeds a preset frequency threshold value, generating a new SQL statement to repeat the step b, and sending the new SQL after regulated to the database server side to be operated. According to the SQL statement execution frequency dynamic regulation method, the bond variable use in a development stage is not needed to be concerned, codes are not needed to be modified, the SQL is regulated automatically through an interlayer, the bound variables are used uniformly, the distribution condition of data values is regularly gathered, the dynamic regulation on the situation whether the SQL uses the bound variables or not is performed, and therefore a channel is regulated dynamically to be adapted to the SQL frequency fluctuation condition and improve the database processing performance.

Description

technical field [0001] The invention relates to a database optimization processing method, in particular to a method for dynamically adjusting the execution frequency of SQL statements. Background technique [0002] During the operation of the database system, the following two situations often occur: [0003] 1. The SQL statement does not use bind variables. The unique value of each predicate may generate different SQL statements during the query, and multiple predicates may even generate a Cartesian product, resulting in a large number of SQL statements generated by the same query function point. , the execution frequency of each SQL is very low, and repeated parsing in the database greatly increases the burden on the database and causes memory fragmentation problems. [0004] 2. SQL statements use bind variables, but due to frequent foreground business operations, a certain SQL execution frequency is too high. When a session tries to obtain a cursor in shared mode, it ne...

AI Technical Summary

Problems solved by technology

[0003] 1. The SQL statement does not use bind variables. The unique value of each predicate may generate different SQL statements during the query, and multiple predicates may even generate a Cartesian product, resulting in a large number of SQL statements generated by the same query function point. , the execution frequency of each SQL is very low, repeated parsing in the database greatly increases the burden on the database, and causes memory fragmentation problems

[0004] 2. SQL statements use bind variables, but due to frequent foreground business operations, a certain SQL execution frequency is too high. When a session tries to obtain a cursor in shared mode, it needs to modify the reference count of the corresponding Mutex structure, resulting in competition And non-idle waiting, affecting the execution time of SQL

[0009] 1. Developers do not have a deep understanding of database expertise, and it is difficult to fully manage and control the release of the version. Now there are still many unused binding variables in various database systems, but the transformation work involves code changes, and there are risks

[0010] 2. The distribution of predicate values ​​will change as the data grows, and the design considerations are not applicable to the changed situation

[0011] 3. The frequency of SQL will change with business growth. Splitting SQL during design involves code changes and cannot be adjusted dynamically

Images