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 APRIL 04, 2025

Applies to:

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

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