RE: PL/SQL and Bind Variables / Literals
- From: Saibabu Devabhaktuni <saibabu_d@xxxxxxxxx>
- To: free <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 19 Jun 2009 00:41:26 -0700 (PDT)
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
Other related posts: