Snapshot Delete Worker Processes Do Not Complete - Hangs (Doc ID 356230.1)

Last updated on DECEMBER 15, 2016

Applies to:

Oracle Advanced Supply Chain Planning - Version 11.5.10 and later
Information in this document applies to any platform.
Concurrent Program: MSCNSW - Memory-Based Snapshot Worker
EXECUTABLE: MSCSDW - Snapshot Delete Worker

Symptoms

While running an ASCP plan, the MRP Snapshot Workers and MRP Snapshot Delete Workers were hanging. When checking the setups of the Standard Manager (20) and the MRP: Snapshot Worker profile options (5), everything was setup correctly. We found that the statement that was hanging was:

declare
ret_code number := 0;
snp_exception exception;
snp_timed_out exception;

begin
ret_code := db
ms_pipe.receive_message(:ipname,10);

if ret_code = 1 then
raise snp_timed_out;
end if;

if ret_code <> 0 then
raise snp_exception;
end if;

dbms_pipe.unpack_message(:opname);
dbms_pipe.unpack_message(:request_id);
dbms_pipe.unpack_message(:msg_type);
dbms_pipe.unpack_message(:file_type);

if dbms_pipe.next_item_type = 9 then
dbms_pipe.unpack_message(:DAT);
dbms_pipe.unpack_message(:CTL);
dbms_pipe.unpack_message(:DIS);
dbms_pipe.unpack_message(:BAD);
end if;

exception
when snp_timed_out then
:time_out :=1;
when others then
:err_msg := '( ' || to_char (ret_code) || ') '|| sqlerrm;
:err_code := sqlcode;

end;

Using <note 186472.1>, I was able to verify the issue by running the following SQL statement
multiple times while the workers were running...

SQL> select /*+ ORDERED */ sql_text
2 from v$session s, v$sqltext t
3 where t.address = s.sql_address
4 and s.sid = 189 --&1
5 order by piece
6 /
Enter value for 1: 189

The output returned was the same SQL statement identified in the issue verification.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms