RE: PL/SQL and Bind Variables / Literals
From: Saibabu Devabhaktuni <saibabu_d_at_yahoo.com>
Date: Fri, 19 Jun 2009 00:41:26 -0700 (PDT)
Message-ID: <178291.27443.qm_at_web56008.mail.re3.yahoo.com>
Date: Fri, 19 Jun 2009 00:41:26 -0700 (PDT)
Message-ID: <178291.27443.qm_at_web56008.mail.re3.yahoo.com>
You could do something like explained below (besides the options of execute immediate, sql hints, etc):
The trick below is to set session_cached_cursors to 0 and changing one of the optimizer parameters at session level for each execution (following test was done on Oracle 10.2.0.3).
00:33:30 SQL> declare 00:33:30 2 a number; 00:33:30 3 begin 00:33:30 4 select /* plsql_bind_test */ count(*) into a from dual; 00:33:30 5 end; 00:33:30 6 /
PL/SQL procedure successfully completed.
00:33:30 SQL> declare 00:33:30 2 a number; 00:33:30 3 begin 00:33:30 4 for i in 1..10 loop 00:33:30 5 execute immediate 'alter session set workarea_size_policy=manual'; 00:33:30 6 execute immediate 'alter session set session_cached_cursors=0'; 00:33:30 7 execute immediate 'alter session set sort_area_size='||(65536+i); 00:33:30 8 select /* plsql_bind_test */ count(*) into a from dual; 00:33:30 9 end loop; 00:33:30 10 end; 00:33:30 11 /
PL/SQL procedure successfully completed.
00:33:30 SQL> select sql_id, executions, parse_calls, optimizer_env_hash_value, last_active_time, sql_text from v$sql 00:33:30 2 where upper(sql_text) like '%SELECT%DUAL%' 00:33:30 3 order by last_active_time 00:33:30 4 / SQL_ID EXECUTIONS PARSE_CALLS OPTIMIZER_ENV_HASH_VALUE LAST_ACTIVE_TIME SQL_TEXT
------------- ---------- ----------- ------------------------ ------------------- -------------------------------------------------
0u5dc4rhw0m32 1 1 460612282 06/19/2009 00:33:29 declare a number; begin for i in 1..10 loop execu te immediate 'alter session set workarea_size_pol icy=manual'; execute immediate 'alter session set session_cached_cursors=0'; execute immediate 'al ter session set sort_area_size='||(65536+i); sele ct /* plsql_bind_test */ count(*) into a from dua l; end loop; end; 0mr7azgm9psws 2 2 460612282 06/19/2009 00:33:29 SELECT COUNT(*) FROM DUAL 0mr7azgm9psws 1 1 3867783226 06/19/2009 00:33:29 SELECT COUNT(*) FROM DUAL 0mr7azgm9psws 1 1 1233731985 06/19/2009 00:33:29 SELECT COUNT(*) FROM DUAL 0mr7azgm9psws 1 1 2823109144 06/19/2009 00:33:29 SELECT COUNT(*) FROM DUAL 0mr7azgm9psws 1 1 1535195843 06/19/2009 00:33:29 SELECT COUNT(*) FROM DUAL 5wjshy32wascb 1 1 460612282 06/19/2009 00:33:29 declare a number; begin select /* plsql_bind_test */ count(*) into a from dual; end; 0mr7azgm9psws 1 1 551597565 06/19/2009 00:33:29 SELECT COUNT(*) FROM DUAL 0mr7azgm9psws 1 1 2150044487 06/19/2009 00:33:29 SELECT COUNT(*) FROM DUAL 0mr7azgm9psws 1 1 3941337865 06/19/2009 00:33:29 SELECT COUNT(*) FROM DUAL 0mr7azgm9psws 1 1 3776777788 06/19/2009 00:33:29 SELECT COUNT(*) FROM DUAL 1dr5f4699fh7m 1 1 460612282 06/19/2009 00:33:29 select sql_id, executions, parse_calls, optimizer _env_hash_value, last_active_time, sql_text from v$sql where upper(sql_text) like '%SELECT%DUAL%' order by last_active_time 0mr7azgm9psws 1 1 3880065502 06/19/2009 00:33:29 SELECT COUNT(*) FROM DUAL
13 rows selected.
Thanks,
Sai
http://sai-oracle.blogspot.com
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jun 19 2009 - 02:41:26 CDT