Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Twin.

Asked: May 06, 2002 - 9:49 pm UTC

Answered by: Tom Kyte - Last updated: August 13, 2019 - 2:08 am UTC

Category: Database - Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Tom,

We have an interesting problem,

When we set cursor_sharing to 'force', doing following SQL having space trailing the return value 'Y'.

SQL> select 'Y' from dual;
SQL> 'Y'
SQL> --------------------------------
SQL> Y


However, after changing cursor_sharing to 'exact, the same sql will return no space trailing the return value 'Y'.

SQL> select 'Y' from dual;
'
-
Y

Could you explain why?

Thanks

Twin

and we said...

Sure -- if you have my book I go into this in some detail in chapter 10 (along with other cursor_sharing=force caveats).


Your query "select 'Y' from dual" is being turned into "select :SYS_BV0 from dual"

It is no longer selecting a fixed width constant. It is selecting a varying width constant now. We no longer "know" that will return a char(1), it can return upto a varchar2(N).

Here is a small example I use in the book to show the differences:


ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 l_theCursor integer default dbms_sql.open_cursor;
3 l_descTbl dbms_sql.desc_tab;
4 l_colCnt number;
5 begin
6 execute immediate 'alter session set cursor_sharing=exact';
7 dbms_output.put_line( 'Without Cursor Sharing:' );
8 for i in 1 .. 2
9 loop
10 dbms_sql.parse( l_theCursor,
11 'select substr( object_name, 1, 5 ) c1,
12 55 c2,
13 ''Hello'' c3
14 from all_objects t'||i,
15 dbms_sql.native );
16
17 dbms_sql.describe_columns( l_theCursor,
18 l_colCnt, l_descTbl );
19
20 for i in 1 .. l_colCnt loop
21 dbms_output.put_line( 'Column ' ||
22 l_descTbl(i).col_name ||
23 ' has a length of ' ||
24 l_descTbl(i).col_max_len ) ;
25 end loop;
26 execute immediate 'alter session set cursor_sharing=force';
27 dbms_output.put_line( 'With Cursor Sharing:' );
28 end loop;
29
30 dbms_sql.close_cursor(l_theCursor);
31 execute immediate 'alter session set cursor_sharing=exact';
32 end;
33 /
Without Cursor Sharing:
Column C1 has a length of 5
Column C2 has a length of 2
Column C3 has a length of 5
With Cursor Sharing:
Column C1 has a length of 30
Column C2 has a length of 22
Column C3 has a length of 32
With Cursor Sharing:

PL/SQL procedure successfully completed.

The substr( object_name, 1, 5 ) gets turned into substr( object_name, :bv1, :bv2 ) -- we no longer KNOW it is 5 characters - we've set it to 30 (30 being larger then 5 and all queries that use substr( object_name, 1, 5 ), "1, 6 )", "1, 7 )", ..... "1, 29 )", "1, 30 )" will all goto 30 so they can share the same cursor. If we did a substr( object_name||object_name, 1, 31 ) -- it'll goto 60 characters, to increase the likelyhood of sharing a cursor.


The constant 55 is replaced with :SYS_BVn. We no longer know it is 55, it is some number. Instead of a number with a max size of 2 bytes -- it is a full number(38) with 22 bytes max possible.

And so on. There are other cursor sharing side effects -- if you have my book, check out chapter 10.

and you rated our response

  (46 ratings)

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

Reviews

An Idea...

August 30, 2005 - 6:44 am UTC

Reviewer: Richard from Buckinghamshire, UK

OK. We all know that Cursor Sharing is a potential stinker, but what about Oracle introducing *intelligent* Cursor Sharing?

By *intelligent*, I mean that Oracle provide a procedure that allows the User to type in a SQL statement, with accompanying details of bind sizes, and then Oracle *monitors* for the statement (similar to Outlines) , and auto-binds it.

e.g. I have a regularly submitted statement like:

select column1 from myTable where column2 = 'abcd1234';

The statement is issued many, many times, with only the 'abcd1234' varying. I am unable, right now, to get the offending .asp code that generates this nasty code fixed.

So, what'd be extremely marvellous would be an API, procedure, whatever that allowed me to type in something like:

auto_bind_this(
statement_body => 'select column1 from myTable where column2 = BIND_TARGET,
bind_target => varChar2,
bind_target_size => 8);

And the nasty code be converted to nice code, e.g.:
select column1 from myTable where column2 = :bv1;
with Oracle knowing the bind variable size.

Crazy? Sane? What say you?



Tom Kyte

Followup  

August 30, 2005 - 7:27 am UTC

semi-sane, not entirely crazy, but i have the same feeling about it as I did about cursor_sharing

It does not motivate the coder to fix their BUG!

(enhancement requests -> itars, only way to get the idea into the system)

Yes, but...

August 30, 2005 - 8:00 am UTC

Reviewer: Richard from Buckinghamshire, UK

"It does not motivate the coder to fix their BUG"

Agreed, but only if the DBA fails to scream and shout and generally make a perfect nuisance of him/herself until the code is fixed!

I have already screamed and shouted (nicely, of course) at the Development Team Manager to sort out the appalling .asp code that accounts for 90%+ of the non-reusable code in the SGA. He has agreed that it needs fixing (hooray!), but, as with many other businesses, there are other things already in the pipeline.

So, the problem is identified and WILL be fixed (it's reached Top Management level), but meanwhile, a quick and focused fix/crutch would be a real winner. I will be suggesting this idea to Oracle, as advised. Maybe, if it is accepted, a warning, similar to those seen when deprecated init parameters are used, could be incorporated?

Tom Kyte

Followup  

August 30, 2005 - 11:06 am UTC

(did you try the security thing with mgmt? it could get the issue elavated - It is my favorite approach.... "no binds = no security")

Looks dangerous but usefull.

August 30, 2005 - 9:23 am UTC

Reviewer: Jonathan from England

How about adding a description to remind DBA as to why it is there.

If you go down this route too far you will end up with a function allowing the DBA to rewrite queries on the fly. Now there's a thought....

Tom Kyte

Followup  

August 30, 2005 - 12:17 pm UTC

we have that in 10g :)




ops$tkyte-ORA10G> create or replace view dept_v
  2  as
  3  select deptno, initcap(dname) dname, loc
  4    from dept
  5   order by loc;

View created.

ops$tkyte-ORA10G>
ops$tkyte-ORA10G> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

ops$tkyte-ORA10G> pause

ops$tkyte-ORA10G> clear screen
ops$tkyte-ORA10G>
ops$tkyte-ORA10G> begin
  2          sys.dbms_advanced_rewrite.declare_rewrite_equivalence
  3          ( name             => 'DEMO_TIME',
  4            source_stmt      => 'select * from dept',
  5            destination_stmt => 'select * from dept_v',
  6            validate         => FALSE,
  7            rewrite_mode     => 'TEXT_MATCH' );
  8  end;
  9  /

PL/SQL procedure successfully completed.

ops$tkyte-ORA10G> pause

ops$tkyte-ORA10G> clear screen
ops$tkyte-ORA10G> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 Operations     BOSTON
        30 Sales          CHICAGO
        20 Research       DALLAS
        10 Accounting     NEW YORK

ops$tkyte-ORA10G> select * from dept for_real;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

ops$tkyte-ORA10G> pause

ops$tkyte-ORA10G> clear screen
ops$tkyte-ORA10G>
ops$tkyte-ORA10G> exec sys.dbms_advanced_rewrite.drop_rewrite_equivalence( 'DEMO_TIME' );

PL/SQL procedure successfully completed.

ops$tkyte-ORA10G>
ops$tkyte-ORA10G> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

ops$tkyte-ORA10G>
 

August 30, 2005 - 11:53 am UTC

Reviewer: A reader

Hi tom
cant the issue of reusing the shared cursor be removed by using the cursor_sharing=similar option??


Tom Kyte

Followup  

August 30, 2005 - 12:26 pm UTC

which issue are you refering to exactly?

issue misinterpreted

August 31, 2005 - 6:48 am UTC

Reviewer: A reader from India

Dear Tom
Actually i meant to say that if in the example that you had given above of with and without cursor sharing if you had used the similar option then i guess you would have got expected results as the similar option does not force it to use the same shared cursor...

Tom Kyte

Followup  

August 31, 2005 - 1:48 pm UTC

I'm not at all sure what you are refering to (more than one example)

BUT if you were refereing to the select 'Y' from dual -- no, similar would do nothing there.

it would still be a bind of unknown width.

and since the bind does not affect the plan, the cursors would be shared.

Security

August 31, 2005 - 8:05 am UTC

Reviewer: Richard from Buckinghamshire, UK

"did you try the security thing with mgmt? it could get the issue elavated - It
is my favorite approach.... "no binds = no security") "

No - but I will, now ;)

Sadly, tragically, and frighteningly, security is not anywhere near tight enough where I work (and never has been anywhere I have worked): hard-coded user/password in scripts, same user/password for disparate sites (Customer sites, that is!), etc.

It amazes me that security is invariably the last item on the list (apart from "are we using binds?" - which is lucky to even make it onto the list) when applications are architected... but, alas, that's the crazy business we call Information Technology.

Given that terrorism, whether overtly violent (bombs) or *soft* (wrecking/hacking critical databases) is very much a part of all our lives, you'd think that security is the Numero Uno issue... well, you'd be thinking wrong, pardner!

