sequence跳号问题及解决办法

一、     问题及现象描述

--创建序列

CREATE SEQUENCE NYYTEST.S_TEST

    START WITH 1

    INCREMENT BY 1

    NOMINVALUE

    MAXVALUE 10000

    NOCYCLE

    CACHE  20

    NOORDER

SQL> select S_TEST.nextval from dual;

   NEXTVAL

----------

         1

SQL> select S_TEST.nextval from dual;

   NEXTVAL

----------

         2

SQL> select S_TEST.nextval from dual;

   NEXTVAL

----------

         3

SQL> select S_TEST.nextval from dual;

   NEXTVAL

----------

         4

SQL> select S_TEST.nextval from dual;

   NEXTVAL

----------

         5

--刷新共享池，刷新共享池会使所有的没有使用DBMS_SHARED_POOL.KEEP固定的对象全部被清除，所以共享池刚刚刷新的时候，SQL和PL/SQL的执行效率会略微下降

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

系统已更改。

--由于cache的20个序列号已经从共享池中被清除，下次再取序列的nextval值取的是21

SQL> select S_TEST.nextval from dual;

   NEXTVAL

----------

        21

关于序列设置cache之后跳号问题的原因：cache会把sequence缓存在lb cache中，在lb cache中对对象的age out是基于lru算法的，如果cache 20，会把这个序列每次取的时候取出来20个，然后再在lb cache中一个一个用，但是如果在用完这20个之前，这个序列被aged out了，那么没用的那些数就丢掉了，而下次再去从dd取出sequence的时候就会去取上次那20个+1的号为开始，再来20个。这就是产生断号的原因。

二、     dbms_shared_pool包

为了避免上述情况，ORACLE给我们提供了把SEQUENCE KEEP到SHARED POOL中的方法，这样就保证了SEQUENCE的CACHE不会被交换出去，从而避免了这种情况的发生。

如果将对象固定在内存中，那么在下一次关闭数据库之前，这个对象就不会失效或者被清空。还需要考虑的是，Metalink的注意事项61760.1:DBMS_SHARED_POOL将被创建为用户SYS。其他用户不拥有这个包。需要访问这个包的任何用户都必须由SYS授予执行权限。如果在SYS模式中创建这个包并在不同的模式中运行示例代码，则首先必须给用户赋予DBMS_SHARED_POOL上的EXECUTE权限。

默认情况下dbms_shared_pool包是不在系统中的，需要运行$ORACLE_HOME/rdbms/admin/dbmspool.sql进行创建

 

SQL> desc dbms_shared_pool;

ERROR:

ORA-04043: 对象 dbms_shared_pool 不存在

 

--创建

SQL> @E:\oracle\RDBMS\ADMIN\dbmspool.sql

程序包已创建。

授权成功。

视图已创建。

程序包体已创建。

 

--查看包对象

SQL> desc dbms_shared_pool;

PROCEDURE ABORTED_REQUEST_THRESHOLD

参数名称                       类型                    输入/输出默认值?

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

 THRESHOLD_SIZE                 NUMBER                  IN

PROCEDURE KEEP

参数名称                       类型                    输入/输出默认值?

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

 NAME                           VARCHAR2                IN

 FLAG                           CHAR                    IN     DEFAULT

PROCEDURE SIZES

参数名称                       类型                    输入/输出默认值?

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

 MINSIZE                        NUMBER                  IN

PROCEDURE UNKEEP

参数名称                       类型                    输入/输出默认值?

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

 NAME                           VARCHAR2                IN

 FLAG                           CHAR                    IN     DEFAULT

 

--授权

SQL> grant execute on dbms_shared_pool to nyytest;

授权成功。

SQL> create synonym nyytest.dbms_shared_pool for dbms_shared_pool;

同义词已创建。

 

procedure keep参数的解释：

exec dbms_shared_pool.keep  keep共有两个参数，flag代表前一个参数的类型，
如果不输入，则默认为package/procedure/function中的一个；

procedure keep(name varchar2, flag char DEFAULT 'P')

name

  --      The name of the object to keep.  There are two kinds of objects:

  --      PL/SQL objects, triggers, sequences, types and Java objects,

  --      which are specified by name, and

  --      SQL cursor objects which are specified by a two-part number

  --      (indicating a location in the shared pool).  For example:

  --        dbms_shared_pool.keep('scott.hispackage')

  --      will keep package HISPACKAGE, owned by SCOTT.  The names for

  --      PL/SQL objects follows SQL rules for naming objects (i.e.,

  --      delimited identifiers, multi-byte names, etc. are allowed).

  --      A cursor can be keeped by

  --        dbms_shared_pool.keep('0034CDFF, 20348871', 'C')

  --      The complete hexadecimal address must be in the first 8 characters.

  --      The value for this identifier is the concatenation of the

  --      'address' and 'hash_value' columns from the v$sqlarea view.  This

  --      is displayed by the 'sizes' call above.

  --      Currently 'TABLE' and 'VIEW' objects may not be keeped.

  flag

  --      This is an optional parameter.  If the parameter is not specified,

  --        the package assumes that the first parameter is the name of a

  --        package/procedure/function and will resolve the name.  Otherwise,

  --        the parameter is a character string indicating what kind of object

  --        to keep the name identifies.  The string is case insensitive.

  --        The possible values and the kinds of objects they indicate are

  --        given in the following table:

  --        Value        Kind of Object to keep

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

  --        P          package/procedure/function

  --        Q          sequence

  --        R          trigger

  --        T          type

  --       JS         java source

  --       JC         java class

  --        JR         java resource

  --        JD         java shared data

  --        C          cursor

 

测试结果

S_TEST和S_TEST2序列都是设置cache20，将s_test序列keep到shared pool中

SQL> select S_TEST.nextval from dual;

 

   NEXTVAL

----------

        24

 

SQL> select S_TEST.nextval from dual;

 

   NEXTVAL

----------

        25

 

SQL> select S_TEST2.nextval from dual;

 

   NEXTVAL

----------

         1

SQL> exec dbms_shared_pool.keep('s_test','q');

 

PL/SQL 过程已成功完成。

 

SQL> select S_TEST2.nextval from dual;

 

   NEXTVAL

----------

        21

 

SQL> select S_TEST.nextval from dual;

 

   NEXTVAL

----------

        26

-- dbms_shared_pool.unkeep的作用是取消已经keep在shared pool中的对象

SQL> exec dbms_shared_pool.unkeep('s_test','q');

 

PL/SQL 过程已成功完成。

 

SQL> select S_TEST.nextval from dual;

 

   NEXTVAL

----------

        27

SQL> alter system flush shared_pool;

 

系统已更改。

 

SQL> select S_TEST.nextval from dual;

 

   NEXTVAL

----------