This note is exactly what i want to know about CURSOR_SHARING. Thanks Tom!

We noticed that our application was breaking which was caused from the padding returned from a varchar() column when we set cursor_sharing='FORCE'. You've only confirmed that this is the correct behavior.

Due you know if cursor_sharing='SIMILAR' has the same issues in 9i (9.2.0.3)?

As I told the Director at my company, Tom Kyte is a freaking walking ORACLE encyclopedia. :D

Thanks!

Hi Tom,
I have a procedure which creates a dynamic select string then open the cursor for that string and creates a insert string then executes the insert string. For eg.

Procedure p (p_1 varchar2) as

Begin
-- here I create a dynamic select string. Which is like
-- SELECT col1,col2,col3 from mytable where col1 = ‘abc’; (number of col can be change)
--- here I open my dbms_sql cursor.for the select string…
LOOP ---retrieve more then 100000 rows and more..
--- here I create a insert string like
myinstring := ‘insert into ‘||p_1 ‘|| (col1,col2,col3) values ( ‘’||valuesfromcursorcol1||’’, ‘’valuesfromcursorcol2||’’,’’|| valuesfromcursorcol3||’’)’;

--then I execute this insert string ..
execute immediate myinstring..
--- so every time this is a new insert in for database so every time it is a hard parse. ----When it get executes it is like :
-- insert into mytab (col1,col2,col3) values (‘val1’,’val2’,’val3’);
END LOOP;
--so everytine it is a hard parse.

End;

So if I use cursor sharing= force will it improves my performance.

This insert stament once created will be same for all rows return but values will be changed. Ie val1 val2 val3 will change.. There is anyway the treat them as bind variable. I am using dbms_sql for opening the dynamically created SELECT string. And execute immediate to execute the insert instring.


Regards,

Vikas Sharma

Hi Tom,

Thanks.

You are right the insert as select would be the best approach. I am sorry for not providing the complete info, that i want to log my failed statements also and complete it for all rows. But if is use the insert as select if one row fails the execution will stop.

This is the requirment of my application.

Pl suggest how can i use bind variables in the above situtation as my no. of variables are not known. This process runs from browswer(mod_plsql), can i mak the use of owa_util.bind_variables but that also has the limt of variables declared.

Is the cursor sharing works only with SELECT, not with INSERTS.

Regards,

Vikas Sharma

Hi!
Just wanted to know that , which one is faster...

open a cursor
loop
insert into some table using cursor's values
end loop

OR

direct insert into some table as select some columns from other table.

Hi there !

There should be no doubt in answering this question. The second statement (insert into...select) will be much faster than the first one. Just imagine, you open a cursor, fetch rows one by one, and then close cursor.

"If you can do some task using single statement then JUST DO IT....don't use PL/SQL for that.....it will degrade performance".

Riaz

Hi Tom, which way would you suggest to evaluate
the CURSOR_SHARING parameter:
1. The need for using SIMILAR
2. The need for using FORCE
Which are the main signs that tell you when to use SIMILAR and when FORCE.
Which of this views are the most useful V$SQL_BIND_DATA,V$SQL,V$SQL_BIND_METADATA
:) Please

Thanks tom,
As I understand what you say is
IN TRY
-------------
OLTP -- force
Mixed workload similar (where you need a different plan for some of the queries)
DSS/DW -- exact

And see if the performance increases?

Because as I understand the goal is fix the code and set this parameter as EXACT

Thanks :)

Thanks :)

"Query output related issues. The lengths of columns that your query fetches will unexpectedly change. Queries that used to return a varchar2(5) and a number(2) might start returning a varchar2(30) and a number(5). The actual size
of the data returned won’t change but the application will be told the potential for a 30 byte column is there – reports and other related applications may be
affected."

Tom,

1. I am not sure if I understood the above response. Is this due to any Oracle bug? Can you please elaborate?

2. On Oracle 9.2.0.1, I need to use cursor_sharing = force for an OLTP app where we cannot change the app. code to use bind variables. For a couple of important tables to get good execution plans I am using histograms. The decision to use 'force' and not 'similar' is because it is an OLTP app. and 'similar' being a new feature seems to have certain bugs (per Oracle tech support). So, I am wondering if cursor_sharing=force (with histograms) is the right mix. I will be evaluating the execution plans -- I thought I will get clarifications from you.

I would really appreciate your suggestions. Thank you.

Tom,
Many thanks for that clear explanation.

I have a query like

select a,b,c from t1 where d=25 and e= ?

I know that all my queries are going to use d=25. In this case, is it okay to use partialy binding.

Tom
1. Is there any difference between following a strategy of
binding constants also (along with variables) and
using cursor_sharing = FORCE?
2. Are there any advantages of binding contants and variables.

From what I cam tell from your book the answers to these
questions are:
1. No
2. No - only disadvantages in terms of suboptimal query
plans at times and other side effects as mentioned in your
book.

Please confirm

Thanx!

1) yes, you should not bind all constants, only those that make sense.  eg:

select substr( x, 1, 5 ) x, y, to_char( z, 'dd-mon-yyyy' ) z
  from t
 where a = 55;


of the constants:

1, 5, dd-mon-yyyy, and 55

which should be bound???  exactly 1 -- 55.  see what happens in sqlplus:


ops$tkyte@ORA920> create table t ( a int, x varchar2(2000), y int, z date );

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into t values ( 55, 'abcdefg', 2, sysdate );

1 row created.

ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> alter session set cursor_sharing = exact;

Session altered.
ops$tkyte@ORA920> variable a number
ops$tkyte@ORA920> exec :a := 55;

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> select substr( x, 1, 5 ) x, y, to_char( z, 'dd-mon-yyyy' ) z
  2    from t t1
  3   where a = :a;

X              Y Z
----- ---------- -----------
abcde          2 27-jul-2003


ops$tkyte@ORA920> alter session set cursor_sharing = force;

Session altered.

ops$tkyte@ORA920> select substr( x, 1, 5 ) x, y, to_char( z, 'dd-mon-yyyy' ) z
  2    from t t2
  3   where a = 55;

X
-----------------------------------------------------------------------------------------------------------------------------------
         Y Z
---------- ---------------------------------------------------------------------------
abcde
         2 27-jul-2003


<b>wow, what happened to X and Z on that second query???  Hmmm</b>

cursor_sharing = force is a crutch you might be able to use temporarily until the developers fix the bug in their code and bind correctly, where appropriate.


2) yes, obviously. I wail on about BINDING all of the time.  It is how our shared sql works.  In the above example where I bound 55 -- that query can be executed over and over with different values for :a without being reparsed.  If other sessions execute that query, they will SOFT parse.

definitely using BINDS where they make sense, literalls otherwise, will have a massive (cannot be overstated -- MASSIVE in capital letters) effect on performance.




You need to re-read chapter 10 if you think #1 was "no"
 

What I meant to ask was:

1. Is cursor_sharing=force any different from binding
all constants? (Here , I am trying to evaluate the
disadvantages of binding constants (alongwith
variables.)?

From your answer, it seems there are differences - e.g. in terms of lengths of results returned as is clear from your example.)

2. My second question should have read:
Are there any disadvantages of binding constants
*always* instead of using literals for constants?
I understand that variabels should be bound always.

And as I said, the answer is that - YES - there are
disadvantages - for example, you can get suboptimal
query plans because the optimizer has less information
to deal with when you bind a constant rather than
specifying it as a literal - esp. in tables with
skewed data. This is mitigated with the "peeking" of
values by optimizer in 9i but the point still remains.

Apologies for not wording questions correctly!

Of course, I stand corrected on my first question.
Looks like there is a difference between
a. using cursor_sharing=force, and
b. binding all constants yourself while keeping
cursor_sharing=exact.
So, If I am trying to prove that binding constants
is disadvantageous, I cant simply point out to the
disadvanatges of cursor_sharing=force option - they
are not equivalent.

Thank you!

Hi Tom
I am unable to reproduce the results of your example on
pages 443-446 (in your expert one on one book)
One reason may be that 9i works differently.
The example tried to prove that cursor_sharing would
result in suboptimal quey plans being generated.

---test@apps> set echo on
test@apps> @schema2
test@apps>
test@apps> set serveroutput on size 200000
test@apps> --create a table
test@apps>
test@apps> drop table t1 cascade constraints;

Table dropped.

Elapsed: 00:00:00.95
test@apps>
test@apps> create table t1
2 as select * from all_objects;

Table created.

Elapsed: 00:00:06.93
test@apps>
test@apps> create index t1_idx1 on t1( object_name );

Index created.

Elapsed: 00:00:04.23
test@apps> create index t1_idx2 on t1( object_type );

Index created.

Elapsed: 00:00:03.45
test@apps> analyze table t1 compute statistics for table for all indexes for all indexed columns;

Table analyzed.

Elapsed: 00:00:04.65
test@apps>
test@apps> @trace
test@apps> set echo on
test@apps> alter session set timed_statistics=true;

Session altered.

Elapsed: 00:00:00.01
test@apps> alter session set events '10046 trace name context forever, level 12';

Session altered.

Elapsed: 00:00:00.02
test@apps> @sel33
test@apps> variable a varchar2(18);
test@apps> exec :a := '%';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
test@apps> select count(*)
2 from t1 no_binding
3 where object_name like :a
4 and object_type in ( 'PROCEDURE','FUNCTION', 'TRIGGER' );

160

Elapsed: 00:00:00.09
test@apps>
test@apps>
test@apps> @force
test@apps> alter session set cursor_sharing=force;

Session altered.

Elapsed: 00:00:00.01
test@apps> @sel33
test@apps> variable a varchar2(18);
test@apps> exec :a := '%';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
test@apps> select count(*)
2 from t1 no_binding
3 where object_name like :a
4 and object_type in ( 'PROCEDURE','FUNCTION', 'TRIGGER' );

160

Elapsed: 00:00:00.08
test@apps>
test@apps>
test@apps>
---

From tkprof output (from command "tkprof apps_ora_3591.trc tk.out aggregate=no") we see that same plans are generated
regardless of cursor_sharing=exact or force option.

---
select count(*)
from t1 no_binding
where object_name like :a
and object_type in ( 'PROCEDURE','FUNCTION', 'TRIGGER' )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.07 0.07 0 173 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.07 0.07 0 173 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 228

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
160 VIEW index$_join$_001
160 HASH JOIN
160 INLIST ITERATOR
160 INDEX RANGE SCAN (object id 112714)
34175 INDEX RANGE SCAN (object id 112713)
.....
alter session set cursor_sharing=force
....
select count(*)
from t1 no_binding
where object_name like :a
and object_type in ( 'PROCEDURE','FUNCTION', 'TRIGGER' )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.08 0.07 0 173 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.08 0.07 0 173 0 1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 228

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
160 VIEW index$_join$_001
160 HASH JOIN
160 INLIST ITERATOR
160 INDEX RANGE SCAN (object id 112714)
34175 INDEX RANGE SCAN (object id 112713)

Hi Tom
I am finding it hard to prove that binding constants in where clause can give us suboptimal query plan. This may be due to the peeking of bind variables that 9i does? I tried to create table with skewed data and then executed selects where I expect the optimizer to do a full scan if
the constant is not bound. It ends up using same indexe
in both cases.

Any help would be appreciated as usual!

Thank you so much!!

Following is what I ran
---
QL> set echo on
SQL> @schema
SQL> 
SQL> set serveroutput on size 200000
SQL> --create a table
SQL> 
SQL> drop table t1 cascade constraints;

Table dropped.

SQL> 
SQL> create table t1 ( name varchar2(20), data varchar2(4000) );

Table created.

SQL> 
SQL> create index t1_idx1 on t1( name, data );

Index created.

SQL> 
SQL> @data
SQL> set echo on
SQL> set serveroutput on size 200000
SQL> truncate table t1;

Table truncated.

SQL> insert into t1
  2  select 'name1', rpad( 'data' || rownum, 200)
  3  from all_objects
  4  where rownum <= 20
  5  union
  6  select 'name2', rpad( 'data' || rownum, 200)
  7  from all_objects
  8  where rownum <= 30;

50 rows created.

SQL> 
SQL> insert /*+ APPEND */ into t1
  2  select 'name3', rpad( 'data' || rownum, 1000)
  3  from all_objects, all_objects
  4  where rownum <= 10000;

10000 rows created.

SQL> /*
DOC>analyze table t1 compute statistics for table for all indexes for all indexed columns;
DOC>*/
SQL> analyze table t1 compute statistics for table for all indexes;

Table analyzed.

SQL> set autotrace traceonly
SQL> @trace
SQL> set echo on
SQL> alter session set timed_statistics=true;

Session altered.

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> @sel3
SQL> select *
  2  from t1 no_binding
  3  where data like 'data%';

10050 rows selected.                  



Statistics
----------------------------------------------------------                      
         42  recursive calls                                                    
        229  db block gets                                                      
       2019  consistent gets                                                    
        258  physical reads                                                     
       1320  redo size                                                          
   10513241  bytes sent via SQL*Net to client                                   
      88142  bytes received via SQL*Net from client                             
        671  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
      10050  rows processed                                                     

SQL> 
SQL> 
SQL> @sel4
SQL> variable x varchar2(20);
SQL> exec :x := 'data';

PL/SQL procedure successfully completed.

SQL> select *
  2  from t1 binding
  3  where data like :x || '%';

10050 rows selected.


Statistics
----------------------------------------------------------                      
          0  recursive calls                                                    
          8  db block gets                                                      
       2015  consistent gets                                                    
          0  physical reads                                                     
          0  redo size                                                          
   10513241  bytes sent via SQL*Net to client                                   
      88142  bytes received via SQL*Net from client                             
        671  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
      10050  rows processed                                                     
SQL> 
---

and tkprof output portions are
--
select *
from t1 no_binding
where data like 'data%'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      671      1.00       1.02        258       2015        229       10050
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      673      1.00       1.02        258       2015        229       10050

Misses in library cache during parse: 1
...
select *
from t1 binding
where data like :x || '%'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      671      0.93       0.95          0       2015          8       10050
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      673      0.93       0.95          0       2015          8       10050

Misses in library cache during parse: 1
.....
-- 

that is what I thought (as I mentioned in the
question )..I read your articles that you pointed to.
Great stuff as usual!

Please keep up the great work!

(1) My understanding is that FORCE option will consider two sql statements that are identical except the literal values in where clause are the same and use the same shared cursor. Is it correct?

(2) What does cursor_sharing = similar do?

(3) also, how oracle treats dynamic sqls? Does oracle use shared pool for dynamic sqls also?

(4) what is the difference between using v$sql and v$sqlarea?

Thanks in advance. I hope you would consider these questions in this thread.

I looked ata your example. could you give me your code for exec gen_data( 'EMP', 50000 )

so I can try on my database. Thanks.

Following is your quote from another thread. I thought the optimizer will do the same even if I don't set the CURSOR_SHARING to similar. Optimizer will go for Full table scan if the result set is going to be 99% of the table by ignoring the index and the optimizer will use the index if it is there, if the result set is just a single row. So, what is so special about SIMILAR option? I don't understand. May be I miss something here. Thanks.

<quote>
cursor_sharing=similar will recognize when substituting a bind variable COULD radically change a plan (eg: "select * from t where x = 5" would not use an index since 99.9% of the data is 5, but "select * from t where x = 6" would use
an index -- just putting in a bind would be fatal) and will generate multiple plans based on the bind variable value. It recognizes that more then one plan is necessary and permits that.
<quote.

Why does not CBO create several alternative plans for query execution and use them dynamically when bind variables are bound and thus their values known?

It should not be that CPU intensive nor hard to program it into CBO to match few plans with few bind variables. Assuming that statistics are kept up-to-date it should work at least in most common cases.

Besides I read your article in this month's Oracle magazine about the query executed under 9iR2 where CBO basically came up with two plans: one for null and one for non null values. Explain plan showed CONCATENATION where the plan bifurcated.

So why not generalize this feature for data ranges for example?

"Why does not CBO create several alternative plans for query execution and use them dynamically when bind variables are bound and thus their values known?"

I guess following could be the reasons:
1. This feature would be mostlikely useful only for skewed data - not always the most common scenario - btw, Oracle's peeking the bind variables feature does this (only once though)
2. Query plan generation is very costly - remember the
difference between soft and hard parses is the query plan generation - you may end up generating multiple plans
(equivalent to multiple hard parses per query) and
many of them would never get used. Even if this is
a one-time cost per query (in a well-tuned system that
is), it may be expensive.

Tom, over to you for the real story!:)
Thanx!
Menon:)

Hi,

1. We have a third party DSS system, which runs over our Oracle tables (currently 8i, but soon to be 9i).

I know that forcing cursor sharing is ordinarily regarded as a temporary fix, until the *faulty* SQL is rewritten, but in our case, the DSS SQL is generated via GUI input from our Account Managers and is, thus, unpredictable in its content. As we have no way of controlling how the SQL is generated, might setting CURSOR_SHARING=FORCE be a good thing to do?

2. Fuzzy, imprecise question.I know, but have you any *top tips* for setting up/running Oracle 9i for such a DSS system?


Thanks.

Thank you!

Tom

I believe we found the source of our performance issues.

When I read chapter 2 in your book on the Oracle Architecture and Shared Pool segment your explanation of performance degradation described what we are experiencing to a tee...especially the decline in performance as the day wears on and having to shutdown the server to 'clear out' or flush out the shared_pool and start the cycle all over again.

I've attached the error message we are receiving regarding
invalid objects.

If the cause of the problem is resulting from the software
developers not using bind variables it is beyond our capacity to correct.

We can only hope that the software vendor employs a competent DBA that knows about this stuff.

By looking at the below message can you assess what the
problem is.

forever thankful...




Errors for PACKAGE BODY DBMS_SHARED_POOL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
23/7 PL/SQL: SQL Statement ignored
23/48 PLS-00201: identifier 'X$KGLOB' must be declared
79/9 PL/SQL: SQL Statement ignored
84/14 PLS-00904: insufficient privilege to access object SYS.V_$SQLAREA
101/7 PL/SQL: Statement ignored
101/10 PLS-00364: loop index variable 'REC' use is invalid

SUBSTR(OBJECT_NAME,1,40) OBJECT_TYPE
---------------------------------------- ------------------
DBMS_SHARED_POOL PACKAGE BODY

1 row selected.

I should have known better....DBMS packages are System
packages owned by SYS.

Hope everything is going ok at the seminar in Quebec...
wish I was there. Will you be doing a seminar on the
east coast anytime soon...D.C. perhaps.

take care...

Hi

From this example you posted

alter session set cursor_sharing=force;
@trace
set termout off
select * from t query1 where id = 0;
select * from t query1 where id = 1000;
select * from t query2 where id = 1000;
select * from t query2 where id = 0;


You said bind peeking set a generic optimized plan and this plan may change day after day or when the shared sql is fluhsed from shared pool. Am I right?

How can we disable bind peeking?

Hi

I read from a poster that this parameter broke his application... how can that be?

