受困於一個引數：session_cached_cursors
這個引數本身不難理解，但是和這個參賽相關的很多話題值得我們深思：
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 引數SESSION_CACHED_CURSORSSession
- 關於open_cursors和session_cached_cursors的引數值Session
- 引數session_cached_cursors的詳細解釋(zt)Session
- 引數 session_cached_cursors 與 open_cursors詳解Session
- 受HttpClient困繞的問題HTTPclient
- 修改open_cursors和session_cached_cursors的引數值Session
- Oracle 11gR2 調整session_cached_cursors引數OracleSession
- 引數session_cached_cursors的工作原理及優缺點分析Session
- 9i資料庫下修改session_cached_cursors引數資料庫Session
- 機器學習最困難的部分：超引數除錯機器學習除錯
- 【最佳化】引數SESSION_CACHED_CURSORS與解析之間的關係Session
- 記錄一個Oracle引數Oracle
- javascript bind()第一個引數以後引數介紹JavaScript
- substring()第一個引數值大於字串最大索引字串索引
- 受困於v$asm_dksigroup裡的欄位REQUIRED_MIRROR_FREE_MB！ASMUI
- 給一個介面傳遞引數，並接收返回的引數
- 一個關於Struts傳引數的問題,請高手來看看............
- oracle的一個隱含引數Oracle
- 一個Java方法能使用多少個引數？Java
- @babel/preset-env的一個引數Babel
- apply()第一個引數是nullAPPNull
- leetcode：41. 缺失的第一個正數（困難，陣列）LeetCode陣列
- 關於靜態引數和動態引數
- 12.MyBatis學習--對映檔案_引數處理_單個引數&多個引數&命名引數MyBatis
- 關於資料泵impdp引數驗證（一）
- 一個效能較好的JVM引數配置JVM
- ABAP FUNCTION 遠端呼叫的一個引數Function
- tar 命令一個比較有用的引數
- shell前一個命令結果作為下一個命令引數
- 請教一個一直以來困擾我的關於DAO模式的問題!!!模式
- 關於歸檔日誌的幾個引數選項
- 一個輕量級的引數校驗框架框架
- 一個vue路由引數傳遞的注意點Vue路由
- 專利問題仍在 Linux飽受法律糾紛困擾Linux
- 什麼是請求引數、表單引數、url引數、header引數、Cookie引數？一文講懂HeaderCookie
- oracle 關於--引數檔案Oracle
- oracle兩個 retention 引數Oracle
- 兩個引數設定