Re: dbms_advanced_rewrite

August 31, 2005 - 10:53 am UTC

Reviewer: Richard from Buckinghamshire, UK

Wow! What a SUPERB bit of functionality! Now then, how hard can it be for Oracle to stretch this idea to incorporate my *intelligent* cursor sharing proposal?!

How about a vote!

does similar binds too??

September 01, 2005 - 6:36 am UTC

Reviewer: A reader from India

Tom,
Ok you mean to say the similar option also binds it just as force for eg.
"select 'Y' from dual" gets converted into "select :SYS_BV0 from dual"...if so then why do they say similar works better than force??

Tom Kyte

Followup  

September 01, 2005 - 6:53 am UTC

cursor sharing=similar lets oracle set up different plans for different queries (all of which look the same after binding!) if the bind variable values could change the plan.  Consider:



ops$tkyte@ORA10GR1> create table t
  2  as
  3  select 99 id, a.*
  4    from all_objects a;
Table created.

ops$tkyte@ORA10GR1> update t set id = 1 where rownum = 1;
1 row updated.

ops$tkyte@ORA10GR1> create index t_idx on t(id);
Index created.

ops$tkyte@ORA10GR1> begin
  2          dbms_stats.gather_table_stats
  3          ( user, 'T',
  4            method_opt => 'for all indexed columns',
  5            cascade    => TRUE );
  6  end;
  7  /

PL/SQL procedure successfully completed.

<b>very skewed data in ID, stats gathered, ID is known to be skewed.  No column information on *any* other column, just ID</b>


ops$tkyte@ORA10GR1> set autotrace traceonly explain
ops$tkyte@ORA10GR1> select * from t where id = 1;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=96)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card=1 Bytes=96)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)



ops$tkyte@ORA10GR1> select * from t where id = 99;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=161 Card=47841 Bytes=4592736)
   1    0   TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=161 Card=47841 Bytes=4592736)

<b>that shows that the optimizer is influenced by the value used against ID, plans will change, but:</b>

ops$tkyte@ORA10GR1> select * from t where owner = 'BIG_TABLE';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=159 Card=478 Bytes=45888)
   1    0   TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=159 Card=478 Bytes=45888)



ops$tkyte@ORA10GR1> select * from t where owner = 'SYS';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=159 Card=478 Bytes=45888)
   1    0   TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=159 Card=478 Bytes=45888)

<b>In my case, big_table owned 4 rows, sys owned 21,534.  the optimizer is blind to owner, changing its value WON'T change plans.  Hence:</b>

ops$tkyte@ORA10GR1> set autotrace off
ops$tkyte@ORA10GR1>


ops$tkyte@ORA10GR1> alter session set cursor_sharing=similar;

Session altered.

ops$tkyte@ORA10GR1> select sql_text from v$sql where sql_text like 'select * from t look_for_me where %';

no rows selected

ops$tkyte@ORA10GR1> set autotrace traceonly statistics;
ops$tkyte@ORA10GR1> select * from t look_for_me where id = 1 and owner = 'BIG_TABLE';

no rows selected


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
       1033  bytes sent via SQL*Net to client
        377  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

ops$tkyte@ORA10GR1> select * from t look_for_me where id = 1 and owner = 'SYS';


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1261  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte@ORA10GR1> set autotrace off
ops$tkyte@ORA10GR1> select sql_text from v$sql where sql_text like 'select * from t look_for_me where %';

SQL_TEXT
-------------------------------------------------------------------------------------------------------------------------
select * from t look_for_me where id = :"SYS_B_0" and owner = :"SYS_B_1"

<b>that set up one plan -- even though owner changed, one plan -- because ID stayed the same.  But:</b>

ops$tkyte@ORA10GR1> set autotrace traceonly statistics;
ops$tkyte@ORA10GR1> select * from t look_for_me where id = 99 and owner = 'BIG_TABLE';


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        685  consistent gets
          0  physical reads
          0  redo size
       1517  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

ops$tkyte@ORA10GR1> select * from t look_for_me where id = 99 and owner = 'SYS';

21533 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2100  consistent gets
          0  physical reads
          0  redo size
    1040718  bytes sent via SQL*Net to client
      16297  bytes received via SQL*Net from client
       1437  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      21533  rows processed

ops$tkyte@ORA10GR1> set autotrace off
ops$tkyte@ORA10GR1> select sql_text from v$sql where sql_text like 'select * from t look_for_me where %';

SQL_TEXT
-------------------------------------------------------------------------------------------------------------------------
select * from t look_for_me where id = :"SYS_B_0" and owner = :"SYS_B_1"
select * from t look_for_me where id = :"SYS_B_0" and owner = :"SYS_B_1"

<b>Now we have another plan - because the only queries that can use the "first" plan are those with ID=1 bind variable values (that is the *similar*).  sys_b_0 would have to be 1 to use that plan, the 1 was added to the "signature" of the query.

The second plan (which is a full scan by the way, you can verify with sql_trace if you like -- i did :) has sys_b_0 = 99, anyone using 99 - can use that plan.

sys_b_1 can be *any* value</b>

ops$tkyte@ORA10GR1> set autotrace traceonly statistics;
ops$tkyte@ORA10GR1> select * from t look_for_me where id = 0 and owner = 'BIG_TABLE';

no rows selected


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
       1033  bytes sent via SQL*Net to client
        377  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

ops$tkyte@ORA10GR1> select * from t look_for_me where id = 0 and owner = 'SYS';

no rows selected


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
       1033  bytes sent via SQL*Net to client
        377  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

ops$tkyte@ORA10GR1> set autotrace off
ops$tkyte@ORA10GR1> select sql_text from v$sql where sql_text like 'select * from t look_for_me where %';

SQL_TEXT
-------------------------------------------------------------------------------------------------------------------------
select * from t look_for_me where id = :"SYS_B_0" and owner = :"SYS_B_1"
select * from t look_for_me where id = :"SYS_B_0" and owner = :"SYS_B_1"
select * from t look_for_me where id = :"SYS_B_0" and owner = :"SYS_B_1"

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

Session altered.

<b>A third plan, a third value for sys_b_0.  And so on.  </b>

In this case, similar will generate a new child cursor for each unique ID value -- but not for each unique owner value.  If ID has sufficiently small values, similar will work well to reduce the hard parsing in the system AS WELL AS reduce the number of child cursors.

If ID has a large range of values, well, you see what happens, a child cursor will be created. 

casting

September 01, 2005 - 9:00 am UTC

Reviewer: A reader

How to explain the following:

declare
l_theCursor integer default dbms_sql.open_cursor;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
begin
dbms_output.put_line( 'With Cursor Sharing and Casting:' );
execute immediate 'alter session set cursor_sharing=force';
dbms_sql.parse( l_theCursor,
'select cast(substr( object_name, 1, 6 ) as varchar2(5)) c1,
cast(55 as number(2)) c2,
cast(''Hello World'' as varchar2(5)) c3
from all_objects a',
dbms_sql.native );
dbms_sql.describe_columns( l_theCursor,
l_colCnt, l_descTbl );
for i in 1 .. l_colCnt loop
dbms_output.put_line( 'Column ' ||
l_descTbl(i).col_name ||
' has a length of ' ||
l_descTbl(i).col_max_len ) ;
end loop;
dbms_sql.close_cursor(l_theCursor);
execute immediate 'alter session set cursor_sharing=exact';
end;
/

With Cursor Sharing and Casting:
Column C1 has a length of 5
Column C2 has a length of 22
Column C3 has a length of 5

PL/SQL procedure successfully completed.

?


Tom Kyte

Followup  

September 01, 2005 - 3:38 pm UTC

what is unexplainable about it? You used cast, not substr.

Still some doubt...

September 01, 2005 - 9:44 am UTC

Reviewer: A reader from India

Tom,
Perfect got it till now...Just correct me if i am wrong.
1.With the similar option atleast one of the literals in the where clause has to be same to reuse the same cursor
whereas the "force" will irespective of literals use the same cursor, so a terrible execution plan ?
2.Also in the example you just showed above if the cursor sharing was exact and the id column had a lots of ranges say from 1 to 1000 then for each execution it has to open a new child cursor(if i dont had the bind variables in place) which would have been even terrible than the force or similar option in terms of parsing.
3.Does the cursor_sharing really take effect if my optimizer is RULE.

Thanks in Advance

Tom Kyte

Followup  

September 01, 2005 - 3:44 pm UTC

1) nope, not at all.

with similar, the optimizer looks at all of the literals it just replaced with binds.

Let's say there are 5 literals replaced with binds.

The optimizer says "2 of them could cause plan changes with different values, 3 will not". Then, the optimizer will ADD those 2 bind values to the "signature" of the plan, eg:

select * from t where a = 1 and b = 2 and c = 3 and d = 4 and e = 5;

a and b are "skewed" and will change the plan with different values. c, d, e are not and will not change the plan.

then ANY VALUE of c, d, e will reuse a plan for the same values of a, b.

That is:

where a = 1 and b = 2 and c = <ANYTHING> and d = <ANYTHING> and e= <ANYTHING>

will share a plan, but:

where a = -1 and b = -2 and c = <ANYTHING> and d = <ANYTHING> and e= <ANYTHING>

will not. for each A,B - there will be a new child cursor set up.


Now, the optimizer sees that same query and this time, finds a,b,c,d,e are not skewed. then:

