Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, atul.

Asked: August 27, 2002 - 10:11 pm UTC

Last updated: October 26, 2023 - 12:52 pm UTC

Version: 8.0.4

Viewed 50K+ times! This question is

You Asked

Sir,

recently in one book i read,

"In the old days, it was up to the developers to build applications
which relied upon sql statements which contained bind variables.

This forced the Oracle kernel to avoid caching similar sql statements
in the SQL cache.

The CURSOR_SHARING=FORCE feature is a huge gain for some applications which do not use
many bind variables, or perhaps none at all."


so is that correct???

We just have to change init parameter so database will start using bind varables???

Thanks.
atul

and Tom said...


Well, you are on 8.0.4 (very old, unsupported) and this was introduced in 816.

To get the OTHER side of the coin, you can read another book "Expert one on one Oracle" (written by me). Specifically in chapter 10 I wrote in part:

<quote>
In Oracle8i, release 2 (version 8.1.6), Oracle introduced a new feature called CURSOR_SHARING. Cursor sharing is an 'auto binder' of sorts. It causes the database to rewrite your query using bind variables before parsing it. This feature will take a query such as:

scott@TKYTE816> select * from emp where ename = 'KING';

and will automatically rewrite it as:

select * from emp where ename = :SYS_B_0

in 8.1.6 and or

select * from emp where ename = :”SYS_B_0”

in 8.1.7. This is a step in the right direction, but should not be the final solution to the problem and should not be used long term. There are side effects from cursor sharing that you must aware of as well. A poorly written program may experience a large gain in performance by setting the CURSOR_SHARING=FORCE parameter, but it will still be running slower than it should, and it will still be limited in the degree to which it can scale. As we observed above, we were able to reduce by a large percentage many of the waits and the time spent waiting by simply using bind variables. We could achieve the same exact results using cursor_sharing=force. It was not until we avoided the soft parses however, that we were able to avoid 100 percent of the waits. Cursor sharing will NOT avoid these soft parses for us. It is a fact that if you can benefit greatly from cursor sharing, you are causing Oracle to parse many queries too frequently. If you are causing many queries to be parsed frequently, then adding cursor sharing will fix the bind variable issue, but you will still be left with the soft parse overhead. While not as egregious as the overhead of bind variables, high soft parse counts will limit your performance and scalability as well. The only correct solution is to use bind variables in the first place and to reuse your cursors whenever possible. For example, if I were coding a Java application I would never code a routine like this:

...
String getWordb(int ID, int IDLang, Connection conn) throws SQLException
{
CallableStatement stmt = null;

stmt = conn.prepareCall("{ call get.wordb (?,?,?)}");
stmt.setInt(1,ID);
stmt.setInt(2,IDLang);
stmt.registerOutParameter (3, java.sql.Types.VARCHAR);
stmt.execute();
String word = stmt.getString (3);
stmt.close();
return word;
}
...

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);
}
...

Here, I am ensuring the use of bind variables by using the placeholders in the statement. Additionally, I am parsing that statement at most once per program execution. This will make a huge difference in performance. In one test, I called the 'bad' and 'good' implementations 1000 times each. The bad implementation, with the soft parses for each call, took two and a half seconds to execute. The good implementation took one second. That was in single user mode. We know by now that adding additional users, each doing thousands of soft parses, will just slow each other down with latch waits during the soft parse. This is overhead we can, and must, avoid in our applications.

Now back to cursor sharing for a moment. I also said above that there are some side effects of cursor sharing that you must be aware of. They fall into the following categories:

o Optimizer related issues. CURSOR_SHARING will remove all character string and numeric constants from the query – the optimizer will have less information to work with. This may very well result in different query plans.
o 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.
o Query plans are harder to evaluate. This is due to the fact that explain plan will 'see' a different query than the database does. This makes query tuning more difficult. Features like autotrace in SQL*Plus are unreliable with CURSOR_SHARING.

</quote>

in short, cursor sharing is a crutch that MIGHT be useful in some cases to help a poorly written program survive for the period of time the developers are hard at work CORRECTING their bug and putting bind variable support into their application




Rating

  (102 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Very good Explanation.

Rajesh, August 28, 2002 - 3:17 pm UTC


Exactly what i want to know about CURSOR_SHARING

Alexey Simonov, February 04, 2003 - 10:11 am UTC

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

Excellent explanation

Peter Tran, April 05, 2003 - 7:34 pm UTC

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!

Tom Kyte
April 05, 2003 - 7:44 pm UTC

It has to (similar). You are using a crutch to help a poorly written application "walk". You have to expect some issues. With crutches it is harder to get up and down stairs, you cannot more as fast and your arms hurt. No different here, when you have to use a crutch, you are slower then you should be and in some amount of pain and should be working really hard to remove the need for crutches.

You've taken away from us the FACT that substr( cname, 1, 10 ) is 10, it is not substr( cname, :x, :y ) where both x and y are somewhat variable APPARENTLY. Since the size of :x and :y in this case would not affect the plan materially, similar will work the SAME as force.



cursor sharing

Vikas Sharma, April 06, 2003 - 7:20 pm UTC

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

Tom Kyte
April 06, 2003 - 8:02 pm UTC

so, why don't you just build an INSERT as SELECT statement and do it right?

short of that -- use BIND VARIABLES -- it isn't hard. You will avoid not only the hard parse -- but that dreaded and expensive soft parse as well....

do it right. use binds, avoid cursor_sharing -- you don't need it, you don't want it, it would be the wrong approach. (the right approach is insert as select really, but at the VERY least -- use binds and parse the insert ONCE)

cursor sharing

Vikas Sharma, April 07, 2003 - 2:19 am UTC

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


Tom Kyte
April 07, 2003 - 8:01 am UTC

If you can dynamically process the SELECT using dbms_sql.column_value -- you should be able to figure out how to dynamically process the insert using dbms_sql.bind_variable.



One more query..

Swapnil, April 07, 2003 - 9:11 am UTC

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.


Tom Kyte
April 07, 2003 - 1:40 pm UTC

insert into some_table
select * from some_other_table;

is going to be faster then a procedural loop.

???

Riaz Shahid, April 07, 2003 - 11:06 am UTC

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


Evaluating the CURSOR_SHARING parameter

Juan Carlos Reyes Pacheco, April 25, 2003 - 11:32 am UTC

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


Tom Kyte
April 25, 2003 - 3:34 pm UTC

OLTP -- force
Mixed workload similar (where you need a different plan for some of the queries)
DSS/DW -- exact

more explanation please

juancarlosreyesp@yahoo.com, April 28, 2003 - 9:54 am UTC

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 :)

