sequence的nocache选项

最新推荐文章于 2023-10-11 16:17:54 发布
最新推荐文章于 2023-10-11 16:17:54 发布
阅读量883 收藏
点赞数
文章标签： 数据库

--问题:
1,创建nocache sequence在高并发访问时,容易导致row cache lock等待事件,主要原因是每次获取nextval时都需要修改rowcache中
的字典信息,本文对此进行测试.
2,相关信息:
2.1使用nocache sequence,还会导致如下问题:
*由于每次修改字典信息都需要commit,可能导致log file sync等待
*nocache sequence在RAC环境下,会对基于sequence生成的列创建的索引造成实例间大量索引块争用
基于以上问题,避免创建nocache sequence.
2.2,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事件

 

--1,创建sequence nocache:
create sequence seq_test nocache;

--获取该对象在rowcache中的地址:
SQL> select object_id from dba_objects where object_name='SEQ_TEST';

 OBJECT_ID
----------
     63014

SQL> select to_char(63014,'xxxxxxxx') from dual;

TO_CHAR(63014,'XXXXXXXX')
---------------------------
     f626

alter session set events 'immediate trace name row_cache level 12';


根据“f626”在跟踪文件中查找到对应rowcache对象，其中own/wat为rowcache lock当前持有或等待该锁的进程：
BUCKET 126:
  row cache parent object: address=0x667408dc cid=13(dc_sequences)
  hash=32f774fd typ=9 transaction=(nil) flags=00000002
  wn=0x66740948[0x66740948,0x66740948] wat=0x66740950[0x66740950,0x66740950] mode=N
  status=VALID/-/-/-/-/-/-/-/-
  data=
  0000f626 00020004 000f0002 00030001 000002c1 00000000 00000000 00000000
  02c10000 00000000 00000000 00000000 00000000 64640ace 64646464 64646464
  00646464 00800000 00000000 00000000 00000000 00000000 000603c2 00000000
  00000000 00000000 2d2d0000 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d
  2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d
  BUCKET 126 total object count=1

==>
地址0x667408dc即为该对象在rowcache中的地址,可通过v$rowcache_parent.address获取相关对象信息.

--2,每次获取nextval时,都会：
*修改该序列的当前值，并commit，以持久化到存储设备(v$rowcache上的GETS，MODIFICATIONS，FLUSHES都增加了10000)
*获取该对象上的Shared-sub-exclusive mode的锁(v$rowcache_parent的LOCK_MODE列值为5)

--session1:
SQL> SELECT s.event,s.TOTAL_WAITS,s.TIME_WAITED
  2    FROM v$session_event s
  3   WHERE s.sid = 159
  4     AND s.event = 'log file sync';

EVENT                TOTAL_WAITS TIME_WAITED
-------------------- ----------- -----------
log file sync                  5          38

SQL> 
SQL> SELECT COUNT,
  2         usage,
  3         fixed,
  4         gets,
  5         getmisses,
  6         modifications,
  7         flushes
  8    FROM v$rowcache
  9   WHERE parameter = 'dc_sequences';

     COUNT      USAGE      FIXED       GETS  GETMISSES MODIFICATIONS    FLUSHES
---------- ---------- ---------- ---------- ---------- ------------- ----------
         5          5          0      40243          5         40243      40243

--session2:
declare
  i int;
begin
  for x in 1..10000 loop
    select seq_test.nextval into i from dual;
  end loop;
end;
/ 

--session1:
sql>select cache_name,existent,lock_mode,lock_request,txn,saddr
  from v$rowcache_parent where address='667408DC'

CACHE_NAME      EXI  LOCK_MODE LOCK_REQUEST TXN      SADDR
--------------- --- ---------- ------------ -------- --------
dc_sequences    Y            5            0 65725460 673905A4  ==>lock_mode=5


SQL> SELECT s.event,s.TOTAL_WAITS,s.TIME_WAITED
  2    FROM v$session_event s
  3   WHERE s.sid = 159
  4     AND s.event = 'log file sync';

EVENT                TOTAL_WAITS TIME_WAITED
-------------------- ----------- -----------
log file sync                  6          38    ==>由于plsql内部批量提交,只提交一次.

SQL> 
SQL> SELECT COUNT,
  2         usage,
  3         fixed,
  4         gets,
  5         getmisses,
  6         modifications,
  7         flushes
  8    FROM v$rowcache
  9   WHERE parameter = 'dc_sequences';

     COUNT      USAGE      FIXED       GETS  GETMISSES MODIFICATIONS    FLUSHES
---------- ---------- ---------- ---------- ---------- ------------- ----------
         5          5          0      50243          5         50243      50243  ==>+10000

--3,作为对比,修改sequence cache为1000进行测试:
--session1:
alter sequence seq_test cache 1000;


SQL> SELECT s.event,s.TOTAL_WAITS,s.TIME_WAITED
  2    FROM v$session_event s
  3   WHERE s.sid = 159
  4     AND s.event = 'log file sync';

EVENT                TOTAL_WAITS TIME_WAITED
-------------------- ----------- -----------
log file sync                  7          38

SQL> 
SQL> SELECT COUNT,
  2         usage,
  3         fixed,
  4         gets,
  5         getmisses,
  6         modifications,
  7         flushes
  8    FROM v$rowcache
  9   WHERE parameter = 'dc_sequences';

     COUNT      USAGE      FIXED       GETS  GETMISSES MODIFICATIONS    FLUSHES
