Cursor_sharing setting in OLTP environment [message #495797] |
Tue, 22 February 2011 05:49 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #495802 is a reply to message #495798] |
Tue, 22 February 2011 06:16 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #495817 is a reply to message #495805] |
Tue, 22 February 2011 08:26 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #495828 is a reply to message #495821] |
Tue, 22 February 2011 10:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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](images/smiley_icons/icon_smile.gif)
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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:
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 Go to previous message](/forum/theme/orafaq/images/up.png) |
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
|
|
|