oracle锁等待

操作系统：centos7.6

数据库版本：Oracle19.3

异常状态：通过查看ash，有很多row cache lock等待。排查trace日志未发现异常。

通过排查ash，主要是由以下系统级的sql引起。

select   /*jskqjobqlod2*/   /*+ no_monitor no_statement_queuing current_instance */   nvl(con_id, 0) CON_ID,    obj# OBJOID,   class_oid CLSOID,         run_time RUNTIME,   2 * priority +    decode(bitand(job_status, 4), 0, 0,          decode(instance_id, :inst_id, -1, 1)) PRIORITY,   1 JOBTYPE,   schedule_limit SCHLIM,    job_weight WEIGHT,   decode(running_instance, NULL, 0, running_instance) INST,   decode(bitand(job_flags, 16384), 0, 0, 1) RUNNOW,   decode(bitand(job_status, 8388608), 0, 0, 1) ENQ_SCHLIM,   affinity SRVNAME from (   select     con_id,        obj#,       class_oid,        run_time,     priority,      job_status, running_instance, schedule_limit,     job_weight,    job_flags,  affinity,         instance_id,     service_flags, class_flags   from     containers(sys.scheduler$_job_refresh)   where         (   database_role = :db_role          or database_role = 'ALL'          or (    database_role is null              and :db_role = 'PRIMARY'))     and (   :guard_role <> 'ALL'          or database_role = 'ALL') union all   select     con_id,        obj#,       class_oid,        run_time,     priority,      job_status, running_instance, schedule_limit,     job_weight,    job_flags,  affinity,         instance_id,     service_flags, class_flags   from     containers(sys.scheduler$_lwjob_refresh)   where        :rac_on = 0     or running_instance = :inst_id) where       run_time <= :lookahead   and bitand(job_flags, 1048576) = 0   and (   (    class_oid is not null            and instance_id is null            and bitand(class_flags, :aff_type) <> 0            and (   (    :def_serv = 0                     and lower(affinity) = lower(:aff)                     and bitand(nvl(service_flags, 0), 128) = 0)                 or (    :def_serv = 1                     and (   lower(affinity) = lower(:aff)                          or bitand(nvl(service_flags, 0), 128) = 128))))        or (    :aff_type = 1            and instance_id is not null            and in

主要是job相关的。想起来由于使用过sql develeper编辑过job，编译以后未保存。保存以后系统恢复正常。