---------- ---------- ---------- ---------- ---------- ------------- ----------
         5          5          0      50244          5         50244      50244  ==>因alter sequence操作增加一次

--session2:
declare
  i int;
begin
  for x in 1..10000 loop
    select seq_test.nextval into i from dual;
  end loop;
end;
/ 

--session1:
SQL> r
  1* select cache_name,existent,lock_mode,lock_request,txn,saddr from v$rowcache_parent where address='667408DC'

CACHE_NAME      EXI  LOCK_MODE LOCK_REQUEST TXN      SADDR
--------------- --- ---------- ------------ -------- --------
dc_sequences    Y            0            0 00       00          ==>只取10次,抓取锁定状态概率太小

SQL> SELECT s.event,s.TOTAL_WAITS,s.TIME_WAITED
  2    FROM v$session_event s
  3   WHERE s.sid = 159
  4     AND s.event = 'log file sync';

EVENT                TOTAL_WAITS TIME_WAITED
-------------------- ----------- -----------
log file sync                  7          38

SQL> 
SQL> SELECT COUNT,
  2         usage,
  3         fixed,
  4         gets,
  5         getmisses,
  6         modifications,
  7         flushes
  8    FROM v$rowcache
  9   WHERE parameter = 'dc_sequences';

     COUNT      USAGE      FIXED       GETS  GETMISSES MODIFICATIONS    FLUSHES
---------- ---------- ---------- ---------- ---------- ------------- ----------
         5          5          0      50254          5         50254      50254 ==>+10000
 
 
--&gt修改只有10次(10000/1000=10)!


--4,通过10046跟踪nextval获取过程
4.1 no cachesequence的
create sequence seq_test_cache0 nocache;

alter session set events '10046 trace name context forever,level 12';

select seq_test_cache0.nextval from dual;
select seq_test_cache0.nextval from dual;
select seq_test_cache0.nextval from dual;

==>跟踪文件节选(删除无关信息)
=====================
PARSING IN CURSOR #2 len=102 dep=1 uid=0 ct=3 lid=0 tim=1361645235588235 hv=3967354608 ad='5faaf59c'
select increment$,minvalue,maxvalue,cycle#,order$,cache,highwater,audit$,flags from seq$ where obj#=:1
END OF STMT
PARSE #2:c=0,e=544,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1361645235588229
BINDS #2:
kkscoacd
 Bind#0
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7f6d838  bln=22  avl=04  flg=05
  value=63095
=====================
PARSING IN CURSOR #4 len=42 dep=0 uid=0 ct=3 lid=0 tim=1361645235610409 hv=3290038783 ad='4fd3d608'
select seq_test_cache0.nextval from dual
END OF STMT
=====================
PARSING IN CURSOR #1 len=129 dep=1 uid=0 ct=6 lid=0 tim=1361645235612206 hv=2635489469 ad='5f940c2c'
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1
END OF STMT
PARSE #1:c=0,e=799,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1361645235612201
BINDS #1:
kkscoacd
 Bind#0
  acdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=4fd49000  bln=24  avl=02  flg=09
  value=1
 Bind#1
  acdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=4fd49012  bln=24  avl=02  flg=09
  value=1
 Bind#2
  acdty=02 mxl=22(15) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=4fd49024  bln=24  avl=15  flg=09
  value=999999999999999999999999999
 Bind#3
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7ebb7e4  bln=24  avl=01  flg=05
  value=0
 Bind#4
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7ebb7c0  bln=24  avl=01  flg=05
  value=0
 Bind#5
  acdty=02 mxl=22(01) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=4fd49036  bln=24  avl=01  flg=09
  value=0
 Bind#6
  acdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=4fd49048  bln=24  avl=02  flg=09
  value=2
 Bind#7
  acdty=01 mxl=32(32) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=01 csi=852 siz=32 ff=0
  kxsbbbfp=4fd4905a  bln=32  avl=32  flg=09
  value="--------------------------------"
 Bind#8
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7ebb79c  bln=24  avl=01  flg=05
  value=0
 Bind#9
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7ebb808  bln=22  avl=04  flg=05
  value=63095
=====================
PARSING IN CURSOR #2 len=42 dep=0 uid=0 ct=3 lid=0 tim=1361645235663488 hv=3290038783 ad='4fd3d608'
select seq_test_cache0.nextval from dual
END OF STMT
=====================
PARSING IN CURSOR #1 len=129 dep=1 uid=0 ct=6 lid=0 tim=1361645235664346 hv=2635489469 ad='5f940c2c'
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1
END OF STMT
PARSE #1:c=0,e=46,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1361645235664341
BINDS #1:
kkscoacd
 Bind#0
  acdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=4fd49000  bln=24  avl=02  flg=09
  value=1
 Bind#1
  acdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=4fd49012  bln=24  avl=02  flg=09
  value=1
 Bind#2
  acdty=02 mxl=22(15) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=4fd49024  bln=24  avl=15  flg=09
  value=999999999999999999999999999
 Bind#3
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7ebb7e4  bln=24  avl=01  flg=05
  value=0
 Bind#4
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7ebb7c0  bln=24  avl=01  flg=05
  value=0
 Bind#5
  acdty=02 mxl=22(01) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=4fd49036  bln=24  avl=01  flg=09
  value=0
 Bind#6
  acdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=4fd49048  bln=24  avl=02  flg=09
  value=3
 Bind#7
  acdty=01 mxl=32(32) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=01 csi=852 siz=32 ff=0
  kxsbbbfp=4fd4905a  bln=32  avl=32  flg=09
  value="--------------------------------"
 Bind#8
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7ebb79c  bln=24  avl=01  flg=05
  value=0
 Bind#9
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7ebb808  bln=22  avl=04  flg=05
  value=63095