Tom Kyte
April 28, 2003 - 10:34 am UTC

yes. that is basically what I said.

A reader, April 28, 2003 - 12:05 pm UTC

Thanks :)

cursor_sharing = force and histograms

Arul, April 29, 2003 - 2:49 pm UTC

"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&#8217;t change but the application will be told the potential for a 30 byte column is there &#8211; 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 Kyte
April 29, 2003 - 8:50 pm UTC

1) no, it is not a bug, just a side effect.

You have a query:

select substr( c1, 1, 5 ) c1 from t;


at parse time, with 1, 5 in there -- the database KNOWS "that is 5 characters". No, introduce cursor_sharing and that becomes:

select substr( c1, :bv1, :bv2 ) c1 from t;

because that query could be:

select substr( c1, 1, 5 ) c1 from t;
select substr( c1, 1, 50 ) c1 from t;
select substr( c1, 1, 500 ) c1 from t;


it no longer KNOWS, so it has to generalize the size. Now, a reporting tool might describe that query to set up an output template. Before cursor_sharing=force, it knew "5 characters" -- after it "knows" NN characters where NN <> 5. report looks bad due to this.

Just a side effect, totally anticipatable if you understand cursor sharing and think through all of the possible ramifications.


2) force is going to give you -- one plan. since 9i does bind variable "peeking", you could find this "unpredicable". Watch:

create table t as select decode(rownum,1,0,1000) id, all_objects.* from all_objects;

create index t_idx on t(id);

analyze table t compute statistics for table for all indexes for all indexed columns;

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;
set termout on

now, if id = 0, use index. if id = 1000, don't. Arguably -- the four queries above are the same under cursor_sharing -- BUT and this is a big but -- they will result in different plans being used.

Pretend the two query1 queries were run in that order on monday -- that is, "id = 0" got in there first. Now -- for the rest of the day, the "id = :bv" with :bv = 0 (peeking) will be in place. You shutdown monday night and restart tuesday. Now the id = 1000 gets in there first....


Using tkprof with aggregate=no, we can see the effect of this:

select * from t query1 where id = :"SYS_B_0"

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.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 4 0 1

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID T (cr=4 r=0 w=0 time=155 us)
1 INDEX RANGE SCAN T_IDX (cr=3 r=0 w=0 time=115 us)(object id 49531)
********************************************************************************
select * from t query1 where id = :"SYS_B_0"

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 2109 0.57 0.57 0 4686 0 31612
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2111 0.57 0.57 0 4686 0 31612

Rows Row Source Operation
------- ---------------------------------------------------
31612 TABLE ACCESS BY INDEX ROWID T (cr=4686 r=0 w=0 time=317553 us)
31612 INDEX RANGE SCAN T_IDX (cr=2166 r=0 w=0 time=161543 us)(object id 49531)


see, id=0 caused the plan -- the generic plan - to be an index access path. but -- tomorrow, simply cause the queries are in different orders:



select * from t query2 where id = :"SYS_B_0"

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 2109 0.38 0.40 0 2531 0 31612
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2111 0.38 0.40 0 2531 0 31612

Rows Row Source Operation
------- ---------------------------------------------------
31612 TABLE ACCESS FULL T (cr=2531 r=0 w=0 time=172266 us)
********************************************************************************
select * from t query2 where id = :"SYS_B_0"

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.01 0.01 0 448 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 448 0 1

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL OBJ#(49530) (cr=448 r=0 w=0 time=13570 us)

I get full scans instead


So, beware (not a bug, desired intended side effect)

cursor_sharing = force and histograms

Arul, April 30, 2003 - 4:26 pm UTC

Tom,
Many thanks for that clear explanation.

Partially dynamic query

Anurag, June 12, 2003 - 9:19 pm UTC

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 Kyte
June 13, 2003 - 7:43 am UTC

100% yes.

cursor_sharing = force and binding all constants as well

A reader, July 26, 2003 - 7:56 pm UTC

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!


Tom Kyte
July 27, 2003 - 9:52 am UTC

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"
 

sorry fot the poorly worded qs!

A reader, July 27, 2003 - 12:52 pm UTC

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!

trying to reproduce your example of cursor_sharing on p443-446

A reader, July 27, 2003 - 3:18 pm UTC

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)





Tom Kyte
July 27, 2003 - 8:02 pm UTC

things change -- yes. see how even the original plan is TOTALLY different from the plan you got?


same caveat applies, you can come up with a test case easily -- once you understand what I was testing (selectivity)



trying to prove constant binding is not good

A reader, July 28, 2003 - 11:01 pm UTC

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
.....
-- 

Tom Kyte
July 29, 2003 - 6:55 am UTC

in 9i, you have "bind variable peeking" meaning the first time the plan is optimized -- we'll peek at the bind variables and use those "facts" to develop the plan.

This offsets much of the issue with overbinding. It is not perfect for it in itself introduces other issues.

search this site for

bind variable peeking


to see other articles on this topic.

thanx

A reader, July 29, 2003 - 12:15 pm UTC

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!



cursor sharing similar

