ITPub博客

首页 > 应用开发 > IT综合 > 受困于一个参数:session_cached_cursors

受困于一个参数:session_cached_cursors

原创 IT综合 作者:warehouse 时间:2009-11-11 12:52:00 0 删除 编辑

这个参数本身不难理解,但是和这个参赛相关的很多话题值得我们深思:

1.cursor open之后oracle做了什么

2.cursor close之后oracle做了什么,资源真的释放了?其实没有,ursor被关闭之后,无非是它占用的资源可以被重复使用了而已

3.cursor被cache之后在uga中保存了什么

4.session和library cache以及uga是如何关联的...

要了解这些东西,需要对oracle的内存结构有清晰的了解...目前一团乱麻...

[@more@]

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 88081764 bytes
Database Buffers 75497472 bytes
Redo Buffers 2945024 bytes
数据库装载完毕。
数据库已经打开。
SQL> show parameter cursor

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 30000
session_cached_cursors integer 0
--验证是否设置了参数session_cached_cursors,重点关注
session cursor cache count,session cursor cache hits的变化

SQL> select a.value, s.username, s.sid, s.serial#,b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'opened cursors current',
6 'session cursor cache count',
7 'session cursor cache hits',
8 'parse count (total)',
9 'session uga memory',
10 'session uga memory max',
11 'session pga memory',
12 'session pga memory max'
13 )
14 and s.sid=159
15 order by name;

VALUE USERNAME SID SERIAL# NAME
---------- ---------- ---------- ---------- ------------------------------
4 SYS 159 3 opened cursors current
2145 SYS 159 3 parse count (total)
0 SYS 159 3 session cursor cache count
0 SYS 159 3 session cursor cache hits
1564244 SYS 159 3 session pga memory
2481748 SYS 159 3 session pga memory max
679992 SYS 159 3 session uga memory
844952 SYS 159 3 session uga memory max

已选择8行。
SQL> select count(*) from tt;

COUNT(*)
----------
11546

SQL> select count(*) from tt;

COUNT(*)
----------
11546

SQL> select parse_calls from v$sql where sql_text='select count(*) from tt';

PARSE_CALLS
-----------
2
--执行了2次 select count(*) from tt,产生了2次parse,一次harse parse,一次soft parse,
同时sql在执行之后确确实实cursor被关闭了,因为在v$open_cursor我们找不到刚刚执行过的sql:
select count(*) from tt
SQL> select sql_text from v$open_cursor where sid=159;

SQL_TEXT
------------------------------------------------------------
select ENQUEUE_ENABLED, DEQUEUE_ENABLED from dba_queues wher
select sql_text from v$open_cursor where sid=159
select wrm.last_ash_sample_id from WRM$_DATABASE_INSTANCE
--设置参数session_cached_cursors
SQL> alter session set session_cached_cursors=10;

会话已更改。

SQL> select count(*) from tt;

COUNT(*)
----------
11546

SQL> select parse_calls from v$sql where sql_text='select count(*) from tt';

PARSE_CALLS
-----------
3
--sql在执行之后oracle会试图关闭cursor,关闭之前会检查是否设置了参数session_cached_cursors,
如果设置了,那么还会判断即将要关闭的cursor对用的sql的parse_calls次数,如果达到3次(注意包括3次),
那么此时oracle不会关闭cursor了,而是把cursor相关的信息放到该session的uga中保存起来,以便该session下次
执行相同的sql而不需要重新产生soft parse,因为该sql的cursor信息直接可以从uga中找到,避免soft parse最终节约的资源
当然是cpu同时也减少了library cache latch事件的等待...
oracle把这种parse称为soft soft parse(softer parse)
这么这里uga中到底保存了cursor的什么信息,不得而知...我想无非是一些cursor的address信息而已,或者说
就是一些内存的地址,而绝对不会是sql的解析信息,sql的解析信息依然在library cache中...这个我们可以通过
dump library cache而获得...
SQL> select sql_text from v$open_cursor where sid=159;