=====================
PARSING IN CURSOR #3 len=42 dep=0 uid=0 ct=3 lid=0 tim=1361645235722233 hv=3290038783 ad='4fd3d608'
select seq_test_cache0.nextval from dual
END OF STMT
=====================
PARSING IN CURSOR #1 len=129 dep=1 uid=0 ct=6 lid=0 tim=1361645235723066 hv=2635489469 ad='5f940c2c'
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1
END OF STMT
PARSE #1:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1361645235723061
BINDS #1:
kkscoacd
 Bind#0
  acdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=4fd49000  bln=24  avl=02  flg=09
  value=1
 Bind#1
  acdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=4fd49012  bln=24  avl=02  flg=09
  value=1
 Bind#2
  acdty=02 mxl=22(15) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=4fd49024  bln=24  avl=15  flg=09
  value=999999999999999999999999999
 Bind#3
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7ebb7e4  bln=24  avl=01  flg=05
  value=0
 Bind#4
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7ebb7c0  bln=24  avl=01  flg=05
  value=0
 Bind#5
  acdty=02 mxl=22(01) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=4fd49036  bln=24  avl=01  flg=09
  value=0
 Bind#6
  acdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=4fd49048  bln=24  avl=02  flg=09
  value=4
 Bind#7
  acdty=01 mxl=32(32) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=01 csi=852 siz=32 ff=0
  kxsbbbfp=4fd4905a  bln=32  avl=32  flg=09
  value="--------------------------------"
 Bind#8
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7ebb79c  bln=24  avl=01  flg=05
  value=0
 Bind#9
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7ebb808  bln=22  avl=04  flg=05
  value=63095

---分析跟踪文件可知:
*在一个实例上,对于每个sequence的第一次访问,首先通过递归sql获取该序列的当前信息
*对于nocache sequence,针对每个nextval请求,都需要通过递归sql更新seq$表相关信息,highwater+1

4.2 cache 100 sequence:
create sequence seq_test_cache100 cache 100;
connect / as sysdba

alter session set events '10046 trace name context forever,level 12';
select seq_test_cache100.nextval from dual t1;
select seq_test_cache100.nextval from dual t2;
==>trace1

shutdown immediate;

startup;
--session1:
alter session set events '10046 trace name context forever,level 12';
select seq_test_cache100.nextval from dual t3;
select seq_test_cache100.nextval from dual t4;
==>trace2

--session2:
alter session set events '10046 trace name context forever,level 12';
select seq_test_cache100.nextval from dual t5;
select seq_test_cache100.nextval from dual t6;
==>trace3


跟踪文件如下:
==>trace1,返回1/2:
=====================
PARSING IN CURSOR #2 len=102 dep=1 uid=0 ct=3 lid=0 tim=1361667940292559 hv=3967354608 ad='5faa78bc'
select increment$,minvalue,maxvalue,cycle#,order$,cache,highwater,audit$,flags from seq$ where obj#=:1
END OF STMT
PARSE #2:c=0,e=126,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1361667940292552
BINDS #2:
kkscoacd
 Bind#0
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7f97634  bln=22  avl=04  flg=05
  value=63097
EXEC #2:c=0,e=265,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1361667940293030
FETCH #2:c=0,e=184,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1361667940293258
STAT #2 id=1 cnt=1 pid=0 pos=1 bj=68 p='TABLE ACCESS BY INDEX ROWID SEQ$ (cr=2 pr=0 pw=0 time=197 us)'
STAT #2 id=2 cnt=1 pid=1 pos=1 bj=102 p='INDEX UNIQUE SCAN I_SEQ1 (cr=1 pr=0 pw=0 time=79 us)'
=====================
PARSING IN CURSOR #1 len=45 dep=0 uid=0 ct=3 lid=0 tim=1361667940294897 hv=392535834 ad='572fda6c'
select seq_test_cache100.nextval from dual t1
END OF STMT
PARSE #1:c=0,e=3219,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,tim=1361667940294890
BINDS #1:
EXEC #1:c=0,e=78,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1361667940295115
WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361667940295177
=====================
PARSING IN CURSOR #2 len=129 dep=1 uid=0 ct=6 lid=0 tim=1361667940295540 hv=2635489469 ad='5f97d21c'
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1
END OF STMT
PARSE #2:c=0,e=163,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1361667940295535
BINDS #2:
kkscoacd
 Bind#0
  acdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=5f97d4d0  bln=24  avl=02  flg=09
  value=1
 Bind#1
  acdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=5f97d4e2  bln=24  avl=02  flg=09
  value=1
 Bind#2
  acdty=02 mxl=22(15) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=5f97d4f4  bln=24  avl=15  flg=09
  value=999999999999999999999999999
 Bind#3
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7fae394  bln=24  avl=01  flg=05
  value=0
 Bind#4
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7fae370  bln=24  avl=01  flg=05
  value=0
 Bind#5
  acdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=5f97d506  bln=24  avl=02  flg=09
  value=100
 Bind#6
  acdty=02 mxl=22(03) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=5f97d518  bln=24  avl=03  flg=09
  value=101
 Bind#7
  acdty=01 mxl=32(32) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=01 csi=852 siz=32 ff=0
  kxsbbbfp=5f97d52a  bln=32  avl=32  flg=09
  value="--------------------------------"
 Bind#8
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7fae34c  bln=24  avl=01  flg=05
  value=0
 Bind#9
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7fae3b8  bln=22  avl=04  flg=05
  value=63097
