编译存储过程时被卡死的处理方法

同事修改存储过程trans_kc21时过程无法被编译被卡死
查V$DB_OBJECT_CACHE

SQL> SELECT * FROM V$DB_OBJECT_CACHE WHERE name=upper('trans_kc21') AND LOCKS!='0';

OWNER                                                            NAME                                                                             DB_LINK                                                          NAMESPACE                                                        TYPE                                                             SHARABLE_MEM      LOADS EXECUTIONS      LOCKS       PINS KEPT CHILD_LATCH INVALIDATIONS HASH_VALUE LOCK_MODE PIN_MODE  STATUS              TIMESTAMP                              PREVIOUS_TIMESTAMP                     LOCKED_TOTAL PINNED_TOTAL PROPERTY                                                                         FULL_HASH_VALUE
---------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------ ---------- ---------- ---------- ---------- ---- ----------- ------------- ---------- --------- --------- ------------------- -------------------------------------- -------------------------------------- ------------ ------------ -------------------------------------------------------------------------------- --------------------------------
SICP3_GZYB                                                       TRANS_KC21                                                                                                                                    TABLE/PROCEDURE                                                  PROCEDURE                                                              341712         34          1          1          1 NO         15307             0 1839741899 NULL      SHARED    VALID               2013-09-26/15:48:21                    2013-09-26/15:46:17                              47           68                                                                                  356a3b5c43dd126d3e52acbe6da83bcb

发现locks=1

按对象查出sid的值
v$access显示目前库缓存对象被锁定的信息,这个被强加的锁用来确保库缓存对象
在sql执行请求它时不会被老化

SQL> select SID from V$ACCESS WHERE object=upper('trans_kc21');

       SID
----------
      6566

查看session 状态

SQL> select a.SID,a.SERIAL#,b.SPID from v$session a,v$process b where a.SID=6566 and a.PADDR=b.ADDR;

       SID    SERIAL# SPID
---------- ---------- ------------------------
      6566         27 23462626

kill这个会话

SQL>alter system kill session '6566,27';

如果这个会话已经是被kill的

SQL> select a.SID,a.SERIAL#,b.SPID,a.STATUS from v$session a,v$process b where a.SID=6566 and a.PADDR=b.ADDR;

       SID    SERIAL# SPID                     STATUS
---------- ---------- ------------------------ --------
      6566         27 23462626                 KILLED

就要在操作系统层来kill

[/@zzld03]#kill -9 23462626

获取数据库每小时的排序统计数据

set pages 9999;
column sorts_memory format 999,999,999
column sorts_disk format 999,999,999
column ratio format .99999

select
to_char(sn.snap_time,’HH24′),
avg(newmem.value-oldmem.value) sorts_memory,
avg(newdsk.value-olddsk.value) sorts_disk
from
stats$sysstat oldmem,
stats$sysstat newmem,
stats$sysstat olddsk,
stats$sysstat newdsk,
stats$snapshot sn
where
newdsk.snap_id=sn.snap_id
and olddsk.snap_id=sn.snap_id-1
and newmem.snap_id=sn.snap_id
and oldmem.snap_id=sn.snap_id-1
and oldmem.name=’sorts (memory)’
and newmem.name=’sorts (memory)’
and olddsk.name=’sorts (disk)’
and newdsk.name=’sorts (disk)’
and newmem.value-oldmem.value>0
and newdsk.value-olddsk.value>0
group by to_char(sn.snap_time,’HH24′);

select
to_char(sn.begin_interval_time,’HH24′),
avg(newmem.value-oldmem.value) sorts_memory,
avg(newdsk.value-olddsk.value) sorts_disk
from
dba_hist_sysstat oldmem,
dba_hist_sysstat newmem,
dba_hist_sysstat olddsk,
dba_hist_sysstat newdsk,
dba_hist_snapshot sn
where
newdsk.snap_id=sn.snap_id
and olddsk.snap_id=sn.snap_id-1
and newmem.snap_id=sn.snap_id
and oldmem.snap_id=sn.snap_id-1
and oldmem.stat_name=’sorts (memory)’
and newmem.stat_name=’sorts (memory)’
and olddsk.stat_name=’sorts (disk)’
and newdsk.stat_name=’sorts (disk)’
and newmem.value-oldmem.value>0
and newdsk.value-olddsk.value>0
group by to_char(sn.begin_interval_time,’HH24′);

oracle rac中让sql语句在指定的节点执行的方法

rac中一条查询语句并行到各个节点执行没完成被中断后被限制到特定节点上执行时查询时查询语句执行不了.
客户情况是四个节点的11g rac,五个险种在做数据转换,由于一个险种的有些数据转换脚本的查询使用了并行执行被并行到别的节点上,而其它险种发现后将并行进程kill了.后面该险种的被kill掉的查询语句在特定节点上执行时当查询特定月份的数据时会卡住.产生的等待事件是gc cr request.

