RE: CURSOR_SHARING survey

From: Saibabu Devabhaktuni <saibabu_d_at_yahoo.com>
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:

1. 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.
2. 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-l

Received on Fri Jan 25 2013 - 08:38:07 CET