EXEC #2:c=4000,e=2030,p=0,cr=1,cu=2,mis=0,r=1,dep=1,og=4,tim=1361667940297784
STAT #2 id=1 cnt=1 pid=0 pos=1 bj=0 p='UPDATE  SEQ$ (cr=1 pr=0 pw=0 time=683 us)'
STAT #2 id=2 cnt=1 pid=1 pos=1 bj=102 p='INDEX UNIQUE SCAN I_SEQ1 (cr=1 pr=0 pw=0 time=22 us)'
FETCH #1:c=4000,e=3092,p=0,cr=1,cu=3,mis=0,r=1,dep=0,og=1,tim=1361667940298324
WAIT #1: nam='log file sync' ela= 11206 buffer#=1869 p2=0 p3=0 obj#=-1 tim=1361667940309605
WAIT #1: nam='SQL*Net message from client' ela= 316 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361667940310011
FETCH #1:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1361667940310065
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361667940310108
WAIT #1: nam='SQL*Net message from client' ela= 267 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361667940310471
STAT #1 id=1 cnt=1 pid=0 pos=1 bj=63097 p='SEQUENCE  SEQ_TEST_CACHE100 (cr=1 pr=0 pw=0 time=3098 us)'
STAT #1 id=2 cnt=1 pid=1 pos=1 bj=0 p='FAST DUAL  (cr=0 pr=0 pw=0 time=6 us)'
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361667940310678
WAIT #0: nam='SQL*Net message from client' ela= 199 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361667940310915
=====================
PARSING IN CURSOR #2 len=45 dep=0 uid=0 ct=3 lid=0 tim=1361667940312104 hv=2858076849 ad='572d0258'
select seq_test_cache100.nextval from dual t2
END OF STMT
PARSE #2:c=0,e=1124,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1361667940312097
BINDS #2:
EXEC #2:c=0,e=73,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1361667940312321
WAIT #2: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361667940312372
FETCH #2:c=0,e=34,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1361667940312451
WAIT #2: nam='SQL*Net message from client' ela= 156 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361667940312681
FETCH #2:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1361667940312731
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361667940312771
WAIT #2: nam='SQL*Net message from client' ela= 184 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361667940312995
STAT #2 id=1 cnt=1 pid=0 pos=1 bj=63097 p='SEQUENCE  SEQ_TEST_CACHE100 (cr=0 pr=0 pw=0 time=32 us)'
STAT #2 id=2 cnt=1 pid=1 pos=1 bj=0 p='FAST DUAL  (cr=0 pr=0 pw=0 time=6 us)'
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361667940313193
WAIT #0: nam='SQL*Net message from client' ela= 8685723 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361667948998958
WAIT #0: nam='control file sequential read' ela= 66 file#=0 block#=1 blocks=1 obj#=-1 tim=1361667949017163
WAIT #0: nam='control file sequential read' ela= 32 file#=1 block#=1 blocks=1 obj#=-1 tim=1361667949017252
WAIT #0: nam='control file sequential read' ela= 31 file#=2 block#=1 blocks=1 obj#=-1 tim=1361667949017317
WAIT #0: nam='control file sequential read' ela= 12 file#=0 block#=15 blocks=1 obj#=-1 tim=1361667949017370
WAIT #0: nam='control file sequential read' ela= 13 file#=0 block#=17 blocks=1 obj#=-1 tim=1361667949017416
=====================
PARSING IN CURSOR #2 len=27 dep=0 uid=0 ct=35 lid=0 tim=1361667968346174 hv=1707809298 ad='573437f0'
ALTER DATABASE CLOSE NORMAL
END OF STMT
PARSE #2:c=0,e=549,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1361667968346169
BINDS #2:
=====================
对sys.mon_mods$,sys.col_usage$更新
=====================
PARSING IN CURSOR #3 len=129 dep=1 uid=0 ct=6 lid=0 tim=1361667968613513 hv=2635489469 ad='5f97d21c'
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1
END OF STMT
PARSE #3:c=0,e=18,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1361667968613508
BINDS #3:
kkscoacd
 Bind#0
  acdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=5f97d4d0  bln=24  avl=02  flg=09
  value=1
 Bind#1
  acdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=5f97d4e2  bln=24  avl=02  flg=09
  value=1
 Bind#2
  acdty=02 mxl=22(15) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=5f97d4f4  bln=24  avl=15  flg=09
  value=999999999999999999999999999
 Bind#3
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7dee8cc  bln=24  avl=01  flg=05
  value=0
 Bind#4
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7dee8a8  bln=24  avl=01  flg=05
  value=0
 Bind#5
  acdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=5f97d506  bln=24  avl=02  flg=09
  value=100
 Bind#6
  acdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=5f97d518  bln=24  avl=02  flg=09
  value=3
 Bind#7
  acdty=01 mxl=32(32) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=01 csi=852 siz=32 ff=0
  kxsbbbfp=5f97d52a  bln=32  avl=32  flg=09
  value="--------------------------------"
 Bind#8
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7dee884  bln=24  avl=01  flg=05
  value=0
 Bind#9
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7dee8f0  bln=22  avl=04  flg=05
  value=63097
