多版本cursor，與session_cached_cursors關係

在想如果一個存在多版本的cursor，如果讓每一個版本在session裡執行超過三次，那麼被cache的cursor是多個還是一個。實驗證明是多個。

create table wxh_tbd as select * from dba_tables;

var a varchar2(10)
var b varchar2(10)
var c varchar2(10)
exec :a := '1'
exec :b := '1'
exec :c := '1'
select * from wxh_tbd where table_name= :a and TABLESPACE_NAME= :b and instances= :c;

var a varchar2(10)
var b varchar2(100)
var c varchar2(10)
exec :a := '1'
exec :b := '1'
exec :c := '1'
select * from wxh_tbd where table_name= :a and TABLESPACE_NAME= :b and instances= :c;

var a varchar2(10)
var b varchar2(1000)
var c varchar2(10)
exec :a := '1'
exec :b := '1'
exec :c := '1'
select * from wxh_tbd where table_name= :a and TABLESPACE_NAME= :b and instances= :c;

var a varchar2(10)
var b varchar2(4000)
var c varchar2(10)
exec :a := '1'
exec :b := '1'
exec :c := '1'
select * from wxh_tbd where table_name= :a and TABLESPACE_NAME= :b and instances= :c;

var a varchar2(100)
var b varchar2(10)
var c varchar2(10)
exec :a := '1'
exec :b := '1'
exec :c := '1'
select * from wxh_tbd where table_name= :a and TABLESPACE_NAME= :b and instances= :c;

var a varchar2(1000)
var b varchar2(10)
var c varchar2(10)
exec :a := '1'
exec :b := '1'
exec :c := '1'
select * from wxh_tbd where table_name= :a and TABLESPACE_NAME= :b and instances= :c;

var a varchar2(4000)
var b varchar2(10)
var c varchar2(10)
exec :a := '1'
exec :b := '1'
exec :c := '1'
select * from wxh_tbd where table_name= :a and TABLESPACE_NAME= :b and instances= :c;

var a varchar2(100)
var b varchar2(100)
var c varchar2(10)
exec :a := '1'
exec :b := '1'
exec :c := '1'
select * from wxh_tbd where table_name= :a and TABLESPACE_NAME= :b and instances= :c;

var a varchar2(1000)
var b varchar2(1000)
var c varchar2(10)
exec :a := '1'
exec :b := '1'
exec :c := '1'
select * from wxh_tbd where table_name= :a and TABLESPACE_NAME= :b and instances= :c;

var a varchar2(2000)
var b varchar2(4000)
var c varchar2(10)
exec :a := '1'
exec :b := '1'
exec :c := '1'
select * from wxh_tbd where table_name= :a and TABLESPACE_NAME= :b and instances= :c;

var a varchar2(4000)
var b varchar2(10)
var c varchar2(100)
exec :a := '1'
exec :b := '1'
exec :c := '1'
select * from wxh_tbd where table_name= :a and TABLESPACE_NAME= :b and instances= :c;

var a varchar2(4000)
var b varchar2(10)
var c varchar2(300)
exec :a := '1'
exec :b := '1'
exec :c := '1'
select * from wxh_tbd where table_name= :a and TABLESPACE_NAME= :b and instances= :c;

var a varchar2(4000)
var b varchar2(10)
var c varchar2(1000)
exec :a := '1'
exec :b := '1'
exec :c := '1'
select * from wxh_tbd where table_name= :a and TABLESPACE_NAME= :b and instances= :c;

var a varchar2(4000)
var b varchar2(1000)
var c varchar2(100)
exec :a := '1'
exec :b := '1'
exec :c := '1'
select * from wxh_tbd where table_name= :a and TABLESPACE_NAME= :b and instances= :c;

var a varchar2(4000)
var b varchar2(100)
var c varchar2(4000)
exec :a := '1'
exec :b := '1'
exec :c := '1'
select * from wxh_tbd where table_name= :a and TABLESPACE_NAME= :b and instances= :c;

var a varchar2(1000)
var b varchar2(4000)
var c varchar2(10)
exec :a := '1'
exec :b := '1'
exec :c := '1'
select * from wxh_tbd where table_name= :a and TABLESPACE_NAME= :b and instances= :c;

var a varchar2(1)
var b varchar2(3000)
var c varchar2(300)
exec :a := '1'
exec :b := '1'
exec :c := '1'
select * from wxh_tbd where table_name= :a and TABLESPACE_NAME= :b and instances= :c;

var a varchar2(1000)
var b varchar2(400)
var c varchar2(400)
exec :a := '1'
exec :b := '1'
exec :c := '1'
select * from wxh_tbd where table_name= :a and TABLESPACE_NAME= :b and instances= :c;

var a varchar2(500)
var b varchar2(2500)
var c varchar2(10)
exec :a := '1'
exec :b := '1'
exec :c := '1'
select * from wxh_tbd where table_name= :a and TABLESPACE_NAME= :b and instances= :c;

var a varchar2(1000)
var b varchar2(100)
var c varchar2(2000)
exec :a := '1'
exec :b := '1'
exec :c := '1'
select * from wxh_tbd where table_name= :a and TABLESPACE_NAME= :b and instances= :c;

var a varchar2(600)
var b varchar2(1000)
var c varchar2(20)
exec :a := '1'
exec :b := '1'
exec :c := '1'
select * from wxh_tbd where table_name= :a and TABLESPACE_NAME= :b and instances= :c;

上面的程式碼執行N次，檢視v$sql裡的版本數是：

col name for a50
select sid, b.name, value
  2    from v$sesstat a, v$statname b
  3   where a.STATISTIC# = b.STATISTIC#
  4     and name = 'session cursor cache count'
  5     and sid = 3230;

       SID NAME                                                    VALUE
---------- -------------------------------------------------- ----------
      3230 session cursor cache count                                  6

select count(*) from v$sql where sql_text like 'select * from wxh_tbd where table_name= :a and TABLESPACE_NAME=%';

  COUNT(*)
----------
         6

看來會在PGA裡cache多個。