[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执行发生了转换.