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.