Slow performance while completing PSR Due Date(DD) tasks on Metasolv 6.2.1.870(build)

Slow performance encountered when attempting to complete Due Date tasks.  Users will report that the Due Date task they're attempting to complete hangs for a long period of time.

PROBLEM

Some PSR order's DD task taking long time to complete. A long running query was observed to be running at that time and taking ~12 minutes per execution. The query below identified the ACTIVE SQL

processes consuming the highest amount of CPU on the DB. Many sessions found were running these processes.

The following SQL can help identify the problem by showing the process that is consuming the highest amount of CPU resources on the database:

select p.spid "Thread ID",
b.name "Background Process",
s.username "User Name",
s.machine,
s.terminal,
s.osuser "OS User",
s.client_identifier "MetaSolv Solution User",
s.program "OS Program",
s.status "STATUS",
s.sid "Session ID",
s.serial# "Serial No.",
sa.CPU_TIME/1000000,
sa.ELAPSED_TIME/1000000, sa.executions,
sa.sql_id,
sa.SQL_TEXT
from v$process p, v$bgprocess b, v$session s , v$SQL Sa
where s.paddr = p.addr
and b.paddr(+) = p.addr
and s.sql_address = sa.address
and s.sql_hash_value = sa.hash_value
and s.status='ACTIVE'
order by sa.CPU_TIME DESC;

Highlights of query:

BUSINESS IMPACT: STUCK THREAD on WebLogic server. Decreased system throughput.

CAUSE

The slow performance is caused by a poor performing query in the PACKAGE_PSR_DUE_DATE procedure. The problematic SQL(Function) call is made in the ASAP.PACKAGE_PSR_DUE_DATE.

Function of_disc_cust_site
(arg_document_number IN asap.serv_req.document_number %Type,
arg_serv_item_id in asap.serv_item.serv_item_id %type,
arg_cust_acct_id IN asap.cust_acct.cust_acct_id %type,
arg_return_text OUT VARCHAR2) Return Number IS

loc_return_code Number;
loc_return_text Varchar2(2000);
loc_proc_name varchar2(50) := 'of_disc_cust_site';
loc_cust_acct_id asap.cust_acct.cust_acct_id %type;
loc_serv_item_id asap.serv_item.serv_item_id %type;

cursor cur_serv_item (csr_doc_nbr asap.serv_req.document_number %type, csr_serv_item_id asap.serv_item.serv_item_id %type)
is select si.serv_item_id, n.ns_comp_id, cd.circuit_design_id
from asap.ns_comp_si n,
asap.serv_item si,
(select si.circuit_design_id
from asap.serv_item si, asap.circuit c
where serv_item_id in (select serv_item_id from asap.serv_req_si where document_number = csr_doc_nbr)
and si.serv_item_id in (select serv_item_id_rel from asap.serv_item_rel where serv_item_id = csr_serv_item_id)
and si.circuit_design_id = c.circuit_design_id
and c.nst_con_type = 1) cd
where n.ns_comp_id in
(select distinct nc.ns_comp_id_parent
from asap.ns_connection nc,
asap.nst_comp_type_con nctc,
asap.nst_config_comp_type ncct
where nc.circuit_design_id = cd.circuit_design_id
and nc.nst_comp_type_con_id = nctc.nst_comp_type_con_id
and ncct.nst_config_type_id = nctc.nst_nst_config_type_id_parent
and ncct.nst_comp_type = nctc.nst_comp_type_parent
and ncct.nst_net_ext_ind = 'Y'
union
select distinct nc.ns_comp_id_child
from asap.ns_connection nc,
asap.nst_comp_type_con nctc,
asap.nst_config_comp_type ncct
where nc.circuit_design_id = cd.circuit_design_id
and nc.nst_comp_type_con_id = nctc.nst_comp_type_con_id
and ncct.nst_config_type_id = nctc.nst_config_type_id_child
and ncct.nst_comp_type = nctc.nst_comp_type_child
and ncct.nst_net_ext_ind = 'Y')
and si.serv_item_id = n.serv_item_id
and si.status in ('1','6','S'); -- Bug 16901003

SOLUTION

To implement the solution, please execute the following steps:

1. Refer Bug 21573008 - Slow Query for PACKAGE_PSR_DUE_DATE of_disc_cust_site Function.
2. The problem should be fixed with an interim patch in the new M6.2.1 build6.2.1.b914(should come by end of Aug 15).

TEMPORARY WORKAROUND

If your company does not specifically use below piece of code in ASAP.PACKAGE_PSR_DUE_DATE in 6.0.15 and if the code is introduced in ASAP.PACKAGE_PSR_DUE_DATE in 6.2.1(M6 Upgrade process) then you can safely comment below code.

