[Cursor_sharing] the difference between setting the cursor_sharing parameter exact, similar, and force

In Oracle, to improve SQL Execution efficiency, You need to reduce hard parsing to achieve shared cursor sharing. The most common method is to bind variables, however, in many cases, variable binding cannot be used at the initial stage due to various reasons. The second method to reduce hard Parsing is to set cursor_sharing.

1. Prepare the environment

Lab Environment

BALLON @ PROD> select * from v $ version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod

PL/SQL Release 10.2.0.1.0-Production

CORE 10.2.0.1.0 Production

TNS for Linux: Version 10.2.0.1.0-Production

NLSRTL Version 10.2.0.1.0-Production

After using the script to insert data:

BALLONTT @ PROD> desc t;

Name Null? Type

-----------------------------------------------

ID VARCHAR2 (5)

Name number (38)

BALLONTT @ PROD> select id, count (*) from t group by id;

Id count (*)

---------------

D 6

A 10000

B 20000

C 20

BALLONTT @ PROD> create index ind_id on t (id );

Index created.

2. When the value is exact (default ):

BALLONTT @ PROD> show parameter cursor_sharing;

NAME TYPE VALUE

----------------------------------------------------------------

Cursor_sharing string EXACT

BALLONTT @ PROD> select count (*) from t where id = 'B ';

COUNT (*)

----------

20000

BALLONTT @ PROD> select count (*) from t where id = 'D ';

COUNT (*)

----------

6

BALLONTT @ PROD> select SQL _text, SQL _id, version_count, executions from v $ sqlarea

Where SQL _text like 'select count (*) from t where id = % ';

SQL _TEXT SQL _ID VERSION_COUNT EXECUTIONS

----------------------------------------------------------------------------

Select count (*) from t where id = 'D' b0gfs7u9r55rv 1 1

Select count (*) from t where id = 'B' fqurbumy7bsg6 1 1

We can see that the two query statements do not use the Bind Variable, each of which has its own corresponding SQL _id and the number of child cursors is one. There is no relationship between the two SQL queries.

View the execution plan of the two SQL statements:

BALLONTT @ PROD> select * from table (dbms_xplan.

Display_cursor ('b0gfs7u9r55rv ', 0, 'advanced '));

PLAN_TABLE_OUTPUT

Bytes ----------------------------------------------------------------------------------------------------

SQL _ID b0gfs7u9r55rv, child number 0

-------------------------------------

Select count (*) from t where id = 'D'

Plan hash value: 3666266488

---------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

----------------------------------------------------------------------------

| 0 | select statement | 1 (100) |

| 1 | sort aggregate | 1 | 2 |

PLAN_TABLE_OUTPUT

Bytes ----------------------------------------------------------------------------------------------------

| * 2 | index range scan | IND_ID | 14 | 28 | 1 (0) | 00:00:01 |

----------------------------------------------------------------------------

The index is used for the first query.

BALLONTT @ PROD> select * from table (dbms_xplan.display_cursor ('fqurbumy7bsg6 ', 0, 'advanced '));

PLAN_TABLE_OUTPUT

Bytes ----------------------------------------------------------------------------------------------------

SQL _ID fqurbumy7bsg6, child number 0

-------------------------------------

Select count (*) from t where id = 'B'

 

Plan hash value: 2966233522

---------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

---------------------------------------------------------------------------

| 0 | select statement | 15 (100) |

| 1 | sort aggregate | 1 | 2 |

 

PLAN_TABLE_OUTPUT

Bytes ----------------------------------------------------------------------------------------------------

| * 2 | table access full | T | 19783 | 39566 | 15 (0) | 00:00:01 |

---------------------------------------------------------------------------

The second query uses the full table Scan

Let's take a closer look at the hard parsing statistics after two SQL queries:

BALLONTT @ PROD> select count (*) from t where id = 'a ';

COUNT (*)

----------

10000

BALLONTT @ PROD> select name, value from v $ sysstat where name like '% parse % ';

NAME VALUE

--------------------------------------------------------------------------

Parse time cpu 2133

Parse time elapsed 4463

Parse count (total) 54889

Parse count (hard) 6579 (number of hard resolutions)

Parse count (failures) 52

BALLONTT @ PROD> select count (*) from t where id = 'C ';

COUNT (*)

----------

20

BALLONTT @ PROD> select name, value from v $ sysstat where name like '% parse % ';

NAME VALUE

--------------------------------------------------------------------------

Parse time cpu 2134

Parse time elapsed 4464

