序列sequence相关保护机制:
*row cache lock:在调用sequence.nextval情况下需要修改数据字典时发生,对应row cache lock事件
*SQ lock:在内存缓存(并非rowcache)上获取sequence.nextval时发生,对应enq:SQ-contention事件
*SV lock:RAC环境下获取cache+order属性的sequence.nextval时发生,对应DFS lock handle事件
下面逐一进行测试：
测试环境:
SQL> select * from v$version;
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Productio
NLSRTL Version 10.2.0.5.0 - Production
1，sequence nocache时等待:row cache lock
drop sequence seq_test;
create sequence seq_test nocache;
create or replace procedure get_seq as
i int;
begin
for x in 1..10000 loop
select seq_test.nextval into i from dual;
end loop;
end;
/
declare
i int;
begin
for idx in 1..10 loop
dbms_job.submit(i,'get_seq;');
end loop;
end;
/
commit;
set serveroutput on
exec session_pkg.get_snap;
exec get_seq;
exec session_pkg.rpt_stat_event(0);
==>
----session stats---
Name Value
redo size 6,892,644
undo change vector size 2,198,884
session pga memory 2,031,616
session uga memory 1,632,248
recursive calls 150,036
session logical reads 40,304
db block changes 40,005
db block gets 30,293
db block gets from cache 30,293
calls to get snapshot scn: kcm 30,005
execute count 20,007 -->execute 20000
enqueue releases 20,006
redo entries 20,003
enqueue requests 20,003
messages sent 12,094
calls to kcmgas 10,285
global enqueue releases 10,019
global enqueue gets sync 10,017
consistent gets from cache 10,011
consistent gets - examination 10,011
consistent gets 10,011
opened cursors cumulative 10,007
parse count (total) 10,007
index fetch by key 10,004
commit cleanouts 10,002
session cursor cache hits 10,001 -->session内缓存cursor,不需要查找library cache,但还是经历软解析
commit cleanouts successfully 10,000
deferred (CURRENT) block clean 10,000
workarea memory allocated 7,337
DB time 4,070 -->40sec
concurrency wait time 3,407 -->wait 34sec
bytes received via SQL*Net fro 616
CPU used when call started 411
CPU used by this session 410
recursive cpu usage 408
sorts (rows) 388
free buffer inspected 385
bytes sent via SQL*Net to clie 379
redo ordering marks 284
gc local grants 246
free buffer requested 246
change write time 107
redo subscn max counts 44
parse time elapsed 24
heap block compress 14
global enqueue get time 13
buffer is not pinned count 8
user calls 6
rows fetched via callback 4
table fetch by rowid 4
SQL*Net roundtrips to/from cli 4
parse count (hard) 3
redo synch writes 2
consistent changes 2
commit cleanout failures: bloc 2
sorts (memory) 1
sql area evicted 1
user commits 1
opened cursors current 1
----session events---
Wait_Class Event Total_waits Time_waited
Commit log file sync 2 1
Concurrency row cache lock 8,850 3,407 -->wait 34sec
Idle SQL*Net message from client 4 2
Network SQL*Net message to client 4 0
Other events in waitclass Other 2 0
----v$session_wait_history---
SEQEvent p1 p2 p3 wcnt wtim
1 SQL*Net message from 1413697536 1 0 1 1
2 SQL*Net message to c 1413697536 1 0 1 0
3 SQL*Net message from 1413697536 1 0 1 1
4 SQL*Net message to c 1413697536 1 0 1 0
5 log file sync 5015 4244409430 0 1 0
6 row cache lock 13 0 5 1 0 -->对dc_sequences进行mode=5的锁请求
7 row cache lock 13 0 5 1 0
8 row cache lock 13 0 5 1 0
9 row cache lock 13 0 5 1 0
10 row cache lock 13 0 5 1 0
PL/SQL 过程已成功完成。
SELECT NAME, parameter1, parameter2, parameter3
FROM v$event_name
WHERE NAME = 'row cache lock';
NAME PARAMETER1 PARAMETER2 PARAMETER3
-------------------- -------------------- -------------------- ----------------
row cache lock cache id mode request
SQL> select type,parameter from v$rowcache where cache#=13;
TYPE PARAMETER
---------------------- -------------------------------------------------------
PARENT dc_sequences
2 设置sequence cache时：
2.1与案例1相同的访问量在增加sequence cache 100时，row cache lock消失
drop sequence seq_test;
create sequence seq_test cache 100;
create or replace procedure get_seq as
i int;
begin
for x in 1..10000 loop
select seq_test.nextval into i from dual;
end loop;
end;
/
declare
i int;
begin
for idx in 1..10 loop
dbms_job.submit(i,'get_seq;');
end loop;
end;
/
commit;
set serveroutput on
exec session_pkg.get_snap;
exec get_seq;
exec session_pkg.rpt_stat_event(0);
==>
----session stats---
Name Value
session pga memory 2,228,224
session uga memory 1,632,248
redo size 70,876
undo change vector size 22,996
recursive calls 11,406
calls to get snapshot scn: kcm 10,203
execute count 10,105
workarea memory allocated 7,345
bytes received via SQL*Net fro 616
session logical reads 411
db block changes 405
sorts (rows) 388
bytes sent via SQL*Net to clie 383
db block gets from cache 305
db block gets 305
enqueue releases 305
enqueue requests 302
messages sent 301
redo entries 203
global enqueue releases 113
global enqueue gets sync 111
consistent gets from cache 106
consistent gets 106
consistent gets - examination 106
parse count (total) 105
opened cursors cumulative 105
calls to kcmgas 103
commit cleanouts 102
session cursor cache hits 102
index fetch by key 102
commit cleanouts successfully 100
deferred (CURRENT) block clean 100
DB time 62-->0.62sec,耗时大大减少
CPU used when call started 42
CPU used by this session 42
recursive cpu usage 38
user calls 6
SQL*Net roundtrips to/from cli 4
buffer is not pinned count 4
rows fetched via callback 2
redo ordering marks 2
commit cleanout failures: bloc 2
consistent changes 2
gc local grants 2
redo synch writes 2
parse count (hard) 2
table fetch by rowid 2
free buffer requested 2
sorts (memory) 1
redo synch time 1
user commits 1
opened cursors current 1
----session events---
Wait_Class Event Total_waits Time_waited
Commit log file sync 1 0-->row cache lock等待事件消失
Idle SQL*Net message from client 4 0
Network SQL*Net message to client 4 0
----v$session_wait_history---
SEQEvent p1 p2 p3 wcnt wtim
1 SQL*Net message from 1413697536 1 0 1 0
2 SQL*Net message to c 1413697536 1 0 1 0
3 SQL*Net message from 1413697536 1 0 1 0
4 SQL*Net message to c 1413697536 1 0 1 0
5 SQL*Net message from 1413697536 1 0 1 0
6 SQL*Net message to c 1413697536 1 0 1 0
7 SQL*Net message from 1413697536 1 0 1 0
8 SQL*Net message to c 1413697536 1 0 1 0
9 log file sync 9827 4244452223 0 1 0
10 SQL*Net message from 1413697536 1 0 1 0
PL/SQL 过程已成功完成。
2.2没有出现enq: SQ - contention，增大cache，循环次数，并发数：
drop sequence seq_test;
create sequence seq_test cache 10000;
create or replace procedure get_seq as
i int;
begin
for x in 1..100000 loop
select seq_test.nextval into i from dual;
end loop;
end;
/
declare
i int;
begin
for idx in 1..100 loop
dbms_job.submit(i,'get_seq;');
end loop;
end;
/
commit;
set serveroutput on
exec session_pkg.get_snap;
exec get_seq;
exec session_pkg.rpt_stat_event(0);
----session stats---
Name Value
session pga memory 2,293,760
session uga memory 1,763,080
recursive calls 100,309
calls to get snapshot scn: kcm 100,036
execute count 100,026
redo size 9,596
workarea memory allocated 7,383
undo change vector size 3,596
DB time 1,043
bytes received via SQL*Net fro 616
CPU used when call started 456
CPU used by this session 455
recursive cpu usage 418
sorts (rows) 388
bytes sent via SQL*Net to clie 383
enqueue releases 110
enqueue requests 107
session logical reads 77
enqueue waits 72
db block changes 53
db block gets from cache 41
db block gets 41
messages sent 37
consistent gets 36
consistent gets from cache 36
consistent gets - examination 36
global enqueue releases 34
global enqueue gets sync 32
redo entries 27
opened cursors cumulative 26
parse count (total) 26
index fetch by key 23
buffer is not pinned count 22
free buffer requested 20
gcs messages sent 18
calls to kcmgas 15
concurrency wait time 15
session cursor cache hits 14
commit cleanouts 14
commit cleanouts successfully 12
deferred (CURRENT) block clean 12
table fetch by rowid 11
rows fetched via callback 11
gc current blocks received 9
gc cr blocks received 9
shared hash latch upgrades - n 9
cluster wait time 7
user calls 6
gc current block receive time 6
SQL*Net roundtrips to/from cli 4
redo ordering marks 2
commit cleanout failures: bloc 2
parse count (hard) 2
redo synch writes 2
consistent changes 2
gc local grants 2
sorts (memory) 1
user commits 1
opened cursors current 1
----session events---
Wait_Class Event Total_waits Time_waited
Cluster gc cr block 2-way 9 0
Cluster gc current block 2-way 9 6
Commit log file sync 1 0
Concurrency latch: library cache pin 3 9
Concurrency latch: library cache 7 5
Concurrency row cache lock 12 2
Configuration enq: SQ - contention 72 74
Idle SQL*Net message from client 4 0
Network SQL*Net message to client 4 0
Other events in waitclass Other 378 7
----v$session_wait_history---
SEQEvent p1 p2 p3 wcnt wtim
1 SQL*Net message from 1413697536 1 0 1 0
2 SQL*Net message to c 1413697536 1 0 1 0
3 SQL*Net message from 1413697536 1 0 1 0
4 SQL*Net message to c 1413697536 1 0 1 0
5 enq: SQ - contention 1397817350 190210 0 1 0 -->SEQ_TEST
6 cursor: pin S 897271524 70136815943712884916963 1 0
7 cursor: pin S 897271524 65884798320612884916959 1 0
8 enq: SQ - contention 1397817350 190210 0 1 0
9 enq: SQ - contention 1397817350 190210 0 1 0
10 cursor: pin S 897271524 66185446031338654720727 1 0
PL/SQL 过程已成功完成。
SQL> SELECT NAME, parameter1, parameter2, parameter3
2 FROM v$event_name
3 WHERE NAME = 'enq: SQ - contention';
NAME PARAMETER1 PARAMETER2 PARAMETER3
-------------------- -------------------- -------------------- -----------------
enq: SQ - contention name|mode object # 0
SQL> SELECT NAME, id1_tag, id2_tag, is_user FROM v$lock_type WHERE TYPE = 'SQ';
NAME ID1_TAG ID2_TAG IS_USER
-------------------- -------------------- -------------------- ---------------
Sequence Cache object # 0 NO
SQL> select object_name,object_type from dba_objects where object_id=190210;
OBJECT_NAME OBJECT_TYPE
--------------------------------------
SEQ_TEST SEQUENCE
3，RAC情况下：
3.1与案例2.2相同的访问量分布在两个实例时:
实例1:
drop sequence seq_test;
create sequence seq_test cache 10000;
create or replace procedure get_seq as
i int;
begin
for x in 1..100000 loop
select seq_test.nextval into i from dual;
end loop;
end;
/
declare
i int;
begin
for idx in 1..50 loop
dbms_job.submit(i,'get_seq;');
end loop;
end;
/
commit;
实例2:
declare
i int;
begin
for idx in 1..50 loop
dbms_job.submit(i,'get_seq;');
end loop;
end;
/
commit;
set serveroutput on
exec session_pkg.get_snap;
exec get_seq;
exec session_pkg.rpt_stat_event(0);
----session stats---
Name Value
session pga memory 1,310,720
session uga memory 1,278,720
session uga memory max 700,968
session pga memory max 655,360
recursive calls 100,913
calls to get snapshot scn: kcm 100,113
execute count 100,077
redo size 15,828
workarea memory allocated 7,161
undo change vector size 5,556
DB time 1,415
sorts (rows) 1,175
table scan rows gotten 776
bytes received via SQL*Net fro 616
bytes sent via SQL*Net to clie 351
session logical reads 276
CPU used when call started 269
CPU used by this session 265
recursive cpu usage 234
consistent gets from cache 205
consistent gets 205
enqueue releases 151
enqueue requests 148
consistent gets - examination 135
buffer is not pinned count 131
db block changes 93
enqueue waits 84
global enqueue releases 81
global enqueue gets sync 78
messages sent 77
opened cursors cumulative 74
db block gets from cache 71
db block gets 71
parse count (total) 64
table fetch by rowid 60
index fetch by key 60
no work - consistent read gets 58
concurrency wait time 57
session cursor cache hits 45
redo entries 45
free buffer requested 33
gcs messages sent 32
rows fetched via callback 32
commit cleanouts 23
calls to kcmgas 22
index scans kdiixs1 21
deferred (CURRENT) block clean 21
commit cleanouts successfully 21
shared hash latch upgrades - n 19
gc cr blocks received 17
gc current blocks received 15
sorts (memory) 10
parse count (hard) 8
consistent changes 7
user calls 6
cluster key scans 4
cluster key scan block gets 4
SQL*Net roundtrips to/from cli 4
workarea executions - optimal 4
ges messages sent 4
index crx upgrade (positioned) 4
cluster wait time 3
opened cursors current 3
table scans (short tables) 2
gc cr block receive time 2
redo synch writes 2
parse time elapsed 2
commit cleanout failures: bloc 2
table scan blocks gotten 2
global enqueue get time 1
user commits 1
buffer is pinned count 1
cursor authentications 1
gc current block receive time 1
cleanouts only - consistent re 1
immediate (CR) block cleanout 1
commit txn count during cleano 1
cleanout - number of ktugct ca 1
----session events---
Wait_Class Event Total_waits Time_waited
Cluster gc current block 2-way 16 1
Cluster gc cr block 2-way 21 2
Commit log file sync 1 0
Concurrency latch: library cache lock 5 6
Concurrency library cache pin 6 1
Concurrency library cache lock 8 1
Concurrency latch: library cache pin 9 12
Concurrency latch: library cache 23 32
Concurrency row cache lock 31 6
Configuration enq: SQ - contention 84 370
Idle SQL*Net message from client 4 3
Network SQL*Net message to client 4 0
Other events in waitclass Other 1,793 61
----v$session_wait_history---
SEQEvent p1 p2 p3 wcnt wtim
1 row cache lock 16 0 3 1 0
2 row cache lock 16 0 3 1 0
3 row cache lock 16 0 3 1 0
4 row cache lock 16 0 3 1 0
5 row cache lock 16 0 3 1 0
6 row cache lock 16 0 3 1 0
7 row cache lock 16 0 3 1 0
8 row cache lock 16 0 3 1 0
9 row cache lock 16 0 3 1 3
10 gc cr block 2-way 1 49289 1 1 0
PL/SQL 过程已成功完成。
SQL> select type,parameter from v$rowcache where cache#=16;
TYPE PARAMETER
---------------------- --------------------------------------------------------
PARENT dc_histogram_defs
SUBORDINATE dc_histogram_data
SUBORDINATE dc_histogram_data ==>为什么不是dc_sequences?
3.2与案例3.1相同的访问量分布在两个实例,修改cache+order时:
实例1:
drop sequence seq_test;
create sequence seq_test cache 10000 order;
create or replace procedure get_seq as
i int;
begin
for x in 1..100000 loop
select seq_test.nextval into i from dual;
end loop;
end;
/
declare
i int;
begin
for idx in 1..50 loop
dbms_job.submit(i,'get_seq;');
end loop;
end;
/
commit;
实例2:
declare
i int;
begin
for idx in 1..50 loop
dbms_job.submit(i,'get_seq;');
end loop;
end;
/
commit;
set serveroutput on
exec session_pkg.get_snap;
exec get_seq;
exec session_pkg.rpt_stat_event(0);
==>
----session stats---
Name Value
session pga memory 1,966,080
session uga memory 1,632,248
recursive calls 100,291
calls to get snapshot scn: kcm 100,038
global enqueue releases 100,032
global enqueue gets sync 100,030
execute count 100,026
DB time 48,855 ==>488sec
global enqueue get time 45,125 ==>451sec,等待大大增加
redo size 10,904
workarea memory allocated 7,336
undo change vector size 4,036
CPU used by this session 2,931
CPU used when call started 2,931
recursive cpu usage 2,927
ges messages sent 1,932
bytes received via SQL*Net fro 616
sorts (rows) 388
bytes sent via SQL*Net to clie 379
session logical reads 92
free buffer inspected 78
db block changes 61
messages sent 48
db block gets from cache 47
db block gets 47
enqueue releases 47
consistent gets from cache 45
consistent gets 45
enqueue requests 44
consistent gets - examination 31
redo entries 31
parse count (total) 26
buffer is not pinned count 24
opened cursors cumulative 23
session cursor cache hits 20
index fetch by key 18
calls to kcmgas 17
commit cleanouts 16
no work - consistent read gets 14
deferred (CURRENT) block clean 14
commit cleanouts successfully 14
table fetch by rowid 11
free buffer requested 8
gcs messages sent 6
user calls 6
gc cr blocks received 6
index scans kdiixs1 5
rows fetched via callback 4
SQL*Net roundtrips to/from cli 4
redo ordering marks 2
commit cleanout failures: bloc 2
redo synch writes 2
consistent changes 2
parse count (hard) 2
gc local grants 2
sorts (memory) 1
parse time elapsed 1
cursor authentications 1
heap block compress 1
concurrency wait time 1
user commits 1
----session events---
Wait_Class Event Total_waits Time_waited
Cluster gc cr block 2-way 7 0
Commit log file sync 1 0
Concurrency library cache pin 1 0
Concurrency library cache lock 1 0
Concurrency row cache lock 15 0
Idle SQL*Net message from client 4 1
Network SQL*Net message to client 4 0
Other events in waitclass Other 119,070 43,482 ==>DFS lock handle等待,434sec
----v$session_wait_history---
SEQEvent p1 p2 p3 wcnt wtim
1 SQL*Net message from 1413697536 1 0 1 0
2 SQL*Net message to c 1413697536 1 0 1 0
3 SQL*Net message from 1413697536 1 0 1 0
4 SQL*Net message to c 1413697536 1 0 1 0
5 DFS lock handle 1398145029 190210 0 1 0
6 DFS lock handle 1398145029 190210 0 1 0
7 DFS lock handle 1398145029 190210 0 1 0
8 DFS lock handle 1398145029 190210 0 1 0
9 DFS lock handle 1398145029 190210 0 1 0
10 DFS lock handle 1398145029 190210 0 1 0
PL/SQL 过程已成功完成。
SQL> SELECT NAME, parameter1, parameter2, parameter3, wait_class
2 FROM v$event_name
3 WHERE NAME = 'DFS lock handle';
NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS
-------------------- -------------------- -------------------- ---------------- -----------
DFS lock handle type|mode id1 id2 Other
SQL> select object_name,object_type from dba_objects where object_id=190210;
OBJECT_NAME OBJECT_TYPE
-------------------------------------------------------------------------------
SEQ_TEST SEQUENCE
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-712456/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/18922393/viewspace-712456/
249
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
