Oracle Sequence不設定cache參數的幾個潛在問題

在Oracle中，我們沒有MYSQL和SQL Server可以使用的自增數據型別。大部分場景下，如果我們需要生成業務無關的（Business-Independent）主鍵列，序列Sequence物件是我們最方便的選擇。

定義Sequence是很簡單的，如果最大程度利用預設值，我們只需要定義sequence物件的名字即可。在序列Sequence物件的定義中，Cache是一個可選擇的參數（預設爲20）。

那麼，這個Cache參數對Sequence的使用帶來什麼好處？如果不設定，會有什麼問題。本篇我們就一起來探討這個問題。

 

一、 Sequence Cache簡析

 簡單的說，Cache就是Oracle每次向Sequence進行請求時，分配出的獨立數位數量。

當我們使用.nextval獲取一個獨立值時，Oracle需要將sequence物件的數據字典資訊更新。如果我們設定cache爲10，那麼第一次請求nextval的時候，就更新數據字典資訊增加10，取出的10個號放在Oracle伺服器的快取中。在以後每次請求nextval的時候，Oracle就從伺服器快取中去獲取序列值，而不需要更新數據字典資訊。只有在分配到快取的10個數字都已經分配完，或者因爲快取重新整理操作剩餘數位被清理的情況下，纔會再次呼叫sequence分配機制 機製，再次分出cache個數字。

在cache問題上，我們經常會疑惑爲什麼生成的序列會「跳號」。實際上就是因爲cache的數位在快取中因爲各種原因被flush出，導致生成的數位序列不連續。在有cache的情況下，sequence只能保證每次獲取到的數位都是唯一、遞增的，從來沒有保證過數位的連續性。

 

如果我們不設定cache，也就是不啓用序列數位快取機制 機製，有什麼缺點呢？

二、 過多的Redo Log生成

首先從Redo的統計情況入手，看看cache的影響。我們選擇Oracle 10g作爲實驗環境。分別建立兩個sequence實驗物件。

SQL> create sequence seq_nocache nocache;
Sequence created

SQL> create sequence seq_cache cache 3;
Sequence created

 我們先對nocache物件進行實驗。我們選擇autotrace工具，進行三次呼叫操作，來觀察各種資源使用情況。

--第一次呼叫
SQL> select seq_nocache.nextval from dual;

   NEXTVAL
----------
         1

執行計劃
----------------------------------------------------------
Plan hash value: 3078288422
------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |     1 |     2   (0)| 00:00:01 |
|   1 |  SEQUENCE        | SEQ_NOCACHE |       |            |          |
|   2 |   FAST DUAL      |             |     1 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------

統計資訊
----------------------------------------------------------
...
        640  redo size
...

--第二次呼叫
SQL> select seq_nocache.nextval from dual;

   NEXTVAL
----------
         2

統計資訊
----------------------------------------------------------
...
        688  redo size
...

--第三次呼叫
SQL> select seq_nocache.nextval from dual;

   NEXTVAL
----------
         3

統計資訊
----------------------------------------------------------
...
        636  redo size
...

篇幅原因，本文只表現部分結果。從結果統計量中，可以發現：雖然我們對sequence物件是採用select操作。但是對nocache的序列物件而言，每次操作都會有600左右的redo log生成。

那麼，對於開啓了cache的sequence物件而言，有什麼不同呢？

SQL> select seq_cache.nextval from dual;

   NEXTVAL
----------
         1

執行計劃
----------------------------------------------------------
Plan hash value: 2754437009
----------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     1 |     2   (0)| 00:00:01 |
|   1 |  SEQUENCE        | SEQ_CACHE |       |            |          |
|   2 |   FAST DUAL      |           |     1 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------

統計資訊
----------------------------------------------------------
...
        688  redo size
...

SQL> select seq_cache.nextval from dual;

   NEXTVAL
----------
         2

統計資訊
----------------------------------------------------------
...
        0  redo size
...

SQL> select seq_cache.nextval from dual;

   NEXTVAL
----------
         3

統計資訊
----------------------------------------------------------
...
          0  redo size
...

--第四次呼叫，獲取新的cache值
SQL> select seq_cache.nextval from dual;

   NEXTVAL
----------
         4

統計資訊
----------------------------------------------------------
...
        636  redo size
...

對cache的sequence物件而言，redo size生成的頻率顯然是低得多。從上面的四次呼叫中，只有第一次和第四次呼叫的時候，才生成了redo log記錄。這個顯然同我們設定的cache=3相對應。設定cache之後，Oracle似乎不用爲每次的nextval進行數據字典修改，生成redo log記錄。只有cache在記憶體中使用結束之後，纔會進行獲取。

在實際的生產環境中，我們對redo size無必要的生成是要盡力避免的。首先，過多的redo log生成，容易造成online redo log的寫入量增加，切換頻繁。第二，redo size和nocache的使用，可能是伴隨着頻繁的commit動作，進而是頻繁的log buffer寫入online log file的過程。同時歸檔量增加，在進行恢復的時候，也要消耗更多的時間。

所以，設定cache可以有效減少redo log的大小。

 

三、 潛在的行鎖爭用

從redo size動作，我們猜測在nextval的時候存在數據字典的頻繁更新風險。我們猜測在nextval的時候，Oracle做了些什麼。於是，我們選擇10046事件，跟蹤設定cache和不設定cache的兩種sequence，在底層遞回呼叫的行爲。

SQL> oradebug setmypid;
已處理的語句

SQL> oradebug unlimit;
已處理的語句

SQL> oradebug event 10046 trace name context forever, level 12
已處理的語句

SQL> select scott.seq_nocache.nextval from dual;

   NEXTVAL
----------
         9

SQL> select scott.seq_nocache.nextval from dual;

   NEXTVAL
----------
        10

SQL> select scott.seq_nocache.nextval from dual;

   NEXTVAL
----------
        11

SQL> select scott.seq_cache.nextval from dual;

   NEXTVAL
----------
         9

SQL> select scott.seq_cache.nextval from dual;

   NEXTVAL
----------
        10

SQL> select scott.seq_cache.nextval from dual;

   NEXTVAL
----------
        11

SQL> oradebug event 10046 trace name context off;
已處理的語句

SQL> oradebug tracefile_name c:\tool\oracle\oracle\product\10.2.0\admin\ots\udump\ots_ora_5932.trc

 開啓跟蹤檔案，我們首先分析nocache的幾次呼叫片段。

--篇幅原因，本部分有省略；

=====================

PARSING IN CURSOR #1 len=42 dep=0 uid=0 ct=3 lid=0 tim=16143418536 hv=311402377 ad='248b5c60'

select scott.seq_nocache.nextval from dual –第一次呼叫nocache

END OF STMT

PARSE #1:c=0,e=110,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=16143418526

BINDS #1:

EXEC #1:c=0,e=13893,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=16143450260

WAIT #1: nam='SQL*Net message to client' ela= 8 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=16143453714

=====================

PARSING IN CURSOR #2 len=129 dep=1 uid=0 ct=6 lid=0 tim=16143457545 hv=2635489469 ad='2891ff84'

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=129,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=16143457535

BINDS #2:

kkscoacd

 Bind#0

  oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00

  oacflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0

  kxsbbbfp=248c69dc  bln=24  avl=02  flg=09

  value=1

 Bind#1

  oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00

  oacflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0

  kxsbbbfp=248c69ee  bln=24  avl=02  flg=09

  value=1

 Bind#2

  oacdty=02 mxl=22(15) mxlc=00 mal=00 scl=00 pre=00

  oacflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0

  kxsbbbfp=248c6a00  bln=24  avl=15  flg=09

  value=999999999999999999999999999

 Bind#3

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0

  kxsbbbfp=088cefb8  bln=24  avl=01  flg=05

  value=0

 Bind#4

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0

  kxsbbbfp=088cef94  bln=24  avl=01  flg=05

  value=0

 Bind#5

  oacdty=02 mxl=22(01) mxlc=00 mal=00 scl=00 pre=00

  oacflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0

  kxsbbbfp=248c6a12  bln=24  avl=01  flg=09

  value=0

 Bind#6

  oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00

  oacflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0

  kxsbbbfp=248c6a24  bln=24  avl=02  flg=09

  value=10

 Bind#7

  oacdty=01 mxl=32(32) mxlc=00 mal=00 scl=00 pre=00

  oacflg=18 fl2=0001 frm=01 csi=852 siz=32 ff=0

  kxsbbbfp=248c6a36  bln=32  avl=32  flg=09

  value="--------------------------------"

 Bind#8

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0

  kxsbbbfp=088cef70  bln=24  avl=02  flg=05

  value=8

 Bind#9

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0

  kxsbbbfp=088cefdc  bln=22  avl=04  flg=05

  value=113487

 （有省略……） 

=====================

PARSING IN CURSOR #2 len=42 dep=0 uid=0 ct=3 lid=0 tim=16145504123 hv=311402377 ad='248b5c60'

select scott.seq_nocache.nextval from dual –第二次呼叫

END OF STMT

PARSE #2:c=0,e=50,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=16145504114

BINDS #2:

EXEC #2:c=15625,e=4237,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=16145528418

WAIT #2: nam='SQL*Net message to client' ela= 8 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=16145532367

=====================

PARSING IN CURSOR #1 len=129 dep=1 uid=0 ct=6 lid=0 tim=16145536517 hv=2635489469 ad='2891ff84'

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=49,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=16145536507

BINDS #1:

kkscoacd

 (……)

 Bind#6

  oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00

  oacflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0

  kxsbbbfp=248c6a24  bln=24  avl=02  flg=09

  value=11

 Bind#7

  oacdty=01 mxl=32(32) mxlc=00 mal=00 scl=00 pre=00

  oacflg=18 fl2=0001 frm=01 csi=852 siz=32 ff=0

  kxsbbbfp=248c6a36  bln=32  avl=32  flg=09

  value="--------------------------------"

 Bind#8

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0

  kxsbbbfp=088cef70  bln=24  avl=02  flg=05

  value=8

 Bind#9

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0

  kxsbbbfp=088cefdc  bln=22  avl=04  flg=05

  value=113487

=====================

PARSING IN CURSOR #1 len=42 dep=0 uid=0 ct=3 lid=0 tim=16147403782 hv=311402377 ad='248b5c60'

select scott.seq_nocache.nextval from dual –第三次呼叫

END OF STMT

=====================

PARSING IN CURSOR #2 len=129 dep=1 uid=0 ct=6 lid=0 tim=16147424639 hv=2635489469 ad='2891ff84'

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=43,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=16147424633

BINDS #2:

kkscoacd

 

Bind#6

  oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00

  oacflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0

  kxsbbbfp=248c6a24  bln=24  avl=02  flg=09

  value=12

 Bind#7

  oacdty=01 mxl=32(32) mxlc=00 mal=00 scl=00 pre=00

  oacflg=18 fl2=0001 frm=01 csi=852 siz=32 ff=0

  kxsbbbfp=248c6a36  bln=32  avl=32  flg=09

  value="--------------------------------"

 Bind#8

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0

  kxsbbbfp=088cef70  bln=24  avl=02  flg=05

  value=8

 Bind#9

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0

  kxsbbbfp=088cefdc  bln=22  avl=04  flg=05

  value=113487

 注意三次呼叫過程中的幾個標註紅色的部分。三次呼叫nextval，之後都存在一個遞回呼叫更新seq$基表的過程。Seq$基表顯然是記錄系統sequence的數據字典表。更新資訊雖然包括了所有欄位，但是bind#6和bind#9需要額外注意。

Bind#6在update語句中對應欄位highwater，顯然是表示當前sequence物件達到的最大數值，也就是更新之後的修改值。Bind#9表示的obj#編號，應該對應的11387就是我們的nocache實驗sequence編號。

SQL> select object_type, object_id from dba_objects where wner='SCOTT' and object_name='SEQ_NOCACHE';

OBJECT_TYPE          OBJECT_ID
------------------- ----------
SEQUENCE                113487

 說明，在沒有cache的情況下，每次呼叫nextval都會促使Oracle去更新且commit數據字典seq$記錄。

那麼，對cache的sequence而言，又是如何呢？

PARSING IN CURSOR #2 len=40 dep=0 uid=0 ct=3 lid=0 tim=16156274459 hv=1095976807 ad='24882bec'

select scott.seq_cache.nextval from dual –第一次呼叫

END OF STMT

PARSE #2:c=0,e=67,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=16156274454

BINDS #2:

EXEC #2:c=0,e=84,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=16156274601

WAIT #2: nam='SQL*Net message to client' ela= 6 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=16156274643

FETCH #2:c=0,e=46,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=16156274725

WAIT #2: nam='SQL*Net message from client' ela= 568 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=16156275360

FETCH #2:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=16156275411

WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=16156275445

WAIT #2: nam='SQL*Net message from client' ela= 2197902 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=16158473393

STAT #2 id=1 cnt=1 pid=0 pos=1 bj=113488 p='SEQUENCE  SEQ_CACHE (cr=0 pr=0 pw=0 time=57 us)'

STAT #2 id=2 cnt=1 pid=1 pos=1 bj=0 p='FAST DUAL  (cr=0 pr=0 pw=0 time=8 us)'

