[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 sharedcursor sharing. The most common method is to bind variables.

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.

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;

COUNT (*)

----------

20000

BALLONTT @ PROD> select count (*) from t where;

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 b0gfs7u9r55rv 1 1

Select count (*) from t where 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

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

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;

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;

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 input 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;

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;

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 0

Select count (*) from t where 0

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

Solution: run the following statement 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;

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;

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: