Poor performance on Snapshot Delete Worker (Doc ID 1664678.1)

Last updated on SEPTEMBER 30, 2024

Applies to:

Symptoms

On Memory-Based Snapshot Worker, performance is getting worse and according　to AWR report, the following SQL is runnning long;

select decode(oper_res.resource_id, null , (-1), rout_oper.department_id) ,
nvl(oper_res.resource_id, (-1)) , nvl(rout.line_id, (-23453)) ,
rout.sr_instance_id , rout.organization_id , to_char((-23453)) ,
rout.routing_sequence_id , rout.assembly_item_id , rout.routing_type ,
rout.routing_comment , (-23453) , nvl(rout.alternate_routing_designator,
to_char((-23453))) , rout_oper.operation_seq_num ,
rout_oper.operation_sequence_id , NVL(oper_seq.resource_seq_num, (-1)) ,
decode(mdr.batchable_flag, 1, 2, NVL(oper_res.basis_type, (-23453))) ,
nvl(oper_res.resource_units, 1) , 1 , NVL(((oper_res.resource_usage*
muc2.conversion_rate)/muc1.conversion_rate), 0) ,
NVL((oper_seq.resource_offset_percent/100),
(msc_snapshot_pk.get_op_leadtime_percent(rout_oper.plan_id,
rout_oper.routing_sequence_id, rout_oper.sr_instance_id,
rout_oper.operation_seq_num)/100)) , (-23453) ,
TO_NUMBER(TO_CHAR(rout_oper.effectivity_date, 'J')) ,

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.