在Oracle数据库中可以将数据库对象Pin到共享池中来提高数据库性能
PS:Pin直译为钉住、压住,个人理解为将对象放入到共享池中,因为不知道翻译什么合适,都已Pin直接描述。
建议Esri所有的存储过程和序列都被Pin,一旦对象驻留在Oracle的共享池中,他们不需要对其进行分析,从而节省大量资源。
在共享池中Pin对象所使用的可用内存可能会被其他进程占用,所以为共享池分配额外的内存是非常必要的,一般情况下,做不Pin对象很少使用 ;这可能会对数据库性能的不利影响。
执行步骤
1:在执行Pin数据库对象之前,需要将SYS用户的共享池(DBMS_SHARED_POOL)权限赋予SDE用户
文章来源: http://resources.arcgis.com/zh-cn/content/kbase?fa=articleShow&d=20504
PS:Pin直译为钉住、压住,个人理解为将对象放入到共享池中,因为不知道翻译什么合适,都已Pin直接描述。
建议Esri所有的存储过程和序列都被Pin,一旦对象驻留在Oracle的共享池中,他们不需要对其进行分析,从而节省大量资源。
在共享池中Pin对象所使用的可用内存可能会被其他进程占用,所以为共享池分配额外的内存是非常必要的,一般情况下,做不Pin对象很少使用 ;这可能会对数据库性能的不利影响。
执行步骤
1:在执行Pin数据库对象之前,需要将SYS用户的共享池(DBMS_SHARED_POOL)权限赋予SDE用户
GRANT execute ON dbms_shared_pool TO sde;
2:在共享池Pin对象
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.
文章来源: http://resources.arcgis.com/zh-cn/content/kbase?fa=articleShow&d=20504