Parse count (total) 54895

Parse count (hard) 6580 (number of hard resolutions)

Parse count (failures) 52

The number of hard resolutions is increased by 1 again.

Because of the huge difference in data, the execution plan for the two queries is different. This also shows that when cursor is set to exact, if the two SQL statements are different, instead of sharing the cursor, the system performs two hard resolutions.

3. When set to force

Oracle automatically replaces the value of the where condition with the bound variable for the entered SQL value. When you enter SQL statements of the same structure in the future, cursor sharing will be used to share the cursor.

BALLONTT @ PROD> alter system set cursor_sharing = force;

System altered.

BALLONTT @ PROD> show parameter cursor_sharing;

NAME TYPE VALUE

--------------------------------------------

Cursor_sharing string FORCE

Clear the cached cursor in the share pool.

BALLONTT @ PROD> alter system flush shared_pool;

System altered.

View hard parsing:

BALLONTT @ PROD> select count (*) from t where id = 'B ';

COUNT (*)

----------

20000

BALLONTT @ PROD> select name, value from v $ sysstat where name like '% parse % ';

NAME VALUE

-----------------------------------------------

Parse time cpu 2163

Parse time elapsed 4506

Parse count (total) 55097

Parse count (hard) 6668

Parse count (failures) 52

 

BALLONTT @ PROD> select count (*) from t where id = 'D ';

COUNT (*)

----------

6

BALLONTT @ PROD> select name, value from v $ sysstat where name like '% parse % ';

NAME VALUE

---------------------------------------------------

Parse time cpu 2164

Parse time elapsed 4507

Parse count (total) 55101

Parse count (hard) 6669

Parse count (failures) 52

Hard parsing and adding 1, this should not be !!

BALLONTT @ PROD> select SQL _text, child_number from v $ SQL where SQL _text like 'select count (*) from t where id % ';

SQL _TEXT CHILD_NUMBER

----------------------------------------------------

Select count (*) from t where id = 'D' 0

Select count (*) from t where id = 'B' 0

The bound variable is not used, and the force setting does not take effect.

Solution: The following statement should be executed twice before setting cursor_sharing:

Alter system flush shared_pool;

BALLONTT @ PROD> alter system flush shared_pool;

System altered.

BALLONTT @ PROD> alter system flush shared_pool;

System altered.

BALLONTT @ PROD> alter system set cursor_sharing = force;

System altered.

After the settings are completed, perform the SQL test.

BALLONTT @ PROD> select count (*) from t where id = 'D ';

COUNT (*)

----------

6

BALLONTT @ PROD> select name, value from v $ sysstat where name like '% parse % ';

NAME VALUE

------------------------------------------------

Parse time cpu 2216

Parse time elapsed 4572

Parse count (total) 55867

Parse count (hard) 6910

Parse count (failures) 55

BALLONTT @ PROD> select count (*) from t where id = 'B ';

COUNT (*)

----------

20000

BALLONTT @ PROD> select name, value from v $ sysstat where name like '% parse % ';

NAME VALUE

-------------------------------------------------

Parse time cpu 2216

Parse time elapsed 4572

Parse count (total) 55869

Parse count (hard) 6910

Parse count (failures) 55

The number of hard resolution times has not changed.

BALLONTT @ PROD> select SQL _text, SQL _id, version_count, executions from v $ sqlarea

2 where SQL _text like 'select count (*) from t where % ';

SQL _TEXT SQL _ID VERSION_COUN EXECUTIONS

-------------------------------------------------------------------------------------

Select count (*) from t where id =: "SYS_ B _0" g82ztj8p3q174 1 2

We can see that the Bind Variable is used for the two queries, and the predicate value is replaced by sys_ B _0. This statement is executed twice and has a child cursor (sub-cursor ).

Let's take a look at the execution plan of the two query statements:

BALLONTT @ PROD> select * from table (dbms_xplan.display_cursor ('g82ztj8p3q174', 0, 'advanced '));

PLAN_TABLE_OUTPUT

Bytes ----------------------------------------------------------------------------------------------------

SQL _ID g82ztj8p3q174, child number 0

-------------------------------------

Select count (*) from t where id =: "SYS_ B _0"

 

Plan hash value: 3666266488

----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

----------------------------------------------------------------------------

| 0 | select statement | 1 (100) |

| 1 | sort aggregate | 1 | 4 |

PLAN_TABLE_OUTPUT

Bytes ----------------------------------------------------------------------------------------------------

