Home » RDBMS Server » Performance Tuning » Cursor_sharing setting in OLTP environment (Oracle 9i R2, 10g R2)
Cursor_sharing setting in OLTP environment [message #495797] Tue, 22 February 2011 05:49 Go to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Hello

In an OLTP environment what cursor_sharing setting is preferred?

Though typically we retain the original setting for most of the parameters except memory settings etc. I have queries in the following context

No. I am not facing any issue as of now (I am not supporting any Live environment)
But I want to know the desgn considerations

First of all in OLTP environment (say one I am referring) we use pl/sql variables which are obviously bind variables
Only in case where plan is expected to change we use hard coded values like 'CREDIT' or 'DEBIT' etc. for acc_type column

Again there can be 2 scenario
1) we use the same query for both acc_type values
2) we use 2 different queries
IF v_parameter = 'CR'
select * from accounts where acc_type='CREDIT'...
else
select * from accounts where acc_type='DEBIT'...
end if;


Again suppose the values are skewed and we gather stats with histograms here

Is't it the setting 'cursor_sharing=similar' which will be useful in above case?

as with this setting optimizer will 'think' which plan to pick depending upon the values and bind variable peeking is taken care in option 2 above with IF ELSE clause?

Please suggest

Regards,
OraKaran

P.S.


BTW
I have carried several tests but not getting conclusive results

For example I created following table with skewed data, created index and gather stats with histogram
SQL> select object_id,count(*) from skewed_data_tab group by object_id;

 OBJECT_ID   COUNT(*)
---------- ----------
         5         30
         6       2970
         7      10797
         8     150000
         9     300000
SQL> create index i_skewed_tab_data on skewed_data_tab(object_id);

SQL> exec dbms_stats.gather_table_stats(user,'SKEWED_DATA_TAB',cascade=>true,
method_opt=>'for all columns size 254');

Then traced with following options
1) 
alter session set events '10046 trace name context forever, level 12';

SQL> begin
for i_outer in(select n from ids order by tstamp)
loop
for i_inner in (select /* for exact */ object_id,object_name,object_type 
from skewed_data_tab where object_id=i_outer.n)
loop
null;
end loop;
end loop;
end;  2    3    4    5    6    7    8    9
 10  /

PL/SQL procedure successfully completed.


2) set termout off
alter session set events '10046 trace name context forever, level 12';
@/u04/scripts/exact.sql 5


cat /u04/scripts/exact.sql
select /* for exact */ object_id,object_name,object_type from skewed_data_tab where object_id=&1;



But not getting consistent results

Also as autotrace gives guessed plan and not actual, I am trying to get tkprof here

[Updated on: Tue, 22 February 2011 07:09] by Moderator

Report message to a moderator