=====================

PARSING IN CURSOR #1 len=40 dep=0 uid=0 ct=3 lid=0 tim=16158473685 hv=1095976807 ad='24882bec'

select scott.seq_cache.nextval from dual –第二次呼叫；

END OF STMT

PARSE #1:c=0,e=36,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=16158473680

BINDS #1:

EXEC #1:c=0,e=73,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=16158473813

WAIT #1: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=16158473855

=====================

PARSING IN CURSOR #2 len=129 dep=1 uid=0 ct=6 lid=0 tim=16158474024 hv=2635489469 ad='2891ff84'

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=30,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=16158474020

BINDS #2:

kkscoacd

 Bind#0

  oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00

  oacflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0

  kxsbbbfp=248c58a0  bln=24  avl=02  flg=09

  value=1

 Bind#1

  oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00

  oacflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0

  kxsbbbfp=248c58b2  bln=24  avl=02  flg=09

  value=1

 Bind#2

  oacdty=02 mxl=22(15) mxlc=00 mal=00 scl=00 pre=00

  oacflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0

  kxsbbbfp=248c58c4  bln=24  avl=15  flg=09

  value=999999999999999999999999999

 Bind#3

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0

  kxsbbbfp=088cefb8  bln=24  avl=01  flg=05

  value=0

 Bind#4

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0

  kxsbbbfp=088cef94  bln=24  avl=01  flg=05

  value=0

 Bind#5

  oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00

  oacflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0

  kxsbbbfp=248c58d6  bln=24  avl=02  flg=09

  value=3

 Bind#6

  oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00

  oacflg=18 fl2=0001 frm=00 csi=00 siz=24 ff=0

  kxsbbbfp=248c58e8  bln=24  avl=02  flg=09

  value=13

 Bind#7

  oacdty=01 mxl=32(32) mxlc=00 mal=00 scl=00 pre=00

  oacflg=18 fl2=0001 frm=01 csi=852 siz=32 ff=0

  kxsbbbfp=248c58fa  bln=32  avl=32  flg=09

  value="--------------------------------"

 Bind#8

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0

  kxsbbbfp=088cef70  bln=24  avl=02  flg=05

  value=8

 Bind#9

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0

  kxsbbbfp=088cefdc  bln=22  avl=04  flg=05

  value=113488

=====================

PARSING IN CURSOR #2 len=40 dep=0 uid=0 ct=3 lid=0 tim=16160280316 hv=1095976807 ad='24882bec'

select scott.seq_cache.nextval from dual –第三次呼叫

END OF STMT

PARSE #2:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=16160280311

BINDS #2:

EXEC #2:c=0,e=77,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=16160280449

WAIT #2: nam='SQL*Net message to client' ela= 6 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=16160280593

FETCH #2:c=0,e=51,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=16160280682

WAIT #2: nam='SQL*Net message from client' ela= 643 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=16160281398

FETCH #2:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=16160281451

WAIT #2: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=16160281482

*** 2012-02-23 13:30:07.421

WAIT #2: nam='SQL*Net message from client' ela= 14238981 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=16174520496

STAT #2 id=1 cnt=1 pid=0 pos=1 bj=113488 p='SEQUENCE  SEQ_CACHE (cr=0 pr=0 pw=0 time=52 us)'

STAT #2 id=2 cnt=1 pid=1 pos=1 bj=0 p='FAST DUAL  (cr=0 pr=0 pw=0 time=10 us)'

在三次呼叫中，只更新了一次seq$數據字典表。而且，更新的bind#6爲13，實際上就是一次更新，多取出三個取值。以後的幾次呼叫中，就不需要在更新該數據記錄了。

由此，我們可以得到結論，無論對於cache還是nocache序列物件，都是存在更新數據字典表seq$的動作的。區別就是在於更新bind#6 highwater的頻度和一次更新步長。

進一步想，如果我們處在一個高併發的情況下，系統頻繁的多對談請求sequence取值。如果sequence沒有設定cache，那麼每次都要更新數據字典，都要進行commit操作。多個對談還會出現該sequence記錄的爭用，出現等待事件row lock contention。

所以，一般情況下，我們建議設定一個較大的cache值，用於進行效能的優化。

 

本篇解析了在單範例環境下，cache對於sequence的重要性。在RAC環境下，cache和noorder選項的作用更大。在RAC中，多個範例爭用情況會讓sequence設計的不合理效果放大。所以，在沒有特殊情況下，還是設定合理的cache值，減少系統潛在效能瓶頸。