Reader, July 29, 2003 - 6:57 pm UTC

(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.

Tom Kyte
July 29, 2003 - 8:17 pm UTC

1) correct.

2) search for

cursor_sharing similar

on this site -- couple of examples.

3) ALL sql is dynamic sql under the covers in Oracle. Yes, dynamic sql uses the shared pool

4) v$sqlarea is an aggregate (that is expensive to query) of v$sql. v$sql shows child cursors, v$sqlarea rolls up the child cursors



what is a child cursor?

Reader, July 29, 2003 - 8:49 pm UTC


Tom Kyte
July 29, 2003 - 9:22 pm UTC

they are cursors for the same sql_text that are different for some reason.

eg:

connect scott/tiger
select * from emp;

connect someone_else_that_has_emp/tiger
select * from emp;

you'll have two cursors for "select * from emp" -- BUT they are different plans and all since there is a scott.emp and a someone_else_that_has_emp.emp.

one is the child cursor of the other.

example ....

Reader, July 29, 2003 - 8:58 pm UTC

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.

Tom Kyte
July 29, 2003 - 9:24 pm UTC

set define off

create or replace procedure gen_data( p_tname in varchar2, p_records in number )
authid current_user
as
l_insert long;
l_rows number default 0;
begin

dbms_application_info.set_client_info( 'gen_data ' || p_tname );
l_insert := 'insert /*+ append */ into ' || p_tname ||
' select ';

for x in ( select data_type, data_length,
nvl(rpad( '9',data_precision,'9')/power(10,data_scale),9999999999) maxval
from user_tab_columns
where table_name = upper(p_tname)
order by column_id )
loop
if ( x.data_type in ('NUMBER', 'FLOAT' ))
then
l_insert := l_insert || 'dbms_random.value(1,' || x.maxval || '),';
elsif ( x.data_type = 'DATE' )
then
l_insert := l_insert ||
'sysdate+dbms_random.value+dbms_random.value(1,1000),';
else
l_insert := l_insert || 'dbms_random.string(''A'',' ||
x.data_length || '),';
end if;
end loop;
l_insert := rtrim(l_insert,',') ||
' from all_objects where rownum <= :n';

loop
execute immediate l_insert using p_records - l_rows;
l_rows := l_rows + sql%rowcount;
commit;
dbms_application_info.set_module( l_rows || ' rows of ' || p_records, '' );
exit when ( l_rows >= p_records );
end loop;
end;
/
set define on

default behavior is .....

Reader, July 30, 2003 - 8:33 pm UTC

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.

Tom Kyte
July 31, 2003 - 6:57 am UTC



if you submitted queries with literals and cursor sharing = exact (bad in most systems, ok in a data warehouse environment) -- then yes, the optimizer would do that.

If you submitted a query with binds and cursor sharing = exact:

select * from t where x = :x


then what you say would NOT be true. the plan would either be a full scan OR index scan -- for both of 5 and 6.


if you submit a query with literals and cursor sharing = force, then it would be just like submitting it with binds. the plan would ALWAYS be either full scan or index scan regardless.

if you submit a query with literals and cursor sharing = similar, then you have the opportunity for 2 different plans like you did with the literals and cursor sharing = exact

CBO with several alternative plans

Piotr Jarmuz, July 31, 2003 - 8:37 am UTC

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?


To Piotr Jarmuz

A reader, July 31, 2003 - 12:03 pm UTC

"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:)




DSS and Forcing Cursor Sharing

Richard, September 19, 2003 - 4:26 am UTC

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.

Tom Kyte
September 20, 2003 - 5:11 pm UTC

1) yes, as long as the side effects are OK...

however, in a DSS system, it is sometimes acceptable to use literals (in a DW it is common in fact). binds are mandatory in transactional systems where you are doing queries per second. binds are not as mandatory in reporting systems where you do minutes per query.

2) well, it is a 3rd party app -- they should have guidelines based on their experiences. it is really all about the application here.

DSS

Richard, September 22, 2003 - 2:44 pm UTC

Thank you!

DBMS_SHARED_POOL

Denise, November 17, 2003 - 10:32 am UTC

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.

Tom Kyte
November 19, 2003 - 6:37 am UTC

it looks like you installed dbms_shared_pool as someone other then SYS or "as SYSDBA". It must be installed as SYS or "as SYSDBA"

Thanks Tom!

Denise, November 19, 2003 - 8:47 am UTC

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...


from your example above

A reader, December 14, 2003 - 1:44 pm UTC

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?

Tom Kyte
December 14, 2003 - 1:51 pm UTC

you do not want to disable bind peeking. It is there for your benefit.


The concept is -- you have an application. It has a query. The query uses binds. The first execute of the query will determine how the query is to be executed optimally for this application at that point. The application will either always use inputs that use the index or never use inputs that use the index. The application "context" (the point at which that query is executed) is what determines that plan more then anything else.

So while the plan "could change", the fact is the plan won't change cause the inputs fed to that query will be enough to tell us "plan a" or "plan b" and either of plan a or plan b will be used every time.

To "disable" bind peeking -- don't use binds. ONLY appropriate in a data warehouse.

exmaple of how cursor_sharing can break an application

A reader, December 14, 2003 - 4:22 pm UTC

Hi

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

Tom Kyte
December 14, 2003 - 5:53 pm UTC

well, i gave a couple of reasons in the original answer -- but there were issues with earlier releases (eg: in the first release they did not quote the identifiers, that could cause real issues)..

'Cursor Sharing = force' not working

Pramit, March 29, 2004 - 5:49 pm UTC

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

Tom Kyte
March 29, 2004 - 6:13 pm UTC

give me a example of a query the client submits, the language the client is written in, and the manner of submitting the query.... (eg: a small test case :)

Sample Data

Pramit, March 29, 2004 - 8:27 pm UTC

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