where a = <anything> and b = <anything> and c = <ANYTHING> and d = <ANYTHING> and e= <ANYTHING>

will share the plan.


2) cursor sharing = exact, would have a separate plan for each unique query.

3) sure it does, it still "auto binds"


unexplainable about casting

September 02, 2005 - 7:23 am UTC

Reviewer: A reader

well, 

SQL> select sql_text from v$sql where sql_text like 'select cast%all_objects a';

gives

select cast(substr( object_name, :"SYS_B_0", :"SYS_B_1" ) as varchar2(:"SYS_B_2")) c1,  cast(:"SYS_B_3" as number(:"SYS_B_4")) c2,  cast(:"SYS_B_5" as varchar2(:"SYS_B_6")) c3  from all_objects a

so was :"SYS_B_2" evaluated to "5" when calling dbms_sql.describe_columns ?

if so, can't 
Richard  from Buckinghamshire, UK 
just use
select column1 from myTable where column2 = cast('abcd1234' as varchar2(8))
to achieve his aim "Oracle knowing the bind variable size." ?

 

Tom Kyte

Followup  

September 03, 2005 - 7:00 am UTC

but it wasn't really a "bind", it was internally marked as a "non bind, bind" ;)

confusing -- but look at what happens with "select * from dual order by 1", the 1 will be "bound" (but it cannot be, so it is a non-bind bind)


But at the end of the day, my point was turning on cursor_sharing force|exact will cause a change in behaviour of existing applications, it is not something to just be turned on, it should only be turned on for those applications that have the bug in them (that of not being bound)

Yes, you can "fix it" after turning on cursor sharing force/similar (the length issue), but the point was "it'll happen to your existing code, that now needs to be fixed"

now i feel exact has a problem...confused

September 02, 2005 - 8:28 am UTC

Reviewer: A reader from India

Dear Tom,
As you say "cursor sharing = exact, would have a separate plan for each unique query".Agreed.I am just curious about the cursors, will it open a seperate child cursor for each unique query or will it share a single cursor if i am explicity specifying binds like
select a from t where a=:a1 and b=:b1;
and assume that a and b are highly skewed.

Thanks


Tom Kyte

Followup  

September 03, 2005 - 7:06 am UTC

with cursor sharing = exact, given that the optimizer environments are the same for all sessions and everything else is the same (eg: table T means the same thing to all users), then:

select a from t where a = :a and b = :b1;

would be hard parsed once (and the plan put into place) and all others would use the same plan when the soft parsed.

There would be one plan. Oracle would use bind variable peeking the first time it was hard parsed, use those bind variable values to decide on the plan and everyone would use that plan.

September 05, 2005 - 3:49 am UTC

Reviewer: A reader from India

Thanks Tom for all your time,just a final question relating to your previous followup.
eg.First Time i execute
select a from t where a=100 and b=100
This query does a index scan and its plan will be stored.
Second time i come and execute
select a from t where a=200 and b=200
For this query i know that it is better to do a FTS as there are lots of rows with a=200 values.
Now optimizer finds that the plan is already stored so instead of doing a FTS will it do a index scan??
(I have already tried this but it behaves properly and does a FTS)
Please explain me with cursor_sharing=exact and plan already stored why did chose to use a diffrent execution plan?







Tom Kyte

Followup  

September 05, 2005 - 10:33 am UTC

with cursor_sharing=exact, the plan for the second query was "not already stored", that query would be hard parsed and a plan generated just for it.

September 05, 2005 - 10:54 am UTC

Reviewer: A reader from India

Quote
"with cursor_sharing=exact, the plan for the second query was not already stored that query would be hard parsed and a plan generated just for it"
I got that the plan was regenerated the second time but i think it wont be hard parsed just soft parsed as it need not do a syntax and a semantic check as i was using binds and it will find it in shared pool.
select a from t where a=:a1 and b=:b1;
right???


Tom Kyte

Followup  

September 05, 2005 - 11:42 am UTC

it has to be hard parsed, it is not in the shared pool.

You did not give me an example with binds.

A soft parse includes:

a) syntax check
b) semantic check
c) hashing of SQL statement
d) lookup in shared pool by hash to find the same statement
e) a check of the statement(s) found in the shared pool to ensure they are
actually the same (select * from t - that must be resolved to "from OWNER.T" in
step (b) and then we must ensure what we find in the shared pool matches
f) a check of the statement(s) found in the shared pool to ensure we can use it
after seeing they are in fact the same. need to make sure the optimizer
environment is the same and all

A hard parse adds to that

g) optimization/row source generation


(see a soft parse is really doing quite a bit isn't it)....


If you submit the query with binds, then the first plan will be used over and over assuming a-f are satisfied.

September 05, 2005 - 12:04 pm UTC

Reviewer: A reader from India

Tom,Actually i got it a bit wrong sorry for that.
As i read in one of your oracle magazines columns parsing has 4 steps
syntax check,semantic check,optimization and row source generation.The first two steps is mandatory thats what a soft parse is and if its finds that particular query in the shared pool it will skip the next two steps thus avaoiding a hard parse.
Now,to the original question with cursor_sharing=exact and query with binds like "select a from t where a=:a1 and b=:b1;" already present in shared pool has to do a hard parse(as you said) for each and every execution(depending on the values for a and b as i said some need to do a FTS and some index scan) then where does the real use of value EXACT comes in??Is it that it does not create the child cursors so that i can save in the memory.pls explain

Tom Kyte

Followup  

September 05, 2005 - 12:57 pm UTC

IT DOES NOT do a hard parse each time.


IF you submit the query "select a from t where a=:a1 and b=:b1;" and a-f above are met, then it will be a soft parse, NOT a hard parse.

September 06, 2005 - 4:53 am UTC

Reviewer: A reader from India

oracle still has to optimize it and generate a FRESH plan so as to decide whether i am better off with a FTS or a index scan, so if it generates a new plan doesnt that add to the hard parse??

Tom Kyte

Followup  

September 06, 2005 - 8:31 am UTC

it does not re-hard parse here, this is what I'm trying to say.

If you submit

select * from t where a = ? and b = ?;

over and over and over, one plan -- just one plan.

September 06, 2005 - 9:10 am UTC

Reviewer: A reader from India

Tom,Now i know you might have definately getting frustrated by now but it is just that you are not getting what i am trying to say.I will give it a try one more time .
"select * from t where a = ? and b = ?;
over and over and over, one plan -- just one plan."
If thats the case then how does it decide that i have to perform this particular action(FTS or index scan) depending on the selectivity of the values present in that particular table.In thats case if the first plan was a FTS then even to find a single value out of 100 million rows will it not do a FTS???
Thanks



Tom Kyte

Followup  

September 06, 2005 - 9:42 am UTC

it did the optimization using bind variable peeking (assuming 9i and above) the first time and EITHER chose the full scan or range scan.

it will do either/or, but not both.

September 06, 2005 - 10:07 am UTC

Reviewer: A reader from India

ok got it,optimizer added the value depending on bind variable peeking to the signature of the query and thats where it decides what has to be done.
Thanks for your time.

dbms_advanced_rewrite.

April 16, 2006 - 6:02 pm UTC

Reviewer: Kashif from Reston, VA

Hi Tom -

I'm curious to know how you would approach tuning queries created by third-part apps, where you have absolutely no control over the SQL code, before the advent of this package. Indexing and generating fresh statistics only gets you so far...

Thanks.

Kashif

Tom Kyte

Followup  

April 16, 2006 - 8:23 pm UTC

well, before I would use this even I would be looking at a sql profile (the ability to gather statistics for a "query") - but that is new in 10g as well.

Prior to that - you had stored outlines and the ability to edit them. You would have to have an "alter session" to enable the stored outlines (possible perhaps via an on-logon trigger)

Seemed SQL in SGA

May 12, 2006 - 6:50 pm UTC

Reviewer: Hector Gabriel Ulloa Ligarius from Santiago of Chile

Hi Team Tom :)

I need to know, as they are similar sentences SQL in sga
you published a SQL, but not it encounter, this changed SQL the literal ones by values , to determine whatever of space saves placing to me cursor_sharing to force|similar|exact

Thank you very much TEAM TOM

Trouble using dbms_advanced_rewrite

July 10, 2006 - 6:23 pm UTC

Reviewer: Brandon Allen from Phoenix, AZ USA

Hi Tom,

I'm trying to use dbms_advanced_rewrite.declare_rewrite_equivalence to pass a RULE hint to a query. First some background on why I'm trying to do this:

I just upgraded from 8.0.6 to 10.2.0.2 and since doing so I've got two 20+ table outer joins coming from my Baan ERP application that are taking about 15 seconds to parse, compared to < 1 second in 8.0.6. If I pass a RULE hint, the parse is very fast and execution plan is the same, but I can't modify the application code to send a RULE hint so I'm trying to do it with dbms_advanced_rewrite.

Now the problem I'm hitting with dbms_advanced_rewrite:

If I don't change anything in the destination_stmt other than adding a RULE hint, I get the following error:

ORA-30394: source statement identical to the destination statement

So then I tried to come up with something else to change to make it work w/o changing the functionality, but everything I've tried so far just gives me the following errors:

ERROR at line 1:
ORA-30389: the source statement is not compatible with the destination statement
ORA-00907: missing right parenthesis
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 185
ORA-06512: at line 2

So far, I tried changing two things that resulted in the error above:

1) Changing the alias name on one of the tables
2) Adding a dummy table with no rows to the join

Any idea what I'm doing wrong? Is it by design that this procedure ignores changes to hints? Very unfortunate if so, because I'd imagine that modifying hints will be something that many people will want to do with it.

Thanks,
Brandon Allen






Tom Kyte

Followup  

July 11, 2006 - 7:45 pm UTC

why is this a problem - hard parse should happen once - and then never again??

meaning, only one parse takes 15, rest would take no time.

one would need an EXAMPLE to work with. An example we could all cut and paste and reproduce with (eg: whack out everything that is NOT relevant, make it tiny, make it small - you might even see what is wrong doing that...)

Wondered the same thing myself

July 12, 2006 - 4:37 pm UTC

Reviewer: Brandon Allen from Phoenix, AZ USA

You bring up a good point (as usual) and one that I was already struggling with (already have an SR open with Oracle Support).  I decided to go out on a limb with this new 10g environment and use sga_target.  This seems to not be working very well to me though because it seems to keep my shared pool too small and age things out even though v$sgastat says I have a ton of free memory as you can see below.  I also have no idea what the "KGH: NO ACCESS" is - I'm waiting on Oracle Support to answer that:

SQL> select * from v$sgastat where bytes > 50000000 order by 3;

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  KGLS heap                    52097288
large pool   free memory                  62080224
shared pool  sql area                    182582712
shared pool  free memory                 617090832
shared pool  KGH: NO ACCESS              779287232
             buffer_cache               1241513984

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
lock_sga                             boolean     TRUE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 2432M
sga_target                           big integer 2432M

So, I'm not sure exactly why, but I am having repeated hard parses of these problematic queries.  I've even tried keeping them pinned with dbms_shared_pool.keep but found that this procedure only keeps heap0 pinned, not heap6 (the execution plan).

Back to my problems with dbms_advanced_rewrite - here is an example showing that it doesn't allow you to simply change/add a hint:

SQL> begin
  2  sys.dbms_advanced_rewrite.declare_rewrite_equivalence(
  3     name=>'test',
  4     source_stmt=>'select * from t1',
  5     destination_stmt=>'select /*+ RULE */ * from t1',
  6     validate=>FALSE,
  7     rewrite_mode=>'TEXT_MATCH');
  8  end;
  9  /
begin
*
ERROR at line 1:
ORA-30394: source statement identical to the destination statement
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 185
ORA-06512: at line 2

Here is an example showing that if I tell Oracle to select from a different table than the one specified in the source_stmt, the rewrite procedure returns successfully, but then when I run the source_stmt, it does not get replaced by the destination_stmt:

SQL> select * from t1;

        C1
----------
         1

SQL> select * from t2;

        C1
----------
         2

SQL> begin
  2  sys.dbms_advanced_rewrite.declare_rewrite_equivalence(
  3     name=>'test',
  4     source_stmt=>'select * from t1',
  5     destination_stmt=>'select * from t2',
  6     validate=>FALSE,
  7     rewrite_mode=>'TEXT_MATCH');
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> select * from user_rewrite_equivalences;

OWNER                          NAME
------------------------------ ------------------------------
SOURCE_STMT
--------------------------------------------------------------------------------
DESTINATION_STMT                                                                 REWRITE_MO
-------------------------------------------------------------------------------- ----------
BAAN                           TEST
select * from t1
select * from t2                                                                 TEXT_MATCH


SQL> select * from t1;

        C1
----------
         1




As for an example where the procedure returns ORA-30389 ("source statement is not compatible with destination statement"), I was unable to reproduce that with a simple test case - so here is the example with the Baan query that is actually having the problem.  Note that the only differences between the source_stmt & destination_stmt are the addition of the RULE hint in the dest (this alone returns the "identical statement" error) and the alias of the 27th table has been changed from a0 to a1 (this causes the "incompatible statement" error):

SQL> begin
  2  sys.dbms_advanced_rewrite.declare_rewrite_equivalence(name=>'test',
  3  source_stmt=>'SELECT * FROM baan.ttccom010105 a,baan.ttccom015105 b,baan.ttccom001105 c,baan.ttcmcs009105 d,baan.ttcmcs064105 e
,baan.ttccom000100 f,baan.ttfacr001105 g,baan.ttccom015105 h,baan.ttfcmg003105 i,baan.ttfacr311105 j,baan.ttfacr310105 k,baan.ttcmcs
055105 l,baan.ttcmcs042107 m,baan.ttccom020105 n,baan.ttcmcs011105 o,baan.ttcmcs041105 p,baan.ttcmcs013105 q,baan.ttcmcs004105 r,baa
n.ttcmcs045105 s,baan.ttcmcs002105 t,baan.ttcmcs031105 u,baan.ttcmcs046105 v,baan.ttccom013105 w,baan.ttccom012105 x,baan.ttcmcs0101
05 y,baan.ttcmcs019105 z,baan.ttcmcs020105 a0 WHERE (1 <> 1 OR a.t$cnpa <> 4) AND z.t$ctit (+) = a.t$ctit AND y.t$ccty (+) = a.t$cct
y AND x.t$cuno (+) = a.t$cuno AND x.t$ccor (+) = a.t$ccor AND w.t$cuno (+) = a.t$cuno AND w.t$cdel (+) = a.t$cdel AND v.t$clan (+) =
 a.t$clan AND u.t$cbrn (+) = a.t$cbrn AND t.t$ccur (+) = a.t$ccur AND s.t$creg (+) = a.t$creg AND r.t$crte (+) = a.t$crte AND c.t$em
no (+) = a.t$crep AND q.t$cpay (+) = a.t$cpay AND p.t$cdec (+) = a.t$cdec AND o.t$ccrs (+) = a.t$ccrs AND n.t$suno (+) = a.t$ccsu AN
D m.t$cotp (+) = a.t$cotp AND l.t$cinm (+) = a.t$cinm AND k.t$mrem (+) = a.t$mrem AND j.t$mstm (+) = a.t$mstm AND i.t$paym (+) = a.t
$paym AND b.t$cuno (+) = a.t$cuno AND b.t$cban (+) = a.t$cban AND a0.t$cbad (+) = b.t$cbad AND h.t$cuno (+) = a.t$cuno AND h.t$cban
(+) = a.t$cban AND g.t$ficu (+) = a.t$cfcg AND f.t$ncmp (+) = a.t$iscn AND e.t$crat (+) = a.t$crat AND d.t$ccic (+) = a.t$ccic ORDER
 BY a.t$cuno',
  4  destination_stmt=>'SELECT /*+ RULE */ * FROM baan.ttccom010105 a,baan.ttccom015105 b,baan.ttccom001105 c,baan.ttcmcs009105 d,ba
an.ttcmcs064105 e,baan.ttccom000100 f,baan.ttfacr001105 g,baan.ttccom015105 h,baan.ttfcmg003105 i,baan.ttfacr311105 j,baan.ttfacr310
105 k,baan.ttcmcs055105 l,baan.ttcmcs042107 m,baan.ttccom020105 n,baan.ttcmcs011105 o,baan.ttcmcs041105 p,baan.ttcmcs013105 q,baan.t
tcmcs004105 r,baan.ttcmcs045105 s,baan.ttcmcs002105 t,baan.ttcmcs031105 u,baan.ttcmcs046105 v,baan.ttccom013105 w,baan.ttccom012105
x,baan.ttcmcs010105 y,baan.ttcmcs019105 z,baan.ttcmcs020105 a1 WHERE (1 <> 1 OR a.t$cnpa <> 4) AND z.t$ctit (+) = a.t$ctit AND y.t$c
cty (+) = a.t$ccty AND x.t$cuno (+) = a.t$cuno AND x.t$ccor (+) = a.t$ccor AND w.t$cuno (+) = a.t$cuno AND w.t$cdel (+) = a.t$cdel A
ND v.t$clan (+) = a.t$clan AND u.t$cbrn (+) = a.t$cbrn AND t.t$ccur (+) = a.t$ccur AND s.t$creg (+) = a.t$creg AND r.t$crte (+) = a.
t$crte AND c.t$emno (+) = a.t$crep AND q.t$cpay (+) = a.t$cpay AND p.t$cdec (+) = a.t$cdec AND o.t$ccrs (+) = a.t$ccrs AND n.t$suno
(+) = a.t$ccsu AND m.t$cotp (+) = a.t$cotp AND l.t$cinm (+) = a.t$cinm AND k.t$mrem (+) = a.t$mrem AND j.t$mstm (+) = a.t$mstm AND i
.t$paym (+) = a.t$paym AND b.t$cuno (+) = a.t$cuno AND b.t$cban (+) = a.t$cban AND a1.t$cbad (+) = b.t$cbad AND h.t$cuno (+) = a.t$c
uno AND h.t$cban (+) = a.t$cban AND g.t$ficu (+) = a.t$cfcg AND f.t$ncmp (+) = a.t$iscn AND e.t$crat (+) = a.t$crat AND d.t$ccic (+)
 = a.t$ccic ORDER BY a.t$cuno',
  5  validate=>FALSE, rewrite_mode=> 'TEXT_MATCH');
  6  end;
  7  /
begin
*
ERROR at line 1:
ORA-30389: the source statement is not compatible with the destination statement
ORA-00907: missing right parenthesis
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 185
ORA-06512: at line 2



 

Tom Kyte

Followup  

July 12, 2006 - 5:44 pm UTC

