Shulou(Shulou.com)06/01 Report--

Analysis of High version of OracleSQL statement

1. What is the high version?

Each time a SQL statement is executed, if the corresponding child cursors under a parent cursor that already exists in library cache cannot be reused (that is, shared) by the SQL statement, a new child cursor will be generated, and a new row will be added to the V$SQL_SHARED_CURSOR to describe the reasons why the SQL statement cannot use the existing child cursors. Of course, when the first child cursor under a parent cursor is generated, a new row is added to the view, except that the value of the column describing the reason on each view is N, and the number of rows in V$SQL_SHARED_CURSOR is the number of versions executed by the SQL statement, that is, the number of categories.

two。 How to produce a high version

A. ORACLE uses child cursors to distinguish a SQL that cannot be shared, because although the SQL is the same, the object that the SQL points to makes it different. In other words, the parent cursor of these SQL is the same, and the HASH_ value is the same. For example, the database has three tables T, there is such a statement, select * from T, because each T is used by different objects or users, and at the database level, these statements are all the same, HASH_VALUE are all the same, but their child cursors are different, which will produce High Version Counts, because the HASH_VALUE is the same, hold LATCH will not be put, so when PARSE will generate LATCH FREE. This is one aspect of generating High Version.

b. For fields of character type, a 32-byte BUFFER is used for the first time when binding variables. If the value is less than 32 bytes, the second time the SQL is executed, if it is less than 32 bytes, the CURSOR can be shared. If it is greater than 32 bytes, it cannot be shared. The reason is that BIND_MISMATCH produces a sub-CURSOR and allocates more than 32 bytes of BIND BUFFER at the same time.

c. For null values, because oracle means nothing to the value of NULL, a new subcursor will be created if the variable is NULL.

3. What are the consequences?

It is found that Oracle cannot reuse these SQL for some reason. When this kind of SQL is executed for a large number of times, it will occupy a lot of shared pool, cause waiting events of library cache pin and library cache, and seriously cause the host to hang.

4. Solution method

a. Database upgrade

L version upgrade (11.2.0.4) upgrade package, but the risk is large, the cycle is long, need to test and verify, build test environment, need to update files have the following files

P13390677_112040_Linux-x86-64_1of7.zip

P13390677_112040_Linux-x86-64_2of7.zip

P13390677_112040_Linux-x86-64_3of7.zip

Regularly clear ALTERSYSTEM FLUSH SHARED_POOL

b. Program modification method

L program sets a fixed size for character variables

Special handling of null values, using different InSert statements

L Insert operation prevents the same primary key from being inserted at the same time.

5. Case analysis

Select sql_id,count (0) from gv$sql groupby sql_id orderby2desc

Number of version_count counterpart cursors:

Select sql_id,sql_text,executions,version_count fromgv$sqlarea where sql_id='6hk042t0trr1t'

Selectsql_id,child_number,sql_text,optimizer_mode,plan_hash_value from gv$sql where sql_id='6hk042t0trr1t'

Check the reasons why sub-cursors cannot be shared (invalidation reasons, such as optimizer_mode_mismatch,BIND_MISMATCH, etc.):

Selectchild_number,optimizer_mode_mismatch,BIND_MISMATCH from gv$sql_shared_cursor where sql_id='6hk042t0trr1t'orderby child_number

When the binding fails, check the value of each bound variable:

Selectposition,LAST_CAPTURED,datatype_string,value_string fromgv$sql_bind_capture where sql_id='6hk042t0trr1t'

When the field length of a bound variable changes:

Select * from dba_hist_sqlbind where sql_id='6hk042t0trr1t'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.