Cursor sharing Part 1

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