Hi Tom,
Using Cursor sharing as a 'crutch' as you call it :) we are trying to make an app run while getting the necessary code changes done..We have an after logon trigger which enables the cursor sharing = force for a particular user. Now observation is that while connecting as that user from sqlplus orcle uses cursor sharing and replaces literals with system binds...But this doesn't happen while running the application...Oracle doesn't use cursor sharing.
One possible reason I can think of is the code itself ....(native dynamic sql or something that forces oracle to use an alternate execution plan)...
Could You suggest anything else I am missing out on......
We have the following parameters in the trigger`
cursor_sharing = force
optimizer = first_rows

and the version is 8.1.7

Hi,
Thanks for an encouraging response. Let me attempt to explain the situation I am currently facing without revealing *properietry* information :)

I have a java program ( uses oracle9i) thin driver. It executes a batch of insert statements
con = mDataSource.getConnection();
stmt = con.createStatement();

for loop on the following code
stmt.addBatch("insert into TABLE_NAME values (1,1,1,1));

con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
con.setAutoCommit(false);

stmt.executeBatch();
conn.commit();


I am currently on oracle 8.1.7

Hope this helps.
Please let me know if you need some more information to analyze this issue.

Thanks and Regards,
Pramit

re: one of two languages typically

I think I am working on the second one...

- Mark

Please check my understanding after reading your explanations.

select * from employees where dept_id = 10 <Query 1 - 100 rows
select * from employees where dept_id = 20 <Query 2 - 100000 rows

(1) If stats on table employees and histogram on dept_id are generated, and there is an index created on dept_id column, the first query would use index and the second would do FTS with cursor_sharing = EXACT or SIMILAR (it does not matter). Both settings EXACT and SIMILAR will behave the same in this case. Is this correct?

(2) If cursor_sharing is is set to FORCE, there is only one plan for both queries that is, in this case, using index because query 1 would have used index. Is this correct?

I was reading above "similar in a nutshell behaves as exact". If EXACT and SIMILAR behave the same, why do we have "SIMILAR" then and where one should use the setting SIMILAR? How SIMILAR differs from EXACT? Thanks.

We have 2 OLTP appliation, 1 in GUI-PB and other one in JSP running in oracle server (8.1.6)

JSP application is using the bind variable. But GUI-PB application is not using bind variable, since it has been disabled in application. (it is problem of Front end ERP)

So to solve the problem, if I set cursor_sharing=FORCE, will it convert the sqls as bind based and will it imporove the performance.

When using cursor_sharing=force, in which way the transformation of the sql text is implemented ?
(1) by a simple subroutine that scans the sql text and hunts for tokens that start and end with '', or start with a number etc, and replaces them with the string ":SYSxxx", or
(2) by building a parse tree of the sql, and accessing the shared pool (eg the dictionary cache to identify objects) ?

If (1) applies, the overhead of the sql text transformation is "only" increased CPU time (which is a mostly-scalable resource); if (2) does instead, we get more latches acquisitions, which are not a scalable resource of course.

Reason for question: "they" have set cursor_sharing=force for the whole instance, and I'm trying to reason on the impact on my literal-free stmts, that are always transformed into themselves - so the aforementioned overhead is the only overhead I get.

thanks!

it is more like #1.  the query is transformed and then the shared pool is inspected.

the downside to instance wide cursor_sharing=force is

a) good applications are negatively impacted, period.

b) plans will change (where x=1 used index, where x=:bind_variable might not)

c) reports will go funny -- select substr( column, 1, 5 ) from t was in the past known to return 5 characters.  select substr( column :bv1, :bv2 ) isn't.  widths of things will change.  select 'Hello World' from dual will become select :bv from dual -- things will change

d) various 3rd party tools will have support issues (oh it isn't working, maybe it is that setting, turn it off and we'll help you)

to name a few.

cursor sharing should be implemented in BAD applications, at the session level, as a crutch to help those programs until they get the serious bugs in them fixed.



It is interesting to note that 

a) plsql static sql, since it is "perfect" with respect to binding is not subject to cursor_sharing=force

b) plsql dynamic sql that contains at least one bind, won't be subject to cursor sharing

ops$tkyte@ORA9IR2> alter system flush shared_pool;
System altered.
 
ops$tkyte@ORA9IR2> alter session set cursor_sharing=force;
Session altered.
 
ops$tkyte@ORA9IR2> declare
  2          l_cursor sys_refcursor;
  3  begin
  4      open l_cursor for 'select 1 from dual look_for_me where dummy = :x and 1=1' using 'x';
  5          close l_cursor;
  6      open l_cursor for 'select 2 from dual look_for_me where dummy = ''x'' and 1=1';
  7          close l_cursor;
  8  end;
  9  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select sql_text from v$sql where sql_text like '%look_for_me%';
 
SQL_TEXT
-------------------------------------------------------------------------------
select :"SYS_B_0" from dual look_for_me where dummy = :"SYS_B_1" and :"SYS_B_2"
=:"SYS_B_3"

<b>way overbound....</b>
 
declare  l_cursor sys_refcursor; begin     open l_cursor for 'select 1 from dua
l look_for_me where dummy = :x and 1=1' using 'x';  close l_cursor;     open l_
cursor for 'select 2 from dual look_for_me where dummy = ''x'' and 1=1';  close
 l_cursor; end;
 
select 1 from dual look_for_me where dummy = :x and 1=1

<b>just right....</b>


but it is not universally true -- only for the perfect language that is plsql :)

Pro*C for example:

                                                                                                         
void process()
{
EXEC SQL BEGIN DECLARE SECTION;
int     n;
varchar x[4000];
EXEC SQL END DECLARE SECTION;
                                                                                                         
    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
                                                                                                         
    strcpy( x.arr, "x" );
    x.len = strlen(x.arr);
                                                                                                         
    exec sql alter system flush shared_pool;
    exec sql alter session set cursor_sharing=force;
                                                                                                         
    exec sql select 1 into :n from dual look_for_me where dummy = :x and 1=1;
    exec sql select 2 into :n from dual look_for_me where dummy = 'x' and 1=1;
                                                                                                         
    exec sql declare c cursor for
    select sql_text from v$sql where sql_text like '%look_for_me%';
                                                                                                         
    exec sql open c;
    for( ; ; )
    {
        exec sql whenever notfound do break;
        exec sql fetch c into :x;
        printf( "%.*s\n", x.len, x.arr );
    }
    exec sql whenever notfound continue;
    exec sql close c;
}
                                                                                                         
<b>results in this:</b>

[tkyte@tkyte-pc-isdn t]$ ./t
select :"SYS_B_0" into :b0  from dual look_for_me where (dummy=:"SYS_B_1" and :"SYS_B_2"=:"SYS_B_3")
select :"SYS_B_0" into :b0  from dual look_for_me where (dummy=:b1 and :"SYS_B_1"=:"SYS_B_2")


Note the ":b1" AND the :sys_b_'s...  
 

I already knew about bullet points a,b,c,d and a-second - I didn't know about b-second which is great to know too (no need to use /*+ cursor_sharing_exact */ for almost all stmts) ... you're preaching to the choir here ;-) but unfortunately politics prevent me from resetting cursor_sharing to exact right now. Sad but true.

(a) Actually I was concerned about the Java part [not written by me, I develop only in pl/sql] of the app; afaik the server can't tell a Java client from a pro*C one, so your last example applies to Java (and other languages) too, am I correct ?

(b) One interesting point: we use *always* the technique of opening SELECT statements inside packaged stored procedures and returning them as ref cursors - since a sp call is actually wrapped inside an anonymous pl/sql block, this means that, even if called by Java (or other languages), my beloved stmts escape the cursor_sharing=force demon (if the OPEN is static, or if it is dynamic but contains at least a bind variable)! Do you agree ?

a) you'll want to test, this was the 9ir2 thin jdbc drivers:

public static void main(String args[])throws Exception
{
    DriverManager.registerDriver
       (new oracle.jdbc.driver.OracleDriver());
                                                                                                                 
    Connection conn=DriverManager.getConnection
        ("jdbc:oracle:thin:@localhost.localdomain:1521:ora9ir2"
        ,"ops$tkyte","foobar");
                                                                                                                 
    conn.setAutoCommit(false);
    Statement stmt = conn.createStatement ();
    stmt.execute( "alter system flush shared_pool" );
    stmt.execute( "alter session set cursor_sharing=force" );
                                                                                                                 
    PreparedStatement pstat =
    conn.prepareStatement
    ("select 1 from dual look_for_me where dummy = ? and 1=1" );
    pstat.setString( 1, "x" );
    ResultSet rset = pstat.executeQuery();
                                                                                                                 
    PreparedStatement pstat2 =
    conn.prepareStatement
    ("select 2 from dual look_for_me where dummy = 'x' and 1=1" );
    ResultSet rset2 = pstat2.executeQuery();
                                                                                                                 
    PreparedStatement pstat3 =
    conn.prepareStatement
    ("select sql_text from v$sql where sql_text like '%look_for_me%'" );
    ResultSet rset3 = pstat3.executeQuery();
                                                                                                                 
    while( rset3.next() ) System.out.println( rset3.getString(1) );
}
}


[tkyte@localhost j]$ java test
select :"SYS_B_0" from dual look_for_me where dummy = :"SYS_B_1" and :"SYS_B_2"=:"SYS_B_3"
select 1 from dual look_for_me where dummy = :1 and 1=1


"plsql like" in its behavior

b) yes, it should.
 

Thanks, of course, as always, for your great answers!

Well, what a strange result we get here - two client technologies behaving differently (Java and pro*c) when a server setting (cursor_sharing) is modified. I thought the server was oblivious about the client technology, thanks to the Net8 "abstraction layer".

And Java being alike pl/sql :-O ...

Just to be sure - your pro*c example was run on the same version/patchset of the Java and pl/sql ones ? I've noticed you were on "tkyte@tkyte-pc-isdn".

Your example verbatim - just added the choice of OCI vs thin drivers:

$ cat Test.java
import java.sql.*;

public class Test {
public static void main(String args[])throws Exception
{
DriverManager.registerDriver
(new oracle.jdbc.driver.OracleDriver());

System.out.println(args[0]);
Connection conn=DriverManager.getConnection (args[0], "dellera", "dellera");

conn.setAutoCommit(false);
Statement stmt = conn.createStatement ();
stmt.execute( "alter system flush shared_pool" );
stmt.execute( "alter session set cursor_sharing=force" );


PreparedStatement pstat =
conn.prepareStatement
("select 1 from dual look_for_me where dummy = ? and 1=1" );
pstat.setString( 1, "x" );
ResultSet rset = pstat.executeQuery();


PreparedStatement pstat2 =
conn.prepareStatement
("select 2 from dual look_for_me where dummy = 'x' and 1=1" );
ResultSet rset2 = pstat2.executeQuery();


PreparedStatement pstat3 =
conn.prepareStatement
("select sql_text from v$sql where sql_text like '%look_for_me%'" );
ResultSet rset3 = pstat3.executeQuery();


while( rset3.next() ) System.out.println( rset3.getString(1) );
}
}

Results on 9.2.0.5:

$ java Test "jdbc:oracle:thin:@localhost:1521:oracle9i"
jdbc:oracle:thin:@localhost:1521:oracle9i
select :"SYS_B_0" from dual look_for_me where dummy = :"SYS_B_1" and :"SYS_B_2"=:"SYS_B_3"
select 1 from dual look_for_me where dummy = :1 and 1=1

$ java Test "jdbc:oracle:oci8:@oracle9i"
jdbc:oracle:oci8:@oracle9i
select :"SYS_B_0" from dual look_for_me where dummy = :"SYS_B_1" and :"SYS_B_2"=:"SYS_B_3"
select :"SYS_B_0" from dual look_for_me where dummy = :1 and :"SYS_B_1"=:"SYS_B_2"

Thin drivers behave like pl/sql - OCI ones like pro*c.
Sigh - we use the OCI ones ...

We use bind variables in all our procedures,functions. Will setting cursor_sharing = force will have any side effects on our existing application? will there be a change in query plans?

As a final contribution - I've checked that opening selects inside packages and returning the result set as a ref cursor (a nice technique for a lot of well-known reasons) brings us the nice pl/sql behaviour regarding cursor_sharing=force, even if we use the OCI drivers (which behave like pro*c - see my previous review):

SQL> create or replace
  2  procedure p (p_s1 out sys_refcursor, p_s2 out sys_refcursor)
  3  is
  4    l_dummy varchar2(1) default 'x';
  5  begin
  6    open p_s1 for select 1 from dual look_for_me where dummy = l_dummy and 1=1;
  7    open p_s2 for select 2 from dual look_for_me where dummy = 'x' and 1=1;
  8  end;
  9  /

Procedure created.

$ cat Test.java
import java.sql.*;
import oracle.jdbc.driver.*;

public class Test {
public static void main(String args[])throws Exception
{
    DriverManager.registerDriver
       (new oracle.jdbc.driver.OracleDriver());

    System.out.println(args[0]);
    Connection conn=DriverManager.getConnection (args[0], "dellera", "dellera");

    conn.setAutoCommit(false);
    Statement stmt = conn.createStatement ();
    stmt.execute( "alter system flush shared_pool" );
    stmt.execute( "alter session set cursor_sharing=force" );

    CallableStatement  cstmt = conn.prepareCall("begin p(?,?); end;");
    cstmt.registerOutParameter(1,OracleTypes.CURSOR);
    cstmt.registerOutParameter(2,OracleTypes.CURSOR);
    cstmt.execute();

    PreparedStatement pstat3 =
    conn.prepareStatement
    ("select sql_text from v$sql where lower(sql_text) like '%look_for_me%'" );
    ResultSet rset3 = pstat3.executeQuery();


    while( rset3.next() ) System.out.println( rset3.getString(1) );
}
}

$ java Test "jdbc:oracle:oci8:@oracle9i"
jdbc:oracle:oci8:@oracle9i
SELECT 1 FROM DUAL LOOK_FOR_ME WHERE DUMMY = :B1 AND 1=1
SELECT 2 FROM DUAL LOOK_FOR_ME WHERE DUMMY = 'x' AND 1=1

So - no literal replacement since it's static sql.
The behaviour is the same as pl/sql for dynamic OPENs too:

SQL> create or replace
  2  procedure p (p_s1 out sys_refcursor, p_s2 out sys_refcursor)
  3  is
  4    l_dummy varchar2(1) default 'x';
  5  begin
  6    open p_s1 for 'select 1 from dual look_for_me where dummy = :1 and 1=1' using l_dummy;
  7         open p_s2 for 'select 2 from dual look_for_me where dummy = ''x'' and 1=1';
  8  end;
  9  /

Procedure created.

$ java Test "jdbc:oracle:oci8:@oracle9i"
jdbc:oracle:oci8:@oracle9i
select :"SYS_B_0" from dual look_for_me where dummy = :"SYS_B_1" and :"SYS_B_2"=:"SYS_B_3"
select 1 from dual look_for_me where dummy = :1 and 1=1

Statements containing at least one bind have their literals preserved.

Version was 9.2.0.5.

Very nice! 

Our DBA says that Cursor_sharing=Force is bad if a cost based optimizer is in use. Is this true. and if so why?

Crystal Reports which binds the literals and runs a query many thosands of times... obviously this most probably be a candidate of CURSOR_FORCE=SHARING. 10g Enterprise Manager's ADDM Suggests and 18% Improvement. Would you in this case alter session cursor_sharing=FORCE, then immediately turn it off after, since there is no alternative for binds (Export PDF App using Crystal (COM Object)) . Runs for about 4 Hours..

I noticed that because of CURSOR_SHARING=FORCE setting ot the server, EXISTS(SELECT 1 FROM is rewritten as EXISTS(SELECT :"SYS_B_1" FROM

I replaced EXISTS(SELECT 1 FROM with EXISTS(SELECT * FROM, but a cursory investigation indicated no performance differences either way IN THIS PARTICULAR CASE.

I have queries containing EXISTS(SELECT 1 FROM all over the place. Should I go ahead and start changing them to EXISTS(SELECT * FROM ? What do you think?

there are some applications that do not use bind variables as frequently as they should, hence the CURSOR_SHARING=FORCE setting is in place until those application are modified to use bind variables.
Does it make sence to you?

What's the bug you are speaking about?

In the presence of CURSOR_SHARING=FORCE setting,
does it make more sence to use
EXISTS(SELECT NULL FROM
than to use
EXISTS(SELECT * FROM ?

NULL could be replaced with a parameter, and I'm not happy with it

hi tom,

we are seeing hugh number of latches (sometimes 400 latches) on library cache and it started when we added the cursor_sharing=force.
any idea why it can happen?

thanks

shay

the statement I run is
select 'event: ',event,count(*) from v$session_wait group by 'event: ',event
union all
select 'latch: ',name,count(*) from v$latch,v$session_wait
where p2=latch# and event = 'latch free'
group by 'latch: ',name

and the results:


'EVENT: EVENT COUNT(*)
------- ---------------------------------------------------------------- ----------
event: SQL*Net message from client 1475
event: SQL*Net message from dblink 1
event: SQL*Net message to client 1
event: db file sequential read 1
event: direct path read 1
event: latch free 42
event: pipe get 12
event: pmon timer 1
event: rdbms ipc message 24
event: smon timer 1
latch: cache buffers chains 1
latch: library cache 36

12 rows selected.

While trying to analyze the table using dbms_stats this is what we got:

BEGIN dbms_stats.gather_table_stats ( ownname => 'CORP', tabname => 'ASSM_PART_XREF', estimate_percent=> 10, block_sample=>FALSE, degree => 4, method_opt => 'FOR ALL COLUMNS SIZE 1', granularity=>'ALL', cascade=>TRUE); END;

*
ERROR at line 1:
ORA-12827: insufficient parallel query slaves available
ORA-06512: at "SYS.DBMS_STATS", line 12192
ORA-06512: at "SYS.DBMS_STATS", line 12211
ORA-06512: at line 1


According to oracle support, the DBA said to set the CURSOR_SHARING parameter to SHARING and flush the shared pool.

In fact it worked after doing this. My suggestion was to set the parallel_min_percent to 0. It was set to 10 earlier.

Can you explain me how the CURSOR_SHARING parameter affect the parallel slaves?

Thanks,

Ok, you say you would do this:

...
CallableStatement stmt = null;

String getWordb(int ID, int IDLang, Connection conn) throws SQLException
{
if ( stmt == null ) {
stmt = conn.prepareCall("{ call get.wordb (?,?,?)}");
stmt.registerOutParameter (3, java.sql.Types.VARCHAR);
}
stmt.setInt(1,ID);
stmt.setInt(2,IDLang);
stmt.execute();
return stmt.getString (3);
}
...

I agree that in a single user environment this would work. But in a multiuser (threaded) environment, wouldn't we need to synchronize this method to make it safe. That would definitely have a serious impact on performance.

Right?

(Is there an alternative in Java, such that we can reference a single underlying cursor from multiple PreparedStatement objects?)

I meant multiuser environment.

I must not have expressed myself clearly. Let me rephrase.

Of course, that method getWordb is an instance method, so let's assume it is a method in the class ZZ:

public class ZZ {
...
CallableStatement stmt = null;

String getWordb(int ID, int IDLang, Connection conn)
throws SQLException
{
if ( stmt == null ) {
stmt = conn.prepareCall("{ call get.wordb (?,?,?)}");
stmt.registerOutParameter (3, java.sql.Types.VARCHAR);
}
stmt.setInt(1,ID);
stmt.setInt(2,IDLang);
stmt.execute();
return stmt.getString (3);
}
...
}

The Java method you wrote reuses the same prepared statement instance for all calls to that method as long as we only have one instance of ZZ.

Let's say that I am writing a web application that uses that method and that is instantiates only one instance of ZZ.

As long as there is only one request at a time to my web app then the method works fine.

But what happens when 2 requests (request a and request b) come in at the same time and both of these requests enter the method at the same time.

Let's say that each user request passes different parameters which translates into different values for variable "ID".

thread for request a executes this line:
stmt.setInt(1,"foo");
and then thread for request b executes the same line:
stmt.setInt(1,"moo");

and then they both go on to execute the rest of that method in whatever order.

That is a big problem, right?

Well we can get around the problem by using the synchronize keyword on the method.

But then we probably have even bigger performance (locking) issues than we had in your first implementation of getWordb, (the one that creates a new PreparedStatement every time). Only this time the resource waiting is in the java code in the web app instead of in the database.

I could get around this by creating multiple instances of ZZ. But then I have to manage this pool of ZZ's, which itself is going to produce some resource waiting issues.

Alternatively, I could start up multiple instances of my web app on multiple machines.

Hi Tom,

Is there a way to get a count of sessions that have cursor_sharing = force? I have a logon trigger that checks the USER value and if it is a certain user, I set this to cursor_sharing = force, and I want to get a count to be sure it is working correctly.

Regards,
Mark

...see the 'sys_...' bind variables in 'some' of my SQL statements, but not all. I'm thinking some of the existing live sessions would not have this parameter set for them until the session dies and gets recreated, firing the logon trigger, implicitly setting the cursor_sharing=force parameter.

Even ALTER SYSTEM SET CURSOR_SHARING=FORCE does not go through each session and set that parameter it seems.



regards,
Mark

Hi,

I know this qn is related with replication but still -

Is there any way we can reduce the soft parse frequency for sybase to oracle table replication ?

Initially because of literals there were a lot of hard parsing which were happening in this sybase to oracle replication(for inserts/deletes) - but we were able to bring the hard parse down by using the "crutch" cursor_sharing SIMILAR for the oracle replication session, but still there there are a lot of soft parsing taking place - almost as same as the number of executes(inserts/deletes) happening from sybase to oracle

Is there any way we can reduce this soft parse frequency in this sybase to oracle replication ?

Unfortunately we don't have access and much knowledge about this sybase to oracle replication, since it is something setup on the sybase side

thanks
Anto

GOOD STUFF.

Is cursor_sharing associated to this ERROR:

ORA 2068 ??

We have a DBLink that acess a view and some times it returns this ERROR.

The DBA tell us to set cursor_sharing=EXACT but I dont think there is anything related to it.

Is it true?

Tks

we set it "force" in init.ora.
use your function remove_constant

we still found tons of distincts:
example
INSERT INTO UPKLIST (SESSION_ID, CONTEXT_ID, PK_ID) VALUES (@, @, @)
22528

We set it "force" in init.ora-- db level.
Also set it "force" for the application account by using the logon trigger for 'alter session set ... = force'.

it's a 3rd pary application.

as stated we set it at db level and set it at app level by using the logon trigger for the app account. how possible, it, app, defeats the parameter force? by hard coded:
alter session set cursor_sharing=exact in the applications?

what are other possibilitied?

TIA

Dear Tom,

In my database cursor_sharing was 'EXACT'. Its a hybrid system. When I change value of cursor_sharing to 'similar' to gain some performance. Some queries starts giving wrong results. e.g. Earlier one query returns 20 rows but after changing this parameter, query start giving only 1 row.

Can you please explain why this happens??

10gr2
cursor_sharing set at db level to FORCE and optimizer_mode set to first_row.

used your tool, we still have:

SQL_TEXT_WO_CONSTANTS
------------------------------------------------------------------------------
COUNT(*)
----------
WITH SUMMARY AS ( SELECT AUDIT_RULES_SYS_ID, AUDIT_RULES_VER_NUM, AUDIT_REF_SY
S_ID, PARENT_SUP_HDR_SYS_ID, SUP_HDR_SYS_ID, ACNT_TYPE, WEIGHT_FACTOR, AUDIT_T
HRESHOLD, MON_EXCEP_AMT_IND, NUM_OF_MONTHS, RETRO_CREDIT_DT, SHORT_PAID_IND, U
SER_ID, CREATE_DT, UPDATE_DT, ACTIVE_IND,ROW_NUMBER() OVER(PARTITION BY @ ORDE
R BY WEIGHT_FACTOR DESC, AUDIT_RULES_VER_NUM DESC) AS WEI FROM AUDIT_RULES WHE
RE ACTIVE_IND='#' AND (AUDIT_REF_SYS_ID = '#' OR AUDIT_REF_SYS_ID IS NULL) AND
PARENT_SUP_HDR_SYS_ID IS NULL AND (SUP_HDR_SYS_ID = '#' OR SUP_HDR_SYS_ID IS
NULL OR PARENT_SUP_HDR_SYS_ID = (SELECT S.PARENT_SUP_SYS_ID FROM SUPPLIER_HDR
S WHERE S.SUPPLIER_HDR_SYS_ID = '#')) AND (ACNT_TYPE = '#' OR ACNT_TYPE IS NUL
L) ) SELECT * FROM SUMMARY WHERE WEI = @
1141

it is from "JDBC Thin Client".

is this a bug of 10gr2?

it's Oracle¿s Driver (Thin) Versions:9.0.1,9.2.0,10

Sean,
I think Tom's comment is that you aren't using bind variables. Use bind variables. Why is it always Java Developers who can't use bind variables (prepared statements)? (bad code) Using the cursor sharing=force you are asking the db to fix your poor programming practices.

Sean,

I found cursor_sharing=force for querys starting with the WITH clause doesn't work on 10.2.0.1 and 10.2.0.3. Not tested in others versions. Check this:

> alter session set cursor_sharing=force;

Sesi¾n modificada.

> with t as (select 'hola' from dual) select * from t;
'HOL
----
hola

> select sql_text from v$sql where sql_text like 'with%';
SQL_TEXT
---------------------------------------------------------------------------------------------
with t as (select 'hola' from dual) select * from t

> select * from (with t as (select 'hola' from dual) select * from t);
'HOLA'
--------------------------------
hola

> select sql_text from v$sql where sql_text like 'select * from (with%';
SQL_TEXT
---------------------------------------------------------------------------------------------
select * from (with t as (select :"SYS_B_0" from dual) select * from t)

I opened a SR with support but I don't know in wich state it is. The workaround is to encapsulate the with query inside a  select * from (...);

Joaquín González

Hi Tom,

I have cursor_sharing=similar. The following queries taking buffer gets > 1000000000. SELECT :"SYS_B_0" FROM DUAL

can you please tell why this much buffer gets?

Hello Thomas,
How do you do?
I have encountered this kind of WEIRD behaviour for the second time.Unfortunately i fail to replicate the senario.
So i will copy paste the queries as they are in the prod environment and request you your inputs.
Oracle Version: 10.2.0.4.0

Test Script
DECLARE
v_modification_indicator CHAR(3);
v_trans_date DATE;
BEGIN

SELECT DECODE(TRIM(TO_CHAR(TRUNC(SYSDATE)-1,'Day')),'Sunday',TRUNC(SYSDATE)-3,TRUNC(SYSDATE)-1)
INTO v_trans_Date
FROM dual;
FOR i IN (
SELECT
f.current_qtr_ind current_qtr_ind
, f.PRIOR_QTR_IND PRIOR_QTR_IND
, f.next_qtr_ind next_qtr_ind
, B.focus_grade_type
FROM OPEN_SALES_ORDER@l_prddw A
, PRODUCT@l_prddw B
, vw_ship_to_customer@l_prddw D
, FML_DISTRICT@l_prddw E
, FISCAL_CALENDAR@l_prddw F
, MYT_COM_USERID_XREF G
,(select distinct zone_name,region_id from myt_com_organization) M
WHERE A.COMP_NO = B.COMP_NO
AND A.ITEM_NO = B.ITEM_NO
AND A.COMMERCIAL_IND = 'Y'
AND A.SALES_ORG = 'ESO'
AND B.BUSINESS_GRP IN ('RS','AZ','LN','SH')
AND B.item_grade NOT LIKE 'BPA%'
AND D.ESR_Flag = B.ESR_Flag
AND A.CUST_NO = D.CUST_SHIPTO
AND D.FML_DISTRICT = E.FML_DISTRICT
AND A.REQ_SHIP_DATE = F.FISCAL_DATE
AND B.focus_grade_type in ('CORE','Semi-Custom','Custom','Semi-custom')
AND G.SALES_REP_ID=NVL(e.fml_ts_code,e.fml_am_code)
AND TRUNC(A.REQ_SHIP_DATE)>=TRUNC(SYSDATE)
AND TRUNC(a.req_ship_date) < (SELECT MAX(fiscal_date)
FROM ddw01.FISCAL_CALENDAR@l_prddw
WHERE fiscal_qtr=3 AND fiscal_year='2008') + 26
and ((trim(A.int_ext_ind)='E' and trim(m.zone_name)='SFNS') or (trim(m.zone_name) <> 'SFNS'))
and m.region_id=G.region_id AND rownum < 10
)
LOOP
DBMS_OUTPUT.PUT_LINE(length(i.current_qtr_ind)||'-'||length(i.PRIOR_QTR_IND) );
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR : '||SUBSTR(SQLERRM,1,150));
END;

gives the length of the indicators as 3.

Now if i execute the same query in a SQL Window and not in the For LOOP i.e

SELECT length(f.current_qtr_ind) current_qtr_ind
, length(f.PRIOR_QTR_IND) PRIOR_QTR_IND
, length(f.next_qtr_ind) next_qtr_ind
, B.focus_grade_type
FROM OPEN_SALES_ORDER@l_prddw A
, PRODUCT@l_prddw B
, vw_ship_to_customer@l_prddw D
, FML_DISTRICT@l_prddw E
, FISCAL_CALENDAR@l_prddw F
, MYT_COM_USERID_XREF G
,(select distinct zone_name,region_id from myt_com_organization) M
WHERE A.COMP_NO = B.COMP_NO
AND A.ITEM_NO = B.ITEM_NO
AND A.COMMERCIAL_IND = 'Y'
AND A.SALES_ORG = 'ESO'
AND B.BUSINESS_GRP IN ('RS','AZ','LN','SH') --LN added by mahesh --Changed by bhaskar for AZ data
AND B.item_grade NOT LIKE 'BPA%'
AND D.ESR_Flag = B.ESR_Flag --Added by CZ (Softtek)
AND A.CUST_NO = D.CUST_SHIPTO
AND D.FML_DISTRICT = E.FML_DISTRICT
AND A.REQ_SHIP_DATE = F.FISCAL_DATE
AND B.focus_grade_type in ('CORE','Semi-Custom','Custom','Semi-custom')
--AND g.SALES_REP_ID=e.fml_am_code --Changed by bhaskar for Telesales inclusion
AND G.SALES_REP_ID=NVL(e.fml_ts_code,e.fml_am_code)
AND TRUNC(A.REQ_SHIP_DATE)>=TRUNC(SYSDATE)
AND TRUNC(a.req_ship_date) < (SELECT MAX(fiscal_date)
FROM ddw01.FISCAL_CALENDAR@l_prddw
WHERE fiscal_qtr=3 AND fiscal_year='2008') + 26
and ((trim(A.int_ext_ind)='E' and trim(m.zone_name)='SFNS') or (trim(m.zone_name) <> 'SFNS'))
and m.region_id=G.region_id AND rownum < 10

I get the length as 1.
Any Idea why this kind of behaviour.
If you reply back saying you cannot guess since no create table,no insert into, no look i would understand.

Thanks,
Bhushan

 EXCEPTION
  WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('ERROR : '||SUBSTR(SQLERRM,1,150));
 END;

Hi,
well First of all apologies for annoying you .. the when others is not actual in the code just made up that test case..yes i agree thay should be never be so loosely used.. now coming to the main issue..are you aware of any set up at DB level or any DB parameter which might cause the value retrieved from the Cursor to be appended by blank spaces.Well why i ask this..there are 2 DB's migrated and Upgraded from 8i to 10g.The development gives expected result i.e. length of the indicator as 1 where as the Prod instance returns the length as 3 for the same piece of code run.The DB Source from where the data is fetcched is the same (verified it thrice) the data in both the instances (prod and dev) is the same. I tried to convince the DBA's but they only come back saying.. it happens during migration..my question is if it happens then it should happen for all...why does it work correct in dev and weird in Prod...? Any hints or any such example you have come across...i will try to make a sample case for sure..but your inputs before that would be highly appreciated.

Thanks,
Bhushan

Well that would be a *very strong wrong assumption* of generalizing of what is done in a 20 lines of code is done everywhere else.Well no consideration expected either.When one is trying to bring out what the issue is and is sure there is 99.99% of no exceptions but wants to cover that .01% (.01 % beacause the system is working weird already, but now totally weird) that is when you code WHEN OTHERS but ONLY while Testing!!!.It is only to catch the exception and the error message in the TEST WINDOW in PL/SQL Developer.Well it has helped me to code that way in testing and i do not see any ill effects for it so far(I still stand by my Line..none of my code has a WHEN OTHERS Exception without a valid RAISE). You were *totally* wrong in preassuming there.Yes will try to have a valid case and will post for everyone's review.

Regards,
Bhushan

For retrieving more than on record we will use cursors.While accessing the fields ,we use cursor.filedname.I want to send the fieldname dynamically while accessing.How?
For Ex:Cursor Filed:upref1,upref2,upref3,upref4
for i in 1..5
whle accessing: cursor.upref||i
end loop.
Is there any method for this

Hello Thomas,
In reference to the above *constructive* discusion we had :) Here is the scenario.I think you might be able to tell just looking at it... because as you said it is related to Datatype CHAR.

DB 1: There is a FIscal_calendar table with following columns (Only some of them given below)
fiscal_week Char(2) ,
fiscal_qtr char(1),
prior_month_ind char(1),
prior_qtr_ind char(1)

DB 2: I have a DB link which connects to this DB 1.The NLS parameters for DB 2 are are
PARAMETER VALUE
1 NLS_LANGUAGE AMERICAN
2 NLS_TERRITORY AMERICA
3 NLS_CURRENCY $
4 NLS_ISO_CURRENCY AMERICA
5 NLS_NUMERIC_CHARACTERS .,
6 NLS_CHARACTERSET UTF8
7 NLS_CALENDAR GREGORIAN
8 NLS_DATE_FORMAT DD-MON-RR
9 NLS_DATE_LANGUAGE AMERICAN
10 NLS_SORT BINARY
11 NLS_TIME_FORMAT HH.MI.SSXFF AM
12 NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
13 NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
14 NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
15 NLS_DUAL_CURRENCY $
16 NLS_COMP BINARY
17 NLS_LENGTH_SEMANTICS BYTE
18 NLS_NCHAR_CONV_EXCP FALSE
19 NLS_NCHAR_CHARACTERSET UTF8
20 NLS_RDBMS_VERSION 10.2.0.4.0
The Sample code piece is:
-- Created on 10/6/2008 by 142037
declare
-- Local variables here
i integer;
t VARCHAR2(10);
begin
-- Test statements here

FOR i IN (SELECT fiscal_week fiscal_week,fiscal_qtr,prior_month_ind,prior_qtr_ind
FROM ddw01.fiscal_calendar@ddw01_sel.world WHERE ROWNUM <10) LOOP
t := i.fiscal_week;
dbms_output.put_line ('week IS '||length(i.fiscal_week) || ','||'fiscal_qtr is ' ||(length(i.fiscal_qtr)));
dbms_output.put_line ('prior_month_ind IS '||length(i.prior_month_ind) || ','||'prior_qtr_ind is ' ||(length(i.prior_qtr_ind)));


END LOOP;
end;

The output i get is:
week IS 6,fiscal_qtr is 3
prior_month_ind IS 3,prior_qtr_ind is 3
week IS 6,fiscal_qtr is 3
prior_month_ind IS 3,prior_qtr_ind is 3
week IS 6,fiscal_qtr is 3
prior_month_ind IS 3,prior_qtr_ind is 3
week IS 6,fiscal_qtr is 3
prior_month_ind IS 3,prior_qtr_ind is 3
week IS 6,fiscal_qtr is 3
prior_month_ind IS 3,prior_qtr_ind is 3
week IS 6,fiscal_qtr is 3
prior_month_ind IS 3,prior_qtr_ind is 3
week IS 6,fiscal_qtr is 3
prior_month_ind IS 3,prior_qtr_ind is 3
week IS 6,fiscal_qtr is 3
prior_month_ind IS 3,prior_qtr_ind is 3
week IS 6,fiscal_qtr is 3
prior_month_ind IS 3,prior_qtr_ind is 3

Actual output should be 2 instead of 6 and 1 instead of 3.

I run the same code in other DB which connects to the same DB using DBlink and whose NLS_parameters are:
PARAMETER VALUE
1 NLS_CALENDAR GREGORIAN
2 NLS_CHARACTERSET US7ASCII
3 NLS_COMP BINARY
4 NLS_CURRENCY $
5 NLS_DATE_FORMAT YYYY-MM-DD
6 NLS_DATE_LANGUAGE AMERICAN
7 NLS_DUAL_CURRENCY $
8 NLS_ISO_CURRENCY AMERICA
9 NLS_LANGUAGE AMERICAN
10 NLS_LENGTH_SEMANTICS BYTE
11 NLS_NCHAR_CHARACTERSET AL16UTF16
12 NLS_NCHAR_CONV_EXCP FALSE
13 NLS_NUMERIC_CHARACTERS .,
14 NLS_RDBMS_VERSION 10.2.0.4.0
15 NLS_SAVED_NCHAR_CS US7ASCII
16 NLS_SORT BINARY
17 NLS_TERRITORY AMERICA
18 NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
19 NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM
20 NLS_TIME_FORMAT HH.MI.SSXFF AM
21 NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZH:TZM

Sample code:

-- Created on 10/6/2008 by 142037
declare
-- Local variables here
i integer;
begin
-- Test statements here

FOR i IN (SELECT fiscal_week,fiscal_qtr,prior_month_ind,prior_qtr_ind
FROM ddw01.fiscal_calendar@ddw01_sel.world WHERE ROWNUM <10) LOOP
dbms_output.put_line ('week IS '||length(i.fiscal_week) || ','||'fiscal_qtr is ' ||(length(i.fiscal_qtr)));
dbms_output.put_line ('prior_month_ind IS '||length(i.prior_month_ind) || ','||'prior_qtr_ind is ' ||(length(i.prior_qtr_ind)));

END LOOP;
end;

Output:
week IS 2,fiscal_qtr is 1
prior_month_ind IS 1,prior_qtr_ind is 1
week IS 2,fiscal_qtr is 1
prior_month_ind IS 1,prior_qtr_ind is 1
week IS 2,fiscal_qtr is 1
prior_month_ind IS 1,prior_qtr_ind is 1
week IS 2,fiscal_qtr is 1
prior_month_ind IS 1,prior_qtr_ind is 1
week IS 2,fiscal_qtr is 1
prior_month_ind IS 1,prior_qtr_ind is 1
week IS 2,fiscal_qtr is 1
prior_month_ind IS 1,prior_qtr_ind is 1
week IS 2,fiscal_qtr is 1
prior_month_ind IS 1,prior_qtr_ind is 1
week IS 2,fiscal_qtr is 1
prior_month_ind IS 1,prior_qtr_ind is 1
week IS 2,fiscal_qtr is 1
prior_month_ind IS 1,prior_qtr_ind is 1


Having a look at the above behaviour i believe it has to do something with the NLS_parameters.
Could you please comment?

Thanks & Regards,
Bhushan Salgar

Could you please tell me looking at which parameter you inferred that one of the database if Multibyte and the other is not...also in order to give me the desired answer i.e te length as 2 and 1 and NOT 6 and 3 which NLS parameter will have to be altered in the DB mentioned above?
Thanks,
Bhushan

Very good Explanation. --thanks Tom

Hi Thomas,
Sorry to pester you with this..but in reference to the above discussion..No the Column_length is not char(6)..if it would have been char(6) i would have got that..rather then troubling you :)

SELECT column_name,Data_type,Data_length,character_set_name,char_col_decl_length,avg_col_len FROM all_tab_columns
WHERE owner='DDW01' AND table_name = 'FISCAL_CALENDAR'
AND column_name IN (
'FISCAL_WEEK' ,
'FISCAL_QTR' ,
'PRIOR_MONTH_IND' ,
'PRIOR_QTR_IND'
)

COLUMN_NAME DATA_TYPE DATA_LENGTH CHARACTER_SET_NAME CHAR_COL_DECL_LENGTH AVG_COL_LEN
1 FISCAL_WEEK CHAR 2 CHAR_CS 2 2
2 FISCAL_QTR CHAR 1 CHAR_CS 1 1
3 PRIOR_MONTH_IND CHAR 1 CHAR_CS 1 1
4 PRIOR_QTR_IND CHAR 1 CHAR_CS 1 1

The behaviour is, the data_length defined is multiplied by three when the data_type is CHAR and is fetched from this multibyte DB. (This behaviour is only when data is fetched over DB Links)

Also,
Does NVL do any conversion of Datatypes because when the above code in the for loop is cahnged to include the nvl function around the columns the length that i get is what is actually defined.

declare
-- Local variables here
i integer;
t VARCHAR2(10);
begin
-- Test statements here

FOR i IN (SELECT nvl(fiscal_week,'X') fiscal_week,nvl(fiscal_qtr,'X') fiscal_qtr,nvl(prior_month_ind,'X') prior_month_ind,nvl(prior_qtr_ind,'X') prior_qtr_ind
FROM ddw01.fiscal_calendar@ddw01_sel.world WHERE ROWNUM <10) LOOP
t := i.fiscal_week;
dbms_output.put_line ('week IS '||length(i.fiscal_week) || ','||'fiscal_qtr is '
||(length(i.fiscal_qtr)));
dbms_output.put_line ('prior_month_ind IS '||length(i.prior_month_ind) ||
','||'prior_qtr_ind is ' ||(length(i.prior_qtr_ind)));


END LOOP;
end;

Output
week IS 2,fiscal_qtr is 1
prior_month_ind IS 1,prior_qtr_ind is 1
week IS 2,fiscal_qtr is 1
prior_month_ind IS 1,prior_qtr_ind is 1
week IS 2,fiscal_qtr is 1
prior_month_ind IS 1,prior_qtr_ind is 1
week IS 2,fiscal_qtr is 1
prior_month_ind IS 1,prior_qtr_ind is 1
week IS 2,fiscal_qtr is 1
prior_month_ind IS 1,prior_qtr_ind is 1
week IS 2,fiscal_qtr is 1
prior_month_ind IS 1,prior_qtr_ind is 1
week IS 2,fiscal_qtr is 1
prior_month_ind IS 1,prior_qtr_ind is 1
week IS 2,fiscal_qtr is 1
prior_month_ind IS 1,prior_qtr_ind is 1
week IS 2,fiscal_qtr is 1
prior_month_ind IS 1,prior_qtr_ind is 1

and the output without the NVL function is 6 and 2 as length.

Thanks & Regards,
Bhushan Salgar

Hi Thomas,
Yes that seems to be logical for returing the unexpected length of CHAR data.INterestingly happens only with CHAR.So if i recollect it right somewhere in your BLOG you have encouraged not to use CHAR and use VARCHAR instead.Well then i think we can add one more reason to it, for not using in future.

Thanks,
Bhushan Salgar

Hello Tom,

Oracle version : 10.2.0.3
Our product vendor proposed some init parameter tuning recommendations. One of them is setting CURSOR_SHARING to
'FORCE' at database level.

I want to be more careful before implementing this change.
I have read that cursor_sharing to force has some side effect with respect to query output size (for example, with substr() function, and can produce ineffecient query execution plan).

Are these side effects still true for 10.2.0.3. ?

Are you aware of any known DB issues that may arise if we set this parameter to FORCE in 10.2.0.3 ?

If setting to 'force' is not recommended, any proof i can show to the vendor about ramification of setting to 'FORCE'.

Thanks for your time/insights and help.

Thanks tom for the very useful info on "sql injection" security issue that arise from application built using cursor_sharing=force. we will take this to the vendor attention.

Can you also comment on other side effects of using cursor_sharing=force if any with respect to query output data size /performance perspective in 10.2.0.3 release.


Thanks

Thanks Tom for the great insights.

<< cursor sharing force will change default column lengths as perceived by applications.

This is exactly i was looking for. It looks this side effect exists irrespective of oracle releases (even can occur in latest releases).

Tom,

we're still required to use the cursor_sharing=FORCE setting, due to incomplete binding in our application -bad design, I understand, but we can't change that right now.

Our db-layer generates for cursors with dynamic SQL Pro*C/C++-code like this:

EXEC SQL EXECUTE
BEGIN
OPEN :myDynamicSQL FOR :db_statement;
END;
END-EXEC;

So the cursors are opened in a PL/SQL block, the statement itself being fully dynamic (no binding at all) and not "visible" to the PL/SQL engine.

Regarding some of your comments above, I'd expect all those statements to be subject to cursor_sharing.

What really puzzles me know is this: on 10.2.03 and 9.2.0.x production systems I discovered, that for some of the corresponding statements literals are replaced, for others they're not and some of those are flooding the shared pool.

So in V$SQL I see

a) several PL/SQL blocks

begin OPEN :Cursor1 FOR :db_statement ; END ;
begin OPEN :Cursor2 FOR :db_statement ; END ;
...

b) the SQL for the cursors, some of them with literals replaced by :"SYS...", and some of them still containing all the literals (same session)

For me that sounds like a bug or is there any reason behind it? It can't possibly depend on the sql text, whether literal replacement takes place or not, can't it?

Thanks in advance,
Thomas

ops$tkyte%ORA11GR1> create or replace procedure open_sql( p_sql in varchar2, p_num in number )
  2  as
  3          l_cursor sys_refcursor;
  4  begin
  5          if ( p_num is null )
  6          then
  7                  open l_cursor for p_sql;
  8          else
  9                  open l_cursor for p_sql using p_num;
 10          end if;
 11  end;
 12  /

Procedure created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> alter system flush shared_pool;

System altered.

ops$tkyte%ORA11GR1> alter session set cursor_sharing=force;

Session altered.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> exec open_sql( 'select 1, 2, 3 from dual look_for_me', null );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> exec open_sql( 'select 4, 2, 3 from dual look_for_me', null );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> exec open_sql( 'select 5, 2, 3 from dual look_for_me', null );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> exec open_sql( 'select 1, 2, 3 from dual look_for_me where :x = 1', 1 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> exec open_sql( 'select 4, 2, 3 from dual look_for_me where :x = 1', 1 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> exec open_sql( 'select 5, 2, 3 from dual look_for_me where :x = 1', 1 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select sql_text from v$sql where sql_text like 'select%look_for_me%';

SQL_TEXT
-------------------------------------------------------------------------------
select 5, 2, 3 from dual look_for_me where :x = 1
select 4, 2, 3 from dual look_for_me where :x = 1
select :"SYS_B_0", :"SYS_B_1", :"SYS_B_2" from dual look_for_me
select 1, 2, 3 from dual look_for_me where :x = 1

in our application we only bind static SQL. Dynamic SQL is really simply OPEN <cursor> FOR <string_with_literals> [NO USING HERE].

In metalink I discovered several bugs with cursor_sharing, one of them occuring if DECODE/NVL is involved (Bug 3818541). That would suit our problem, but it's been fixed in previous releases.

So, if you can't think of another reason, why some statements don't get literal-replaced, I will file a bug and go for another workaround (in addition to the workaround named cursor_sharing).

Thanks,
Thomas

you're right, I misunderstood the effect of bug 3818541.

Providing a reproducible testcase is difficult until I understand what really makes the difference between those statements that get literal-replaced and those that don't
(complex statements, variing over time, well dynamic). The problem is reproducible in the sense, that it's always the same type of statements that get literal-replaced and those that don't, regardless of when and where (which production system).

Here are some excerpts of what I consider to be relevant:

--------------------
Example 1: "pure dynamic", literals are replaced (always, on all production system)

* generated code, before Pro*C

EXEC SQL BEGIN DECLARE SECTION;
char db_statement[20000];
EXEC SQL END DECLARE SECTION;
...
string myStr = "\
:db_dynStatement; \

... C-code that replaces the tag ":db_dynStatement" by the dynamic part of the statement (in this case the complete statement)

(void) strcpy (db_statement, myStr.c_str());

EXEC SQL EXECUTE
BEGIN
OPEN :LvsElmSqlStraCore_Cursor FOR :db_statement;
END;
END-EXEC;

* db_dynStatement being in this example (taken from v$sql; note: literal-replaced, though way overbound)

SELECT Pl.PlNam, Pl.Tsn, :"SYS_B_00", -:"SYS_B_01"
FROM Pl_T Pl, LttPlKla_T Lpk
WHERE :"SYS_B_02"=:"SYS_B_03"
AND Lpk.LttNam = :"SYS_B_04"
AND Pl.PlKlaNam = Lpk.PlKlaNam
AND Pl.ArtId IS NULL
AND Pl.ElbNam = :"SYS_B_05"
AND Pl.PlKapFrei > :"SYS_B_06"
AND Pl.PlKnzSprEinl = :"SYS_B_07"
AND EXISTS ( SELECT :"SYS_B_08" FROM Tb_T Tb WHERE Tb.TbNam = Pl.TbNam AND Tb.BeNam = Pl.BeNam AND Tb.TbTypSprEinlMan = :"SYS_B_09" AND Tb.TbSta = :"SYS_B_10")
ORDER BY :"SYS_B_11", Lpk.LttPlKlaSortNr, Pl.PlFachSortD

--------------------
Example 2: dynamic, literals are not replaced (never)

* generated code, before Pro*C

EXEC SQL BEGIN DECLARE SECTION;
...
char db_statement[20000];
EXEC SQL END DECLARE SECTION;

string myStr = "\
SELECT NVL(SUM(DECODE(aup.AupFsrSta,100, \
GREATEST(aup.AupMgSoll,aup.AupMgIst+aup.AupMgBra+aup.AupMgFehl)- GREATEST(aup.AupMgFehl,aup.AupMgWws), \
GREATEST( 0,aup.AupMgIst+aup.AupMgBra+aup.AupMgFehl - GREATEST(aup.AupMgFehl,aup.AupMgWws)) ) ),0) \
INTO :db_rFelder :db_rIndFelder \
FROM Aup_T aup \
WHERE aup.AupFsrSta >= :db_pFelder_FsrStaVon \
AND aup.AupFsrSta < :db_pFelder_FsrStaBis \
:db_dynStatement; \
";

... C-code that replaces those ":db_pFelder" pseudo-bind variables by current values
... C-code that replaces ":db_dynStatement" by the dynamic part of the statement

EXEC SQL EXECUTE
BEGIN
OPEN :LvsAumSqlMgAup_Cursor FOR :db_statement;
END;
END-EXEC;

(void) strcpy (db_statement, myStr.c_str());

* V$SQL.sql_text

SELECT NVL(SUM(DECODE(aup.AupFsrSta,100,
GREATEST(aup.AupMgSoll,aup.AupMgIst+aup.AupMgBra+aup.AupMgFehl)- GREATEST(aup.AupMgFehl,aup.AupMgWws),
GREATEST( 0,aup.AupMgIst+aup.AupMgBra+aup.AupMgFehl - GREATEST(aup.AupMgFehl,aup.AupMgWws)) ) ),0)
INTO :db_rFelder :db_rIndFelder
FROM Aup_T aup
WHERE aup.AupFsrSta >= 100
AND aup.AupFsrSta < 650
AND aup.ArtId = '00100000000000000069036732'
AND aup.LgoNam= 'WMS'
AND aup.SprGrundNam = '010'
AND aup.BstChgNam3 IS NULL
AND aup.BstQlf = 10
AND aup.WerkLief = '5899'
AND aup.BstSobArt IS NULL
AND aup.BstSobRef IS NULL

--------------------
Example 3: not really dynamic, anyway literals are not replaced (never)

* generated code, before Pro*C

EXEC SQL BEGIN DECLARE SECTION;
char db_statement[20000];
EXEC SQL END DECLARE SECTION;

string myStr = "\
SELECT DECODE( COUNT( DISTINCT Art.ArtId ), 1, MIN( Bst.ArtId), '*' ) ArtId, \
DECODE( COUNT( DISTINCT Art.MndNr ), 1, MIN( Art.MndNr ), '*' ) MndNr, \
DECODE( COUNT( DISTINCT Art.Werk ), 1, MIN( Art.Werk ), '*' ) Werk, \
DECODE( COUNT( DISTINCT Art.ArtId ), 1, SUM(Bst.BstMg), -1 ) BstMg, \
NVL( MIN( Art.ArtKlaAbc ), 0) ArtKlaAbcMin, \
NVL( MIN( Art.ArtKnzZulag ), 0 ) ArtKnzZulag, \
DECODE( COUNT( DISTINCT Art.ArtElmSel ), 1, MIN( Art.ArtElmSel), '*' ) ArtElmSel, \
DECODE( COUNT( DISTINCT ArtGrp.ArtGrpElmSel ), 1, MIN( ArtGrp.ArtGrpElmSel), '*' ) ArtGrpElmSel, \
DECODE( COUNT( DISTINCT Art.ArtElmSel2 ), 1, MIN( Art.ArtElmSel2), '*' ) ArtElmSel2, \
DECODE( COUNT( DISTINCT Bst.BstQlf ), 1, MIN( Bst.BstQlf), 0 ) BstQlf, \
DECODE( COUNT( DISTINCT Art.ArtId ), 1, 'N', 'J' ) MischTe, \
LEAST( DECODE( COUNT( DISTINCT Bst.TeNam ), 1, NVL( SUM( (SELECT Bst.BstMg / NVL(GREATEST(ArtLtt.ArtLttMgMax,1), 1000) * 100 FROM ArtLtt_T ArtLtt WHERE ArtLtt.ArtId= Bst.ArtId AND ArtLtt.LttNam = Ltt.LttNam ) ), -1), -1 ), 999) FlGrd, \
DECODE( COUNT( DISTINCT Art.ArtId ), 1, MIN( Bst.BstId), -1 ) BstId, \
NVL( MIN(Te.AufId), '*' ) AufId, \
NVL( MIN(Pl.PlElmSel), '*' ) PlElmSel, \
NVL( MIN(Ltt.LttElmSel), '*' ) LttElmSel, \
NVL( MIN( (SELECT ArtLtt.ArtLttElmSel FROM ARTLTT_T ArtLtt WHERE ArtLtt.ArtId= Bst.ArtId AND ArtLtt.LttNam = Ltt.LttNam ) ), '*' ) ArtLttElmSel, \
DECODE( COUNT( DISTINCT Art.EtbNam ), 1, MIN(Art.EtbNam), '*' ) EtbNam, \
DECODE( COUNT( DISTINCT Art.ArtId ), 1, MIN( Art.ArtTypKom), -1 ) ArtTypKom, \
MAX( ArtKlaGfg ) ArtKlaGfg, \
DECODE( COUNT( DISTINCT Bst.LgoNam ), 1, MIN( Bst.LgoNam), '*' ) LgoNam \
INTO :db_rFelder :db_rIndFelder \
FROM ArtGrp_T ArtGrp, \
Art_t Art, \
Bst_T Bst, \
Pl_T Pl, \
Ltt_T Ltt, \
TE_T Te \
WHERE Te.TeNam = :db_pFelder_TeNam \
AND Bst.BstQlf != :db_pFelder_BstQlf \
AND Ltt.LttNam (+) = Te.LttNam \
AND Pl.PlNam (+) = Te.PlNam \
AND Bst.TeNam IN \
( \
SELECT TeNam \
FROM Te_T \
START WITH TeNam = :db_pFelder_TeNam \
CONNECT BY PRIOR TeNam = TeNamUbo \
) \
AND Art.ArtId = Bst.ArtId \
AND ArtGrp.ArtGrpNam(+) = Art.ArtGrpNam \
GROUP BY Te.TeNam; \
";

... C-code that replaces those ":db_pFelder" pseudo-bind variables by current values
... C-code that replaces ":db_dynStatement" by the dynamic part of the statement (if any, in this case there is none)

(void) strcpy (db_statement, myStr.c_str());

EXEC SQL EXECUTE
BEGIN
OPEN :LvsElmSqlTeDat_Cursor FOR :db_statement;
END;
END-EXEC;

* V$SQL.sql_text (literals still in place)

"SELECT DECODE( COUNT( DISTINCT Art.ArtId ), 1, MIN...

I understand of course that Example 3 would not need to be a dynamic SQL since there is no dynamic part at all. The developer explained to me, that Pro*C can't handle subqueries in the SELECT list (as of version 9.x at least), so he made it dynamic to avoid Pro*C-checks. We solved this one by rewriting the query and making it static and well-bound again. Nevertheless it caused a lot of headache and there are tougher examples, for which we currently don't have a workaround.

I hope you can get the basic idea. The whole approach seems quite absurd ("unbind" in C-code, make it auto-bind by Oracle), but currently we have to deal with it.

Thanks a lot for your endurance.

ops$tkyte%ORA10GR2> variable x refcursor
ops$tkyte%ORA10GR2> variable y refcursor
ops$tkyte%ORA10GR2> alter session set cursor_sharing=force;

Session altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter system flush shared_pool;

System altered.

ops$tkyte%ORA10GR2> begin
  2          open :x for 'select dummy into :x from dual look_for_me_1 where 1=1';
  3          open :y for 'select dummy from dual look_for_me_2 where 1=1';
  4  end;
  5  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> print x

D
-
X

ops$tkyte%ORA10GR2> print y

D
-
X

ops$tkyte%ORA10GR2> select sql_text from v$sql where sql_text like 'select%look_for_me%';

SQL_TEXT
-------------------------------------------------------------------------------
select dummy from dual look_for_me_2 where :"SYS_B_0"=:"SYS_B_1"
select dummy into :x from dual look_for_me_1 where 1=1

Who in the world could possibly think of the innocent looking, though superfluous, into-clause? The first test confirmed your statement, that should fix our most urgent problems. I think a complete redesign will be next on our agenda.

Thank you very much!
Thomas

Hi Tom,

SQL> create table dummy (val varchar2(1));

Table creee.

SQL> sho parameter cursor_sharing

NAME                                 TYPE                             VALUE
------------------- -------------- -----------------
cursor_sharing      string         FORCE
SQL> variable mybind varchar2(1);
SQL> exec :mybind := 'Y';

Procedure PL/SQL terminee avec succes.

SQL> select /* watch_for_me */ * from dummy where val = :mybind;

aucune ligne selectionnee

SQL> exec :mybind := 'N';

Procedure PL/SQL terminee avec succes.

SQL> select /* watch_for_me */ * from dummy where val = :mybind;

aucune ligne selectionnee

SQL> select q.sql_text, q.sql_id, b.child_number, b.position, b.name, b.value_string
  2  from v$sql q
  3  left join v$sql_bind_capture b
  4  on (q.sql_id=b.sql_id)
  5  where q.sql_text like '%watch_for_me%'
  6  order by q.sql_id, b.child_number, b.position;

SQL_TEXT
-----------------------------------------------------------
SQL_ID        CHILD_NUMBER   POSITION NAME
------------- ------------ ---------- ------------------------------
VALUE_STRING
-----------------------------------------------------------
select /* watch_for_me */ * from dummy where val = :mybind
aukzmrxa9zaca            0          1 :MYBIND
Y

This is a 10gR2 database with CURSOR_SHARING=FORCE.
(was set before I got in, no-one knows why...)

My questions are :
- Why do I only see the first value assigned to :mybind ?
- Is this expected behavior ?
- How can I get every values used ?

The underlying purpose of my question is be able to write a query/proc to check whether the "legacy" value of cursor_sharing is relevant to us.

My idea is to be able to count the average number of "system bound variables values set" used for a single SQL.
(In my example, I would like to be able to show that statement aukzmrxa9zaca was used with 2 different values "sets" (one set consisting of mybind='Y', one other consisting of mybind='N').

ops$tkyte%ORA10GR2> alter session set cursor_sharing=force;

Session altered.

ops$tkyte%ORA10GR2> select * from dual where 1=2;

no rows selected

ops$tkyte%ORA10GR2> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dw9c32t0u4c9r, child number 0
-------------------------------------
select * from dual where :"SYS_B_0"=:"SYS_B_1"

Plan hash value: 3752461848

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     2 (100)|          |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(:SYS_B_0=:SYS_B_1)


19 rows selected.

Just realized how my example above could be confusing...

I showed cursor_sharing was set to FORCE, but I used bind variables in my example...
Anyway, the example remain true with literals, and thus my questions :

SQL> create table dummy (val varchar2(1));

Table creee.

SQL> sho parameter cursor_sharing

NAME                TYPE           VALUE
------------------- -------------- -----------------
cursor_sharing      string         FORCE
SQL> select /* watch_for_me */ * from dummy where val = 'N';

aucune ligne selectionnee

SQL> select /* watch_for_me */ * from dummy where val = 'Y';

aucune ligne selectionnee

SQL> select q.sql_text, q.sql_id, b.child_number, b.position, b.name, b.value_string
  2  from v$sql q
  3  left join v$sql_bind_capture b
  4  on (q.sql_id=b.sql_id)
  5  where q.sql_text like '%watch_for_me%'
  6  order by q.sql_id, b.child_number, b.position;

SQL_TEXT
-------------------------------------------------------------
SQL_ID        CHILD_NUMBER   POSITION NAME
------------- ------------ ---------- -----------------------
VALUE_STRING
-------------------------------------------------------------
select /* watch_for_me */ * from dummy where val = :"SYS_B_0"
gc73nzubsgann            0          1 :SYS_B_0
N

- Why do I only see the first value assigned to :"SYS_B_0" ?
- Is this expected behavior ?
- How can I get every values used ?

Hi Tom,

You answered :
Unless you see a ton of SQL in your shared pool with :"SYS_B_0" in it, you are not using cursor sharing = force.

I don't see a ton.... I see all of them ! (in fact I know and checked that cursor_sharing=FORCE was set instance wide)

not sure what you are trying to do there
In fact I'm trying to measure how beneficial is this setting (cursor_sharing=FORCE) without changing it.

I've found some post of yours explaining how to measure if your application could benefit from setting it to non-default value, but what I'm trying to do is the opposite : cursor_sharing IS set to force, and I'd like to measure if I would lose much by setting it back to default value.

No-one here knows why it was set to this non-default value, and the instance is some kind of DWH, hence I'm wondering if this was relevant.

Hi Tom,

As a big fan of you and your work, I know and understand all you just explained.

My concern was to be able to measure how beneficial it would be to set cursor_sharing back to its default value of EXACT.

I know you have some code that can show if enabling it (setting it to SIMILAR or FORCE) would be beneficial "parse wise".

I was looking for some code to show if it would be beneficial (or at least not armful) to set it back to default (parse wise).

But as far as I understand, I cannot really get that kind of information out of the database.

Can you confirm ?

Hi Tom, 

  I've read a lot for cursor_sharing from your articles. But I've tried something very simple and my Oracle 11g doesn't automatically rewrite queries (bind them).
 For example I executed the next code :


alter session set cursor_sharing=force;


declare
      type rc is ref cursor;
      l_rc rc;
      l_dummy all_objects.object_name%type;
      l_start number default dbms_utility.get_time;
  begin
      for i in 1 .. 200
      loop
         execute immediate
          'select object_name 
             from t1 
            where object_id = '|| i ;
          
          
          -- dbms_output.put_line(l_dummy);
      end loop;
      dbms_output.put_line
       (round((dbms_utility.get_time-l_start)/100, 2) ||
        ' Seconds...' );
  end;
  

  Here as you see i have generated more sql statemenst dynamically and when I looked at the v$sqlarea they weren't parsed only onced but 200 times. But when i execute selects manually from sqlplus:

sql> select * from all_objects where object_id=1;
1 row selected;
sql> select * from all_objects where object_id=4;
1 row selected;
sql> select * from all_objects where object_id=2;
1 row selected;

the result from v$sql is 
  'select * from all_objects where object_id = :"SYS_B_0" '!!

My question is why with EXECUTE IMMEDIATE select statements are not bind automatically ?

Kind regards,
Stefan 

Hi to all,

First thank you for your fast respond. I've been thinking that setting "cursor_sharing" to "FORCE" will match the same SQLs and will not do a parse call. But I've realized that is not right.

Thank you for your help.

Regards,
Stefan

Hi Tom,

I have your book 'Effective Oracle by Design'..
I am currently reading this book. In chapter 5 (Chapter 5 - Statement Processing), you mentioned like,

your book content starts here
--------------

"We simply set up the four users A, B, C, and D. We grant CREATE SESSION and CREATE TABLE privileges to users A, B, and D, and grant just CREATE SESSION to C. Then we run the following script:

create public synonym emp for d.emp;
alter system flush shared_pool;

connect a/a  
create table emp ( x int );
select * from emp;

connect b/b  
create table emp ( x int );
select * from emp;

connect d/d  c
reate table emp ( x int );
grant select on emp to c;
select * from emp;

connect c/c  
select * from emp;Now, let’s take a quick peek at V$SQL, which shows all of the currently cached SQL statements.

ops$tkyte@ORA920> select address, executions, sql_text
   2    from v$sql
   3   where upper(sql_text) like 'SELECT * FROM EMP%';

ADDRESS  EXECUTIONS SQL_TEXT  
-------- ---------- ------------------------------  
58DBD9CC          1 select * from emp  
58DBD9CC          1 select * from emp  
58DBD9CC          2 select * from empWe can see that there are three versions of the query in the shared pool and that one of the versions was used two times—that is the query submitted by users C and D. 

We can also query V$SQL_SHARED_CURSOR to determine exactly why Oracle developed three query plans, to discover the root cause for the mismatch here. This view has many columns that can be used to determine why a plan in the shared pool was not shared. For example, the ROW_LEVEL_SEC_MISMATCH column would be flagged for two queries that appear to be the same but differed at runtime based on the security predicate returned by Oracle’s Virtual Private Database (VPD) feature. The OUTLINE_MISMATCH column would be flagged if one query used a query outline and the other did not. Other columns in this view provide similar mismatch information. In this example, we can see this information regarding our queries:

ops$tkyte@ORA920> select kglhdpar, address,
  2         auth_check_mismatch, translation_mismatch  
  3    from v$sql_shared_cursor  
  4   where kglhdpar in  
  5   ( select address   
  6       from v$sql  
  7      where upper(sql_text) like 'SELECT * FROM EMP%' )
  8  /

KGLHDPAR ADDRESS  AUTH_CHECK_MISMATCH TRANSLATION_MISMATCH  
-------- -------- ------------------- --------------------  
58DBD9CC 59D4C9C8 N                   N  
58DBD9CC 59E54EB0 Y                   Y  
58DBD9CC 5898D42C Y                   YIn this case, Oracle flagged two columns: AUTH_CHECK_MISMATCH, meaning the authorization/translation check failed for the existing cursor (query plan), and TRANSLATION_ MISMATCH, meaning the base objects of the child cursor (query plan) do not match. This shows us exactly why there are three queries plans (child cursors): The base objects referenced were different.

"

your book content ends here
---------------------------
I dint understand the following statement from there

"AUTH_CHECK_MISMATCH, meaning the authorization/translation check failed for the existing cursor (query plan), and TRANSLATION_ MISMATCH, meaning the base objects of the child cursor (query plan) do not match. This shows us exactly why there are three queries plans (child cursors): The base objects referenced were different". 

Can you please explain what is AUTH_CHECK_MISMATCH and TRANSLATION_ MISMATCH columns meant for..?

As i am new to this, i understand very little about these views(v$SQL, v$V$SQL_SHARED_CURSOR).

Could you please guide me to a link where i can find much examples regarding how to use v$SQL view (it would be great if it is from your site).


Regards,
Pointers.

Hi tom,
I read some document regarding Asynchronous communation in operating system will reduce the huge I/O waits(dbwr waits buffers to physical location,need to conform from OS). For this do we need to set any parameters in pfile?

and what happen if i set parllel_max_servers=160 for 3TB database with 8gb ram...?? does it gives any performance degradation?


Thanks
krrish

Dear Tom,

Back to december 2008 in Paris when I asked you a question about bind variable in stored plsql procedure and where you told me that I don't have to worry about bind variables in PLSQL but that I have to worry about how is being called my stored procedure


And here I am; we have an application which is connecting to our database and executing one of our stored procedure. Let's name this procedure as My_package.My_proc(p1,p2,p3,..);

when I issue the following select

select sql_text, executions
from v$sql
where sql_text like '%My_Package.My_Proc%'
and executions = 1
;

it returns 529 records(in TEST environment) each ressembling to the following (with different input parameter values)

BEGIN My_Package.My_proc(p1 => 3829
,p2 => TO_DATE('19/01/2011','dd/mm/yyyy')
,p3 => t_array(111,112,113,114,115,116)...; END;

meaning that the call to My_Package.My_proc is not re-used and is filling up the library cache of the shared pool

As far as I know that plsql is not subjet to cursor_sharing=force, how can I solve this problem?

(a) replace
BEGIN
My_Package.My_proc(p1 => 3829
,p2 => TO_DATE('19/01/2011','dd/mm/yyyy')
,p3 => t_array(111,112,113,114,115,116)...;
END;
by setting cursor_sharing=force and
Call My_Package.My_proc(p1 => 3829
,p2 => TO_DATE('19/01/2011','dd/mm/yyyy')
,p3 => t_array(111,112,113,114,115,116)...;


(b) how can the calling application change their code so that the call to My_Package.My_proc will be re-executed many times


Thanks in advance

They are using snap langage. They are constructing several sql statement strings (cmdStr) that ressemble to

BEGIN My_Package.My_proc(p1 => 3829
,p2 => TO_DATE('19/01/2011','dd/mm/yyyy')
,p3 => t_array(111,112,113,114,115,116)
...);
END;

and then executiong them within a forall loop using such a kind of call

if (this.dbConnection.Exec(obj.cmdStr) == DBMAP::statusErrorCN)


Could you please suggest a solution?

Do we(oracle developers) have to ask them(snap developers) to change something so that their calls to our package should be re-used (executions in v$sql > 1) ?

Thanks in advance

Thanks for your answer,

In between, I have asked them to do the following in TEST

(a) when they connect to our Oracle data base issue

alter session set cursor_sharing=FORCE;

(b) change their statement from

BEGIN 
      My_Package.My_proc(p1 => 3829 
                        ,p2 => sysdate
                        ,p3 => 145);
     END;

To

CALL My_Package.My_proc(3829 
                            ,sysdate
                            ,145);

and the situation is as shown below:

before the change:

select sql_text, executions
from v$sql
where executions
where sql_text like '%My_Package.My_proc%';

sql_text executions

BEGIN My_Package.My_proc(p1 => 3829..) END; 1
BEGIN My_Package.My_proc(p1 => 7891..) END; 1
BEGIN My_Package.My_proc(p1 => 1456..) END; 1
BEGIN My_Package.My_proc(p1 => 9222..) END; 1
BEGIN My_Package.My_proc(p1 => 3829..) END; 1
BEGIN My_Package.My_proc(p1 => 1456..) END; 1
BEGIN My_Package.My_proc(p1 => 1522..) END; 1
BEGIN My_Package.My_proc(p1 => 3829..) END; 1
BEGIN My_Package.My_proc(p1 => 3829..) END; 2
BEGIN My_Package.My_proc(p1 => 3829..) END; 1
BEGIN My_Package.My_proc(p1 => 3829..) END; 1
etc...



after the change:

select sql_text, executions
from v$sql
where executions
where sql_text like '%My_Package.My_proc%';

sql_text executions

CALL My_Package.My_proc(SYS_B_00,...) ; 18671
CALL My_Package.My_proc(SYS_B_00,...) ; 8380

I asked them what else then calling our package they are doing when they connect to our database and they answered that they are only calling several stored procedures belonging to My_Package.

So It seems to me that it is advisable to implement this change in PRODUCTION. Isn't it?

What do you think?

Thanks in advance for your answer

Hi Tom,

My sql queries are using very much bind variables but still
the execution time for "select" is very slow.

My cursor_sharing parameter is EXACT.

Do i need to set it to "FORCE" in lieu to increase the performance?

Regards
Vishu

Hi Tom,

Wow there is a lot more to bind variables then I thought.

I know that cursor_sharing FORCE is designed to avoid the hard parse but still incurs a soft parse.

So what (parsing?) happens when a bind variable is hard coded? I thought a soft parse still occured.

Say we have
SELECT column_a from my_tab where column_b = 1;
and
SELECT column_a from my_tab where column_b = 2;

We know (aside from setting a parameter etc)
Oracle is likely to hard parse both queries

Now say the data is skewed so column_b = 1 returns
1 row and column_b = 2 returns 500,000

If we use a bind variable and Oracle does not reassess
the selectivity of the predicate then do we risk getting
an inefficient execution plan due to Oracle not fully parsing the statement?

My theory on this tends to be supported the tale of
Mrs Early Bird site which suffered per performance on rainy days which I believed you helped with.



Regards
Doug

Hi Tom
What I mean by hard coding a bind variable is
select * from emp where empno = :empno
as opposed to letting CURSOR_SHARING
rewriting the SQL to
select * from emp where empno = :”SYS_B_0

I recall reading that you mentioned using CURSOR_SHARING
might eliminate hard parsing however you could have a significant amount of soft parsing.

So does Oracle treat this statement
select * from emp where empno = :empno
where I explicitly used a bind variable any differently to
a statement it generated on my behalf ie
select * from emp where empno = :”SYS_B_0

If so, how does it treat it differently?

Regards
Doug

Ok I promise never to use that mismash of terminology again :)

If you use "select * from emp where empno = :x", you - YOU THE PROGRAMMER - can write code so that you parse that statement ONCE and execute it a billion times - skipping a billion minus one parses.

If you 'rely' on the very bad setting of cursor sharing=force/similar - YOU - YOU will make us parse a billion times.

....sorry but I still don't understand
why is Oracle treating my code
select * from emp where empno = :empno
differently to lazy programmer whose code
got written as
select * from emp where empno = :”SYS_B_0
by use of cursor_sharing

{
if (myPreparedStmt == null) 
  myPreparedStmt = prepareStatement( 'select * from emp where empno = :x' );

myPreparedStmt.bind( ':x', the_empno_passed_to_me );
myPreparedStmt.execute()
while myPreparedStmt.next()
  do something.....
}

{
myPreparedStmt = 
  prepareStatement( 'select * from emp where empno = '+the_empno_passed_to_me );

myPreparedStmt.execute()
while myPreparedStmt.next()
  do something.....

myPreparedStmt.close()
}

May be reading this link may help you understand what Tom is talking about.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:801662252143

Regards

Raj

Okay... nnow I have it Thanks Tom appreciate your patience

Hi Tom,

Can you elaborate more about this:

I would code it like this:

...
CallableStatement stmt = null;

String getWordb(int ID, int IDLang, Connection conn) throws SQLException
{
if ( stmt == null ) {
stmt = conn.prepareCall("{ call get.wordb (?,?,?)}");
stmt.registerOutParameter (3, java.sql.Types.VARCHAR);
}
stmt.setInt(1,ID);
stmt.setInt(2,IDLang);
stmt.execute();
return stmt.getString (3);
}

I am ensuring the use of bind variables by using the placeholders in the statement.

Does the "stmt.close();" instruction avoid the use of bind variable?

Team,

Was reading through this link
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/improving-rwp-cursor-sharing.html#GUID-250B8578-4407-4A10-B609-67C2276EBBBB

<quote>
If a statement uses an ORDER BY clause, then the database does not perform literal replacement
in the clause because it is not semantically correct
to consider the constant column number as a literal.
</quote>

However the below test case ( from 19.9 ) show's that auto bind happen for literals in the "order by" clause, is that not something correct ?

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0

demo@QES1> show parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
demo@QES1> set serveroutput off
demo@QES1> alter session set cursor_sharing = force;

Session altered.

demo@QES1> select * from emp where empno = 7499 order by 2 desc;

     EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-1981       1600        300         30

demo@QES1> select * from table( dbms_xplan.display_cursor );

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID  cx6g4gyp8071q, child number 0
-------------------------------------
select * from emp where empno = :"SYS_B_0" order by :"SYS_B_1" desc

Plan hash value: 4024650034

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |     1 |       |     0   (0)|          |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=:SYS_B_0)


19 rows selected.

demo@QES1>

SQL> select child_number, sql_text from v$sql
  2  where sql_text like 'select * from scott.emp where empno >%';

CHILD_NUMBER SQL_TEXT
------------ ----------------------------------------------------------------
           0 select * from scott.emp where empno > :"SYS_B_0" order by :"SYS_
             B_1"

           1 select * from scott.emp where empno > :"SYS_B_0" order by :"SYS_
             B_1"

2 rows selected.

SQL> select * from v$sql_shared_cursor
  2  where sql_id = '19m73brqx4wpw'
  3  @pr
==============================
SQL_ID                        : 19m73brqx4wpw
ADDRESS                       : 00007FFF10DACF78
CHILD_ADDRESS                 : 00007FFF179D71C8
CHILD_NUMBER                  : 0
UNBOUND_CURSOR                : N
SQL_TYPE_MISMATCH             : N
OPTIMIZER_MISMATCH            : N
OUTLINE_MISMATCH              : N
STATS_ROW_MISMATCH            : N
LITERAL_MISMATCH              : N
FORCE_HARD_PARSE              : N
EXPLAIN_PLAN_CURSOR           : N
BUFFERED_DML_MISMATCH         : N
PDML_ENV_MISMATCH             : N
INST_DRTLD_MISMATCH           : N
SLAVE_QC_MISMATCH             : N
TYPECHECK_MISMATCH            : N
AUTH_CHECK_MISMATCH           : N
BIND_MISMATCH                 : N
DESCRIBE_MISMATCH             : N
LANGUAGE_MISMATCH             : N
TRANSLATION_MISMATCH          : N
BIND_EQUIV_FAILURE            : N
INSUFF_PRIVS                  : N
INSUFF_PRIVS_REM              : N
REMOTE_TRANS_MISMATCH         : N
LOGMINER_SESSION_MISMATCH     : N
INCOMP_LTRL_MISMATCH          : N
OVERLAP_TIME_MISMATCH         : N
EDITION_MISMATCH              : N
MV_QUERY_GEN_MISMATCH         : N
USER_BIND_PEEK_MISMATCH       : N
TYPCHK_DEP_MISMATCH           : N
NO_TRIGGER_MISMATCH           : N
FLASHBACK_CURSOR              : N
ANYDATA_TRANSFORMATION        : N
PDDL_ENV_MISMATCH             : N
TOP_LEVEL_RPI_CURSOR          : N
DIFFERENT_LONG_LENGTH         : N
LOGICAL_STANDBY_APPLY         : N
DIFF_CALL_DURN                : N
BIND_UACS_DIFF                : N
PLSQL_CMP_SWITCHS_DIFF        : N
CURSOR_PARTS_MISMATCH         : N
STB_OBJECT_MISMATCH           : N
CROSSEDITION_TRIGGER_MISMATCH : N
PQ_SLAVE_MISMATCH             : N
TOP_LEVEL_DDL_MISMATCH        : N
MULTI_PX_MISMATCH             : N
BIND_PEEKED_PQ_MISMATCH       : N
MV_REWRITE_MISMATCH           : N
ROLL_INVALID_MISMATCH         : N
OPTIMIZER_MODE_MISMATCH       : N
PX_MISMATCH                   : N
MV_STALEOBJ_MISMATCH          : N
FLASHBACK_TABLE_MISMATCH      : N
LITREP_COMP_MISMATCH          : N
PLSQL_DEBUG                   : N
LOAD_OPTIMIZER_STATS          : N
ACL_MISMATCH                  : N
FLASHBACK_ARCHIVE_MISMATCH    : N
LOCK_USER_SCHEMA_FAILED       : N
REMOTE_MAPPING_MISMATCH       : N
LOAD_RUNTIME_HEAP_FAILED      : N
HASH_MATCH_FAILED             : N
PURGED_CURSOR                 : N
BIND_LENGTH_UPGRADEABLE       : N
USE_FEEDBACK_STATS            : N
REASON                        :
CON_ID                        : 3
==============================
SQL_ID                        : 19m73brqx4wpw
ADDRESS                       : 00007FFF10DACF78
CHILD_ADDRESS                 : 00007FFF1A238078
CHILD_NUMBER                  : 1
UNBOUND_CURSOR                : N
SQL_TYPE_MISMATCH             : N
OPTIMIZER_MISMATCH            : N
OUTLINE_MISMATCH              : N
STATS_ROW_MISMATCH            : N
LITERAL_MISMATCH              : N
FORCE_HARD_PARSE              : N
EXPLAIN_PLAN_CURSOR           : N
BUFFERED_DML_MISMATCH         : N
PDML_ENV_MISMATCH             : N
INST_DRTLD_MISMATCH           : N
SLAVE_QC_MISMATCH             : N
TYPECHECK_MISMATCH            : N
AUTH_CHECK_MISMATCH           : N
BIND_MISMATCH                 : N
DESCRIBE_MISMATCH             : N
LANGUAGE_MISMATCH             : N
TRANSLATION_MISMATCH          : N
BIND_EQUIV_FAILURE            : N
INSUFF_PRIVS                  : N
INSUFF_PRIVS_REM              : N
REMOTE_TRANS_MISMATCH         : N
LOGMINER_SESSION_MISMATCH     : N
INCOMP_LTRL_MISMATCH          : N
OVERLAP_TIME_MISMATCH         : N
EDITION_MISMATCH              : N
MV_QUERY_GEN_MISMATCH         : N
USER_BIND_PEEK_MISMATCH       : N
TYPCHK_DEP_MISMATCH           : N
NO_TRIGGER_MISMATCH           : N
FLASHBACK_CURSOR              : N
ANYDATA_TRANSFORMATION        : N
PDDL_ENV_MISMATCH             : N
TOP_LEVEL_RPI_CURSOR          : N
DIFFERENT_LONG_LENGTH         : N
LOGICAL_STANDBY_APPLY         : N
DIFF_CALL_DURN                : N
BIND_UACS_DIFF                : N
PLSQL_CMP_SWITCHS_DIFF        : N
CURSOR_PARTS_MISMATCH         : N
STB_OBJECT_MISMATCH           : N
CROSSEDITION_TRIGGER_MISMATCH : N
PQ_SLAVE_MISMATCH             : N
TOP_LEVEL_DDL_MISMATCH        : N
MULTI_PX_MISMATCH             : N
BIND_PEEKED_PQ_MISMATCH       : N
MV_REWRITE_MISMATCH           : N
ROLL_INVALID_MISMATCH         : N
OPTIMIZER_MODE_MISMATCH       : N
PX_MISMATCH                   : N
MV_STALEOBJ_MISMATCH          : N
FLASHBACK_TABLE_MISMATCH      : N
LITREP_COMP_MISMATCH          : N
PLSQL_DEBUG                   : N
LOAD_OPTIMIZER_STATS          : N
ACL_MISMATCH                  : N
FLASHBACK_ARCHIVE_MISMATCH    : N
LOCK_USER_SCHEMA_FAILED       : N
REMOTE_MAPPING_MISMATCH       : N
LOAD_RUNTIME_HEAP_FAILED      : N
HASH_MATCH_FAILED             : Y
PURGED_CURSOR                 : N
BIND_LENGTH_UPGRADEABLE       : N
USE_FEEDBACK_STATS            : N
REASON                        :
CON_ID                        : 3

Once you close the connection that was used to create the CallableStatement, the next time the code is executed, the statement will not be null but an error will be thrown when the code attempts to execute it because there is no open connection behind the statement.