SQL_TEXT
------------------------------------------------------------
select count(*) from tt
select ENQUEUE_ENABLED, DEQUEUE_ENABLED from dba_queues wher
select sql_text from v$open_cursor where sid=159
select wrm.last_ash_sample_id from WRM$_DATABASE_INSTANCE

SQL> select a.value, s.username, s.sid, s.serial#,b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'opened cursors current',
6 'session cursor cache count',
7 'session cursor cache hits',
8 'parse count (total)',
9 'session uga memory',
10 'session uga memory max',
11 'session pga memory',
12 'session pga memory max'
13 )
14 and s.sid=159
15 order by name;

VALUE USERNAME SID SERIAL# NAME
---------- ---------- ---------- ---------- ------------------------------
4 SYS 159 3 opened cursors current
2189 SYS 159 3 parse count (total)
2 SYS 159 3 session cursor cache count
7 SYS 159 3 session cursor cache hits
1171028 SYS 159 3 session pga memory
2481748 SYS 159 3 session pga memory max
679992 SYS 159 3 session uga memory
1486168 SYS 159 3 session uga memory max

已选择8行。
--设置了session_cached_cursors之后,很显然select count(*) from tt在执行之后被cache了,
同时cursor也处于打开状态,意味着cursor所占用的资源暂时不能被覆盖了...
同时session cursor cache count和session cursor cache hits的值都增加了,
session cursor cache hits表示uga中cached cursor被击中的次数,也就是说
是softer parse的次数,我们希望session cursor cache hits/parse count (total)的比例越大越好,说明
参数session_cached_cursors的设置发挥了作用
SQL> select count(*) from tt;

COUNT(*)
----------
11546

SQL> select parse_calls from v$sql where sql_text='select count(*) from tt';

PARSE_CALLS
-----------
4

SQL> select a.value, s.username, s.sid, s.serial#,b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'opened cursors current',
6 'session cursor cache count',
7 'session cursor cache hits',
8 'parse count (total)',
9 'session uga memory',
10 'session uga memory max',
11 'session pga memory',
12 'session pga memory max'
13 )
14 and s.sid=159
15 order by name;

VALUE USERNAME SID SERIAL# NAME
---------- ---------- ---------- ---------- ------------------------------
4 SYS 159 3 opened cursors current
2192 SYS 159 3 parse count (total)
4 SYS 159 3 session cursor cache count
8 SYS 159 3 session cursor cache hits
1171028 SYS 159 3 session pga memory
2481748 SYS 159 3 session pga memory max
679992 SYS 159 3 session uga memory
1486168 SYS 159 3 session uga memory max

已选择8行。

SQL> alter session set session_cached_cursors=0;

会话已更改。

SQL> select a.value, s.username, s.sid, s.serial#,b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'opened cursors current',
6 'session cursor cache count',
7 'session cursor cache hits',
8 'parse count (total)',
9 'session uga memory',
10 'session uga memory max',
11 'session pga memory',
12 'session pga memory max'
13 )
14 and s.sid=159
15 order by name;

VALUE USERNAME SID SERIAL# NAME
---------- ---------- ---------- ---------- ------------------------------
4 SYS 159 3 opened cursors current
2194 SYS 159 3 parse count (total)
5 SYS 159 3 session cursor cache count
8 SYS 159 3 session cursor cache hits
1171028 SYS 159 3 session pga memory
2481748 SYS 159 3 session pga memory max
679992 SYS 159 3 session uga memory
1486168 SYS 159 3 session uga memory max

已选择8行。

SQL> alter session set session_cached_cursors=0;

会话已更改。

SQL> select a.value, s.username, s.sid, s.serial#,b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'opened cursors current',
6 'session cursor cache count',
7 'session cursor cache hits',
8 'parse count (total)',
9 'session uga memory',
10 'session uga memory max',
11 'session pga memory',
12 'session pga memory max'
13 )
14 and s.sid=159
15 order by name;

VALUE USERNAME SID SERIAL# NAME
---------- ---------- ---------- ---------- ------------------------------
4 SYS 159 3 opened cursors current
2194 SYS 159 3 parse count (total)
5 SYS 159 3 session cursor cache count
8 SYS 159 3 session cursor cache hits
1171028 SYS 159 3 session pga memory
2481748 SYS 159 3 session pga memory max
679992 SYS 159 3 session uga memory
1486168 SYS 159 3 session uga memory max

已选择8行。

SQL> select count(*) from tt;

COUNT(*)
----------
11546

SQL> select sql_text from v$open_cursor where sid=159;

SQL_TEXT
------------------------------------------------------------
select ENQUEUE_ENABLED, DEQUEUE_ENABLED from dba_queues wher
select sql_text from v$open_cursor where sid=159
select wrm.last_ash_sample_id from WRM$_DATABASE_INSTANCE
--屏蔽session_cached_cursors之后,再次执行select count(*) from tt,
执行之后发现cursor被关闭了,同时session cursor cache count和session cursor cache hits的值不再变化
SQL> select a.value, s.username, s.sid, s.serial#,b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'opened cursors current',
6 'session cursor cache count',
7 'session cursor cache hits',
8 'parse count (total)',
9 'session uga memory',
10 'session uga memory max',
11 'session pga memory',
12 'session pga memory max'
13 )
14 and s.sid=159
15 order by name;

VALUE USERNAME SID SERIAL# NAME
---------- ---------- ---------- ---------- ------------------------------
4 SYS 159 3 opened cursors current
2197 SYS 159 3 parse count (total)
5 SYS 159 3 session cursor cache count
8 SYS 159 3 session cursor cache hits
1171028 SYS 159 3 session pga memory
2481748 SYS 159 3 session pga memory max
679992 SYS 159 3 session uga memory
1486168 SYS 159 3 session uga memory max

已选择8行。

SQL>
--=========================================
--下面是又一个测试案例,和上面的测试不是一起做的,所以看到的statistics可能会出现一点不连贯
SQL> select a.value, s.username, s.sid, s.serial#,b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'opened cursors current',
6 'session cursor cache count',
7 'session cursor cache hits',
8 'parse count (total)',
9 'session uga memory',
10 'session uga memory max',
11 'session pga memory',
12 'session pga memory max'
13 )
14 and s.sid=159
15 order by name;

VALUE USERNAME SID SERIAL# NAME
---------- ---------- ---------- ---------- ------------------------------
4 SYS 159 3 opened cursors current
2225 SYS 159 3 parse count (total)
1 SYS 159 3 session cursor cache count
6 SYS 159 3 session cursor cache hits
1433172 SYS 159 3 session pga memory
2350676 SYS 159 3 session pga memory max
549064 SYS 159 3 session uga memory
1420704 SYS 159 3 session uga memory max

已选择8行。

SQL> alter session set session_cached_cursors=100000;

会话已更改。

SQL> select a.value, s.username, s.sid, s.serial#,b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'opened cursors current',
6 'session cursor cache count',
7 'session cursor cache hits',
8 'parse count (total)',
9 'session uga memory',
10 'session uga memory max',
11 'session pga memory',
12 'session pga memory max'
13 )
14 and s.sid=159
15 order by name;

VALUE USERNAME SID SERIAL# NAME
---------- ---------- ---------- ---------- ------------------------------
4 SYS 159 3 opened cursors current
2227 SYS 159 3 parse count (total)
1 SYS 159 3 session cursor cache count
6 SYS 159 3 session cursor cache hits
1039956 SYS 159 3 session pga memory
2350676 SYS 159 3 session pga memory max
549064 SYS 159 3 session uga memory
1420704 SYS 159 3 session uga memory max

已选择8行。

SQL> declare
2 msql varchar2(500);
3 mcur number;
4 mstat number;
5 jg varchar2(4000);
6 begin
7 for j in 1..4 loop
8 for i in 1..100 loop
9 mcur:=dbms_sql.open_cursor;
10 msql:='select object_id from t where object_id='||i;
11 dbms_sql.parse(mcur,msql,dbms_sql.native);
12 dbms_sql.define_column(mcur,1,jg,4000);
13 mstat:=dbms_sql.execute(mcur);
14 dbms_sql.close_cursor(mcur);
15 end loop;
16 end loop;
17 end;
18 /

PL/SQL 过程已成功完成。

SQL> declare
2 msql varchar2(500);
3 mcur number;
4 mstat number;
5 jg varchar2(4000);
6 begin
7 for j in 1..4 loop
8 for i in 1..100 loop
9 mcur:=dbms_sql.open_cursor;
10 msql:='select object_id from t where object_id='||i;
11 dbms_sql.parse(mcur,msql,dbms_sql.native);
12 dbms_sql.define_column(mcur,1,jg,4000);
13 mstat:=dbms_sql.execute(mcur);
14 dbms_sql.close_cursor(mcur);
15 end loop;
16 end loop;
17 end;
18 /

PL/SQL 过程已成功完成。

SQL> declare
2 msql varchar2(500);
3 mcur number;
4 mstat number;
5 jg varchar2(4000);
6 begin
7 for j in 1..4 loop
8 for i in 1..100 loop
9 mcur:=dbms_sql.open_cursor;
10 msql:='select object_id from t where object_id='||i;
11 dbms_sql.parse(mcur,msql,dbms_sql.native);
12 dbms_sql.define_column(mcur,1,jg,4000);
13 mstat:=dbms_sql.execute(mcur);
14 dbms_sql.close_cursor(mcur);
15 end loop;
16 end loop;
17 end;
18 /

PL/SQL 过程已成功完成。

SQL> select a.value, s.username, s.sid, s.serial#,b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'opened cursors current',
6 'session cursor cache count',
7 'session cursor cache hits',
8 'parse count (total)',
9 'session uga memory',
10 'session uga memory max',
11 'session pga memory',
12 'session pga memory max'
13 )
14 and s.sid=159
15 order by name;

VALUE USERNAME SID SERIAL# NAME
---------- ---------- ---------- ---------- ------------------------------
4 SYS 159 3 opened cursors current
3469 SYS 159 3 parse count (total)
115 SYS 159 3 session cursor cache count
934 SYS 159 3 session cursor cache hits
1039956 SYS 159 3 session pga memory
2350676 SYS 159 3 session pga memory max
614528 SYS 159 3 session uga memory
1420704 SYS 159 3 session uga memory max

已选择8行。

SQL>
--对比cursor cache前后的uga memory的值,确实发生了变化,不过这个信息很多时候观察不到
--===============================
SQL> select sql_text from v$open_cursor where sid=159 and sql_text like 'select
object_id from t%'
2 ;

SQL_TEXT
------------------------------------------------------------
select object_id from t where object_id=59
select object_id from t where object_id=29
select object_id from t where object_id=56
select object_id from t where object_id=80
select object_id from t where object_id=39
select object_id from t where object_id=93
select object_id from t where object_id=64
select object_id from t where object_id=81
select object_id from t where object_id=87
select object_id from t where object_id=18
select object_id from t where object_id=25

SQL_TEXT
------------------------------------------------------------
select object_id from t where object_id=27
select object_id from t where object_id=24
select object_id from t where object_id=16
select object_id from t where object_id=40
select object_id from t where object_id=62
select object_id from t where object_id=76
select object_id from t where object_id=26
select object_id from t where object_id=77
select object_id from t where object_id=23
select object_id from t where object_id=95
select object_id from t where object_id=92

SQL_TEXT
------------------------------------------------------------
select object_id from t where object_id=34
select object_id from t where object_id=53
select object_id from t where object_id=86
select object_id from t where object_id=48
select object_id from t where object_id=15
select object_id from t where object_id=65
select object_id from t where object_id=74
select object_id from t where object_id=78
select object_id from t where object_id=89
select object_id from t where object_id=46
select object_id from t where object_id=51