Tom Kyte
March 29, 2004 - 8:36 pm UTC

who knows what your connection pool might be doing here -- if you write a small test program that uses the same connection semantics, and you issue the alter session -- does it work (if so, indicates your connection pool is playing games on you)

should have guessed the language was going to be "java". no binds -> one of two languages typically.

re: one of two...

Mark A. Williams, March 30, 2004 - 12:11 am UTC

re: one of two languages typically

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

- Mark

similar in a nutshell behaves as exact?

David, May 01, 2004 - 2:16 pm UTC

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?

Tom Kyte
May 01, 2004 - 5:51 pm UTC

1) yes, if it decided to full scan for 20 and index range for 10.

2) yes, if query 1 were parsed first.

Why similar?

Patrick, May 01, 2004 - 8:14 pm UTC

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.

Tom Kyte
May 02, 2004 - 9:32 am UTC

the situation was:

if I submit query1 and query2 using exact, I would expect "index range" for query1 and "full scan" for query2.

If i submit same queries using similar, I would expect the same.... EXCEPT we would be using bind variables

(the except part went unsaid)



exact doesn't auto-bind.
similar does.

bind enabled

Raaghid, May 19, 2004 - 1:15 am UTC

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.

Tom Kyte
May 19, 2004 - 9:51 am UTC

you want to add

alter SESSION set cursor_sharing=force;


to the PB application only. never set it at the system level. things that don't need binds are negatively impacted by it.

also, 816 was "early cursor sharing" implementation. You want to use it in a very focused, localized sense.

cursor_sharing=force: impl. of sql text transformation

Alberto Dell'Era, August 29, 2004 - 4:14 pm UTC

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!

Tom Kyte
August 29, 2004 - 5:12 pm UTC

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...  
 

Alberto Dell'Era, August 29, 2004 - 7:36 pm UTC

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 ?

Tom Kyte
August 29, 2004 - 7:59 pm UTC

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.
 

Alberto Dell'Era, August 30, 2004 - 3:45 pm UTC

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".

Tom Kyte
August 30, 2004 - 5:20 pm UTC

100%, all were 9204 on RHAS3.0

same box.

thin and OCI drivers are different!

Alberto Dell'Era, August 30, 2004 - 7:14 pm UTC

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 ...

A reader, August 30, 2004 - 9:28 pm UTC

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?


Tom Kyte
August 31, 2004 - 8:30 am UTC

please read this page, we discussed this topic at length.

if everything is "bound", why would you even consider setting this?!?

Save The Literals - return selects as ref cursors

Alberto Dell'Era, August 31, 2004 - 9:32 am UTC

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! 

A reader, August 31, 2004 - 5:45 pm UTC


Excellent thread this!

Richard, November 25, 2004 - 5:16 am UTC

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

Tom Kyte
November 25, 2004 - 9:52 am UTC

well, cursor_sharing=force is a crutch you can use to sometimes help a really poorly written application limp to the end of the day while the programmers are busy fixing their bug.

cursor_sharing=force is something that ultimately you want to turn off as soon as possible.

cursor_sharing=force is a crutch, it is not something I'd be proud to say "i have it set that way".

However, ask the DBA for their reasoning and then I'll expain why they are *wrong*. It is just as bad in CBO as in RBO but if you are in an emergency situation -- it might be the only thing you can do for limp thru to the end of the day (while standing behind the developers asking "hey, you done fixing your bug yet..." over and over).


So -- it is evil in CBO and RBO, but sometimes a necessary evil. the optimizer mode is NOT what makes you say "i'll have this on or off" -- a buggy application that is killing the shared pool with thousands of literal sql statements is what makes you consider this.

CURSOR_SHARING=FORCE

Mike, February 24, 2005 - 12:33 am UTC

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..

Tom Kyte
February 24, 2005 - 5:36 am UTC

I'd try sharing, see if that results in a manageable number of child cursors in the shared pool and then flip to force if not.


and yes, i would set it on for a session only, not at the system level

EXISTS(SELECT 1 FROM vs. EXISTS(SELECT * FROM

Alexander, May 06, 2005 - 10:58 am UTC

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?

Tom Kyte
May 06, 2005 - 11:46 am UTC

where exists ( select null
from .... )

is what I like to use. but I would worry more about why cursor_sharing isn't exact then changing my where exists.

You have a bug in the developed code that needs fixing -- change that, leave the where exists alone

what kind of bug?

Alexander, May 06, 2005 - 12:27 pm UTC

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



Tom Kyte
May 06, 2005 - 12:40 pm UTC

that is the bug -- that they are not binding right. don't spend time fixing where exists, put all resources into fixing the real bug (was my point, don't waste time on where exists, fix the real bug)


just let the where exists alone, it is not relevant WHAT it selects, the database knows "it is not really returning any columns")

high number of latches (library cache) since we started using cursor_sharing

shay ittah, May 15, 2005 - 9:27 am UTC

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

Tom Kyte
May 15, 2005 - 9:45 am UTC

400 is a very small number when it comes to latching -- how did you do your analysis and what does the number "400" mean exactly.


(and the real fix is of course in the application, if you are forced to use cursor sharing, it means you have a bug in the code (no binds). It also means you are now soft parsing a lot more than you need which is really bad -- as compared to hard parsing a lot more than you should which is just a worse really bad situation)

shay ittah, May 15, 2005 - 10:33 am UTC

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.

Tom Kyte
May 15, 2005 - 10:50 am UTC

and what was it before the change? and what does it mean -- is it "bad", did it affect performance, what as the sum of the waited time, was it relevant? was it 400 across ALL sessions and if so, 1 session or thousands of sessions.

"400" is still sort of not meaningful in this context.

400 could be *really good* -- a dramatic improvement
400 could be ok
400 could be not good

but without quantative numbers to put it into perspective, it is simply 20*20.



CURSOR_SHARING and PARALLEL SLAVES,