global cache cr request
当一个进程访问需要一个或者多个块时,它会首先检查自己的CACHE是否存在该块,如果发现没有,就会先通过global cache赋予这些块共享访问的权限,然后再访问。假如,通过global cache 发现这些块已经在另一个实例的CACHE里面,那么这些块就会通过CACHE FUSION,在节点之间直接传递,同时出现global cache cr request等待事件
注意:在10G以后,global cache cr request 已经简称为 gc cr request

查询语句如下:


            SELECT /*+ INDEX(MBF,IDX_MT_BIZ_FIN_DATE)USE_NL(MBF,MFF) */
                   mff.hospital_id AKB020,    --医疗机构编号
                   mff.serial_no AAZ218,    --业务序列号
                   mff.serial_fee AAZ213,    --费用序列号
                   mff.stat_type AKA063,    --统计类别
                   mff.fee_batch PKA001,    --费用批次
                   decode(mff.medi_item_type,0,'2',1,'1',2,'4',3,'5') AKE003,    --项目药品类型(0:项目,1:西药,2:中成药,3:中草药)
                   mff.item_code AKE001,    --中心药品项目编码
                   mff.item_name AKE002,    --中心药品项目名称
                   mff.his_item_code AKE005,    --医院药品项目编码
                   mff.his_item_name AKE006,    --医院药品项目名称
                   mff.serial_apply AAZ267,    --申请序列号
                   mff.fee_date PKA051,    --费用发生时间
                   mff.model PKA052,    --剂型
                   mff.factory PKA053,    --厂家
                   mff.standard PKA054,    --规格
                   mff.unit PKA055,    --计量单位
                   mff.price PKA056,    --单价
                   mff.dosage PKA057,    --用量
                   mff.money PKA058,    --金额
                   mff.reduce_money PKA059,    --冲减金额(主要为计算方便使用)
                   mff.usage_flag PKA060,    --使用标志(1:出院带药 2:抢救用药 3:急诊)
                   mff.usage_days PKA061,    --出院带药天数
                   mff.opp_serial_fee PKA062,    --对应费用序列号
                   mff.input_staff PKA063,    --录入人工号
                   mff.input_man PKA064,    --录入人
                   mff.input_date PKA065,    --录入时间
                   mff.calc_flag PKA066,    --计算标志
                   mff.frozen_flag PKA067,    --费用冻结标志,用来表识参保人所在单位的基本医疗保险被冻结期间录入的费用。0:未冻结;1:已冻结;2:冻结已处理
                   mff.frozen_serial_fee PKA068,    --对应冻结的费用序列号
                   mff.trans_date PKA069,    --费用上传时间
                   mff.recipe_no PKA070,    --处方号
                   mff.hos_serial PKA071,    --对应医院费用号
                   mff.recipe_hospital_id PKA072,    --处方医院编号
                   mff.recipe_hospital_name PKA073,    --处方医院名称
                   mff.doctor_no PKA074,    --处方医生编号
                   mff.doctor_name PKA075,    --处方医生姓名
                   mff.audit_flag PKA076,    --审核标志
                   mff.trans_flag PKA044,    --传输标志(0:未传输 1:已成功传输 2:未成功传输)
                   mff.valid_flag AAE100  --有效标志
              FROM gzyb.mt_biz_fin/*@gzybcx_link*/ mbf,
                   gzyb.mt_fee_fin/*@gzybcx_link*/ mff
             WHERE mbf.hospital_id = '004003'
               AND mbf.fin_date >= to_date('20130101', 'yyyy-mm-dd')
               AND mbf.fin_date  < to_date('20130201', 'yyyy-mm-dd')
               AND mbf.valid_flag = '1'
               AND mbf.hospital_id = mff.hospital_id
               AND mbf.serial_no = mff.serial_no;

在指定的特定节点执行上面的语句会产生gc cr request,因为之前被分发到别的节点上执行,该查询的部分数据被缓存到其它的节点上了.而现在不能并行在指定了特定实例来运行上面的查询,所以查询不能访问其它节点实例的sga,因为我的机器名是JINGYONG.从下面的查询可以看到上面的语句在没加parallel提示时该语句是在特定节点prddb02执行的.

      SID    SERIAL# USERNAME       PROGRAM                  MACHINE                  
---------- ---------- -------------  -----------------------  ------------------------
      6345       1387 SICP3_GZYB     oracle@prddb02 (J001)    prddb02                 
      6454        371 SICP3_GZYB     oracle@prddb02           WORKGROUP\POWERSI-HP    
      2508       1513 SICP3_GZYB     oracle@prddb02 (J000)    prddb02                 
      7865       234  SICP3_GZYB     oracle@prddb02 (J002)    WORKGROUP\JINGYONG