EXEC #3:c=8001,e=1376,p=0,cr=1,cu=2,mis=0,r=1,dep=1,og=4,tim=1361667968615090
STAT #3 id=1 cnt=1 pid=0 pos=1 bj=0 p='UPDATE  SEQ$ (cr=1 pr=0 pw=0 time=147 us)'
STAT #3 id=2 cnt=1 pid=1 pos=1 bj=102 p='INDEX UNIQUE SCAN I_SEQ1 (cr=1 pr=0 pw=0 time=16 us)'
=====================
PARSING IN CURSOR #1 len=23 dep=0 uid=0 ct=35 lid=0 tim=1361667979499015 hv=1128586795 ad='5f9113fc'
ALTER DATABASE DISMOUNT
END OF STMT
PARSE #1:c=0,e=404,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1361667979499010
BINDS #1:
WAIT #1: nam='rdbms ipc reply' ela= 2 from_process=5 timeout=910 p3=0 obj#=-1 tim=1361667979499383
WAIT #1: nam='rdbms ipc reply' ela= 70 from_process=5 timeout=910 p3=0 obj#=-1 tim=1361667979499492
WAIT #1: nam='reliable message' ela= 2 channel context=1598563252 channel handle=1598527012 broadcast message=1599171232 obj#=-1 tim=1361667979499992
WAIT #1: nam='reliable message' ela= 327 channel context=1598563252 channel handle=1598527012 broadcast message=1599171232 obj#=-1 tim=1361667979500361
XCTEND rlbk=0, rd_only=1
EXEC #1:c=0,e=1400,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1361667979500544
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361667979500689
WAIT #1: nam='SQL*Net message from client' ela= 103 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361667979500838
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361667979500920
WAIT #0: nam='SQL*Net message from client' ela= 131 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361667979501087
*** 2014-03-09 14:53:31.010 60679 kcrr.c
ARCH: Archival disabled due to shutdown: 1089
*** 2014-03-09 14:53:32.010 60679 kcrr.c
ARCH: Archival disabled due to shutdown: 1089

==>trace2,返回3/4:
=====================
PARSING IN CURSOR #9 len=198 dep=1 uid=0 ct=3 lid=0 tim=1361668025640695 hv=4125641360 ad='5fa87170'
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
END OF STMT
PARSE #9:c=0,e=632,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1361668025640689
BINDS #9:
kkscoacd
 Bind#0
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7d9b808  bln=22  avl=01  flg=05
  value=0
 Bind#1
  acdty=01 mxl=32(17) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=01 csi=852 siz=32 ff=0
  kxsbbbfp=b7d9b7dc  bln=32  avl=17  flg=05
  value="SEQ_TEST_CACHE100"
 Bind#2
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7d9b7b8  bln=24  avl=02  flg=05
  value=1
EXEC #9:c=20001,e=26071,p=5,cr=75,cu=0,mis=1,r=0,dep=1,og=4,tim=1361668025666990
FETCH #9:c=0,e=56,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=1361668025667097
STAT #9 id=1 cnt=1 pid=0 pos=1 bj=18 p='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=54 us)'
STAT #9 id=2 cnt=1 pid=1 pos=1 bj=37 p='INDEX RANGE SCAN I_OBJ2 (cr=2 pr=0 pw=0 time=27 us)'
=====================
PARSING IN CURSOR #11 len=102 dep=1 uid=0 ct=3 lid=0 tim=1361668025667840 hv=3967354608 ad='5fa625e0'
select increment$,minvalue,maxvalue,cycle#,order$,cache,highwater,audit$,flags from seq$ where obj#=:1
END OF STMT
PARSE #11:c=0,e=548,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1361668025667835
BINDS #11:
kkscoacd
 Bind#0
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7db65e8  bln=22  avl=04  flg=05
  value=63097
EXEC #11:c=0,e=1261,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1361668025669347
WAIT #11: nam='db file sequential read' ela= 91 file#=1 block#=500 blocks=1 obj#=0 tim=1361668025669533
FETCH #11:c=0,e=198,p=1,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1361668025669598
STAT #11 id=1 cnt=1 pid=0 pos=1 bj=68 p='TABLE ACCESS BY INDEX ROWID SEQ$ (cr=2 pr=1 pw=0 time=196 us)'
STAT #11 id=2 cnt=1 pid=1 pos=1 bj=102 p='INDEX UNIQUE SCAN I_SEQ1 (cr=1 pr=0 pw=0 time=20 us)'
=====================
PARSING IN CURSOR #12 len=116 dep=1 uid=0 ct=3 lid=0 tim=1361668025670565 hv=854877822 ad='5fa85404'
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataobj#,o.flags from obj$ o where o.obj#=:1
END OF STMT
PARSE #12:c=0,e=409,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1361668025670559
BINDS #12:
kkscoacd
 Bind#0
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7de3748  bln=22  avl=04  flg=05
  value=63097