sns, July 05, 2005 - 11:01 am UTC

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,

Tom Kyte
July 05, 2005 - 11:31 am UTC

it may well might not have.

// *Action: either re-execute query with lower PARALLEL_MIN_PERCENT or
// wait until some running queries are completed, thus freeing
// up slaves


is just as reasonable an answer -- you didn't have what you wanted, just sitting on your hands could have achieved the same goal.


setting cursor_sharing would not have been in my list of the top 100 things to do in this case.

David Tramer, August 02, 2005 - 5:38 pm UTC

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?)

Tom Kyte
August 02, 2005 - 7:26 pm UTC

the database is multiuser, I don't get what you mean??!?!?


do you mean "multiuser" or "i have written a massively multiTHREADED, but single user, program"

Follow up

David Tramer, August 03, 2005 - 4:42 pm UTC

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.



Tom Kyte
August 03, 2005 - 6:13 pm UTC

well, that is a problem with your java implementation. You would have that rampant problem all over the place.

This is a java programming implementation detail with multi-threaded programming.

Pro*c has the same issue, if you decide to multi-thread.

If you want to write multi-threaded code, more power to you, but you'll be designing a lot for re-entrancy and other standard issues.

Cursor_sharing

Mark, November 09, 2005 - 8:01 am UTC

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

Tom Kyte
November 11, 2005 - 9:56 am UTC

well, you'd be able to see the effect in v$sql - do you see queries with the distinctive :"sys_bv_nn" binds in it?

in 10g, you have

v$ses_optimizer_env

that'll show you the setting for any session though.

Yes I do

Mark, November 14, 2005 - 9:01 am UTC

...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

Tom Kyte
November 14, 2005 - 1:21 pm UTC

could they just be old statements (before you made the change).


the alter system won't flush the shared pool of old statements.

How to reduce soft parse for sybase to oracle replication ?

A reader, April 28, 2006 - 2:06 pm UTC

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

Tom Kyte
April 28, 2006 - 5:35 pm UTC

You would have to ask sybase since parsing.....

is 100%

totally

and completely

controlled by the application executing the sql. that would be "sybase" in this case.

CURSOR_SHARING

Fábio Blessa, July 25, 2006 - 4:37 pm UTC

GOOD STUFF.

CURSOR_SHARING

Fábio Blessa, July 25, 2006 - 4:46 pm UTC

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

Tom Kyte
July 25, 2006 - 4:51 pm UTC

[tkyte@localhost ~]$ oerr ora 2068
02068, 00000, "following severe error from %s%s"
// *Cause: A severe error (disconnect, fatal Oracle error) received from
// the indicated database link. See following error text.
// *Action: Contact the remote system administrator.


would need to see rest of stack.

could it - sure, cursor sharing might be causing a fatal error via a "bug". Same could be said of most every thing though.

cursor_sharing = force seems not working

A reader, October 20, 2006 - 4:51 pm UTC

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


Tom Kyte
October 20, 2006 - 5:41 pm UTC

well, sessions can turn it on and off at will as well.

what is executing those inserts ?

Not sure what's going on.

A reader, October 20, 2006 - 7:46 pm UTC

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.

Tom Kyte
October 21, 2006 - 11:02 am UTC

what is it written in, what is executing the SQL itself. What do these inserts truly look like (not after I've re-written them)

what do the applications defeat cursor_sharing-=force?

A reader, October 21, 2006 - 11:52 pm UTC

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

Tom Kyte
October 22, 2006 - 7:42 am UTC

if you don't give me what I ask for, well, sorry - I don't 'guess'. I've asked a couple of times how the application interacts with the data base. You haven't answered, not much more I can say....



Error when using CURSOR_SHARING = SIMILAR

Anudeep Gupta, January 09, 2007 - 11:02 pm UTC

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??

cursor_sharing=force seems not working

Sean, June 24, 2008 - 11:20 am UTC

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?
Tom Kyte
June 24, 2008 - 12:52 pm UTC

what jdbc driver version are you using?

and why is it ALWAYS a java program :(

Sean, June 24, 2008 - 4:07 pm UTC

it's Oracle¿s Driver (Thin) Versions:9.0.1,9.2.0,10
Tom Kyte
June 24, 2008 - 5:41 pm UTC

can you test with each, I believe there was an issue with the older ones regarding this.


just write a small program that parses and executes:

select 1 x from dual 9_0_1;
select 2 x from dual 9_0_1;

and from dual 9_2_0 and so on (based on the jdbc version) and see if some of them do not have the rewrite happening.

no bind variables

Jim, June 24, 2008 - 4:46 pm UTC

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.
Tom Kyte
June 24, 2008 - 5:47 pm UTC

well, it can only put a bandage on it, it cannot fix it.

cursor sharing force can sometimes turn a hard parse problem into a soft parse problem (from very very bad to very bad). It cannot reduce the amount of overall parsing

and it can introduce some wickedly bad behavior all of a sudden. Plan changes, bind peeking where you didn't mean to, everyones code is affected, the database behavior changes - many obvious and not obvious side effects here.

cursor_sharing and with clause

Joaquín González, July 15, 2008 - 6:13 am UTC

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

SELECT :"SYS_B_0" FROM DUAL

Subbu, July 25, 2008 - 12:59 pm UTC

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?
Tom Kyte
July 29, 2008 - 10:16 am UTC

show us.

PLease Provide your INputs.

Bhushan, September 20, 2008 - 2:24 pm UTC

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



Tom Kyte
September 21, 2008 - 1:59 pm UTC

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


gosh, I hate that more than anything else - that is the worst of the worst practices. Please explain why you do that.


and you are correct, without an example to reproduce with - it is really hard to comment. you have a char(3) variable there in the code, but you don't use it in the code, that makes me VERY VERY VERY suspicious - of your code, that the code you supply me has no bearing to any code you actually run.

Any Parameter Set?

Bhushan, September 25, 2008 - 11:27 am UTC

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
Tom Kyte
September 25, 2008 - 3:42 pm UTC

.... the when others is not actual
in the code just made up that test case ...

why? why would you put it there? It indicates to me that you do that in real code (else why would you randomly throw it into a test case?)

It does not annoy me, it literally scares the daylights out of me - because I know you must be doing this in "real life" too - else it would not be done in a simple test (simple tests typically SKIP things we do in real life, not add things we'd never think of doing)

And every time I see it - every. single. time. I will call it out - loud, clear, unsympathetically. Unless and until people stop doing it.


I can think of things like the use of the CHAR instead of the VARCHAR2 datatype and cursor sharing and multi-byte charactersets that *might* do that.

Why don't you present a FULL TEST CASE - with create tables and data and everything and then we can LOOK

When Others

Bhushan, September 25, 2008 - 3:55 pm UTC

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
Tom Kyte
September 25, 2008 - 4:49 pm UTC

... Well that would be a *very strong wrong assumption* ...

why would it? Why would you include in a 20 line snippet of code a worst practice that you would not do in real life?

why?

Why would you expose the world to this worst practice? Why would you make it look normal by going out of your way to include it in a snippet?

I think it is not a wrong assumption.
I think it is actually probably more accurate than not in real life.


... code WHEN OTHERS but ONLY while Testing!!! ...

that is so wrong, you never want an when others like that - ESPECIALLY WHEN TESTING - all you do is hide the error, the error line number - you make it harder to debug the code - why would testing make this any different?

It is bad in real code
It is perhaps worse in test code, because test code frequently becomes 'real code' and then it just gets stuck in and left.


... It is only to catch the exception
and the error message in the TEST WINDOW in PL/SQL Developer....

if you let the error propagate - would you not, well, get the error message in plsql developer - ALONG WITH THE LINE NUMBER the error occurred on and wouldn't that be nice....


... You were *totally* wrong in preassuming there. ...

No, I am not, hey - maybe you are the exception to the rule, but truth be told - by doing this in plsql developer - all you are doing is making *your job harder*, you have accomplished nothing positive - and when you post the code on the internet with a when others then null equivalent I will do exactly what I just did, loudly. clearly. over and over.

Until it stops.

cursor

vineela, September 26, 2008 - 6:37 am UTC

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
Tom Kyte
September 26, 2008 - 1:15 pm UTC

for retrieving a single record, or zero records you will use a cursor.

you seem to be using plsql in your psuedo code, you will use dbms_sql to dynamically access the i'th column, you do not do that by name, but by position.

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

Issue with CHAR datatype over DBLINK

Bhushan, October 06, 2008 - 11:42 am UTC

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

Tom Kyte
October 06, 2008 - 3:08 pm UTC

yes, one database is multi-byte and one is not. So the maximum possible lengths of things (with the multi-byte database) are "bigger"

More insigt please...

Bhushan, October 06, 2008 - 3:14 pm UTC

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
Tom Kyte
October 07, 2008 - 1:03 pm UTC

look at your characterset, one of them would have a string be longer than what you defined - the char(2) could be a char(6) - 6 bytes instead of 2 bytes since (n) is in bytes - not characters.

you will not modify any nls parameters - you would have to change the entire characterset and you are not going to be doing that - that would involve a database rebuild in your case

Very good Explanation.

Bijender Rathi, October 06, 2008 - 11:44 pm UTC

Very good Explanation. --thanks Tom

Bhushan, October 08, 2008 - 3:31 am UTC

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
Tom Kyte
October 08, 2008 - 10:04 pm UTC

but the data selected over the dblink can be 6 bytes - because single byte data can be mapped into multi-byte data.

There is casting going on here - the worst case is being assumed. The field *could be* up to 6 bytes - because of the multi-byte issue.

do this with two single byte databases and you'd see a different answer.

Hmm..That Makes Sense..

Bhushan, October 09, 2008 - 12:48 am UTC

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

setting cursor_sharing=FORCE at instance level

Dan, January 08, 2009 - 12:14 pm UTC

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.
Tom Kyte
January 08, 2009 - 1:38 pm UTC

if your product vendor needs cursor sharing = force, then

your product vendor ships code that is extremely insecure

Ask your vendor "why are you using string concatenation, everyone knows that code that does that is subject to SQL Injection. Did you purposely ship insecure code? "

I would never set this to force at the instance level. it is an "application level band-aid that the application can set while the developers are busy getting the bug (lack of bind variables) fixed in their application"

The "proof" to the vendor is "google sql injection" and then "convince US you are not subject to it"


You could probably hack their application easily, give it a try. If you google up sql injection and read the sql injection walkthru papers - you'll be breaking their application in no time.

cursor_sharing=force

Dan, January 08, 2009 - 8:27 pm UTC

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
Tom Kyte
January 08, 2009 - 9:19 pm UTC

sql injection arises from NOT BINDING INPUTS. not because of cursor sharing=force.

However, if you "need" to use cursor sharing force - that means 100% that they do not bind, and are subject to sql injection.


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

There could also be bind peeking related issues - especially for any code that is already binding when they should be (you will be affecting *everything*, not just their code, all code)

cursor_sharing

Dan, January 08, 2009 - 10:12 pm UTC

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).

when is dynamic SQL in PL/SQL really subject to cursor_sharing?

Thomas, January 21, 2009 - 9:48 am UTC

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

Tom Kyte
January 21, 2009 - 1:34 pm UTC

do some of them "sort of sometimes partially use binds"

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



cursor sharing will only rewrite if there are no binds at all.

quite a valuable hint, but...

Thomas, January 22, 2009 - 3:40 am UTC

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
Tom Kyte
January 22, 2009 - 9:12 am UTC

that bug shows the rewrite took place (no literals in the query) but a cursor mismatch was being raised - so it was the same sql statement over and over in that case - no literals. That is not what you describe.

can you share

a) an example of 2 or 3 of the queries that should be auto-bound but are not
b) with the source code snippet that prepares them