sid=7865,serial#=234就上面执行的sql语句.

当给上面的sql语句加上并行parallel提示

            SELECT /*+ INDEX(MBF,IDX_MT_BIZ_FIN_DATE)USE_NL(MBF,MFF) parallel(mff,1) */
                   mff.hospital_id AKB020,    --医疗机构编号
                   mff.serial_no AAZ218,    --业务序列号
                   mff.serial_fee AAZ213,    --费用序列号
                   mff.stat_type AKA063,    --统计类别
                   mff.fee_batch PKA001,    --费用批次
                   decode(mff.medi_item_type,0,'2',1,'1',2,'4',3,'5') AKE003,    --项目药品类型(0:项目,1:西药,2:中成药,3:中草药)
                   mff.item_code AKE001,    --中心药品项目编码
                   mff.item_name AKE002,    --中心药品项目名称
                   mff.his_item_code AKE005,    --医院药品项目编码
                   mff.his_item_name AKE006,    --医院药品项目名称
                   mff.serial_apply AAZ267,    --申请序列号
                   mff.fee_date PKA051,    --费用发生时间
                   mff.model PKA052,    --剂型
                   mff.factory PKA053,    --厂家
                   mff.standard PKA054,    --规格
                   mff.unit PKA055,    --计量单位
                   mff.price PKA056,    --单价
                   mff.dosage PKA057,    --用量
                   mff.money PKA058,    --金额
                   mff.reduce_money PKA059,    --冲减金额(主要为计算方便使用)
                   mff.usage_flag PKA060,    --使用标志(1:出院带药 2:抢救用药 3:急诊)
                   mff.usage_days PKA061,    --出院带药天数
                   mff.opp_serial_fee PKA062,    --对应费用序列号
                   mff.input_staff PKA063,    --录入人工号
                   mff.input_man PKA064,    --录入人
                   mff.input_date PKA065,    --录入时间
                   mff.calc_flag PKA066,    --计算标志
                   mff.frozen_flag PKA067,    --费用冻结标志,用来表识参保人所在单位的基本医疗保险被冻结期间录入的费用。0:未冻结;1:已冻结;2:冻结已处理
                   mff.frozen_serial_fee PKA068,    --对应冻结的费用序列号
                   mff.trans_date PKA069,    --费用上传时间
                   mff.recipe_no PKA070,    --处方号
                   mff.hos_serial PKA071,    --对应医院费用号
                   mff.recipe_hospital_id PKA072,    --处方医院编号
                   mff.recipe_hospital_name PKA073,    --处方医院名称
                   mff.doctor_no PKA074,    --处方医生编号
                   mff.doctor_name PKA075,    --处方医生姓名
                   mff.audit_flag PKA076,    --审核标志
                   mff.trans_flag PKA044,    --传输标志(0:未传输 1:已成功传输 2:未成功传输)
                   mff.valid_flag AAE100  --有效标志
              FROM gzyb.mt_biz_fin/*@gzybcx_link*/ mbf,
                   gzyb.mt_fee_fin/*@gzybcx_link*/ mff
             WHERE mbf.hospital_id = '004003'
               AND mbf.fin_date >= to_date('20130101', 'yyyy-mm-dd')
               AND mbf.fin_date  < to_date('20130201', 'yyyy-mm-dd')
               AND mbf.valid_flag = '1'
               AND mbf.hospital_id = mff.hospital_id
               AND mbf.serial_no = mff.serial_no;

这时查询虽然指定了运行的特定实例还是被并行到其它节点上执行了从而能访问其它实例sga所以查询很快就能查询出来
从下面的查询可以看到当加并行parallel后上面的sql虽然是在特定实例执行但却被并行到其它节点执行了

       SID    SERIAL# USERNAME       PROGRAM                  MACHINE                  
---------- ---------- -------------  -----------------------  ------------------------
      6345       1387 SICP3_GZYB     oracle@prddb02 (J001)    prddb02                 
      6454        371 SICP3_GZYB     oracle@prddb02           WORKGROUP\POWERSI-HP    
      2508       1513 SICP3_GZYB     oracle@prddb02 (J000)    prddb02  

因此上面的sql在特定实例执行卡住的原因找到了.

要想让并行进程只在单节点上执行可以设置parallel_force_local参数为true

SQL> show parameter force

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_force_local                 boolean     TRUE

如果想要让job也在指定的节点上运行而不被并行到其它节点上执行在创建job时要指定instance参数,将instance参数指定为你要使用的节点