For example:
SQL> select * from dept where dept_id = 'literal_value';
Oracle takes the above statement and replaces the predicate with something as below:
SQL> select * from dept where dept_id=:"SYS_B_0";
And now the oracle compares the above sql statement to the plans that it already has in the memory to see if the plan can be reused or not, perhaps leading to a better utilization of shared_pool and reduction in number hard parses performed by the system.
The cursor_sharing parameter can be set to three values:
- EXACT: This is the default setting. With this value in place, the query is not rewritten to use bind variables.
- FORCE: This setting rewrites the query, replacing all literals with bind values and setting up a one-size-fits-all plan—a single plan for the rewritten query. I'll demonstrate what that implies in a moment.
- SIMILAR: This setting also rewrites the query, replacing the literals with bind variables, but can set up different plans for different bind variable combinations. This last point is why CURSOR_SHARING=SIMILAR might reduce the number of plans generated. Because multiple plans may be generated, the setting of SIMILAR may or may not reduce the number of actual plans you observe in the shared pool.
SQL> alter session set cursor_sharing=EXACT;
Session altered.
SQL> select * from dual CS_EXACT where dummy='A';
no rows selected
SQL> select * from dual CS_EXACT where dummy='B';
no rows selected
SQL> alter session set cursor_sharing=FORCE;
Session altered.
SQL> select * from dual CS_FORCE where dummy='A';
no rows selected
SQL> select * from dual CS_FORCE where dummy='B';
no rows selected
SQL> alter session set cursor_sharing=SIMILAR;
Session altered.
SQL> select * from dual CS_SIMILAR where dummy='A';
no rows selected
SQL> select * from dual CS_SIMILAR where dummy='B';
no rows selected
SQL> select sql_text
2 from v$sql
3 where sql_text like 'select * from dual CS% where dummy=%'
4 order by sql_text;
SQL_TEXT
--------------------------------------------------------------------------------
select * from dual CS_EXACT where dummy='A'
select * from dual CS_EXACT where dummy='B'
select * from dual CS_FORCE where dummy=:"SYS_B_0"
select * from dual CS_SIMILAR where dummy=:"SYS_B_0"
CURSOR_SHARING=EXACT: From the above example as we can see oracle uses different plans for each of the select statements which include the word "CS_EXACT". With this setting every SQL statement you excute will be new and a new plan is generated for every query that we execute and the plans are not shared. And a new entry is created in V$SQL as you can see from above for every SQL Statement we execute.
CURSOR_SHARING=FORCE: When this is the setting for the parameter then for each statement that I have executed above which include the word "CS_FORCE" the literal values 'A' and 'B' are replaced by "SYS_B_0". The oracle uses the same plan for each of the SQL whether the predicate is either 'A' or 'B' since rest of the statement is similar and thus we see just one entry in V$SQL for the two SQL that we have executed.
CURSOR_SHARING=SIMILAR: When this is the setting for the parameter then for each statement that I have executed above which include the word "CS_FORCE" the literal values 'A' and 'B' are replaced by "SYS_B_0". The oracle uses the same plan for each of the SQL whether the predicate is either 'A' or 'B' since rest of the statement is similar and thus we see just one entry in V$SQL for the two SQL that we have executed. So, the settings FORCE and SIMILAR looks similar huh? But there is one difference between both i.e., when the setting is SIMILAR oracle not only checks for similar looking statements but also compares the plans. For example when I executed the SELECT statement with the literal value 'A' it generates a plan which will say a FULL TABLE SCAN for the statement. Now when I executed the same SELECT statement with the literal value 'B' the it also generates a plan for the statement and compares to the plan that is already stored i.e., when literal value is 'A'. If the explain plan for the SELECT statement when literal is 'A' a FULL TABLE SCAN and also a FULL TABLE SCAN (lets assume for now) when the literal value is 'B' then we see only one entry in the dynamic view V$SQL. If both the plans are different say a FULL TABLE SCAN when literal value is 'A' and an INDEX SCAN when literal value is 'B' which is not in this case as per our assumption then we will see two different entries in V$SQL for the SQL statements which contain the word "CS_SIMILAR" and you would see something as below (which is not with our example above): The results displayed shown below are with respect to an imaginary table "t" which is a big table of about atleast 100 rows and has an index on it and the data is skewed
SQL> alter session set cursor_sharing=FORCE;
Session altered.
SQL> select * from t CS_FORCE where t_id=1;
1 row selected.
SQL> select * from t CS_FORCE where t_id='99';
1 row selected.
SQL> alter session set cursor_sharing=SIMILAR;
Session altered.
SQL> select * from t CS_SIMILAR where t_id='1';
1 row selected.
SQL> select * from t CS_SIMILAR where t_id='99';
1 row selected.
SQL> select sql_text
2 from v$sql
3 where sql_text like 'select * from t CS% where t_id=%'
4 order by sql_text;
SQL_TEXT
--------------------------------------------------------------------------------
select * from t CS_FORCE where t_id=:"SYS_B_0"
select * from t CS_SIMILAR where t_id=:"SYS_B_0"
select * from t CS_SIMILAR where t_id=:"SYS_B_0"
From the above example we can see that the plans are different say a FULL TABLE SCAN when literal value is '1' and an INDEX SCAN when literal value is '99' we see two different entries in V$SQL for the SQL statements which contain the word "CS_SIMILAR".
No comments:
Post a Comment