EXEC #12:c=0,e=1186,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1361668025671954
FETCH #12:c=0,e=43,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=1361668025672047
STAT #12 id=1 cnt=1 pid=0 pos=1 bj=18 p='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=41 us)'
STAT #12 id=2 cnt=1 pid=1 pos=1 bj=36 p='INDEX UNIQUE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=23 us)'
=====================
PARSING IN CURSOR #24 len=45 dep=0 uid=0 ct=3 lid=0 tim=1361668025672382 hv=1136384337 ad='5f8eee70'
select seq_test_cache100.nextval from dual t3
END OF STMT
PARSE #24:c=32002,e=32693,p=6,cr=83,cu=0,mis=1,r=0,dep=0,og=1,tim=1361668025672377
BINDS #24:
EXEC #24:c=0,e=67,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1361668025672577
WAIT #24: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1361668025672628
=====================
PARSING IN CURSOR #13 len=102 dep=1 uid=0 ct=3 lid=0 tim=1361668025672823 hv=3967354608 ad='5fa625e0'
select increment$,minvalue,maxvalue,cycle#,order$,cache,highwater,audit$,flags from seq$ where obj#=:1
END OF STMT
PARSE #13:c=0,e=49,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1361668025672819
BINDS #13:
kkscoacd
 Bind#0
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7c3bbf0  bln=22  avl=04  flg=05
  value=63097
EXEC #13:c=0,e=198,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1361668025673285
FETCH #13:c=0,e=131,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1361668025673456
STAT #13 id=1 cnt=1 pid=0 pos=1 bj=68 p='TABLE ACCESS BY INDEX ROWID SEQ$ (cr=2 pr=0 pw=0 time=130 us)'
STAT #13 id=2 cnt=1 pid=1 pos=1 bj=102 p='INDEX UNIQUE SCAN I_SEQ1 (cr=1 pr=0 pw=0 time=14 us)'
=====================
PARSING IN CURSOR #14 len=129 dep=1 uid=0 ct=6 lid=0 tim=1361668025674260 hv=2635489469 ad='5f9507d8'
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1
END OF STMT
PARSE #14:c=0,e=629,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1361668025674254
BINDS #14:
kkscoacd
 Bind#0
  acdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=5f8e7af8  bln=24  avl=02  flg=09
  value=1
 Bind#1
  acdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=5f8e7b0a  bln=24  avl=02  flg=09
  value=1
 Bind#2
  acdty=02 mxl=22(15) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=5f8e7b1c  bln=24  avl=15  flg=09
  value=999999999999999999999999999
 Bind#3
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7c9167c  bln=24  avl=01  flg=05
  value=0
 Bind#4
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7c91658  bln=24  avl=01  flg=05
  value=0
 Bind#5
  acdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=5f8e7b2e  bln=24  avl=02  flg=09
  value=100
 Bind#6
  acdty=02 mxl=22(03) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=5f8e7b40  bln=24  avl=03  flg=09
  value=103
 Bind#7
  acdty=01 mxl=32(32) mxlc=00 mal=00 scl=00 pre=00
  acflg=18 fl2=0001 frm=01 csi=852 siz=32 ff=0
  kxsbbbfp=5f8e7b52  bln=32  avl=32  flg=09
  value="--------------------------------"
 Bind#8
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7c91634  bln=24  avl=01  flg=05
  value=0
 Bind#9
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=b7c916a0  bln=22  avl=04  flg=05
  value=63097
WAIT #14: nam='db file sequential read' ela= 101 file#=2 block#=6128 blocks=1 obj#=0 tim=1361668025677121
EXEC #14:c=0,e=2790,p=1,cr=1,cu=3,mis=1,r=1,dep=1,og=4,tim=1361668025677268
STAT #14 id=1 cnt=1 pid=0 pos=1 bj=0 p='UPDATE  SEQ$ (cr=1 pr=1 pw=0 time=369 us)'
STAT #14 id=2 cnt=1 pid=1 pos=1 bj=102 p='INDEX UNIQUE SCAN I_SEQ1 (cr=1 pr=0 pw=0 time=20 us)'
FETCH #24:c=4000,e=4878,p=1,cr=3,cu=4,mis=0,r=1,dep=0,og=1,tim=1361668025677563
WAIT #24: nam='log file sync' ela= 261 buffer#=85 p2=0 p3=0 obj#=0 tim=1361668025677892
WAIT #24: nam='SQL*Net message from client' ela= 251 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1361668025678223
FETCH #24:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1361668025678276
WAIT #24: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1361668025678319
WAIT #24: nam='SQL*Net message from client' ela= 254 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1361668025678612
STAT #24 id=1 cnt=1 pid=0 pos=1 bj=63097 p='SEQUENCE  SEQ_TEST_CACHE100 (cr=3 pr=1 pw=0 time=4880 us)'
STAT #24 id=2 cnt=1 pid=1 pos=1 bj=0 p='FAST DUAL  (cr=0 pr=0 pw=0 time=6 us)'
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1361668025678808
WAIT #0: nam='SQL*Net message from client' ela= 243 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1361668025679089
=====================
PARSING IN CURSOR #15 len=45 dep=0 uid=0 ct=3 lid=0 tim=1361668025679928 hv=1873222268 ad='5f8e7824'
select seq_test_cache100.nextval from dual t4
END OF STMT
PARSE #15:c=0,e=772,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1361668025679922
BINDS #15:
EXEC #15:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1361668025680147
WAIT #15: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1361668025680197
FETCH #15:c=0,e=30,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1361668025680274
WAIT #15: nam='SQL*Net message from client' ela= 135 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1361668025680484
FETCH #15:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1361668025680535
WAIT #15: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1361668025680577
WAIT #15: nam='SQL*Net message from client' ela= 198 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1361668025680813
STAT #15 id=1 cnt=1 pid=0 pos=1 bj=63097 p='SEQUENCE  SEQ_TEST_CACHE100 (cr=0 pr=0 pw=0 time=31 us)'
STAT #15 id=2 cnt=1 pid=1 pos=1 bj=0 p='FAST DUAL  (cr=0 pr=0 pw=0 time=5 us)'
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1361668025681013

 

