Home » RDBMS Server » Performance Tuning » Bind variable versus CURSOR_SHARING='SIMILAR' (oracle10g)
Bind variable versus CURSOR_SHARING='SIMILAR' [message #397455] Fri, 10 April 2009 21:25 Go to next message
shrinika
Messages: 266
Registered: April 2008
Senior Member
Is it not required to use
bind variable when CURSOR_SHARING='SIMILAR'?

scott@orcl> alter system set cursor_sharing='EXACT';

System altered.

scott@orcl> 
scott@orcl> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

scott@orcl> 
scott@orcl> SELECT * FROM dual WHERE dummy = 'LITERAL1';

no rows selected

scott@orcl> 
scott@orcl> SELECT * FROM dual WHERE dummy = 'LITERAL2';

no rows selected

scott@orcl> 
scott@orcl> COLUMN sql_text FORMAT A60;
scott@orcl> 
scott@orcl> SELECT sql_text,
  2           executions
  3    FROM   v$sql
  4    WHERE  INSTR(sql_text, 'SELECT * FROM dual WHERE dummy') > 0
  5    AND    INSTR(sql_text, 'sql_text') = 0
  6    ORDER BY sql_text;

SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
SELECT * FROM dual WHERE dummy = 'LITERAL1'                           1
SELECT * FROM dual WHERE dummy = 'LITERAL2'                           1

scott@orcl> 


In above example, both queries were parsed separately.

Now let us use bind variable....


scott@orcl> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

scott@orcl> 
scott@orcl> 
scott@orcl> VARIABLE dummy VARCHAR2(30);
scott@orcl> EXEC :dummy := 'BIND_VARIABLE1';

PL/SQL procedure successfully completed.

scott@orcl> 
scott@orcl> SELECT * FROM dual WHERE dummy = :dummy;

no rows selected

scott@orcl> 
scott@orcl> EXEC :dummy := 'BIND_VARIABLE2';

PL/SQL procedure successfully completed.

scott@orcl> 
scott@orcl> SELECT * FROM dual WHERE dummy = :dummy;

no rows selected

scott@orcl> 
scott@orcl> 
scott@orcl> SELECT sql_text,
  2           executions
  3    FROM   v$sql
  4    WHERE  INSTR(sql_text, 'SELECT * FROM dual WHERE dummy') > 0
  5    AND    INSTR(sql_text, 'sql_text') = 0
  6    ORDER BY sql_text;

SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
SELECT * FROM dual WHERE dummy = :dummy                               2

scott@orcl> 
scott@orcl> 


After using the bind variables, it parsed only one
time for both the query... It is good.


Now let us change the cursor_sharing to SIMILAR and
try to run both the query without bind variable...


scott@orcl> alter system set cursor_sharing='SIMILAR';

System altered.

scott@orcl> 
scott@orcl> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

scott@orcl> 
scott@orcl> SELECT * FROM dual WHERE dummy = 'LITERAL1';

no rows selected

scott@orcl> 
scott@orcl> SELECT * FROM dual WHERE dummy = 'LITERAL2';

no rows selected

scott@orcl> 
scott@orcl> COLUMN sql_text FORMAT A60;
scott@orcl> 
scott@orcl> SELECT sql_text,
  2           executions
  3    FROM   v$sql
  4    WHERE  INSTR(sql_text, 'SELECT * FROM dual WHERE dummy') > 0
  5    AND    INSTR(sql_text, 'sql_text') = 0
  6    ORDER BY sql_text;

SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
SELECT * FROM dual WHERE dummy = :"SYS_B_0"                           2

scott@orcl> 


Now after changing CURSOR_SHARING='SIMILAR', it parsed
only one time for both the query, even though we are not using
bind variable...

So, my question is, Is it not required to use
bind variable when CURSOR_SHARING='SIMILAR'?
Re: Bind variable versus CURSOR_SHARING='SIMILAR' [message #397457 is a reply to message #397455] Fri, 10 April 2009 22:16 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
I think so, the answer is
Quote:

Setting CURSOR_SHARING to EXACT allows SQL statements to share the SQL area only when their texts match exactly. This is the default behavior. Using this setting, similar statements cannot shared; only textually exact statements can be shared.

Setting CURSOR_SHARING to either SIMILAR or FORCE allows similar statements to share SQL. The difference between SIMILAR and FORCE is that SIMILAR forces similar statements to share the SQL area without deteriorating execution plans. Setting CURSOR_SHARING to FORCE forces similar statements to share the executable SQL area, potentially deteriorating execution plans. Hence, FORCE should be used as a last resort, when the risk of suboptimal plans is outweighed by the improvements in cursor sharing.



But, the one you achieve, the one you will lose
Quote:

The optimal solution is to write sharable SQL, rather than rely on the CURSOR_SHARING parameter. This is because although CURSOR_SHARING does significantly reduce the amount of resources used by eliminating hard parses, it requires some extra work as a part of the soft parse to find a similar statement in the shared pool.



Memory Configuration and Use

I am thinking about some example to prove it cleanly..
Re: Bind variable versus CURSOR_SHARING='SIMILAR' [message #397459 is a reply to message #397457] Fri, 10 April 2009 22:54 Go to previous messageGo to next message
shrinika
Messages: 266
Registered: April 2008
Senior Member
Thank you!
Re: Bind variable versus CURSOR_SHARING='SIMILAR' [message #397461 is a reply to message #397459] Fri, 10 April 2009 23:19 Go to previous message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
I already read Sir Thomas Kyte's article, however, I really did not understand all of this.
On Sharing, Splitting, and Deleting

Would you like to make extension your idea? I am very interesting into your topic.

Thank you!

[Updated on: Fri, 10 April 2009 23:20]

Report message to a moderator

Previous Topic: List partition and Index partitioning (merged)
Next Topic: create dynamic partitions in oracle 9
Goto Forum:
  


Current Time: Tue Dec 06 14:01:16 CST 2016

Total time taken to generate the page: 0.10062 seconds