SQL_TEXT
------------------------------------------------------------
select object_id from t where object_id=67
select object_id from t where object_id=69
select object_id from t where object_id=70
select object_id from t where object_id=75
select object_id from t where object_id=96
select object_id from t where object_id=79
select object_id from t where object_id=83
select object_id from t where object_id=85
select object_id from t where object_id=50
select object_id from t where object_id=57
select object_id from t where object_id=61

SQL_TEXT
------------------------------------------------------------
select object_id from t where object_id=94
select object_id from t where object_id=100
select object_id from t where object_id=88
select object_id from t where object_id=12
select object_id from t where object_id=42
select object_id from t where object_id=52
select object_id from t where object_id=20
select object_id from t where object_id=66
select object_id from t where object_id=8
select object_id from t where object_id=71
select object_id from t where object_id=6

SQL_TEXT
------------------------------------------------------------
select object_id from t where object_id=73
select object_id from t where object_id=82
select object_id from t where object_id=1
select object_id from t where object_id=33
select object_id from t where object_id=72
select object_id from t where object_id=99
select object_id from t where object_id=22
select object_id from t where object_id=45
select object_id from t where object_id=55
select object_id from t where object_id=4
select object_id from t where object_id=37

SQL_TEXT
------------------------------------------------------------
select object_id from t where object_id=97
select object_id from t where object_id=10
select object_id from t where object_id=43
select object_id from t where object_id=38
select object_id from t where object_id=68
select object_id from t where object_id=47
select object_id from t where object_id=9
select object_id from t where object_id=31
select object_id from t where object_id=84
select object_id from t where object_id=63
select object_id from t where object_id=49

SQL_TEXT
------------------------------------------------------------
select object_id from t where object_id=32
select object_id from t where object_id=98
select object_id from t where object_id=17
select object_id from t where object_id=54
select object_id from t where object_id=35
select object_id from t where object_id=7
select object_id from t where object_id=21
select object_id from t where object_id=36
select object_id from t where object_id=2
select object_id from t where object_id=3
select object_id from t where object_id=14

SQL_TEXT
------------------------------------------------------------
select object_id from t where object_id=28
select object_id from t where object_id=13
select object_id from t where object_id=91
select object_id from t where object_id=44
select object_id from t where object_id=30
select object_id from t where object_id=58
select object_id from t where object_id=11
select object_id from t where object_id=5
select object_id from t where object_id=41
select object_id from t where object_id=19
select object_id from t where object_id=90

已选择99行。

SQL>
--========================================
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 88081764 bytes
Database Buffers 75497472 bytes
Redo Buffers 2945024 bytes
数据库装载完毕。
数据库已经打开。
SQL> select a.value, s.username, s.sid, s.serial#,b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'opened cursors current',
6 'session cursor cache count',
7 'session cursor cache hits',
8 'parse count (total)',
9 'session uga memory',
10 'session uga memory max',
11 'session pga memory',
12 'session pga memory max'
13 )
14 and s.sid=159
15 order by name;

VALUE USERNAME SID SERIAL# NAME
---------- ---------- ---------- ---------- ------------------------------
4 SYS 159 3 opened cursors current
2145 SYS 159 3 parse count (total)
0 SYS 159 3 session cursor cache count
0 SYS 159 3 session cursor cache hits
1564244 SYS 159 3 session pga memory
2481748 SYS 159 3 session pga memory max
679992 SYS 159 3 session uga memory
844952 SYS 159 3 session uga memory max

已选择8行。

SQL> show parameter cursor

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 30000
session_cached_cursors integer 0
SQL> declare
2 msql varchar2(500);
3 mcur number;
4 mstat number;
5 jg varchar2(4000);
6 begin
7 for j in 1..4 loop
8 for i in 1..1000 loop
9 mcur:=dbms_sql.open_cursor;
10 msql:='select object_id from t where object_id='||i;
11 dbms_sql.parse(mcur,msql,dbms_sql.native);
12 dbms_sql.define_column(mcur,1,jg,4000);
13 mstat:=dbms_sql.execute(mcur);
14 dbms_sql.close_cursor(mcur);
15 end loop;
16 end loop;
17 end;
18 /