==>trace3,返回5/6:
=====================
PARSING IN CURSOR #1 len=45 dep=0 uid=0 ct=3 lid=0 tim=1361668044137150 hv=2103998400 ad='5f8e2460'
select seq_test_cache100.nextval from dual t5
END OF STMT
PARSE #1:c=0,e=1270,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1361668044137143
BINDS #1:
EXEC #1:c=0,e=110,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1361668044137405
WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361668044137466
FETCH #1:c=0,e=58,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1361668044137577
WAIT #1: nam='SQL*Net message from client' ela= 321 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361668044138006
FETCH #1:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1361668044138058
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361668044138099
WAIT #1: nam='SQL*Net message from client' ela= 316 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361668044138507
STAT #1 id=1 cnt=1 pid=0 pos=1 bj=63097 p='SEQUENCE  SEQ_TEST_CACHE100 (cr=0 pr=0 pw=0 time=68 us)'
STAT #1 id=2 cnt=1 pid=1 pos=1 bj=0 p='FAST DUAL  (cr=0 pr=0 pw=0 time=6 us)'
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361668044138695
WAIT #0: nam='SQL*Net message from client' ela= 5649 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361668044144379
=====================
PARSING IN CURSOR #2 len=45 dep=0 uid=0 ct=3 lid=0 tim=1361668044145150 hv=2965907780 ad='5f8e2224'
select seq_test_cache100.nextval from dual t6
END OF STMT
PARSE #2:c=0,e=715,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1361668044145144
BINDS #2:
EXEC #2:c=0,e=66,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1361668044145356
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361668044145403
FETCH #2:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1361668044145476
WAIT #2: nam='SQL*Net message from client' ela= 140 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361668044145688
FETCH #2:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1361668044145737
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361668044145778
WAIT #2: nam='SQL*Net message from client' ela= 232 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361668044146046
STAT #2 id=1 cnt=1 pid=0 pos=1 bj=63097 p='SEQUENCE  SEQ_TEST_CACHE100 (cr=0 pr=0 pw=0 time=29 us)'
STAT #2 id=2 cnt=1 pid=1 pos=1 bj=0 p='FAST DUAL  (cr=0 pr=0 pw=0 time=5 us)'
WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361668044146210
WAIT #0: nam='SQL*Net message from client' ela= 2254861 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361668046401110
XCTEND rlbk=0, rd_only=1

 

---分析跟踪文件可知:
*在一个实例上,对于每个sequence的第一次访问,首先通过递归sql获取该序列的当前信息
*shutdown immediate时,不会丢失缓存中的sequence(trace1中记录了对seq$更新了2次)
*对于cache sequence,会在内存中保留cache#个序号,通过递归sql更新seq$表相关信息,更新highwater+cache#

 

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

转载于:http://blog.itpub.net/18922393/viewspace-712354/