search for dbms_rewrite on this site - you'll see how I used a view...


second one (t1/t2) you probably did not set query_rewrite_integrity appropriately?

ops$tkyte@ORA10GR2> create table t1 as select 1 x from dual;

Table created.

ops$tkyte@ORA10GR2> create table t2 as select 2 x from dual;

Table created.

ops$tkyte@ORA10GR2> alter session set query_rewrite_integrity = trusted;

Session altered.

ops$tkyte@ORA10GR2> begin
  2          sys.dbms_advanced_rewrite.declare_rewrite_equivalence
  3          ( name             => 'DEMO_TIME',
  4            source_stmt      => 'select * from t1',
  5            destination_stmt => 'select * from t2',
  6            validate         => FALSE,
  7            rewrite_mode     => 'TEXT_MATCH' );
  8  end;
  9  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> select * from t1
  2  /

         X
----------
         2

ops$tkyte@ORA10GR2> select * from t1 for_real
  2  /

         X
----------
         1

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec sys.dbms_advanced_rewrite.drop_rewrite_equivalence( 'DEMO_TIME' );

PL/SQL procedure successfully completed.


in order to even look at the last bit - I would have to have a test case - AS SMALL AS POSSIBLE, I mean - really - as small as possible 

You were right (big shocker :-)

July 12, 2006 - 8:31 pm UTC

Reviewer: Brandon Allen from Phoenix, AZ USA

You were right about the query_rewrite_integrity - once I set that to trusted, my example of using dbms_advanced_rewrite to force the query of a different table worked perfectly.

As for the problem with dbms_advanced_rewrite on that big ugly Baan query, I haven't been able to reproduce the problem on a small example yet.

I think I'll change my focus to back to minimizing the hard parsing to begin with and I'm thinking a good place to start is with abandoning ASMM, setting sga_target=0 and going "old school" to see if that stops the premature shared pool aging and low gethitratio I'm seeing.

Thanks for your help!
Brandon

Tom Kyte

Followup  

July 12, 2006 - 8:55 pm UTC

ASSM won't affect parse times, or shared pool memory.

or did you mean "automatic memory management (AMM)"

Oh, the joy of acronyms

July 13, 2006 - 4:57 pm UTC

Reviewer: Brandon Allen from Phoenix, AZ USA

I said "ASMM", not ASSM - but probably not a wise choice on my part to use the acronym considering the ease of confusion - sorry about that. By ASMM, I was referring to "Automatic Shared Memory Management".

dbms_advanced_rewrite and bind variables

December 18, 2006 - 9:57 am UTC

Reviewer: Dusan from Czech republic

Hi Tom,

The dbms_advanced_rewrite is great but if we want to rewrite query which uses bind variables, it doesn't work.


Wrote file afiedt.buf

1 begin
2 sys.dbms_advanced_rewrite.declare_rewrite_equivalence
3 ( name => 'DEMO_TIME',
4 source_stmt => 'select x, y, sum(z) from t where x=:x group by x,
y',
5 destination_stmt => 'select * from v where x=:x',
6 validate => FALSE,
7 rewrite_mode => 'TEXT_MATCH' );
8* end;
sysadm@I5ETP801> /
begin
*
ERROR at line 1:
ORA-30353: expression not supported for query rewrite
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 176
ORA-06512: at line 2

Any idea how to rewrite this?

regards,

Dusan

January 17, 2007 - 3:43 pm UTC

Reviewer: Greg Norris from a place where the nuts hunt the squirrels

[The dbms_advanced_rewrite is great but if we want to rewrite query which uses bind variables, it doesn't work.]

Assuming that you have access to Metalink, DocID 392214.1 covers a possible workaround. It involves rewriting the query without specifying the where clause, using a rewrite mode of GENERAL. This can potentially cause other statements to be rewritten as well, so use caution if you decide to employ this method.

Note: I haven't attempted this, so no promises.

Note:392214.1 from Metalink - it works!

March 19, 2007 - 8:43 am UTC

Reviewer: Dusan from Czech rep.

I have used the workaround from Metalink note, it works. Nevertheless, we have to be carefull here, since this more general approach may cause other queries to be rewritten as well.

Thanks, Gregg!

valasekd@DBBAFIR> drop table t;

Table dropped.

Elapsed: 00:00:00.03
valasekd@DBBAFIR>
valasekd@DBBAFIR> create table t ( a number
2 ,b number
3 ,c number);

Table created.

Elapsed: 00:00:00.01
valasekd@DBBAFIR>
valasekd@DBBAFIR> insert into t values(1,1,1);

1 row created.

Elapsed: 00:00:00.00
valasekd@DBBAFIR> insert into t values(2,2,2);

1 row created.

Elapsed: 00:00:00.00
valasekd@DBBAFIR> drop table V;

Table dropped.

Elapsed: 00:00:00.03
valasekd@DBBAFIR>
valasekd@DBBAFIR> create table v ( a number
2 ,b number
3 ,c number);

Table created.

Elapsed: 00:00:00.03
valasekd@DBBAFIR>
valasekd@DBBAFIR> insert into v values(1,1,999999999);

1 row created.

Elapsed: 00:00:00.00
valasekd@DBBAFIR>
valasekd@DBBAFIR> begin
2 sys.dbms_advanced_rewrite.declare_rewrite_equivalence
3 ( name => 'V_as_T_equi'
4 ,source_stmt => 'select a, b, max(c) from t group by a,b'
5 ,destination_stmt => 'select * from v'
6 ,validate => FALSE
7 ,rewrite_mode => 'GENERAL' );
8 end;
9 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
valasekd@DBBAFIR>
valasekd@DBBAFIR> variable x number
valasekd@DBBAFIR>
valasekd@DBBAFIR> exec :x:=1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
valasekd@DBBAFIR>
valasekd@DBBAFIR> -- selected from table t
valasekd@DBBAFIR> select a, b, max(c) from t where a=:x group by a,b;

A B MAX(C)
--------- --------- ---------
1 1 1

Elapsed: 00:00:00.01
valasekd@DBBAFIR>
valasekd@DBBAFIR>
valasekd@DBBAFIR> alter session set query_rewrite_integrity =trusted;

Session altered.

Elapsed: 00:00:00.00
valasekd@DBBAFIR>
valasekd@DBBAFIR> -- selected from table v
valasekd@DBBAFIR> select a, b, max(c) from t where a=:x group by a,b;

A B MAX(C)
--------- --------- ---------
1 1 999999999

Elapsed: 00:00:00.01
valasekd@DBBAFIR>
valasekd@DBBAFIR> alter session set query_rewrite_integrity =enforced;

Session altered.

Elapsed: 00:00:00.00
valasekd@DBBAFIR> -- selected from table t
valasekd@DBBAFIR> select a, b, max(c) from t where a=:x group by a,b;

A B MAX(C)
--------- --------- ---------
1 1 1

Elapsed: 00:00:00.00
valasekd@DBBAFIR>

materialized views and cursor_sharing=similar

September 17, 2007 - 12:13 pm UTC

Reviewer: Susan from Newton, MA

Tom,

Because our application is using similar literals we thought we try using the SIMILAR setting and see if it helped with SGA utilization. We're working on getting the code fixed to use bind variables.

However after changing from EXACT we ran into errors with our materialized view refreshes.

ORA-12048: error encountered while refreshing materialized view
ORA-01008: not all variables bound

Switching back to EXACT resolved the error. I couldn't find any info on google or metalink on this and I was wondering if you had any thoughts.

Thanks.

Tom Kyte

Followup  

September 18, 2007 - 3:32 pm UTC

I would never ever in a million years use cursor_sharing=similar|force AT THE INSTANCE LEVEL.

You may use this - temporarily - in a buggy application whilst the developers fix this priority one security, performance and scalability bug.

Never in the init.ora
Only in the application(s) that need it while they are fixing the code.


and "our application is using similar literals" - that doesn't compute. Literals cannot be "similar", they are either

a) the same
b) different

cursor_sharing=similar is a mode whereby Oracle decides what is safe to bind and what is not. Consider:

ops$tkyte%ORA10GR2> /*
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop table t;
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t
ops$tkyte%ORA10GR2> as
ops$tkyte%ORA10GR2> select case when rownum = 1 then 1 else 99 end id, a.*
ops$tkyte%ORA10GR2>   from all_objects a
ops$tkyte%ORA10GR2> /
ops$tkyte%ORA10GR2> create index t_idx on t(id);
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt=>'for all indexed columns size 254' );
ops$tkyte%ORA10GR2> */
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter session set cursor_sharing=force;

Session altered.

ops$tkyte%ORA10GR2> alter system flush shared_pool;

System altered.

ops$tkyte%ORA10GR2> declare
  2          c sys_refcursor;
  3  begin
  4          for i in 1 .. 100
  5          loop
  6                  open c for 'select /* 1 look for me vary object_id */ * from t where object_id = ' || i;
  7                  close c;
  8                  open c for 'select /* 2 look for me vary id */ * from t where id = ' || i;
  9                  close c;
 10                  open c for 'select /* 3 look for me vary id, object_id constant */ * from t where object_id = 0 and id = ' || i;
 11                  close c;
 12                  open c for 'select /* 4 look for me vary object id, id constant */ * from t where id = 0 and object_id = ' || i;
 13                  close c;
 14          end loop;
 15  end;
 16  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select sql_text, count(*) from v$sql where sql_text like 'select /* _ look for me%' group by sql_text order by sql_text;

