多版本cursor，与session_cached_cursors关系

最新推荐文章于 2023-07-28 10:50:56 发布

转载最新推荐文章于 2023-07-28 10:50:56 发布 · 96 阅读

本文通过实验探讨了在Oracle数据库中，当使用不同长度的变量执行相同查询时，PGA中会缓存多少个游标的实例。结果显示，每个不同版本的游标在会话中执行超过三次后，确实会在缓存中产生多个版本。

在想如果一个存在多版本的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多个。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-681646/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-681646/