By default it is Cursor_Sharing is EXACT it means ONLY
THOSE STATEMENT ARE SHAREABLE WHICH ARE IDENTICAL IN TEXT. Means in such case,
if someone wants a query shareable then they have to fix the TEXT of the query
and have to run same text over and over again, which I think not possible in
the real production environment.
I opened two session on Toad 10.2 using following
database
----------------------------------------------------------------
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 32-bit Windows: Version
10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
And run following query which gave me one row
To do our test, I prepared on table, created an
index, Updated that table and gathered statistics.
Following are the exact statement that I executed.
1:- CREATE TABLE SCOTT.OBJ_TAB PCTFREE 90 PCTUSED 10
AS SELECT * FROM SYS.DBA_OBJECTS WHERE 1=2;
2:- INSERT INTO SCOTT.OBJ_TAB SELECT * FROM DBA_OBJECTS;
3:- create index scott.obj2 on scott.obj_tab(owner) compute statistics;
4:- UPDATE SCOTT.OBJ_TAB SET OWNER='SYS' WHERE OWNER NOT IN ('PM')
5:- begin
dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'obj_tab',estimate_percent=>100,method_opt=>'for all columns SIZE AUTO',cascade=>true);
end;
SQL> Show parameter Cursor_sharing
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
cursor_sharing string EXACT
Select /*Exact*/ * from scott.DBA_OBJECTS where ='SMITH'
While executing this query, I used same ‘SMITH’
literal value in two different session and it turns out that this query is
shareable. On v$sqlarea, v$sql it gave one unique SQL_ID. However, when I
changed the literal value to ‘ALLEN’ then, one additional row was added into
v$SQLAREA and v$SQL.
Every time any session changes the value of
literal, a new row is added into v$SQLAREA and just because of this same query
is parsed again. It means if query text remains same then only query will be
shared otherwise it is not shareable.
Disadvantage: Non-Reusability, Load on
SHARED_POOL, More Hard parsing
Advantage: Correct
cardinality feeding to Optimizer, right execution path and correct index
picking.
After this test I changed my CURSOR_SHARING
parameter to FORCE;
SQL> alter system set cursor_sharing=force scope=memory;
System altered.
CURSOR_SHARING to FORCE means it 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. (courtesy oracle docs)
After this I ran following query :
select /*force*/ *
from scott.obj_tab where owner='PM’
This query gave me 52 rows and one thing I
noticed, when I queried V$SQLAREA using
following query:
SELECT * FROM V$SQLAREA WHERE SQL_TEXT LIKE '%force%'
The SQL_TEXT column showed following value
select /*force*/ * from scott.obj_tab where owner=:"SYS_B_0" a
automatically generated bind variable of character type. This is the trick, after
changing CURSOR_SHARING parameter, lateral values are automatically
replaced by BIND variable(s) to make query shareable.
However, this behavior has a limitation. If we
remember what oracle documents says about FORCE value of CURSOR_SHARING, “, unless the literals affect the meaning of the statement”. So
It means
