An Idea...
Richard, August 30, 2005 - 6:44 am UTC
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?
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...
Richard, August 30, 2005 - 8:00 am UTC
"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?
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.
Jonathan, August 30, 2005 - 9:23 am UTC
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....
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>
A reader, August 30, 2005 - 11:53 am UTC
Hi tom
cant the issue of reusing the shared cursor be removed by using the cursor_sharing=similar option??
August 30, 2005 - 12:26 pm UTC
which issue are you refering to exactly?
issue misinterpreted
A reader, August 31, 2005 - 6:48 am UTC
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...
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
Richard, August 31, 2005 - 8:05 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") "
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
Richard, August 31, 2005 - 10:53 am UTC
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??
A reader, September 01, 2005 - 6:36 am UTC
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??
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
A reader, September 01, 2005 - 9:00 am UTC
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.
?
September 01, 2005 - 3:38 pm UTC
what is unexplainable about it? You used cast, not substr.
Still some doubt...
A reader, September 01, 2005 - 9:44 am UTC
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
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
A reader, September 02, 2005 - 7:23 am UTC
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." ?
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
A reader, September 02, 2005 - 8:28 am UTC
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
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.
A reader, September 05, 2005 - 3:49 am UTC
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?
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.
A reader, September 05, 2005 - 10:54 am UTC
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???
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.
A reader, September 05, 2005 - 12:04 pm UTC
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
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.
A reader, September 06, 2005 - 4:53 am UTC
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??
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.
A reader, September 06, 2005 - 9:10 am UTC
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
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.
A reader, September 06, 2005 - 10:07 am UTC
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.
Kashif, April 16, 2006 - 6:02 pm UTC
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
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
Hector Gabriel Ulloa Ligarius, May 12, 2006 - 6:50 pm UTC
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
Brandon Allen, July 10, 2006 - 6:23 pm UTC
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
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
Brandon Allen, July 12, 2006 - 4:37 pm UTC
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
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 :-)
Brandon Allen, July 12, 2006 - 8:31 pm UTC
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
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
Brandon Allen, July 13, 2006 - 4:57 pm UTC
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
Dusan, December 18, 2006 - 9:57 am UTC
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
Greg Norris, January 17, 2007 - 3:43 pm UTC
[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!
Dusan, March 19, 2007 - 8:43 am UTC
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
Susan, September 17, 2007 - 12:13 pm UTC
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.
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
A reader, November 29, 2007 - 9:38 am UTC
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>
November 29, 2007 - 6:48 pm UTC
do you have access to support to file a bug?
dbms_advanced_rewrite
A reader, November 30, 2007 - 11:15 am UTC
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
A reader, December 04, 2007 - 9:08 am UTC
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
Ram, November 25, 2008 - 3:44 am UTC
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
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
Stephen O'Donnell, January 27, 2009 - 6:36 am UTC
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?
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
Salman Syed, January 28, 2009 - 3:19 pm UTC
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)?
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
Salman Syed, January 30, 2009 - 1:32 pm UTC
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?
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
Ken, March 26, 2010 - 11:25 pm UTC
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.
March 27, 2010 - 8:25 am UTC
1) no
2) not unless you can change the code...
PARSE count with FORCE.
lalu., March 30, 2010 - 12:40 am UTC
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.
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.
lalu., March 30, 2010 - 12:43 am UTC
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.
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
Lasse Jenssen, April 03, 2010 - 9:10 am UTC
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
Ian, August 12, 2010 - 8:38 am UTC
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
A reader, September 30, 2010 - 9:09 pm UTC
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"?
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
Rajeshwaran, August 28, 2013 - 12:30 pm UTC
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%' ;
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
Uma, September 09, 2013 - 7:41 pm UTC
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?
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
Mervin, September 17, 2013 - 2:50 pm UTC
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?
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.
Alex A, February 22, 2018 - 9:29 pm UTC
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?
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.
Rajeshwaran Jeyabal, August 12, 2019 - 1:34 pm UTC
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
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