ORACLE中seq$表更新頻繁的分析

在分析ORACLE的AWR報告時，發現SQL ordered by Executions（記錄了按照SQL的執行次數排序的TOP SQL。該排序可以看出監控範圍內的SQL執行次數）下有一個SQL語句執行非常頻繁，一個小時執行了上萬次：

update seq$ set increment$=:2, minvalue=:3, maxvalue=:4, cycle#=:5, order$=:6, cache=:7, highwater=:8, audit$=:9, flags=:10 where obj#=:1

那麼seq$這個數據字典表是做什麼用的呢？ 其實這個數據字典表是保存的是資料庫下序列對象（SEQUENCE）的相關信息，而且它用來維護序列的變化。如下所示，我們通過實驗來驗證一下，我們啟用10046事件，跟蹤一下會話（level=4 表示啟用SQL_TRACE並捕捉跟蹤文件中的綁定變數），我們跟蹤會話創建序列的過程。下麵測試環境為Oracle 11g

SQL> show user;

USER is "TEST"

SQL> alter session set events '10046 trace name context forever, level 4';

Session altered.

SQL> create sequence my_sequence_test

  2  start with 1

  3  increment by 1

  4  maxvalue 999999999

  5  nocache;

Sequence created.

SQL> alter session set events '10046 trace name context off';

Session altered.

SQL> SELECT    a.VALUE

  2         || b.symbol

  3         || LOWER(c.instance_name)

  4         || '_ora_'

  5         || d.spid

  6         || '.trc' trace_file

  7    FROM (SELECT VALUE

  8            FROM v$parameter

  9           WHERE NAME = 'user_dump_dest') a,

 10         (SELECT SUBSTR (VALUE, -6, 1) symbol

 11            FROM v$parameter

 12           WHERE NAME = 'user_dump_dest') b,

 13         (SELECT instance_name

 14            FROM v$instance) c,

 15         (SELECT spid

 16            FROM v$session s, v$process p, v$mystat m

 17           WHERE s.paddr = p.addr AND s.SID = m.SID AND m.statistic# = 0) d

 18  /

TRACE_FILE

--------------------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/gsp/gsp/trace/gsp_ora_28201.trc

[oracle@DB-Server trace]$ tkprof gsp_ora_28201.trc  anay_out_28201.txt aggreage=yes;

LRM-00101: unknown parameter name 'aggreage'

error during command line parsing, cannot continue.

[oracle@DB-Server trace]$ tkprof gsp_ora_28201.trc  anay_out_28201.txt aggregate=yes;

TKPROF: Release 11.2.0.1.0 - Development on Tue Aug 29 22:52:08 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

使用tkprof將跟蹤文件轉換成可讀格式的文件後，你會註意到：在創建序列時，會往數據字典表seq$中插入一條記錄（其實創建序列的本質就是在seq$和obj$中插入了一條記錄），如下截圖所示：

tkprof格式化後的輸出文件裡面，沒有綁定變數，在原始跟蹤文件gsp_ora_28201.trc中，你可以看到對應綁定變數的值

使用下麵腳本，你就會發現這個都是對應序列對象的一些信息（序列對象的OBJECT_ID、MINVALUE、MAXVALUE、CACHE等等）

SQL> show user;

USER is "SYS"

SQL> select obj#,increment$,minvalue,maxvalue,cycle#,cache,highwater

  2  from seq$

  3  where obj#=97570;

      OBJ# INCREMENT$   MINVALUE   MAXVALUE     CYCLE#      CACHE  HIGHWATER

---------- ---------- ---------- ---------- ---------- ---------- ----------

     97570          1          1  999999999          0          0          1

SQL> select object_type,object_name from dba_objects

  2  where object_id=97570;

OBJECT_TYPE         OBJECT_NAME

-------------------  -----------------------------------------------

SEQUENCE            MY_SEQUENCE_TEST

SQL> select * from dba_sequences where sequence_name='MY_SEQUENCE_TEST';

SEQUENCE_OWNER SEQUENCE_NAME     MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER

-------------- ---------------- ---------- ---------- ------------ - - ---------- -----------

TEST           MY_SEQUENCE_TEST          1  999999999            1 N N          0           1

SQL> 

那麼，我們接下來使用SQL TRACE看看使用SEQUENCE時，會對seq$表有啥操作。如下所示，我們在啟用SQL_TRACE後，執行3次該SQL語句

SQL> show user;

USER is "TEST"

SQL> select my_sequence_test.currval, my_sequence_test.nextval from dual;

   CURRVAL    NEXTVAL

---------- ----------

         1          1

SQL> alter session set sql_trace=true;

Session altered.

SQL> select my_sequence_test.currval, my_sequence_test.nextval from dual;