Cursor_sharing parameter of Oracle

Let's take a look at the explanation of this parameter in the official documentation.

CURSOR_SHARING
Property Description
Parameter type String
Syntax CURSOR_SHARING = {SIMILAR | EXACT | FORCE}
Default value EXACT
Modifiable alter session, ALTER SYSTEM
Basic No

CURSOR_SHARINGdetermines what kind of SQL statements can share the same cursors.

Values:

FORCE

Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

SIMILAR

Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.

EXACT

Only allows statements with identical text to share the same cursor.

Description of the cursor_sharing Parameter

This parameter is set in Oracle to meet the needs of some previously developed programs. It contains a large number of similar statement, but is used when rewriting is unrealistic. (This parameter is not recommended for oracle)

When do I need to modify this parameter? The following conditions must be met.

One is that a large number of shared pool hit miss affects the user response time (that is, the current shared pool cannot meet the needs of shared SQL statement storage, Alan: currently, libary cache does not have the explain and SQL to be reused. If this problem is not found, setting this parameter may cause worse performance. This parameter only reduces the parse time.

In addition, there are a large number of similar statement in the existing program. You can set this parameter to achieve better performance.

Cursor_sharing has three optional values: exact, similar, and force. When the value is exact, it is also the default oracle processing method. When a statement parse is used, first check whether exact statement exists in the shared pool (that is, whether the statement exists exactly the same as the statement to be parsed in the shared pool ), if not, execute hard parse.

If this parameter is set to similar, if the exact statement cannot be found in the shared pool, a new query is performed in the shared pool, is to find whether the statement to be parsed is a similar statement. The similar statement must be explained here. The similar statement is the same statement except the value of some literal statements. For example:

Select * from a where a = 1;

Select * from a where a = 2;

When cursor_sharing is set to similar, if such a statement is found in the shared pool, the next check will be performed, check whether the execution plan of the cached statement in the shared pool is suitable for the statements currently parsed. If it is suitable, the statement in the shared pool is used instead of hard parse. If cursor_sharing is set to force, when similar statement is found in the shared pool, the execution plan will not be checked, but the statement in the shared pool will be used directly.

Setting cursor_sharing to force is actually dangerous. This may form a sub optimal execution plan. For example, for a range query statement, such

For select * from a where a> 10 and a <20 statements of this type, the execution plan of the statements in the cache may be unsuitable for the statements being parsed, rather than the optimal execution plan.

This seems to reduce the time for parse evil, but greatly increase the execution time.

For newly developed applications, it is best not to set this parameter, but to bind variables to shared statements. For statements that are not suitable for sharing, no variable is bound. Keep cursor_sharing by default, that is, exact.

The key to adjusting cursor_sharing is to check whether the condition for adjusting cursor_sharing exists.

Whether there is a large shared pool hit miss. If this condition does not exist, there is no need to adjust this parameter. Cursor_sharing aims to reduce the parse time, but in the whole db time, it is possible that parse time only occupies a small part.