ArcSDE性能优化-对于SDE 库更改 Oracle 序列的高速缓存大小_oracle数据库序列号高速缓存大小-CSDN博客

看的很好，存下来~

ArcSDE性能优化-对于SDE 库更改 Oracle 序列的高速缓存大小

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：

[sql] view plain copy print ?

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

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

2：

[sql] view plain copy print ?

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')

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')

[sql] view plain copy print ?

介绍一下：<pre class="sql" name="code">exec sys.DBMS_SHARED_POOL.KEEP</pre>
<pre></pre>
<pre class="sql" name="code"> -- 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
-- ----- ----------------------
-- 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
-- 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.
</pre>
附带：执行CACHE的性能对比，非ArcSDE例子
<pre class="sql" name="code">一个网友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的时候性能确实很差．</pre>
<pre></pre>
<pre></pre>