| * 2 | index range scan | IND_ID | 6 | 24 | 1 (0) | 00:00:01 |

----------------------------------------------------------------------------

Two Queries use the same execution plan: Index scan. This is the force setting.

When the parameter cusor_sharing is set to force, we can draw the following conclusions based on the experiment:

Oracle automatically replaces the value of the where condition with the bound variable for the entered SQL value. In the future, when you enter SQL statements of the same structure, the cursor sharing will be used to share the cursor. When you replace the bound variable for the first time, Oracle will obtain an execution plan based on the bind peeking value, corresponds to a sub-cursor. In subsequent SQL statements, as long as the parent cursor can be shared, Oracle will forcibly use the generated unique sub-cursor for sharing. It doesn't matter whether it is the best execution plan for SQL statements. The rule of FORCE value is simple, and it forces the replacement of binding variables for SQL statements. Use the first bind peeking value to generate an execution plan, and then use it all. This method achieves cursor sharing, avoiding a large number of library cache hard parsing and limiting the number of version_count for a parent cursor. If the SQL statement itself is "Good SQL", that is, the distribution of condition columns is relatively average, and there is no excessive offset distribution. We think this kind of FORCE is very beneficial. However, if the distribution of data columns is uneven, it is very difficult to use the bind peeking generated for the first input and share the execution plan. We said that the potential problems encountered when using cursor_sharing to set FORCE are the same as the bind peeking value we used to bind variables.

4. When set to similar

When cursor_sharing is set to SIMILAR, Oracle will process the SQL literal statements that do not use the bound variable, and replace the where condition with the bound variable automatically. When executing the statement, A new child cursor sub-cursor is generated to match the value of each condition.

Above force settings:

BALLONTT @ PROD> alter system flush shared_pool;

System altered.

BALLONTT @ PROD> alter system flush shared_pool;

System altered.

BALLONTT @ PROD> alter system set cursor_sharing = similar;

System altered.

BALLONTT @ PROD> show parameter cursor_sharing;

NAME TYPE VALUE

------------------------------------------------------------------------------------------

Cursor_sharing string SIMILAR

Collect statistics and histograms:

BALLONTT @ PROD> exec dbms_stats.gather_table_stats ('balontt', 't', cascade => true, METHOD_OPT => 'for columns size auto ');

PL/SQL procedure successfully completed.

BALLONTT @ PROD> select column_name, num_distinct, NUM_BUCKETS, HISTOGRAM from dba_tab_col_statistics

2 where wner = 'balontt' and table_name = 'T ';

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM

--------------------------------------------------------------------

ID 4 4 4 FREQUENCY

NAME 10000 1 NONE

Test:

BALLONTT @ PROD> select count (*) from t where id = 'D ';

COUNT (*)

----------

6

BALLONTT @ PROD> select name, value from v $ sysstat where name like '% parse % ';

NAME VALUE

-----------------------------------------------------

Parse time cpu 2412

Parse time elapsed 4792

Parse count (total) 62015

Parse count (hard) 8059

Parse count (failures) 64

BALLONTT @ PROD> select count (*) from t where id = 'B ';

COUNT (*)

----------

20000

BALLONTT @ PROD> select name, value from v $ sysstat where name like '% parse % ';

NAME VALUE

---------------------------------------------------

Parse time cpu 2412

Parse time elapsed 4792

Parse count (total) 62017

Parse count (hard) 8060

Parse count (failures) 64

Hard resolution times plus 1

BALLONTT @ PROD> select SQL _text, SQL _id, child_number, executions from v $ SQL where SQL _text like 'select count (*) from t where id % ';

SQL _TEXT SQL _ID CHILD_NUMBER EXECUTIONS

----------------------------------------------------------------------------------

Select count (*) from t where id =: "SYS_ B _0" g82ztj8p3q174 0 1

Select count (*) from t where id =: "SYS_ B _0" g82ztj8p3q174 1 1

BALLONTT @ PROD> select SQL _text, SQL _id, version_count, executions from v $ sqlarea where SQL _Text like 'select count (*) from t where id % ';

SQL _TEXT SQL _ID VERSION_COUNT EXECUTIONS

----------------------------------------------------------------------------------

Select count (*) from t where id =: "SYS_ B _0" g82ztj8p3q174 2 2

The bound variable is used, but two child cursor.

To make it clearer that two SQL statements use two different sub-cursors, that is, two execution plans are used, and we will query them one by one:

BALLONTT @ PROD> select count (*) from t where id = 'D ';

COUNT (*)

----------

6

BALLONTT @ PROD> select SQL _text, SQL _id, child_number from v $ SQL where SQL _text like '% select count (*) from t where id % ';

SQL _TEXT SQL _ID CHILD_NUMBER

-------------------------------------------------------------------------

Select count (*) from t where id =: "SYS_ B _0" g82ztj8p3q174 0

View the execution plan:

BALLONTT @ PROD> select * from table (dbms_xplan.display_cursor ('g82ztj8p3q174', 0, 'advanced '));

PLAN_TABLE_OUTPUT

Bytes ----------------------------------------------------------------------------------------------------

SQL _ID g82ztj8p3q174, child number 0

-------------------------------------

Select count (*) from t where id =: "SYS_ B _0"

Plan hash value: 3666266488

----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

----------------------------------------------------------------------------

| 0 | select statement | 1 (100) |

| 1 | sort aggregate | 1 | 2 |

 

PLAN_TABLE_OUTPUT

Bytes ----------------------------------------------------------------------------------------------------

| * 2 | index range scan | IND_ID | 1 | 2 | 1 (0) | 00:00:01 |

----------------------------------------------------------------------------

The execution plan is the index path.

Query again:

BALLONTT @ PROD> select count (*) from t where id = 'B ';

COUNT (*)

----------

20000

BALLONTT @ PROD> select SQL _text, SQL _id, child_number from v $ SQL where SQL _text like 'select count (*) from t where id %'

2;

SQL _TEXT SQL _ID CHILD_NUMBER

-------------------------------------------------------------------------

Select count (*) from t where id =: "SYS_ B _0" g82ztj8p3q174 0

Select count (*) from t where id =: "SYS_ B _0" g82ztj8p3q174 1

View the execution plan of the Child cursor (chilid_number = 1:

BALLONTT @ PROD> select * from table (dbms_xplan.display_cursor ('g82ztj8p3q174', 1, 'advanced '));

PLAN_TABLE_OUTPUT

Bytes ----------------------------------------------------------------------------------------------------

SQL _ID g82ztj8p3q174, child number 1

-------------------------------------

Select count (*) from t where id =: "SYS_ B _0"

Plan hash value: 2966233522

---------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

---------------------------------------------------------------------------

| 0 | select statement | 15 (100) |

| 1 | sort aggregate | 1 | 2 |

PLAN_TABLE_OUTPUT

Bytes ----------------------------------------------------------------------------------------------------

| * 2 | table access full | T | 20081 | 40162 | 15 (0) | 00:00:01 |

---------------------------------------------------------------------------

The execution plan generates a new Execution Plan for the Full table scan.

Therefore, we can draw a conclusion:

When cursor_sharing is set to SIMILAR, Oracle will process the SQL literal statements that do not use the bound variable, and replace the where condition with the bound variable automatically, A new child cursor sub-cursor is generated for setting values for each condition, which corresponds to the parent cursor. When a statement is input, if an SQL sub-cursor with the same condition value exists before, the sub-cursor is shared. Otherwise, a new child cursor is generated to generate a matching execution plan. SIMILAR is the evolutionary version of FORCE. In SIMILAR mode, Oracle is sensitive to the condition of cursor sharing. If the bound variable value changes, the execution plan may be mismatched. Therefore, Oracle performs a bind peeking operation on each new value to generate an execution plan. The sharing of execution plan cursors only occurs when the variables are bound to the same resource. Compared with FORCE, cursor_sharing = SIMILAR does alleviate the issue of execution plan mismatch caused by a single bind peeking. However, two new problems will be introduced: if the value of the corresponding condition column is relatively small, the number of execution plans generated can be controlled at least. If a continuous value or a large number of values are involved, the number of child cursor corresponding to the parent cursor increases, and the traversal time from the child cursor column increases each time, latch and pin increase in time. This is also a common problem with the similar value. The standard for generating child cursor is not whether the execution plan is the same, but the bound variable value is the same. In this way, if the data distribution is relatively average, all values correspond to the same execution plan. Therefore, the execution plans of many child cursors must be the same. This will bring about performance and other problems. When no histogram exists after the column of the predicate condition, its function is similar to that of force, and the subcursor will be shared (you can delete the histogram on the column after the where condition and repeat the last step for experimental verification)

Cursor_sharing is set to similar, which has some bugs, even in force. For example, the export time of EXPDP is changed to that of Materialized View. Oracle does not recommend that you set this parameter. In 12C, ORACLE abolished similar settings.