| Bind variable versus CURSOR_SHARING='SIMILAR' [message #397455] |
Fri, 10 April 2009 21:25  |
shrinika
Messages: 306
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  |
trantuananh24hg
Messages: 744
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..
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