SQL_TEXT                                             COUNT(*)
-------------------------------------------------- ----------
select /* 1 look for me vary object_id */ * from t          1
 where object_id = :"SYS_B_0"

select /* 2 look for me vary id */ * from t where           1
id = :"SYS_B_0"

select /* 3 look for me vary id, object_id constan          1
t */ * from t where object_id = :"SYS_B_0" and id
= :"SYS_B_1"

select /* 4 look for me vary object id, id constan          1
t */ * from t where id = :"SYS_B_0" and object_id
= :"SYS_B_1"


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

Session altered.

ops$tkyte%ORA10GR2> alter system flush shared_pool;

System altered.

ops$tkyte%ORA10GR2> declare
  2          c sys_refcursor;
  3  begin
  4          for i in 1 .. 100
  5          loop
  6                  open c for 'select /* 1 look for me vary object_id */ * from t where object_id = ' || i;
  7                  close c;
  8                  open c for 'select /* 2 look for me vary id */ * from t where id = ' || i;
  9                  close c;
 10                  open c for 'select /* 3 look for me vary id, object_id constant */ * from t where object_id = 0 and id = ' || i;
 11                  close c;
 12                  open c for 'select /* 4 look for me vary object id, id constant */ * from t where id = 0 and object_id = ' || i;
 13                  close c;
 14          end loop;
 15  end;
 16  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select sql_text, count(*) from v$sql where sql_text like 'select /* _ look for me%' group by sql_text order by sql_text;

SQL_TEXT                                             COUNT(*)
-------------------------------------------------- ----------
select /* 1 look for me vary object_id */ * from t          1
 where object_id = :"SYS_B_0"

select /* 2 look for me vary id */ * from t where         100
id = :"SYS_B_0"

select /* 3 look for me vary id, object_id constan        100
t */ * from t where object_id = :"SYS_B_0" and id
= :"SYS_B_1"

select /* 4 look for me vary object id, id constan          1
t */ * from t where id = :"SYS_B_0" and object_id
= :"SYS_B_1"




Note how that when we have cursor_sharing=force, there is one copy - the first guy into the shared pool picks the plan for everyone.

where id = 1 - that should use an index
where id = 99 - that should not

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

Session altered.

ops$tkyte%ORA10GR2> select * from t where id = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 470836197

------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU
------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    97 |     2   (0
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    97 |     2   (0
|*  2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0
------------------------------------------------------------------------

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

   2 - access("ID"=1)

ops$tkyte%ORA10GR2> select * from t where id = 99;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 50192 |  4754K|   235   (4)| 00:00:02
|*  1 |  TABLE ACCESS FULL| T    | 50192 |  4754K|   235   (4)| 00:00:02
------------------------------------------------------------------------

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

   1 - filter("ID"=99)



cursor sharing=force does not care about that, it just says

"rip out ALL literals, replace them all with BINDS, then optimize"

hence - in this case - all of the "where id = ?" queries - all 100 would use an index range scan (because we hard parsed id = 1 first)


now, cursor sharing = similar - anytime we varied the ID= values - oracle did a new hard parse. similar says:

"rip out all literals, replace with binds but before we continue - peek at the statistics in the dictionary and see if any stats exist that would cause us to possibly pick different plans given different inputs - if so - make sure we only reuse plans that had the SAME inputs for that bind"

that is why when we vary id = values, we get 100 copies of the sql in the shared pool - it was NOT SAFE TO BIND against id = - because different plans are expected. It is safe to bind against object_id because we have nothing in the dictionary that would cause us to come up with a different plan for different inputs - binding object_id is SAFE, binding ID is not.



but in your case, NEVER set cursor_sharing = force|similar at the instance level, only set it at the SESSION LEVEL and then only in the buggy applications while the coders fix them.

dbma_advanced_rewrite

November 29, 2007 - 9:38 am UTC

Reviewer: A reader

Tom,
This posting is not related to the original question. If you can take a quick look to see what is wrong here, I would be grateful. As you can see after a rewrite, select from t1 produces a strange date.

Thanks

SQL> drop table t1;

Table dropped.

SQL> create table t1 (c1 date);

Table created.

SQL> begin
  2  sys.dbms_advanced_rewrite.drop_rewrite_equivalence( 'DEMO_TIME' );
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select * from t1;

no rows selected

SQL> select sysdate from dual;

SYSDATE
---------
29-NOV-07

SQL> begin
  2            sys.dbms_advanced_rewrite.declare_rewrite_equivalence
  3            ( name             => 'DEMO_TIME',
  4              source_stmt      => 'select * from t1',
  5              destination_stmt => 'select sysdate from dual',
  6              validate         => FALSE,
  7              rewrite_mode     => 'TEXT_MATCH' );
  8    end;
  9   /

PL/SQL procedure successfully completed.

SQL> select * from t1;

C1
---------
00-DECEMB

SQL> begin
  2  sys.dbms_advanced_rewrite.drop_rewrite_equivalence( 'DEMO_TIME' );
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select * from t1;

no rows selected

SQL> select sysdate from dual;

SYSDATE
---------
29-NOV-07

SQL>

Tom Kyte

Followup  

November 29, 2007 - 6:48 pm UTC

do you have access to support to file a bug?

dbms_advanced_rewrite

November 30, 2007 - 11:15 am UTC

Reviewer: A reader

Thanks Tom. Yes, I have access to support to file a bug. I just wanted to make sure that it is actually a bug not an expected behavior. I will open a SR now.

dbms_advanced_rewrite

December 04, 2007 - 9:08 am UTC

Reviewer: A reader

Here is an update on the SR that I opened.

Support told me that if the rewrite is written as 'select to_date(sysdate,<format mask>) from dual', then the problem does not show up. They said it was happening because of conversion. I verified this and it works.

My next question was where was conversion happening? They responded:
=========================
Basically dual table is having single dummy column of varchar type and so whatever data requested is given as it does implicit conversion when executing the query normally. But here when the query is used for advanced rewrite, the conversion is not happening and thats why the issue. The rewrite routine also checks that the datatype of the source and destination statement to be same. So when using to_date, the mismatch will not happen and gives the correct result.
=========================

The logic seems to be correct so I have closed the SR. There are numerous times when we do "select sysdate from dual" without thinking that implicit conversion is taking place behind the scenes. You have also pointed this many times that implicit conversion can cause strange behavior. This seems to be a good example.

Thanks

Trailing spaces when using Cursor Sharing=SIMILAR

November 25, 2008 - 3:44 am UTC

Reviewer: Ram from India

Tom,

we are facing the trailing spaces problem when we execute query select 'Y' from dual from the web server which has windows 2000 server as operating system.But it is working fine when we execute the query from web server which has Windows XP or Windows 2003 server as operating system.

Can you tell us some temporary solution.we are working on the code changes to use binding variables.

Thanks and Regards,
Ram
Tom Kyte

Followup  

November 25, 2008 - 11:46 am UTC

I'm not aware of any "problems" in that regard.

I can say you should just replace:

select 'y' from dual;

with

[this space left intentionally blank]


or better yet, if this is just a 'test to see if connection is good', do something actually USEFUL like

begin dbms_session.set_identifier( :my_unique_sessionid ); end;

if that statement succeeds, connection is OK.


(read about trim() function)

Cursor sharing similar vs bind variable peeking

January 27, 2009 - 6:36 am UTC

Reviewer: Stephen O'Donnell

Tom,

This is an interesting thread and got me thinking about bind variable peeking, and your story about how the database runs slow, but only when its raining on a Monday ;-)

If I have cursor_sharing = similar, then Oracle sort of bind variable peeks each time it receives a similar query, so if I have skewed data it will tend to get good plans always, but at the expense of many child cursors.

If I use cursor_sharing=exact, then Oracle will only ever peek once and then I may get a sub-optimal plan for subsequent values.

It almost sounds like cursor_sharing = similar has an advantage, in that it will reuse plans for columns without skewed data and do the right thing for non-skewed, while 'exact' would always choose the same plan and hence potentially pick a bad plan.

I know that technically the developer would 'hardcode' the skewed values and not bind them to get around this, but what are your thoughts on 'advantage' of cursor_sharing = similar I mentioned above, or am I getting it totally wrong?
Tom Kyte

Followup  

January 28, 2009 - 2:49 pm UTC

cursor_sharing similar or force is something you use when *no binds are ever used anywhere and you are trying to get the database to bind* and you cannot modify the code (but you have started to force the owner of the code to fix the security bug....)

Otherwise you use binds when you mean to and literals when you don't mean to.


If you do not bind, if you use string concatenation, you are subject to SQL Injection - period (regardless of cursor sharing setting). Any code that just concatenates inputs from the outside (parameters - any parameters - to your routine) must be reviewed by 5 people that do not like you at all (so they are critical of your code) and then reviewed by 5 people that don't like them (to be more critical) to ensure it is safe.


Bind variable peeking with in-clause

January 28, 2009 - 3:19 pm UTC

Reviewer: Salman Syed from Pittsburgh, PA

Tom,

How does cursor sharing work for statements with an in-clause in the select statement? For example, will the following two statement result in SQL reuse:

select * from employee where employee_id in (1,2)
select * from employee where employee_id in (1,2,3)?


Tom Kyte

Followup  

January 30, 2009 - 12:10 pm UTC

they are not the same exact string


these queries will NOT be shared:

select * from dual;
Select * from dual;
Select *
from dual;

that will result in three entries in v$sql. The strings must be *exact*

and since you haven't any binds whatsoever, bind peeking as referenced in your subject does not come into play.

Further clarification

January 30, 2009 - 1:32 pm UTC

Reviewer: Salman Syed from Pittsburgh, PA

My apologies.

What we do is we use your str2tbl function. So we bind values as a string to the input of the function.

So the query would actually be
select * from employee where employee_id in (select * from table(str2tbl(':bind_val'))

And the two separate binds would be '1,2' and '1,2,3'.

Would cursor sharing work for this?

Also, we tested the other option you present where a view is created as a table using a sys_context variable. And then the query simply becomes select * from employee where employee_id in (select * from IN_LIST). We tried both in our queries and the performance of the IN_LIST method was slower.

Have you compared performance or do you have suggestions as to when one would be better than the other?
Tom Kyte

Followup  

January 30, 2009 - 4:21 pm UTC

see
http://asktom.oracle.com/Misc/varying-in-lists.html

no, cursor_sharing = force|similar would not let the two be shared, the would result in different strings after the literal substitution. One would have 2 and one would have 3 binds.

DBMS_ADVANCED_REWRITE and literals

March 26, 2010 - 11:25 pm UTC

Reviewer: Ken from Delray Beach

I came into a system using cursor sharing = force since it does not use binds variables and I am pressuring the third party to recode it. With that said the application sumbits some statements wrapped within begin/end blocks with hard coded literals which based on seeing literals in the shared pool and searching on metalink bypasses the cursor sharing=force setting.

I need to create an outline on this query but one of the hard coded values is runid which increments regularly. I don't want to create an outline everytime a new run is created. I thought about DBMS_ADVANCED_REWRITE but i suspect i'll have the same issue regarding bind variables.

1. Is there a way to have DBMS_ADVANCED_REWRITE convert a non binded statement to a one with binds?

2. Is there a way i can rewrite all the lines of a statement except the one with literal and have the rewrite use the that line as well?

The simplified version of the query would look like this.

select *
from t1
where x=1
and y=2 -- this literal changes between runs, others don't
and z=3

Thank you.
Tom Kyte

Followup  

March 27, 2010 - 8:25 am UTC

1) no

2) not unless you can change the code...