I assume it is always the same sql, it is not "random" one day not bound, one day bound?

examples

Thomas, January 23, 2009 - 6:08 am UTC

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.
Tom Kyte
January 23, 2009 - 9:15 am UTC

bingo - binds - you have "binds"

why would they select INTO, with a ref cursor. You don't do that.

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




get rid of the INTO clause.

Also, don't your developers know about stored procedures? Why would they put all of the SQL in the application - if you stored procedured this stuff, it wouldn't be an issue at all

Or views even? Cannot they use a view???

outstanding

Thomas, January 23, 2009 - 11:20 am UTC

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

CURSOR_SHARING=FORCE efficiency

Nicosa, May 12, 2009 - 12:40 pm UTC

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').
Tom Kyte
May 13, 2009 - 11:02 am UTC

- Why do I only see the first value assigned to :mybind ?

because that is the definition of what this view does

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2.htm#REFRN30310

One of the bind values used for the bind variable during a past execution of its associated SQL statement. Bind values are not always captured for this view. Bind values are displayed by this view only when the type of the bind variable is simple (this excludes LONG, LOB, and ADT datatypes) and when the bind variable is used in the WHERE or HAVING clauses of the SQL statement.


- Is this expected behavior ?

yes

- How can I get every values used ?

you cannot, we do not track that level of minutiae



... 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. ...

well, you do not need to look at bind values for that, you need to look at the SQL. If your SQL is using bind variables all by itself (as yours is), you absolutely DO NOT need, want nor desire cursor sharing force. Do you know what force does? It rewrites sql with literals - a query like:


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.



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