PL/SQL 过程已成功完成。

SQL> declare
2 msql varchar2(500);
3 mcur number;
4 mstat number;
5 jg varchar2(4000);
6 begin
7 for j in 1..4 loop
8 for i in 1..1000 loop
9 mcur:=dbms_sql.open_cursor;
10 msql:='select object_id from t where object_id='||i;
11 dbms_sql.parse(mcur,msql,dbms_sql.native);
12 dbms_sql.define_column(mcur,1,jg,4000);
13 mstat:=dbms_sql.execute(mcur);
14 dbms_sql.close_cursor(mcur);
15 end loop;
16 end loop;
17 end;
18 /

PL/SQL 过程已成功完成。

SQL> select a.value, s.username, s.sid, s.serial#,b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'opened cursors current',
6 'session cursor cache count',
7 'session cursor cache hits',
8 'parse count (total)',
9 'session uga memory',
10 'session uga memory max',
11 'session pga memory',
12 'session pga memory max'
13 )
14 and s.sid=159
15 order by name;

VALUE USERNAME SID SERIAL# NAME
---------- ---------- ---------- ---------- ------------------------------
4 SYS 159 3 opened cursors current
10213 SYS 159 3 parse count (total)
0 SYS 159 3 session cursor cache count
0 SYS 159 3 session cursor cache hits
1564244 SYS 159 3 session pga memory
2481748 SYS 159 3 session pga memory max
679992 SYS 159 3 session uga memory
1486168 SYS 159 3 session uga memory max

已选择8行。

SQL> declare
2 msql varchar2(500);
3 mcur number;
4 mstat number;
5 jg varchar2(4000);
6 begin
7 for j in 1..4 loop
8 for i in 1..1000 loop
9 mcur:=dbms_sql.open_cursor;
10 msql:='select object_id from t where object_id='||i;
11 dbms_sql.parse(mcur,msql,dbms_sql.native);
12 dbms_sql.define_column(mcur,1,jg,4000);
13 mstat:=dbms_sql.execute(mcur);
14 dbms_sql.close_cursor(mcur);
15 end loop;
16 end loop;
17 end;
18 /

PL/SQL 过程已成功完成。

SQL> alter session set session_cached_cursors=2000;

会话已更改。

SQL> declare
2 msql varchar2(500);
3 mcur number;
4 mstat number;
5 jg varchar2(4000);
6 begin
7 for j in 1..4 loop
8 for i in 1..1000 loop
9 mcur:=dbms_sql.open_cursor;
10 msql:='select object_id from t where object_id='||i;
11 dbms_sql.parse(mcur,msql,dbms_sql.native);
12 dbms_sql.define_column(mcur,1,jg,4000);
13 mstat:=dbms_sql.execute(mcur);
14 dbms_sql.close_cursor(mcur);
15 end loop;
16 end loop;
17 end;
18 /

PL/SQL 过程已成功完成。

SQL> select a.value, s.username, s.sid, s.serial#,b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'opened cursors current',
6 'session cursor cache count',
7 'session cursor cache hits',
8 'parse count (total)',
9 'session uga memory',
10 'session uga memory max',
11 'session pga memory',
12 'session pga memory max'
13 )
14 and s.sid=159
15 order by name;

VALUE USERNAME SID SERIAL# NAME
---------- ---------- ---------- ---------- ------------------------------
4 SYS 159 3 opened cursors current
18226 SYS 159 3 parse count (total)
2 SYS 159 3 session cursor cache count
2 SYS 159 3 session cursor cache hits
1564244 SYS 159 3 session pga memory
2481748 SYS 159 3 session pga memory max
679992 SYS 159 3 session uga memory
1486168 SYS 159 3 session uga memory max

已选择8行。

SQL>
--让人不解的事出现了,为什么for i in 1..100中循环100次cursor进行了cache,而改为1000之后cursor不
cache了?

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

请登录后发表评论 登录
全部评论

注册时间:2007-12-07

  • 博文量
    717
  • 访问量
    5098154