在Oracle数据库中可以将数据库对象Pin到共享池中来提高数据库性能
PS:Pin直译为钉住、压住,个人理解为将对象放入到共享池中,因为不知道翻译什么合适,都已Pin直接描述。
建议Esri所有的存储过程和序列都被Pin,一旦对象驻留在Oracle的共享池中,他们不需要对其进行分析,从而节省大量资源。
在共享池中Pin对象所使用的可用内存可能会被其他进程占用,所以为共享池分配额外的内存是非常必要的,一般情况下,做不Pin对象很少使用 ;这可能会对数据库性能的不利影响。
执行步骤
1:在执行Pin数据库对象之前,需要将SYS用户的共享池(DBMS_SHARED_POOL)权限赋予SDE用户
2:在共享池Pin对象
PS:Pin直译为钉住、压住,个人理解为将对象放入到共享池中,因为不知道翻译什么合适,都已Pin直接描述。
建议Esri所有的存储过程和序列都被Pin,一旦对象驻留在Oracle的共享池中,他们不需要对其进行分析,从而节省大量资源。
在共享池中Pin对象所使用的可用内存可能会被其他进程占用,所以为共享池分配额外的内存是非常必要的,一般情况下,做不Pin对象很少使用 ;这可能会对数据库性能的不利影响。
执行步骤
1:在执行Pin数据库对象之前,需要将SYS用户的共享池(DBMS_SHARED_POOL)权限赋予SDE用户
- GRANT execute ON dbms_shared_pool TO sde;
- EXECUTE dbms_shared_pool.keep ('STANDARD', 'P');
- 可以对SYS用户下的以下包进行Pin
- STANDARD
- DBMS_STANDARD
- DBMS_UTILITY
- DBMS_DESCRIBE
- DBMS_OUTPUT
3:作为SDE用户,可以将频繁使用的对象进行Pin
- EXECUTE dbms_shared_pool.keep ('VERSION_UTIL', 'P');
- EXECUTE dbms_shared_pool.keep ('LOCK_UTIL', 'P');
4:数据库对象并不限于存储过程。数据库序列、 触发器和游标可以全部PIN在共享池中。ESRI 建议PIN经常使用的序列。
- EXECUTE dbms_shared_pool.keep ('SDE.CONNECTION_ID_GENERATOR', 'Q');
- -- 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.