[20180803]cursor_sharing = force.txt
[20180803]cursor_sharing = force.txt
--//連結:https://jonathanlewis.wordpress.com/2018/06/23/cursor_sharing-force/
--//重複測試:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
create table t1
as
select
rownum n1,
rownum n2,
lpad(rownum,10) small_vc,
rpad('x',100,'x') padding
from dual
connect by
level <= 1e4
;
alter system flush shared_pool;
alter session set cursor_sharing=force;
declare
m_ct number;
m_n1 number := 20;
begin
execute immediate
'select /*+ trace this */ count(*) from t1 where n2 = 15 and n1 = :b1'
into m_ct using m_n1;
dbms_output.put_line(m_ct);
execute immediate
'select /*+ trace this too */ count(*) from t1 where n1 = 15 and n2 = 15'
into m_ct;
dbms_output.put_line(m_ct);
end;
/
--//alter session set cursor_sharing=exact;
select sql_id, parse_calls, executions, rows_processed, sql_text
from v$sql
where sql_text like 'select%trace this%' and sql_text not like '%v$sql%' ;
SQL_ID PARSE_CALLS EXECUTIONS ROWS_PROCESSED SQL_TEXT
------------- ----------- ---------- -------------- ----------------------------------------------------------------------------------------------------
cbu4s78h5pfj5 1 1 1 select /*+ trace this too */ count(*) from t1 where n1 = :"SYS_B_0" and n2 = :"SYS_B_1"
cru67sufptx8x 1 1 1 select /*+ trace this */ count(*) from t1 where n2 = 15 and n1 = :b1
--//你可以發現有變數和常量的語句沒有發生轉換,很奇怪.而2個是變數的語句發生了轉換.
--//如果在sqlplus下執行:
alter session set cursor_sharing=force;
variable b1 number
exec :b1 := 15;
select /*+ SQL*Plus session */ count(*) from t1 where n2 = 15 and n1 = :b1;
select sql_id, parse_calls, executions, rows_processed, sql_text
from v$sql
where sql_text like 'select%Plus session%' and sql_text not like '%v$sql%' ;
SQL_ID PARSE_CALLS EXECUTIONS ROWS_PROCESSED SQL_TEXT
------------- ----------- ---------- -------------- ----------------------------------------------------------------------------------------------
gq2qy2a9yuta7 1 1 1 select /*+ SQL*Plus session */ count(*) from t1 where n2 = :"SYS_B_0" and n1 = :b1
--//而在sqlplus執行發生了轉換.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2193116/,如需轉載,請註明出處,否則將追究法律責任。