ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于sequence的争用

关于sequence的争用

原创 Linux操作系统 作者:redhouser 时间:2011-11-30 16:46:31 0 删除 编辑

序列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/,如需转载,请注明出处,否则将追究法律责任。

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

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    790796