The setting of the cursor_sharing parameter causes slow SQL Execution with placeholders.

Our application developers found that the execution time of an SQL statement when binding a variable is much slower than when not binding a variable, even slowed down to dozens of times.

In the application, if the executed SQL statement is not bound to a variable, it may cause the Shared Pool to earn an item waiting for the event. In addition, this situation is common in the development of many application systems. In this regard, the Oracle database system provides a compromise solution to set the value of the initialization parameter cursor_sharing to force or similar. In this way, the constants of SQL statements running in Oracle, such as characters and numbers, are automatically converted to variables, so that similar SQL statements become the same, reducing the usage of shared pools.

In the database system where the problem occurs, the value of its initialization parameter cursor_sharing is force.

From the symptoms, the SQL Execution Plan Using Variable binding is different from that without variable binding. The execution plan adopted by the former is unreasonable.

This SQL statement is also complex. The where clause contains both custom variable statements and many constant statements. In constant conditions, there is a placeholder clause, followed by the where keyword.

This type of writing is common when assembling SQL statements in JAVA. When a condition judgment statement needs to be added, add "and xx = yy" directly to "where 1 = 1 and xx = yy ".

Is this method common?

Statement

The problematic SQL statement is as follows:

SELECT *

FROM (SELECT row _. *, ROWNUM rownum _

FROM (select count (*)

From (select t1.id as id,

'Sms 'as type,

T1.empid as empid,

T1.deptno as deptno,

T1.content as title,

To_char (t1.send _ time, 'yyyy-MM-dd hh24: mi: ss') as plantime,

T1.sysuid as sysuid,

T1.custid as custid,

T1.mobile as contact,

T1.mark as mark

From liw.g_send_back t1

Where 1 = 1

And t1.send _ time> =

To_date ('2017-11-01 00:00:00 ',

'Yyyy-mm-dd hh24: mi: ss ')

And t1.send _ time <

(To_date ('2017-12-06 00:00:00 ',

'Yyyy-mm-dd hh24: mi: ss') + 1)

And t1.deptno = '000000'

Union all

Select t1.id as id,

'Sms 'as type,

T1.empid as empid,

T1.deptno as deptno,

T1.content as title,

To_char (t1.send _ time, 'yyyy-MM-dd hh24: mi: ss') as plantime,

T1.sysuid as sysuid,

T1.custid as custid,

T1.mobile as contact,

T1.mark as mark

From listmg_send t1

Where 1 = 1

And t1.send _ time> =

To_date ('2017-11-01 00:00:00 ',

'Yyyy-mm-dd hh24: mi: ss ')

And t1.send _ time <

(To_date ('2017-12-06 00:00:00 ',

'Yyyy-mm-dd hh24: mi: ss') + 1)

And t1.deptno = '000000') T

Where rownum< 10001

Order by T. plantime desc, T. id) row _

Where rownum <=: B)

WHERE rownum _>:

The SQL where condition contains a "1 = 1" condition. www.bkjia.com is a common method for developers to dynamically add conditions when the conditions are not met. This method has been used in many types of applications.

This SQL statement obviously removes the Bind Variable used for rownum condition judgment. Other conditions are constant assignments. This is because the SQL statement of the internal result set is spelled out by the application, and the conditions are flexible, so it is not easy to write statements with variables. Therefore, we allow the database system to automatically modify these constants as variables before execution, so that SQL statements with different constants can share cursor and reduce hard analysis.

Set cursor_sharing = force to implement this automatic conversion. But the placeholder ("1 = 1") will also be automatically replaced by: "SYS_ B _02" =: "SYS_ B _03 ".

However, when cursor_sharing = exact, the system optimizer does another operation. It ignores the placeholder ("1 = 1"), because it does not need to be determined, thus saving the CPU execution time. Very clever!

Analysis

I did some simple tests first:

Test 1. Cancel the variables set in the SQL statement so that the SQL statement is generated by the system and the statement runs properly;

Test 2. Change cursor_sharing to the default value (exact). The SQL statement is used to bind the variable and the statement execution is normal;

Test 3. Remove the placeholder ("1 = 1") in the SQL statement. The application uses the Bind Variable. Set cursor_sharing to force, and the statement execution is normal.

The test results show that if there is no placeholder, it is normal.

What's going on?

It seems that they must go to their respective analysis execution plans to understand.