PARSE count with FORCE.

March 30, 2010 - 12:40 am UTC

Reviewer: lalu.

Hi Tom,

CURSOR_SHARING=FORCE is set in my database.
I was just taking some application module trace and found something in the trace file:

select distinct rule.*
from
cust_customer cust, crm_credit_control_rule rule where (cust.recid =
:"SYS_B_0" or nvl(cust.PARENTCUSTOMERID,:"SYS_B_1") = :"SYS_B_2" )and
rule.CUSTOMERTYPE = cust.CUSTOMERTYPE and rule.CUSTOMERCATEGORY =
cust.CATEGORYID


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 220 0.02 0.04 0 0 0 0
Execute 220 0.33 0.25 0 0 0 0
Fetch 220 35.03 34.25 0 479160 0 218
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 660 35.38 34.55 0 479160 0 218

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 60 (IBOSS2)

Rows Row Source Operation
------- ---------------------------------------------------
1 HASH UNIQUE (cr=2178 pr=0 pw=0 time=158684 us)
1 MERGE JOIN (cr=2178 pr=0 pw=0 time=157681 us)
4 TABLE ACCESS BY INDEX ROWID CRM_CREDIT_CONTROL_RULE (cr=2 pr=0 pw=0 time=200 us)
4 INDEX FULL SCAN UNIQUE_TYPE_CATEGORY (cr=1 pr=0 pw=0 time=42 us)(object id 57196)
1 SORT JOIN (cr=2176 pr=0 pw=0 time=157477 us)
1 TABLE ACCESS FULL CUST_CUSTOMER (cr=2176 pr=0 pw=0 time=157356 us)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 HASH (UNIQUE)
1 MERGE JOIN
4 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'CRM_CREDIT_CONTROL_RULE' (TABLE)
4 INDEX MODE: ANALYZED (FULL SCAN) OF
'UNIQUE_TYPE_CATEGORY' (INDEX (UNIQUE))
1 SORT (JOIN)
1 TABLE ACCESS MODE: ANALYZED (FULL) OF 'CUST_CUSTOMER'
(TABLE)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 652 0.00 0.00
SQL*Net message from client 652 61.13 1735.72
SQL*Net break/reset to client 424 0.00 0.12
library cache lock 5 0.00 0.00
gc cr block 2-way 21 0.00 0.01
gc current block 2-way 6 0.00 0.00
********************************************************************************

The PARSE count shows 220, same as execution and fetch count.
Is it the expected behave.
The query comes from a pl/sql procedure.

Thanks.
lalu.

Tom Kyte

Followup  

April 05, 2010 - 10:27 am UTC

there is precisely one way to affect the parse count:

that is to modify the application.


period, nothing else will do it, the ONLY THING cursor_sharing=force/similar can do is turn a hard parse problem into a SOFT PARSE PROBLEM.


cursor_sharing = force/similar will never

a) reduce the parse count
b) remove the huge security bug (sql injection) you have because you are not binding.

it can be used only as a temporary stop gap "use it while the developers actually FIX THE CODE".


Time to get into that plsql code and ask two very important questions:

a) why are you using dynamic sql - do you really need to (almost surely the answer is "NO") and you must be using dynamic sql because there is no other way in plsql to not use binds correctly.

b) why are you NOT using bind variables - it would make the code EASIER to code in the first place.


and then correct the code by using static sql (best) or binds in the dynamic sql (second best). either way - parse counts would go down.

March 30, 2010 - 12:43 am UTC

Reviewer: lalu.

Hi Tom,

Apologize for coming up again.
The trace shows a high query mode fetch, where as there is a test environment and almost zero load on it.

Thanks.
lalu.
Tom Kyte

Followup  

April 05, 2010 - 10:30 am UTC

read consistency, you are querying a table that is undergoing heavy modification. Instead of blocking your query like other databases might, we read about that locked data in the undo segments - the additional IOs are reads to undo to undo the modifications to the blocks.

Normal, and to be expected. You don't see it in test since in test - there are no other users modifying the data.

Hi Lula

April 03, 2010 - 9:10 am UTC

Reviewer: Lasse Jenssen from Norway

This is normal and expected. The parse section in trace file shows both hard and soft parses. Although using bind, you might end up soft parsing (if not writing code reusing your cursors).

/Lasse

Quick question on dbms_advanced_rewrite

August 12, 2010 - 8:38 am UTC

Reviewer: Ian from London

Tom

Coming back to dbms_advanced_rewrite for a second - if you use certain constructs in the SQL you get an ORA-30353: expression not supported for query rewrite
Cause: The select clause referenced UID, USER, ROWNUM, SYSDATE, CURRENT_TIMESTAMP, MAXVALUE, a sequence number, a bind variable, correlation variable, a set result,a trigger return variable, a parallel table queue column, collection iterator, etc.

Just wondering why these restrictions? It makes what would be an incredibly useful feature somewhat less useful that it might be. Particularly the restriction on sysdate and binds.

Could you shed some light?

Regards

Ian

CURSOR_SHARING

September 30, 2010 - 9:09 pm UTC

Reviewer: A reader

Hi Tom,

Can you please tell me what is the disadvantage of using Cursor_sharing as "similar" or "force"? I mean if those settings have so many advantages why don't we use "similar" or "exact" every time instead of default "exact"?
Tom Kyte

Followup  

October 01, 2010 - 5:48 am UTC

They do not have so many advantages. They are a bandage you can use one a very sick application during the period of time you are fixing the security hole it has.

cursor sharing = force/similar implement an "auto binder" of sorts - it'll take a query in the form of "select * from emp where empno = 1234" and turn it automagically into "select * from emp where empno = :x"

For an application that was written correctly - all this does is make it take longer to execute (that is a bad thing). You've increased the code path.

For an application that purposely used bind variables (it happens - it should happen from time to time - it is correct every now and then) you have defeated their goal without giving them a chance.

For an application that counts on the database knowing things - like lengths of attributes selected in a query, precisions and scale - you have broken them. Consider:

ops$tkyte%ORA11GR2> create table t
  2  as
  3  select *
  4    from all_users
  5   where rownum = 1;

Table created.


ops$tkyte%ORA11GR2> alter session set cursor_sharing=exact;

Session altered.

ops$tkyte%ORA11GR2> select substr(username,1,10) uname,
  2         to_char(user_id,'999,999') u_id,
  3         to_char(created,'Dy Mon DD, YYYY' ) created
  4    from t cs_exact;

UNAME      U_ID     CREATED
---------- -------- ----------------
SYS               0 Thu Aug 13, 2009

<b>nicely formatted - the database knows the substr is 10 characters and so on...</b>

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

Session altered.

ops$tkyte%ORA11GR2> select substr(username,1,10) uname,
  2         to_char(user_id,'999,999') u_id,
  3         to_char(created,'Dy Mon DD, YYYY' ) created
  4    from t cs_force;

UNAME
------------------------------
U_ID
-------------------------------------------------------------------------------------------------------------------------
CREATED
---------------------------------------------------------------------------
SYS
       0
Thu Aug 13, 2009


<b>Not so nice, what happened?</b>

