sequence跳号问题及解决办法

转载自：点击打开链接

一、    问题及现象描述

--创建序列

CREATE SEQUENCE NYYTEST.S_TEST

    STARTWITH 1

    INCREMENTBY 1

   NOMINVALUE

    MAXVALUE10000

   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到SHAREDPOOL中的方法，这样就保证了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 tonyytest;

授权成功。

SQL> create synonym nyytest.dbms_shared_pool fordbms_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 twokinds of objects:

 --     PL/SQL objects, triggers, sequences, types andJava 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).  Forexample:

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

 --     will keep package HISPACKAGE, owned by SCOTT.  Thenames 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 8characters.

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

 --     'address' and 'hash_value' columns from the v$sqlareaview.  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 parameteris not specified,

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

 --       package/procedure/function and will resolve thename.  Otherwise,

 --       the parameter is a character string indicating what kind ofobject

 --       to keep the name identifies.  The string is caseinsensitive.

 --       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> execdbms_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> execdbms_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

----------

       41