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,编译以后未保存。保存以后系统恢复正常。