ops$tkyte%ORA11GR2> alter session set cursor_sharing=exact;

Session altered.

ops$tkyte%ORA11GR2> column sql_text format a80
ops$tkyte%ORA11GR2> select sql_text
  2    from v$sql
  3   where sql_text like 'select substr(username%';

SQL_TEXT
--------------------------------------------------------------------------------
select substr(username,1,10) uname,        to_char(user_id,'999,999') u_id,
   to_char(created,'Dy Mon DD, YYYY' ) created   from t cs_exact

select substr(username,:"SYS_B_0",:"SYS_B_1") uname,        to_char(user_id,:"SY
S_B_2") u_id,        to_char(created,:"SYS_B_3" ) created   from t cs_force

<b>overbinding - that is what happened - you wouldn't bind those things but now you are...</b>



this page is full of caveats - full of the disadvantages - not sure why you asked what you did?

Cursor_sharing = Similar

August 28, 2013 - 12:30 pm UTC

Reviewer: Rajeshwaran

Tom,

I have data skew on ID column and set cursor_sharing=similar and queries were against OBJECT_ID column.

I am not sure why do I see 2 sql's in shared pool after the below script executed in 10.2.0.5 ? I looked into v$sql_shared_cursor but I don't get any clue, can you help me?

drop table t purge;
set serveroutput off;

create table t 
as
select a.* , 99 id
from all_objects a;

update t set id = 1 where rownum = 1;

create index t_ind on t(id);

begin
 dbms_stats.gather_table_stats
 (user,'T',
  method_opt=>'for all indexed columns size 254',
  cascade=>true );
end;
/

alter session set cursor_sharing=similar;
select * from t look_for_me3 where object_id = 1 ;  

select sql_text
from v$sql
where upper(sql_text) like 'SELECT%*%FROM%T%LOOK_FOR_ME3%WHERE%OBJECT_ID%' ;

select * from t look_for_me3 where object_id = 99 ;  

select sql_text
from v$sql
where upper(sql_text) like 'SELECT%*%FROM%T%LOOK_FOR_ME3%WHERE%OBJECT_ID%' ;

select * from t look_for_me3 where object_id = 0;  

select sql_text
from v$sql
where upper(sql_text) like 'SELECT%*%FROM%T%LOOK_FOR_ME3%WHERE%OBJECT_ID%' ;

Tom Kyte

Followup  

September 04, 2013 - 5:15 pm UTC

#1 you do not have skewed data, you are using object_id in your where clause, not id.


#2 you have two different sql statements, they have different sql_ids, they have different lengths. One of them has a trailing blank:


ops$tkyte%ORA11GR2> select sql_id, sql_text, dump(sql_text,16) dump
  2  from v$sql
  3  where upper(sql_text) like 'SELECT%*%FROM%T%LOOK_FOR_ME3%WHERE%OBJECT_ID%' ;

SQL_ID
-------------
SQL_TEXT
-------------------------------------------------------------------------------
DUMP
-------------------------------------------------------------------------------
5xf4dca370n2x
select * from t look_for_me3 where object_id = :"SYS_B_0"
Typ=1 Len=57: 73,65,6c,65,63,74,20,2a,20,66,72,6f,6d,20,74,20,6c,6f,6f,6b,5f,66
,6f,72,5f,6d,65,33,20,77,68,65,72,65,20,6f,62,6a,65,63,74,5f,69,64,20,3d,20,3a,
22,53,59,53,5f,42,5f,30,22

fnpvzd3uccrgx
select * from t look_for_me3 where object_id = :"SYS_B_0"
Typ=1 Len=58: 73,65,6c,65,63,74,20,2a,20,66,72,6f,6d,20,74,20,6c,6f,6f,6b,5f,66
,6f,72,5f,6d,65,33,20,77,68,65,72,65,20,6f,62,6a,65,63,74,5f,69,64,20,3d,20,3a,
22,53,59,53,5f,42,5f,30,22,20




Cursor_sharing and Exadata

September 09, 2013 - 7:41 pm UTC

Reviewer: Uma from New York

Tom,

DBAs strongly advise the development team to use cursor_sharing = force in Oracle 11g on Exadata saying it is a great feature to solve performance issues in 11g with Exadata. Is it true? Any thing changed in 11g with Exadata for this parameter?
Tom Kyte

Followup  

September 10, 2013 - 10:14 am UTC

I've never heard more misguided advice in my life.

please ask them for supporting documentation on this - it is like the opposite of the truth.

Relation between cursor_sharing=force and adaptive cursor charing

September 17, 2013 - 2:50 pm UTC

Reviewer: Mervin from St Louis, MO

Tom,

DBA in our company suggets to use cursor_sharing=force to enable adaptive cursor sharing. I though cursor_sharing and adaptive cursor sharing are used for two different purpose and we need to gather just histogram to enable adaptive cursor sharing. Is it true that cursor_sharing parameter should be set to FORCE to enable adaptive cursor sharing? How they are related?




Tom Kyte

Followup  

September 24, 2013 - 11:09 pm UTC

adaptive cursor sharing works on queries with bind variables - any query with bind variables.

if you are not using bind variables, then adaptive cursor sharing never comes into play - you'll get a different plan possibly for every query already since you are using literals and hard parsing every single query.

turning on cursor sharing force would cause queries that do not use binds to use binds, which would make adaptive cursor sharing come into effect for them.


However, cursor sharing = force does not fix the huge security bug of sql injection you have if you are not using bind variables - so I consider anything that needs to use cursor sharing = force to be a bug in the developed code.


the correct solution is to use bind variables in the first place and never need cursor sharing = force.


But - in short - if you do not use binds, you are not using adaptive cursor sharing. Using cursor sharing = force would cause to you use adaptive cursor sharing when appropriate.

February 22, 2018 - 9:29 pm UTC

Reviewer: Alex A

Hi folks,

I have a query with a lot of predicate values I'm trying to pull out of OEM in production, a mix of literals and binds. When I try to save the binds so I can test with, it only provides a small subset. I'm thinking this has to do with us having cursor_sharing=force, and the missing ones are actually static literals being "over binded". Do you think this is correct? If so, is there anyway to get them all?
Connor McDonald

Followup  

February 23, 2018 - 1:58 am UTC

Not sure what you mean. The only thing that will truly capture *all* binds is a trace, and that will work even if cursor_sharing is enabled

original query

SQL> alter session set cursor_sharing = force;

Session altered.

SQL>
SQL> exec dbms_monitor.session_trace_enable(binds=>true);

PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2  from   t
  3  where  object_type = 'INDEX'
  4  and    object_id = 123;

no rows selected



=====================
PARSING IN CURSOR #2369434011856 len=79 dep=0 uid=107 oct=3 lid=107 tim=807825657433 hv=2615614130 ad='7ff7774b8730' sqlid='fmtbjz6dyf6pk'
select *
from   t
where  object_type = :"SYS_B_0"
and    object_id = :"SYS_B_1"
END OF STMT
PARSE #2369434011856:c=0,e=685,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=807825657432
=====================


BINDS #2369434011856:

 Bind#0
  oacdty=01 mxl=32(05) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0100 frm=01 csi=873 siz=32 off=0
  kxsbbbfp=b5310350  bln=32  avl=05  flg=09
  value="INDEX"
 Bind#1
  oacdty=02 mxl=22(03) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0100 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=b5310328  bln=22  avl=03  flg=09
  value=123



PDB lock down profiles on setting cursor_sharing parameter at session.

August 12, 2019 - 1:34 pm UTC

Reviewer: Rajeshwaran Jeyabal

Team,

was created a lockdown profile in 12.2 database, to restrict setting cursor_sharing = FORCE.
but please see below, unable to set cursor_sharing to all possible values.
Kindly advice if this is an expected behavior.

c##sys@ORA12CR2> create lockdown profile p1;

Lockdown Profile created.

c##sys@ORA12CR2> alter lockdown profile p1 disable statement = ('ALTER SESSION') clause=('SET')
  2     option=('CURSOR_SHARING')
  3     value=('FORCE');

Lockdown Profile altered.

c##sys@ORA12CR2> alter session set container=PDB1;

Session altered.

c##sys@ORA12CR2> alter system set pdb_lockdown = p1 scope=both;

System altered.

c##sys@ORA12CR2> conn demo/demo@pdb1
Connected.
demo@PDB1> alter session set CURSOR_SHARING=FORCE;
ERROR:
ORA-01031: insufficient privileges


demo@PDB1> alter session set CURSOR_SHARING=SIMILAR;
ERROR:
ORA-01031: insufficient privileges


demo@PDB1> alter session set CURSOR_SHARING=exact;
ERROR:
ORA-01031: insufficient privileges

Connor McDonald

Followup  

August 13, 2019 - 2:08 am UTC

VALUE is not an additional filter, it is a forced (no pun intended) setting.

From the docs:

"The purpose of using this clause is to simultaneously set a default value for an option and restrict users from setting or modifying the value."

So this command:

SQL> alter lockdown profile p1 disable statement = ('ALTER SESSION') clause=('SET')
  2     option=('CURSOR_SHARING')
  3     value=('FORCE');


is saying: No-one gets to set cursor_sharing, and it will default to FORCE.

So if your intent was to make sure everyone does 'exact' you would disable as per above, but set the value to EXACT

More to Explore

DBMS_SQL

More on PL/SQL routine DBMS_SQL here