Slow Performance Completing Due Date (DD) and Group Disconnect (GRPDISC) Tasks (Doc ID 2813286.1)

Last updated on JANUARY 11, 2022

Applies to:

Symptoms

After upgrading/installing 6.3.1.267, the DD and GRPDISC task completion times have been observed to take approximately 8-10 minutes. The query below was observed and associated with the slow running process.

SQL_ID = ds2j78yvmh3ks

SELECT drwo.object_id,
  d.design_id,
  drwo.object_shape,
  dottc.si_dm_column_value,
  src.circuit_activity_ind
  FROM design d,
  design_ord_summ dos,
  asap.serv_item si,
  asap.drawing_tbs_table_col dttc,
  asap.drawing_object drwo,
  asap.do_tbs_table_col dottc,
  asap.service_request_circuit src
 WHERE d.design_ord_sum_id = dos.design_ord_sum_id
  AND dttc.drawing_id = drwo.drawing_id
  AND drwo.object_id = dottc.object_id
  AND dttc.tbs_column_nm = dottc.tbs_column_nm
  AND dottc.do_tbs_seq = 0
  AND dttc.drwng_type_nm = 'GLR'
  AND dttc.tbs_table_nm = 'design'
  AND dttc.tbs_column_nm = 'design_id'
  AND dttc.si_dm_column_value = TO_CHAR (d.design_id)
  AND dttc.drwng_tbs_seq = 0
  AND d.serv_item_id = si.serv_item_id
  AND ( SUBSTR (dottc.SI_DM_COLUMN_VALUE, 1, 4) = 'CONN'
  OR SUBSTR (dottc.SI_DM_COLUMN_VALUE, 1, 4) = 'VIRT')
  AND SUBSTR (dottc.SI_DM_COLUMN_VALUE, 1, 10) <> 'CONN0CRCS0'
  AND src.circuit_design_id = si.circuit_design_id
  AND src.document_number = dos.document_number
  AND d.issue_status IN ('1', '2')
  AND EXISTS
  (SELECT 1
  FROM asap.ns_con_label_ckt nclc,
  asap.ns_cr_ns_con_spec_ckt ncncsc,
  asap.NS_COMP_REL_NS_CON_SPEC ncrncs,
  asap.NS_COMP_REL_NS ncrn,
  asap.ns_con_rel ncr
  WHERE ncncsc.NS_CRCSCKT_ID =
  SUBSTR (
  dottc.SI_DM_COLUMN_VALUE,
  5,
  INSTR (dottc.SI_DM_COLUMN_VALUE, 'CRCS') - 5)
  AND ncncsc.NS_CON_LABEL_ID = nclc.NS_CON_LABEL_ID
  AND ncncsc.NS_CKT_SEQ = nclc.NS_CKT_SEQ
  AND ncrncs.NS_CRCS_ID =
  CASE
  WHEN INSTR (dottc.SI_DM_COLUMN_VALUE, 'ID') >
  0
  THEN
  SUBSTR (
  dottc.SI_DM_COLUMN_VALUE,
  INSTR (dottc.SI_DM_COLUMN_VALUE,
  'CRCS')
  + 4,
  INSTR (dottc.SI_DM_COLUMN_VALUE,
  'ID')
  - ( INSTR (dottc.SI_DM_COLUMN_VALUE,
  'CRCS')
  + 4))
  ELSE
  SUBSTR (
  dottc.SI_DM_COLUMN_VALUE,
  INSTR (dottc.SI_DM_COLUMN_VALUE,
  'CRCS')
  + 4)
  END
  AND ( ( ncrncs.NS_COMP_ID_PARENT =
  ncrn.NS_COMP_ID_PARENT
  AND ncrncs.NS_COMP_ID_CHILD =
  ncrn.NS_COMP_ID_CHILD)
  OR ( ncrncs.NS_COMP_ID_CHILD =
  ncrn.NS_COMP_ID_CHILD
  AND ncrncs.NS_COMP_ID_PARENT =
  ncrn.NS_COMP_ID_PARENT))
  AND ncrncs.NS_CR_SEQ = ncrn.NS_CR_SEQ
  AND ncr.circuit_design_id_parent =
  nclc.circuit_design_id
  AND SUBSTR (dottc.SI_DM_COLUMN_VALUE, 1, 4) = 'CONN'
  AND SUBSTR (dottc.SI_DM_COLUMN_VALUE, 1, 10) <>
  'CONN0CRCS0'
  AND ncr.document_number = :1
  AND ncr.circuit_design_id_child = si.circuit_design_id
  UNION
  SELECT 1
  FROM ASAP.CIRCUIT C, asap.ns_con_rel ncr
  WHERE c.circuit_design_id =
  CASE
  WHEN INSTR (dottc.SI_DM_COLUMN_VALUE, 'ID') >
  0
  THEN
  SUBSTR (
  dottc.SI_DM_COLUMN_VALUE,
  5,
  INSTR (dottc.SI_DM_COLUMN_VALUE,
  'ID')
  - 5)
  ELSE
  SUBSTR (
  dottc.SI_DM_COLUMN_VALUE,
  INSTR (dottc.SI_DM_COLUMN_VALUE,
  'VIRT')
  + 4)
  END
  AND ncr.circuit_design_id_parent = c.circuit_design_id
  AND SUBSTR (dottc.SI_DM_COLUMN_VALUE, 1, 4) = 'VIRT'
  AND ncr.circuit_design_id_child = si.circuit_design_id
  AND ncr.document_number = :2)

Changes

The problem was introduced in MetaSolv Solution 6.3.1.267.

Cause

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