cuian7084
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
sqlplus常用命令
gezi_的博客
05-08 3215
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/seawavecau/archive/2006/11/22/1403903.aspx               ToT show和set命令是两条用于维护SQL*Plus系统变量的命令     SQL> show all --查看所有68个系统变量值     SQL> show user -
Oracle在创建序列（sequence）时有个参数你可以选择cache或者nocache
u012143723的专栏
03-26 8542
Oracle在创建序列（sequence）时有个参数你可以选择cache或者nocache，下面来讲一下两者的区别： 先来看下创建sequence的语句： create sequence SEQ_ID   minvalue 1   maxvalue 99999999   start with 1   increment by 1   cache n  / nocache  --其中n
oracle trc文件_ORACLE中seq$表更新频繁的分析
weixin_35858091的博客
01-23 415
在分析ORACLE的AWR报告时，发现SQL ordered by Executions(记录了按照SQL的执行次数排序的TOP SQL。该排序可以看出监控范围内的SQL执行次数)下有一个SQL语句执行非常频繁，一个小时执行了上万次：updateseq$setincrement$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:...
由pga增高,吃swap空间导致的服务器夯机事件
ArchonSbkd的博客
05-24 170
pga高、swap高
oracle设置当前用户所有序列的cache为nocache
每天学习一点点
07-29 1158
declare v_sql varchar2(100); v_table_name varchar2(100); v_ref sys_refcursor; begin for v_ref in (select object_name from user_objects where object_type = 'SEQUENCE') loop v_sql := 'alter sequence ' || v_ref.object_name || ' noca.
SequenceDiagram
最新发布
02-06
SequenceDiagram
分享ORACLE SEQUENCE跳号总结
09-09
在ORACLE数据库中，序列（SEQUENCE）是使用非常频繁的一个数据库对象，但是有时候会遇到序列（SEQUECNE）跳号(skip sequence numbers)的情形，那么在哪些情形下会遇到跳号呢，下面通过本文给大家详解介绍，一起看看...
SequenceDiagram.zip
03-06
本文介绍了一个Windows .NET控件，用于以UML顺序图的形式动态可视化对象及其交互。该控件具有类似于调试器的中断功能。另外，它提供了一个实际使用的示例，其中实时拦截和可视化了应用程序的执行
bamboo-sequence.jar
11-29
在简单系统中，我们常常使用db的id自增方式来标识和保存数据，随着系统的复杂，数据的增多，分库分表成为了常见的方案，db自增已无法满足要求。这时候全局唯一的id生成系统就派上了用场。当然这只是id生成其中的一种...
sequence-diagram.zip
08-27
sequence-diagram-js 所需jar包，其中sequence-diagram-js支持自定义颜色
openGauss2.0.0之Java简单测试Sequence
若兰幽竹
01-14 747
•创建序列后，在表中使用序列的nextval()函数和generate_series(1,N)函数对表插入数据，请保证nextval的可调用次数大于等于N+1次，否则会因为generate_series()函数会调用N+1次而导致报错。2. 另一种是使用CREATE SEQUENCE自定义一个新的Sequence，然后将nextval(‘sequence_name’)函数读取的序列值，指定为某一字段的默认值，这样该字段就可以作为唯一标识符。
sequence跳号问题以及相应知识拓展
潇鹞枫语
05-09 1228
SEQUENCE跳号可能是事务ROLLBAK或者实例被宕过或者是SEQUENCE的CACHE被交换出去过。第一个或者第二个解决方法比较难解决（可以从业务上来避免）。第三个对于有特殊要求的SEQUENCE或者包、存储过程、触发器等等，可以使用DBMS_SHARED_POOL.KEEP方法，把他们永久保留在SHARED POOL中，从而可以实现一些特定的用途。用SEQUENCE值来生成的主键经常出现断...
Oracle序列sequence cache nocache、RAC
开着奥迪卖小猪
01-03 4022
一、问题描述：新增了个表，上了生产环境之后，发现ID跳号且穿插，如图1所示。 而测试环境只是跳号，如图2所示。 图1 生产环境 图2 ...
Oracle Sequence Cache 参数说明
热门推荐
专注Oracle EBS---低调的奢华
03-03 1万+
之前整理的一篇文章：      ORACLE SEQUENCE 介绍 http://blog.csdn.net/tianlesoftware/archive/2009/10/30/4745039.aspx   之前整理的一篇文章。 那是还是写blog初期的作品。 2009年10月份的。 转眼一年，写Blog 也比以前成熟了很多。   一. 理论知识 先看一个创建Sequence的
LightDB 创建/修改 sequence支持order/noorder/nocycle/nocache
yunmu666的博客
04-21 223
LightDB
oracle序列keep,分享ORACLE SEQUENCE跳号总结
weixin_33258874的博客
04-03 606
在ORACLE数据库中，序列(SEQUENCE)是使用非常频繁的一个数据库对象，但是有时候会遇到序列(SEQUECNE)跳号(skip sequence numbers)的情形，那么在哪些情形下会遇到跳号呢？事务回滚引起的跳号不管序列有没有CACHE、事务回滚这种情况下，都会引起序列的跳号。如下实验所示：SQL> create sequence my_sequence2 start with...
oracle修改字符集的风险,字符集问题的初步探讨(三)-字符集的更改
weixin_42519497的博客
04-09 861
字符集问题的初步探讨(三)-字符集的更改原文发表于itpub技术丛书《Oracle数据库DBA专题技术精粹》,未经许可,严禁转载本文.2． 字符集的更改数据库创建以后，如果需要修改字符集，通常需要重建数据库，通过导入导出的方式来转换。我们也可以通过以下方式更改ALTER DATABASE CHARACTER SET注意:修改数据库字符集时必须谨慎，修改之前一定要为数据库备份。由于不能回退这项操作,...
ALTER SEQUENCE 语句
qq_37363709的博客
07-22 1377
ALTER SEQUENCE 语句
重庆思庄技术分享——修改序列cache
m0_61458430的博客
10-11 96
尝试alter sequence emp_id_seq cache;会出现报错，语法有问题。尝试alter sequence emp_id_seq cache;会出现报错，语法有问题。那修改序列为cache时的语句又是什么样的呢？那修改序列为cache时的语句又是什么样的呢？
Oracle Sequence Nocache
05-17
Oracle Sequence 是一种生成唯一序列号的工具，可以用于创建主键或其他需要唯一值的列。NOCACHE 是 Oracle Sequence 的一个属性，用于指定 Sequence 不使用缓存来提高性能，而是每次都从磁盘上读取当前值。 使用 NOCACHE 属性可以确保生成的 Sequence 值不会丢失，因为它们不会被缓存。但是，由于每次都要从磁盘上读取当前值，因此在高并发环境中可能会影响性能。因此，在设计 Sequence 时应该考虑具体的场景和业务需求，选择合适的缓存策略。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值