Re: Cursor_sharing setting in OLTP environment [message #495798 is a reply to message #495797] Tue, 22 February 2011 05:55 Go to previous messageGo to next message
cookiemonster
Messages: 11088
Registered: September 2008
Location: Rainy Manchester
Senior Member
Exact would be prefered, the other settings are work arounds for coders who don't know how to use bind variables.
Go to asktom and search for cursor_sharing. The topic is covered in detail.
Re: Cursor_sharing setting in OLTP environment [message #495801 is a reply to message #495798] Tue, 22 February 2011 06:10 Go to previous messageGo to next message
Michel Cadot
Messages: 59499
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In an OLTP environment what cursor_sharing setting is preferred?

Always EXACT unless your application is badly written cannot be rewritten.

Regards
Michel
Re: Cursor_sharing setting in OLTP environment [message #495802 is a reply to message #495798] Tue, 22 February 2011 06:16 Go to previous messageGo to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Hello cookiemonster

Thanks for quick reply

Indeed I read on asktom

However there wasn't an exact scenario (OLTP+ bind variable + skewed_data + histogram + IF-ELSE) or I wasn't able to understand it then

I read Tom has advised to keep default (exact) and use other settings as workarounds only, as you too have mentioned.

But wont the following cause 5 different plans with setting as 'exact' whereas similar would be wise to use only 2 plans and giving us the control, which to use when depending upon values if we used If-ELSE condition?

SQL> select object_id,count(*) from skewed_data_tab group by object_id;

 OBJECT_ID   COUNT(*)
---------- ----------
         5         30
         6       2970
         7      10797
         8     150000
         9     300000

select * from skewed_data_tab where object_id=n;


Regards,
OraKaran



Re: Cursor_sharing setting in OLTP environment [message #495805 is a reply to message #495802] Tue, 22 February 2011 07:01 Go to previous messageGo to next message
cookiemonster
Messages: 11088
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why would you have 5 plans?
Re: Cursor_sharing setting in OLTP environment [message #495817 is a reply to message #495805] Tue, 22 February 2011 08:26 Go to previous messageGo to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Hello cookiemonster

There are 2 plans as displayed by autotrace

But in v$sql there are 5 different addresses while literals are used

SQL> create table data as select * from t;

Table created.

SQL> select object_id,count(*) from data group by object_id;

 OBJECT_ID   COUNT(*)
---------- ----------
         5         30
         6       2970
         7      10797
         8     150000
         9     300000

SQL> create index i_data on data(object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'DATA',cascade=>true,method_opt=>'for all columns size 254');

PL/SQL procedure successfully completed.

SQL> show parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
SQL>
SQL> select sql_text,child_number from v$sql where sql_text like 'select /* test exact cursor_sharing */ * from data%';

no rows selected

SQL> set autotrace traceonly explain
SQL> select /* test exact cursor_sharing */ * from data where object_id=9;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=367 Card=300000 Byte
          s=26400000)

   1    0   TABLE ACCESS (FULL) OF 'DATA' (Cost=367 Card=300000 Bytes=
          26400000)

SQL> select /* test exact cursor_sharing */ * from data where object_id=5;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=30 Bytes=2640
          )

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'DATA' (Cost=4 Card=30 By
          tes=2640)

   2    1     INDEX (RANGE SCAN) OF 'I_DATA' (NON-UNIQUE) (Cost=3 Card
          =30)




SQL> select /* test exact cursor_sharing */ * from data where object_id=8;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=367 Card=150000 Byte
          s=13200000)

   1    0   TABLE ACCESS (FULL) OF 'DATA' (Cost=367 Card=150000 Bytes=
          13200000)




SQL> select /* test exact cursor_sharing */ * from data where object_id=7;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=164 Card=10797 Bytes
          =950136)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'DATA' (Cost=164 Card=107
          97 Bytes=950136)

   2    1     INDEX (RANGE SCAN) OF 'I_DATA' (NON-UNIQUE) (Cost=24 Car
          d=10797)




SQL> select /* test exact cursor_sharing */ * from data where object_id=6;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=47 Card=2970 Bytes=2
          61360)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'DATA' (Cost=47 Card=2970
           Bytes=261360)

   2    1     INDEX (RANGE SCAN) OF 'I_DATA' (NON-UNIQUE) (Cost=8 Card
          =2970)

SQL> set autotrace off

SQL> select sql_text,child_number,address from v$sql where sql_text like 'select /* test exact cursor_sharing */ * from data%';

SQL_TEXT
--------------------------------------------------------------------------------
CHILD_NUMBER ADDRESS
------------ ----------------
select /* test exact cursor_sharing */ * from data where object_id=6
           0 000000008E94CB68

select /* test exact cursor_sharing */ * from data where object_id=9
           0 000000008E8EADB0

select /* test exact cursor_sharing */ * from data where object_id=8
           0 000000008E8F4A50


SQL_TEXT
--------------------------------------------------------------------------------
CHILD_NUMBER ADDRESS
------------ ----------------
select /* test exact cursor_sharing */ * from data where object_id=7
           0 000000008E9EFEE8

select /* test exact cursor_sharing */ * from data where object_id=5
           0 000000008E913F38



Regards,
OraKaran
Re: Cursor_sharing setting in OLTP environment [message #495821 is a reply to message #495817] Tue, 22 February 2011 09:07 Go to previous messageGo to next message
cookiemonster
Messages: 11088
Registered: September 2008
Location: Rainy Manchester
Senior Member
So you're running 5 seperate sql statements - that'll give you 5 plans obviously.
What are you actually trying to prove here?
Re: Cursor_sharing setting in OLTP environment [message #495828 is a reply to message #495821] Tue, 22 February 2011 10:10 Go to previous messageGo to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Hello cookiemonster

I am confused and not in the state of proving anything Smile

Not 5 plans, exact treated them as 5 different statements. Right

Here are the observations :

with literal values
similar produced 2 plans and 5 child numbers (cursor)
exact produced 2 plans and 5 addresses (trating it as 5 diff statements)

with bind variables
similar produced 1 plan with 1 address and 1 child number(cursor)
exact produced 1 plan with 1 address and 1 child number(cursor)

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


Please refer the following extract from above link

Quote:

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.

Followup May 2, 2004 - 9am Central time zone:
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.



My point is
SIMILAR would be different than EXACT only in case of auto bind
And Auto bind can cause issue only and only in case of bind variable peeking

If we take care of bind variable peeking by using IF-ELSE construct as mentioned in my original post, gather stats with histograms, wouldn't it be more feasible to have cursor_sharing=SIMILAR.

And if answer is No, why it is not feasible to set cursor_sharing=similar, in OLTP with conditions mentioned?

Regards,
OraKaran
Re: Cursor_sharing setting in OLTP environment [message #495835 is a reply to message #495828] Tue, 22 February 2011 10:56 Go to previous messageGo to next message
cookiemonster
Messages: 11088
Registered: September 2008
Location: Rainy Manchester
Senior Member
OraKaran wrote on Tue, 22 February 2011 16:10

with literal values
exact produced 2 plans and 5 addresses (trating it as 5 diff statements)

5 plans - just that most of them are identical

OraKaran wrote on Tue, 22 February 2011 16:10

with bind variables
similar produced 1 plan with 1 address and 1 child number(cursor)
exact produced 1 plan with 1 address and 1 child number(cursor)

Which would make sense as with bind variables there is nothing for cursor sharing to do.

OraKaran wrote on Tue, 22 February 2011 16:10

My point is
SIMILAR would be different than EXACT only in case of auto bind

In the case of? The difference is that it does auto-bind.

OraKaran wrote on Tue, 22 February 2011 16:10

And Auto bind can cause issue only and only in case of bind variable peeking

Not at all convinced that's true, if it was we would recommend it more often.

OraKaran wrote on Tue, 22 February 2011 16:10

If we take care of bind variable peeking by using IF-ELSE construct as mentioned in my original post, gather stats with histograms, wouldn't it be more feasible to have cursor_sharing=SIMILAR.

You're taking care of bind variable peeking by not supplying binds. using cursor_sharing=similar gives you binds back so why would you want to?

OraKaran wrote on Tue, 22 February 2011 16:10

why it is not feasible to set cursor_sharing=similar, in OLTP with conditions mentioned?

Why would you want to? What do you think it's actually going to achieve?
Bear in mind that your example would normally be an unusal case that would only affect a small percentage of queries in the system.
cursor_sharing effects all queries (unless you set it temporarily at session level).
Re: Cursor_sharing setting in OLTP environment [message #495922 is a reply to message #495835] Wed, 23 February 2011 04:52 Go to previous messageGo to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Hello cookiemonster

Thanks for your replies and your patience

1)
OraKaran
Quote:

And Auto bind can cause issue only and only in case of bind variable peeking


cookiemonster
Quote:

Not at all convinced that's true, if it was we would recommend it more often.

Apart from changing (in fact extending) length of some characters fields and possible bind variable peeking what could be the issue using bind variable or Auto bind?


2)
OraKaran
Quote:

If we take care of bind variable peeking by using IF-ELSE construct as mentioned in my original post, gather stats with histograms, wouldn't it be more feasible to have cursor_sharing=SIMILAR.


cookiemonster
Quote:

You're taking care of bind variable peeking by not supplying binds. using cursor_sharing=similar gives you binds back so why would you want to?


Here do you mean to say if I provide literal which is subsequently converted to bind variable (+ value to signature if needed) then with cursor_sharing=SIMILAR, optimizer will always wisely pick the correct plan depending upon distribution of data (with proper stats , histograms are available)?
And if picking the correct plan is hapening so implicitly, then why not use cursor_sharing=similar?

Finally it will use shared pool more efficiently than it would have used with cursor_sharing=EXACT. Right?

Could you share a reason to use EXACT instead of SIMILAR in OLTP?

I may be wrong but so far I am not convinced with why not use cursor_sharing=SIMILAR in OLTP

Will it by any chance generate more plans / child cursores than it will while cursor_sharing=EXACT?

Regards,
OraKaran
Re: Cursor_sharing setting in OLTP environment [message #495934 is a reply to message #495922] Wed, 23 February 2011 06:01 Go to previous messageGo to next message
cookiemonster
Messages: 11088
Registered: September 2008
Location: Rainy Manchester
Senior Member
Similar can give bad plans. A while back I had a problem with a top-n query:
SELECT column
FROM (SELECT column
      FROM table
      ORDER BY other column)
WHERE rownum = 1;

The app that was running that had cursor_sharing=similar.
So it changed the where clause to:
WHERE rownum = :bind;

Which gave a sub-optimal plan. It got to the point that it was taking over a minute to execute when it should have taken less than a second.
Changing cursor_sharing to exact fixed it instantly.

If you are using bind variables properly then the only thing cursor_sharing=similar can do is replace constants with binds.
That makes the optimiser do more work and hides information from it (bind variable peeking may or may not overcome this).

You are using a really unusual case to argue in favour of similar while ignoring all the normal cases.
If 99% of your sql uses binds then you'll get the same amount of plans/child cursors with either setting. Similar is only an improvement in that respect if you hardly use binds (which is the reason it exists).

If you've got a case where you end up doing different versions of a sql in an if else why would you want similar? Why not just use one sql with a bind in the first place.


OraKaran wrote on Wed, 23 February 2011 10:52

Quote:

You're taking care of bind variable peeking by not supplying binds. using cursor_sharing=similar gives you binds back so why would you want to?



Here do you mean to say if I provide literal which is subsequently converted to bind variable (+ value to signature if needed) then with cursor_sharing=SIMILAR, optimizer will always wisely pick the correct plan depending upon distribution of data (with proper stats , histograms are available)?
And if picking the correct plan is hapening so implicitly, then why not use cursor_sharing=similar?

No I'm saying it might not pick the correct plan. In this particular case constants are more likely to give the correct plan, and if you don't know what constants to use then you'll be using a bind anyway and similar will behave the same as exact.


To sum up.
Normally binds are good, which is why you should code them in in the first place.
Occasionally binds are bad - there is no upside to replacing a constant with a bind (there isn't always a downside though).

similar exists to make up for a lack of binds in the code.
If the code already has all the binds it needs then the only thing similar does is run the risk of causing issues by replacing constants.

If that downside didn't exist everyone would use similar.
Re: Cursor_sharing setting in OLTP environment [message #495951 is a reply to message #495797] Wed, 23 February 2011 08:13 Go to previous message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Hello cookiemonster

Your reply is really nice and neat
I think it is almost fullstop to my queries on cursor_sharing in OLTP

I have nothing to reply as of now but to test and think over it.

Many thanks for your time

Thanks and Regards,
OraKaran
Previous Topic: Identify blocking sessions
Next Topic: performance tuning
Goto Forum:
  


Current Time: Thu Oct 30 23:56:39 CDT 2014

Total time taken to generate the page: 0.12645 seconds