BEFORE-COMMENTED:

            --BUG 16901003 - Calling the new function here only for product bundles
            IF ins_item_type_cd IN ('SYSTEM', 'PRDBUNDLE')
            THEN
               --BUG 18415660 - Calling this function to move customer site to In-Service
               IF ins_serv_item_status IN ('1', '6')
               THEN
                  IF of_set_component_status (ins_document_number,
                                              ins_serv_item_id,
                                              ins_cust_acct_id,
                                              loc_return_text
                                             ) = -1
                  THEN
                     RAISE logic_error;
                  END IF;
               --BUG 18415660 END
               ELSIF ins_serv_item_status IN ('S', '7', '8')
               THEN
                  IF of_disc_cust_site (ins_document_number,
                                        ins_serv_item_id,
                                        ins_cust_acct_id,
                                        loc_return_text
                                       ) = -1
                  THEN
                     RAISE logic_error;
                  END IF;
               END IF;
            END IF;

COMMENTED:

/** Remove extra code from MSS 6.2.1
            --BUG 16901003 - Calling the new function here only for product bundles
            IF ins_item_type_cd IN ('SYSTEM', 'PRDBUNDLE')
            THEN
               --BUG 18415660 - Calling this function to move customer site to In-Service
               IF ins_serv_item_status IN ('1', '6')
               THEN
                  IF of_set_component_status (ins_document_number,
                                              ins_serv_item_id,
                                              ins_cust_acct_id,
                                              loc_return_text
                                             ) = -1
                  THEN
                     RAISE logic_error;
                  END IF;
               --BUG 18415660 END
               ELSIF ins_serv_item_status IN ('S', '7', '8')
               THEN
                  IF of_disc_cust_site (ins_document_number,
                                        ins_serv_item_id,
                                        ins_cust_acct_id,
                                        loc_return_text
                                       ) = -1
                  THEN
                     RAISE logic_error;
                  END IF;
               END IF;
            END IF;
*/

Hope above helps in fixing the slowness for completing DD tasks for your Metasolv applications, please leave your feedback or query.

MetaSolv DD tasks not completed by Background Processor

In MetaSolv M6 we know that DD tasks assigned to system work queue are completed by Background Processor.

Background Processor consists of three applications that are distributed between a remote server and the system administrator's workstation. The individual applications are:

Job Master (JMASTER.EXE)
Job Worker or (JWKR.EXE)
Job Manager or (JMANAGER.EXE)

The Background Processor also includes a job queue, which is a table in the MetaSolv Solution database. DD task is a system task which is associated with job(query task table for DD task and check job_id column) the job goes into the job queue database table. Job Master checks the queue periodically. When it finds a job, Job Master initiates a Job Worker and gives it the ID of the job to be processed. Job Manager acts as a window on the job queue that lets you manipulate the queue.

I have observed from experience that sometimes DD tasks are not completing. There may be following reasons for such system task like DD are not completing.

1. Background Processor is not running.
2. Background Processor is running but still DD is not completing.

Before investigating understand below
1. DD task is associated with a job.
2. The job is completed by running jWorker.
3. To invoke jWorker , jMaster(needs jManager to be configured) needs to be up and  running.
4. In some cases(network issues, system on which Background Processor is configured is  down etc) there are issues with jWorker gets stuck or jMaster is not able to invoke them.  Also sometimes even jWorker is running and picking up the jobs but could not complete it  and which moves job status to any of  ('NOR', 'EXE', 'ERR', 'COM') statuses.

In order to fix the issue execute below queries. Below queries will help only when Background Processor is running but not able to pick or complete the jobs because of which DD task doesn't complete.

1    To Update all stuck DD tasks in M6

update job_queue jq
set jq.job_scheduled_date   = sysdate,
jq.job_start_date       = sysdate,
jq.job_queue_status     = 'REA',
jq.job_percent_change   = '0',
jq.job_percent_complete = '0'
where jq.job_id in (select t.job_id
from job_queue jq, task t
where jq.job_queue_status in ('NOR', 'EXE', 'ERR', 'COM')
and t.job_id = jq.job_id
and t.task_status = 'Ready'
and t.task_type = 'DD');        

2   To Update stuck DD task for a specific Order in M6

update job_queue jq
set jq.job_scheduled_date   = sysdate,
jq.job_start_date       = sysdate,
jq.job_queue_status     = 'REA',
jq.job_percent_change   = '0',
jq.job_percent_complete = '0'
where jq.job_id in (select t.job_id
from job_queue jq, task t
where jq.job_queue_status in ('NOR', 'EXE', 'ERR', 'COM')
and t.job_id = jq.job_id
and t.task_status = 'Ready'
and t.task_type = 'DD'
and t.document_number=give your document number);

Also look into job_error table for the job_id as mentioned above to ensure there is no validation failure for the DD task.

Hope above helps in your issues related to stuck DD tasks. Please leave your feedback or query.