ArcSDE用户中
• state_id_generator_nc• connection_id_generator
• version_id_generator
三个序列经常使用,但是默认的Cache SIZE等于0
Oracle 序列是一个数据库对象,提供唯一的整数值。序列高速缓存的大小决定多少 Oracle 预前分配在内存中,在共享池中的值。通过预分配值,Oracle 将返回下一个唯一值从内存提供更快地访问信息。
设置高速缓存大小 0 可能导致丢失的序列值,如果系统关机突然再大一点。当系统出现故障时,保留在内存中的值都将丢失的序列。
例如,假设已创建一个序列的高速缓存大小为 100。第一次使用序列,则 Oracle 缓存值 1- 100 的内存。随后,Oracle 会话使用高速缓存的信息,并使用值 1- 45。此时 Oracle 关闭突然。当序列用于启动、 Oracle 缓存值 101-200 的内存,从而在值中丢失了 46 -100 之后。
此外,已缓存共享池中的序列值在数据库的操作时可以造成老化。可以使用DMBS_SHARED_POOL 存储过程,避免老化序列。
更改序列的Cache SIZE值步骤
1:
- ALTER SEQUENCE sde.connection_id_generator CACHE 1000
- ALTER SEQUENCE sde.state_id_generator_nc CACHE 1000
- ALTER SEQUENCE sde.version_id_generator CACHE 1000
- exec sys.DBMS_SHARED_POOL.KEEP('sde.connection_id_generator', 'Q')
- exec sys.DBMS_SHARED_POOL.KEEP('sde.state_id_generator_nc', 'Q')
- exec sys.DBMS_SHARED_POOL.KEEP('sde.version_id_generator', 'Q')
- 介绍一下:<pre name="code" class="sql">exec sys.DBMS_SHARED_POOL.KEEP
- -- Keep an object in the shared pool. Once an object has been keeped in
- -- the shared pool, it is not subject to aging out of the pool. This
- -- may be useful for certain semi-frequently used large objects since
- -- when large objects are brought into the shared pool, a larger
- -- number of other objects (much more than the size of the object
- -- being brought in, may need to be aged out in order to create a
- -- contiguous area large enough.
- -- WARNING: This procedure may not be supported in the future when
- -- and if automatic mechanisms are implemented to make this
- -- unnecessary.
- -- Input arguments:
- -- 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
- -- ----- ----------------------
- --<span style="white-space:pre"> </span> P package/procedure/function
- --<span style="white-space:pre"> </span> Q sequence
- --<span style="white-space:pre"> </span> R trigger
- --<span style="white-space:pre"> </span> T type
- -- JS java source
- -- JC java class
- --<span style="white-space:pre"> </span> JR java resource
- --<span style="white-space:pre"> </span> JD java shared data
- --<span style="white-space:pre"> </span> C cursor
- -- If and only if the first argument is a cursor address and hash-value,
- -- the flag parameter should be set to 'C' (or 'c').
- -- Exceptions:
- -- An exception will raised if the named object cannot be found.
附带:执行CACHE的性能对比,非ArcSDE例子
- 一个网友RAC 系统上的测试时结果:
- nocache: 2100s
- cache =1000: 55s
- 差别很明显。
- 测试一:
- SQL> create sequence seq_1 nocache;
- 序列已创建。
- SQL> set timing on;
- SQL> declare
- 2 x number;
- 3 begin
- 4 for i in 1 .. 10000 loop
- 5 select seq_1.nextval into x from dual;
- 6 end loop;
- 7 end;
- 8 /
- PL/SQL 过程已成功完成。
- 已用时间: 00: 00: 02.26
- 测试二:
- SQL> create sequence seq_2 cache 20;
- 序列已创建。
- 已用时间: 00: 00: 00.01
- SQL> declare
- 2 x number;
- 3 begin
- 4 for i in 1 .. 10000 loop
- 5 select seq_2.nextval into x from dual;
- 6 end loop;
- 7 end;
- 8 /
- PL/SQL 过程已成功完成。
- 已用时间: 00: 00: 00.46
- 测试三:
- SQL> create sequence seq_3 cache 100;
- 序列已创建。
- 已用时间: 00: 00: 00.05
- SQL> declare
- 2 x number;
- 3 begin
- 4 for i in 1 .. 10000 loop
- 5 select seq_3.nextval into x from dual;
- 6 end loop;
- 7 end;
- 8 /
- PL/SQL 过程已成功完成。
- 已用时间: 00: 00: 00.37
- 测试四:
- SQL> create sequence seq_4 cache 1000;
- 序列已创建。
- 已用时间: 00: 00: 00.04
- SQL> declare
- 2 x number;
- 3 begin
- 4 for i in 1 .. 40000 loop
- 5 select seq_4.nextval into x from dual;
- 6 end loop;
- 7 end;
- 8 /
- PL/SQL 过程已成功完成。
- 已用时间: 00: 00: 01.31
- SQL> declare
- 2 x number;
- 3 begin
- 4 for i in 1 .. 40000 loop
- 5 select seq_1.nextval into x from dual;
- 6 end loop;
- 7 end;
- 8 /
- PL/SQL 过程已成功完成。
- 已用时间: 00: 00: 09.33
- SQL>
- 小结:
- 在自己的本本上测试的,Oracle 11gR2. 单Instance数据库单会话循环不间断取1-4万个值。
- nocache: 2.26s 10000
- cache:20 0.46s 10000
- cache:100 0.37s 10000
- cache:1000 1.31s 40000
- nocache: 9.33s 40000
- 基本上cache 大于20的时候性能基本可以接受,nocache的时候性能确实很差.
- http://blog.csdn.net/linghe301/article/details/6852693