My Oracle Support Banner

After Upgrade to 19c Database Performance Issue in Process Manufacturing (OPM) Quality Workbench When Viewing Samples By Organization (Doc ID 2896712.1)

Last updated on SEPTEMBER 16, 2022

Applies to:

Oracle Process Manufacturing Product Development - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version

After database upgrade to 19c,  find performance issues in accessing quality workbench and viewing samples by organization.
The below SQL was identified  to be long running in the AWR report.

SQL ID - 9msa950k90b3v
select f.organization_code,a.sample_no,a.sample_id,a.sample_desc,substr(g.concatenated_segments,0,40) item_number,a.revision,d.meaning,a.parent_lot_number,a.lot_number, a.retain_as,a.priority from gmd _samples a,gmd_sample_spec_disp b,gmd_event_spec_disp c,gem_lookups d,org_access_view e,mtl_parameters f,mtl_system_items_kfv g where a.delete_mark = 0 and a.sample_id = b.sample_id and b.event_spec_d isp_id = c.event_spec_disp_id and a.sampling_event_id = c.sampling_event_id and c.spec_used_for_lot_attrib_ind = 'Y' and b.disposition = d.lookup_code and d.lookup_type = 'GMD_QC_SAMPLE_DISP' and a.sa mple_type = 'I' and a.organization_id = f.organization_id and e.organization_id = f.organization_id and f.process_enabled_flag ='Y' and e.responsibility_id = fnd_global.resp_id and g.inventory_item_id = a.inventory_item_id and g.organization_id = f.organization_id and b.disposition = :1 order by lpad(regexp_substr(a.sample_no,'^[[:digit:]]*'),20)||regexp_substr(a.sample_no,'[^[:digit:]].*')


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Responsibility: Quality Manager
2. Navigation: Quality Workbench
3. Click on Samples icon on left side of form, then expand View By "Samples By Organization"



Changes

 Upgrade database version to 19c

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


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