查​询​o​r​a​c​l​e​中​正​在​运​行​的​存​储​过​程

1、如何查看ORACLE中正在运行的存储过程


select owner,name from v$db_object_cache where type like '%PROCE%' and locks >0 and pins >0;

2、如何停止正在运行的存储过程
可以查看正在运行的SESSION，把相应存储过程的SESSION给KILL掉即可！存储过程中的SQL都是可以跟踪到的（SESSION）
select * from v$access o where o.OBJECT like 'XXWPF007%'
select * from v$session a WHERE A.SID=sid
alter system kill session 'sid,serial#'




动态性能表--V$DB_OBJECT_CACHE 


　　本视图提供对象在library cache(shared pool)中对象统计，提供比v$librarycache更多的细节，并且常用于找出shared pool中的活动对象。


v$db_object_cache中的常用列：OWNER：对象拥有者
NAME：对象名称
TYPE：对象类型(如，sequence,procedure,function,package,package body,trigger)
KEPT：告知是否对象常驻shared pool(yes/no)，有赖于这个对象是否已经利用PL/SQL 过程DBMS_SHARED_POOL.KEEP“保持”（永久固定在内存中）
SHARABLE_MEM：共享内存占用
PINS：当前执行对象的session数

LOCKS：当前锁定对象的session数

瞬间状态列：
下列列保持对象自初次加载起的统计信息：
LOADS：对象被加载次数。


示例：
1.shared pool执行以及内存使用总计
下列查询显示出shared pool内存对不同类别的对象
同时也显示是否有对象通过DBMS_SHARED_POOL.KEEP()过程常驻shared pool。
SELECT type, kept, COUNT(*), SUM(sharable_mem)
FROM V$DB_OBJECT_CACHE

GROUP BY type, kept;

2.通过载入次数找出对象
SELECT owner, name sharable_mem, kept, loads
FROM V$DB_OBJECT_CACHE
WHERE loads > 1 ORDER BY loads DESC;


找出使用的内存超过10M并且不在常驻内存的对象。
SELECT owner, name, sharable_mem, kept
FROM V$DB_OBJECT_CACHE
WHERE sharable_mem > 102400 AND kept = 'NO'
ORDER BY sharable_mem DESC;

======================================================================

关于v$db_object_cache 
显示在库缓存缓存（共享池）中被缓存的库对象。它比动态性能表 V$LIBRARYCACHE提供更多细节，并且在寻找共享池
中活动对象方面更加有用。这些对象包括表，索引，簇，PL/SQL过程和包装，并触发。在共享池对象级别的统计信息。 

列名数据类型描述OWNERVARCHAR2(64)对象所有者(如果是应用的sql语句，此列值一般都为空)NAMEVARCHAR2(1000)对象名称 (sql语句或者匿名块/游标的前1000个字符)DB_LINKVARCHAR2(64)数据库的链接名 如果存在dblinkNAMESPACEVARCHAR2(28)库缓存中对象命名类型: TABLE/PROCEDURE, BODY, TRIGGER, INDEX,CLUSTER, OBJECTTYPEVARCHAR2(28)对象类型 (for example, sequence, procedure, function, package, package body, trigger)SHARABLE_MEMNUMBER对象占用可共享内存的大小（单位：btyes）LOADSNUMBER  这个对象被加载到内存的次数. 当这个对象无效的时候这个值仍然会增加.EXECUTIONSNUMBER  无效的（Not used. See V$SQLAREA to see actual execution counts.）LOCKSNUMBER当前锁住这个对象的session数PINSNUMBER当前执行这个对象的session数KEPTVARCHAR2(3)(YES | NO) Depends on whether this object has been "kept" (permanently pinned in memory)  with the PL/SQL procedure DBMS_SHARED_POOL.KEEPCHILD_LATCHNUMBER保护这个对象的子锁数目.

2.关联的参数：     
shared_pool_size                        共享池的大小
shared_pool_reserved_size        保留池的大小

3.关联的视图:     
V$LIBRARYCACHE                      这个视图描述了对象从实例启动开始在库缓冲池中的信息集合
V$LIBRARY_CACHE_MEMORY     Displays information about memory allocated to library cache memory 
                                                  objects in different namespaces.
V$OBJECT_DEPENDENCY           能够通过当前装载在共享池中的包,过程或游标来决定依赖哪一个对象
V$SUBCACHE                            显示当前加载到库缓冲区的下级缓存的信息
V$SQL_SHARED_MEMORY     
V$SHARED_POOL_ADVICE       共享池大小设置建议器
V$SGASTAT                              SGA的详细信息
DBA_KEEPSIZES 


4.相关的包:     
DBMS_SHARED_POOL         显示共享池中对象的大小, keeping或者unkeeping对象以减少内存碎片

5.相关脚本:     
objpool.sql                         列出进场被加载的对象准备被PINNING住.
shared_pool_freelist.sql 

6.相关sql语句: 
--形成生成pin住共享池中当前没有被pin住的对象的sql语句。
--可以把v$db_object_cache中经常被加载但是没有被pin住的包，函数和过程等keep进来。
select 'exec sys.DBMS_SHARED_POOL.keep('||chr(39)||owner||'.'||NAME||chr(39)||','||chr(39)||'P'||chr(39)||');' as sql_to_run 
from  V$DB_OBJECT_CACHE where TYPE in ('PACKAGE','FUNCTION','PROCEDURE') and loads > 50 and kept='NO' and executions > 50;

SQL_TO_RUN
-------------------------------------------------------------
exec sys.DBMS_SHARED_POOL.keep('JXXXT.IN_GZ_LOGS','P');
exec sys.DBMS_SHARED_POOL.keep('JXXXT.IN_YC_LOGS','P');
exec sys.DBMS_SHARED_POOL.keep('JXXXT.IN_DX_GROUPSEND','P');
exec sys.DBMS_SHARED_POOL.keep('JXXXT.IN_JJ_LOGS','P');
exec sys.DBMS_SHARED_POOL.keep('JXXXT.IS_MOBILEPHONE','P');
exec sys.DBMS_SHARED_POOL.keep('JXXXT.IN_JA_LOGS','P');
exec sys.DBMS_SHARED_POOL.keep('JXXXT.IN_DX_SENDALLTEMP','P');

在执行exec sys.DBMS_SHARED_POOL.keep('JXXXT.IN_GZ_LOGS','P');可能会报出未定义的错误
需要在sqlplus下执行 dbmspool.sql这脚本  位于$ORACLE_HOME/rdbms/admin/下


--显示共享池中不同类型对象的分布. 
--显示是否存使用过程DBMS_SHARED_POOL.KEEP()进行pin住的对象.
col type for a20
col kept for a4
select type,count(*),kept,round(SUM(sharable_mem)/1024,0) share_mem_kilo 
from V$DB_OBJECT_CACHE where sharable_mem != 0 GROUP BY type, kept order by 3,4;
TYPE                   COUNT(*) KEPT SHARE_MEM_KILO
-------------------- ---------- ---- --------------
APP CONTEXT                   1 NO                1
SEQUENCE                      2 NO                3
NON-EXISTENT                  3 NO                3
PIPE                          5 NO                6
PUB_SUB                       5 NO                8
TRIGGER                       4 NO               14
FUNCTION                      4 NO               21
SYNONYM                      12 NO               56
VIEW                         29 NO               66
TABLE                        78 NO              161
PACKAGE BODY                 11 NO              166
PACKAGE                      12 NO              623
CURSOR                    42270 NO           332424
INDEX                         4 YES               5
CLUSTER                       6 YES              12
TABLE                        20 YES              43


--找出加载次数比较多的对象
col name for a80 trunc
SELECT owner,sharable_mem,kept,loads,name from V$DB_OBJECT_CACHE WHERE loads > 2 ORDER BY loads DESC; 

可以看到许多对象（表）被反复loads的次数很大，在v$db_object_cache表里被反复load多数是因为缓存不够，被挤出。
而造成这种原因多数是因为没有绑定变量，大量重复加载一样的语句造成的。而通过增加share_pool不能解决根本问题
解决方法：
1，修改sql语句，改用变量代替常量（开发来完成）
2，可以keep一些经常用到的小表。dbms_shared_pool数据包，可以通过 loads的次数和表的大小综合考虑要keep那些表

--找出使用了大量内存的对象.使用DBMS_SHARED_POOL.KEEP()进行pin住.
SELECT owner,name,sharable_mem,kept FROM V$DB_OBJECT_CACHE 
WHERE sharable_mem > 102400 AND kept = 'NO' ORDER BY sharable_mem DESC;


--对象在共享池中消耗的内存 在8点中所描述的查找需要连续内存的查找语句
col name for a40
col type for a30
select OWNER,NAME,TYPE,SHARABLE_MEM from V$DB_OBJECT_CACHE where SHARABLE_MEM > 10000
and type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE') order by SHARABLE_MEM desc;

--数据库稳定时，决定哪个对象进行pin住操作.
set linesize 150
col Oname for a40
col owner for a15
col Type for a20
SELECT owner||'.'||name Oname,substr(type,1,12) "Type", sharable_mem "Size",executions,loads,
kept FROM V$DB_OBJECT_CACHE WHERE type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE')
AND executions > 0  ORDER BY executions desc,loads desc,  sharable_mem desc;

--列出大的没有被pin住的对象.
set linesize 150
col sz for a10
col name for a100
col keeped for a6
select to_char(sharable_mem / 1024,'999999') sz_in_K, decode(kept, 'yes','yes  ','') keeped,
owner||','||name||lpad(' ',29 - (length(owner) + length(name))) || '(' ||type||')'name,
null extra, 0 iscur from v$db_object_cache v where sharable_mem > 1024 * 1000;

--列出大的没有被pin住的过程，包和函数
col type for a25
col name for a40    
col owner for a25
select owner,name,type,round(sum(sharable_mem/1024),1) sharable_mem_K from v$db_object_cache  where kept = 'NO'
and (type = 'PACKAGE' or type = 'FUNCTION' or type = 'PROCEDURE')
group by owner,name,type order by 4;

7.其它 
需要被pin入内存中的对象主要有：常用的较大的存储对象，如standard、diutil包；编译的常用的triggers；sequences。
最好在开机时就将其pin入内存中（我以为这里可以编写适当的开机trigger）。
这样，既是使用命令alter system flush shared_pool时，也不会讲这些object flush掉。
具体pin对象到内存的方法使用DBMS_SHARED_POOL.keep存储过程。可以用unkeep方法解除其pin状态。

8.db_object_cache和碎片化 
碎片化造成在共享池中虽然有许多小的碎片可以使用，但没有足够大的连续空间，这在共享池中是普遍的现象。
消除共享池错误的关键就是即将加载对象的大小是否可能会产生问题。一旦知道了这个存在问题的PL/SQL，那么
就可以在数据库启动时(这时共享池是完全连续的)就将这个代码固定。这将确保在调用大型包时，它已经在共
享池里，而不是在共享池中搜索连续的碎片(在使用系统时，这些碎片可能就不复存在)。
可以查询V$DB_OBJECT_CACHE视图来判断PL/SQL是否很大并且还没有被标识为"kept"的标记。今后需要加载这些
对象时，可能会产生问题(因为它们的大小和需要占用大量连续的内存)。通过查询V$DB_OBJECT_CACHE表，可以
发现那些没有固定，但由于所需空间太大而很有可能导致潜在问题的对象。