... (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'). ...

that would have nothing to do with cursor sharing though - not sure what you are trying to do there.

Sorry for possible confusion...

Nicosa, May 12, 2009 - 1:02 pm UTC

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 ?

Tom Kyte
May 13, 2009 - 11:06 am UTC

same answers as above. we do not record the number of unique bind inputs anywhere.

Trying to be more clear

Nicosa, May 13, 2009 - 12:21 pm UTC

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.
Tom Kyte
May 13, 2009 - 3:31 pm UTC

... and the instance is some kind of DWH ...

and that is precisely the system type that can actually be exempt from binds for the data warehouse queries.

In a data warehouse, you run queries that take many seconds (minutes, hours, days...)

In a data warehouse, you do not run many hundreds of queries per second (like OLTP does)

When you run many queries per second -> you must bind
When you run queries that take many seconds -> you might not want to bind


To get the 'best plan' for a warehouse query - you typically would like to see a couple of literals in there - to help the optimizer decide what to do.


What if you have a query such as:


select *
from t1, t2
where t1.key = t2.key
and t1.col = :x
and t2.col = :y;


what if t1/t2.col is very skewed. 0.01% of the rows in t1 are returned if t1.col is < 100. 90% of the data is returned if t1.col > 100. Likewise for t2.

If you query

where t1.key = t2.key and t1.col = 1 and t2.col = 1000;
where t1.key = t2.key and t1.col = 1000 and t2.col = 1;
where t1.key = t2.key and t1.col = 1000 and t2.col = 1000;
where t1.key = t2.key and t1.col = 1 and t2.col = 1;


you would likely want *four different plans*

with cursor sharing force, you get 1 size fits all - first guy to parse picks the plans for everyone else.

How to measure potential benefit of setting it back to default (EXACT) ?

Nicosa, May 18, 2009 - 9:55 am UTC

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 ?
Tom Kyte
May 23, 2009 - 11:19 am UTC

correct. we do not track the bind minutiae to the level that would be required.


however, given you wrote:

... and the instance is some kind of DWH ...


I do believe you have all of the evidence that you need, if in fact that is a correct characterization of the system itself.

CURSOR_SHARING - FORCE

Stefan, May 21, 2009 - 4:42 pm UTC

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 

Tom Kyte
May 23, 2009 - 1:24 pm UTC

cursor sharing will NEVER EVER REDUCE THE NUMBER OF TIMES PARSE HAPPENS

cursor sharing can turn a hard parse problem into a soft parse problem, but that is it.

cursor sharing will NEVER reduce the number of parse calls - why do you think it would?

cursor_sharing

Stefan, May 25, 2009 - 4:38 am UTC

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
Tom Kyte
May 26, 2009 - 9:39 am UTC

it can ONLY turn a hard parse into a soft parse

but a parse is parse by any other name - so it'll still be a parse.

Only the application developer can make the number of parse calls decrease....

Pointers

Pointers, October 20, 2009 - 7:34 am UTC

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.

Tom Kyte
October 22, 2009 - 4:27 pm UTC

auth_check_mismatch - you didn't have access to something
translation mismatch - the base objects (the emp table) were not the same.

The second user did not have access to the EMP table the first query referenced (auth mismatch) and upon further inspection - there was an EMP table the second user could see which was a DIFFERENT emp table form the first user - so translation mismatch (different objects)


have you looked in the reference guide? It explains the columns in v$sql and v$sql_shared_cursor

Once you know the contents and what the contents mean, you should be "example free"? I mean, what sort of guide to querying v$sql could there be? (consider EMP instead of V$SQL - what sort of guide to EMP - beyond "this is the emp table, these are the attributes and this is what these attributes mean...")

Asynch I/O on windows systems

A reader, October 13, 2010 - 4:02 am UTC

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
Tom Kyte
October 13, 2010 - 7:09 am UTC

async IO will be used if available. You can set filesystemio_options, and that would be done in an OS specific manner (review your OS specific guides for information).


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

well, think about it.

If you have a 2 cpu machine, can you really do 130 things at a time? What would happen if you did? Might it get really bogged down?

If you have a single 3tb disk - can you really do 130 IO intensive things at a time? ...... ditto ....

If you have 8gb of ram and you set your memory target to 10gb .....


In other words - it depends. It might be OK (cannot tell, one of the most important numbers - cpu count - is completely missing here - don't post it, you should be able to figure out what we'd say based on what I just said). It might be horrible.

It depends. Think about the resources you have available and whether 130 things at once makes sense.

cursor_sharing not working

A reader, January 19, 2011 - 6:54 am UTC

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

Tom Kyte
January 23, 2011 - 3:45 pm UTC

what is the calling application written in, what language?

snap langage and bind variables

A reader, January 24, 2011 - 3:45 am UTC

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

Tom Kyte
February 01, 2011 - 10:03 am UTC

I've never even heard of "snap"

but they need to ask the makers of snap if they support bind variables and if they do not - you need to immediately discontinue use the language snap.

A reader, February 02, 2011 - 2:39 am UTC

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
Tom Kyte
February 02, 2011 - 7:43 am UTC

I hate cursor sharing = force. I would never set it in an init.ora and have it be in effect system wide.

I think they should use bind variables so as to actually FIX the problem and to ensure they are not subject to sql injection.

Cursor environment

Vishu, February 05, 2011 - 6:00 am UTC

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
Tom Kyte
February 06, 2011 - 12:11 pm UTC

I'm at a loss here. Of course not.

Just because you use binds doesn't mean "it will go fast". If your query hits a ton of data, is inefficiently indexed, is written in a really inefficient fashion - then it might "go slow"

Bind variables is not fast=true, nothing is. You'd want to get your hands on a tkprof report and see what it is doing.

Can using Bind variables actually hinder SQL execution

Doug, April 29, 2012 - 6:55 pm UTC

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
Tom Kyte
April 30, 2012 - 8:20 am UTC

how can a bind variable be "hard coded" ??? what does that mean?

In an OLTP system where you are executing many queries per second - bind variables are going to be mandatory pretty much (else you will NOT be executing many queries per second)


In a report/warehouse system where you are executing queries that take many seconds - bind variables might not be what you want to use - to get the best plan possible. Here you want literals (but must be VERY VERY VERY careful to avoid sql injection)


What do I mean?

A reader, April 30, 2012 - 7:25 pm UTC

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






Tom Kyte
May 01, 2012 - 3:36 pm UTC

What I mean by hard coding a bind variable is
select * from emp where empno = :empno


please never use that term "hard coding a bind variable" again!

hard coding is what you do when you do not use a bind.

binding is how you avoid hard coding.

putting the two together is beyond confusing!!!!


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.

Okay, but why is this so?

A reader, May 01, 2012 - 5:44 pm UTC




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


Tom Kyte
May 02, 2012 - 12:52 pm UTC

If you use cursor sharing=force/similar, that means the programmer is generating sql like:

select * from emp where empno = 1;
select * from emp where empno = 2;
...
select * from emp where empno = 100303124;

and so on. In short, the developer CANNOT be parsing a single select statement ONCE and reusing it over and over and over again. They MUST be parsing each and ever single one. Even though they turn into soft parses - THEY MUST BE PARSED.

forget about the fact it apparently becomes a bind later - in your code - you have literals. Since each sql statement is different - you MUST be parsing each and every one. If you execute a search for 1,000 different empnos - you would have parsed 1,000 times.


On the other hand, if the developer uses

select * from emp where empno = :x;

how many times would you have to parse that to search for 1,000 different empnos? ONCE - you would/could parse that just ONCE and execute it over and over and over again. YOU - the developer, can reduce the number of parses of N (where N is any number) down to 1 if you want.


for example, you could write a subroutine that looks at a non-local variable (so this variable exists across calls to this subroutine) and say "if this statement hasn't been parsed - let's parse it" and then bind/execute it - NEVER closing it.

Something like (this is psuedo code and will not compile in any language):
{
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.....
}


whereas if you used literals, you would only be able to do this:
{
myPreparedStmt = 
  prepareStatement( 'select * from emp where empno = '+the_empno_passed_to_me );

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

myPreparedStmt.close()
}


you would parse every single time.

watch this
http://www.youtube.com/watch?v=1oddFEyUAjs

to see how much less work you are doing if you parse every time you execute (it is about a 30% reduction in throughput in that case!)

To @ Reader

Raj, May 02, 2012 - 5:54 am UTC

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

Cool

A reader, May 02, 2012 - 7:55 pm UTC

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

Java code

A reader, July 11, 2012 - 12:54 pm UTC

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?

Tom Kyte
July 12, 2012 - 5:28 pm UTC

every time you call prepare, you are parsing. It might be a hard parse (sql could not be found in the shared pool) or a soft parse (it was found after A LOT of work).

But it will be a parse. A parse is bad, only "no parse" is good.

The above technique will avoid the parse - if you are going to execute that method a lot, it will avoid a lot of parses.


Here you can see the overhead of a soft parse (and a hard parse). In short, you will see a workload go from 100% without parsing, to 70% with soft parsing (30% reduction) to 10% with hard parsing (yes, 90% reduction)

http://www.youtube.com/watch?v=1oddFEyUAjs


cursor_sharing = force.

Rajeshwaran Jeyabal, June 28, 2021 - 2:20 pm UTC

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>

Connor McDonald
July 02, 2021 - 5:15 am UTC

Yeah, I reckon thats a doc bug - I have *never* known cursor sharing to be selective on which literals it choose to replace.

It is perhaps more bad wording. We *do* replace the literal, but the different in ORDER BY means that we will not share the cursor, eg

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




Reuse PreparedStatement

A reader, October 24, 2023 - 1:06 pm UTC

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.
Chris Saxon
October 26, 2023 - 12:52 pm UTC

Good point

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.