RE: CURSOR_SHARING survey
Date: Thu, 24 Jan 2013 23:38:07 -0800 (PST)
Message-ID: <1359099487.73195.YahooMailNeo_at_web161303.mail.bf1.yahoo.com>
Chris,
We don't typically set cursor_sharing to force (unless any application bug related to not using bind variables is introduced) for the following reasons:
- Any literal predicates that we want to be seen by optimizer for better execution plan also gets converted to system bind variable. Queries that filter by few types of status or flag column can be hard coded as literals.
- soft parsing overhead
Here is the test case for soft parse overhead:
alter session set cursor_sharing=exact;
declare
a number;
begin
for i in 1..100 loop
execute immediate 'select /* test111_exact */ 1 from dual where 1=1' into a;
end loop;
end;
/
alter session set cursor_sharing=force;
declare
a number;
begin
for i in 1..100 loop
execute immediate 'select /* test111_force */ 1 from dual where 1=1' into a;
end loop;
end;
/
select sql_id, executions, parse_calls, cpu_time, elapsed_time, sql_text from v$sql where sql_text like '%test111%' and sql_text not like '%v$sql%'; ttp
SQL_ID EXECUTIONS PARSE_CALLS CPU_TIME ELAPSED_TIME SQL_TEXT
------------- ---------- ----------- ---------- ------------ ------------------------------------------------- arna8v7g2w734 100 1 0 7463 select /* test111_exact */ 1 from dual where 1=1 58g19t2wb16k6 1 1 20000 23476 declare a number; begin for i in 1..100 loop exec
ute immediate 'select /* test111_exact */ 1 from
dual where 1=1' into a; end loop; end;
1mrw3tncs2b96 100 100 10000 17851 select /* test111_force */ :"SYS_B_0" from dual
where :"SYS_B_1"=:"SYS_B_2"
g6jzbm246bma1 1 1 50000 41911 declare a number; begin for i in 1..100 loop exec
ute immediate 'select /* test111_force */ 1 from
dual where 1=1' into a; end loop; end;
Thanks,
Sai
http://sai-oracle.blogspot.com
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jan 25 2013 - 08:38:07 CET