How to Overcome Performance Related Issues in CMRO when AHL Operation Key Flexfields are Involved (Doc ID 971748.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Complex Maintenance, Repair & Overhaul - Version 12.1 and later
Information in this document applies to any platform.
***Checked for relevance on 12-Mar-2013***
***Checked for the relevance on 3-Apr-2015***

Goal

This article is intended to provide guidelines in improving / resolving performance related issues in the CMRO Application Flows that make use of the AHL Opration flexfield.

Example of such flows are: Pushing a Visit to Production, associating a MR in Planning module, etc.

You may encounter such an issue if it is determined that FULL TABLE SCANS are made on AHL_OPERATIONS_B table (after collecting the sorted tkprofs for example).

Example of such a query might be:

SELECT ro.operation_id
FROM ahl_operations_vl o,
ahl_route_operations ro
WHERE o.operation_id = ro.operation_id
AND ro.route_id = 129190
AND o.revision_status_code = 'COMPLETE'
AND o.revision_number IN
(SELECT MAX(revision_number)
FROM ahl_operations_b_kfv
WHERE concatenated_segments = o.concatenated_segments
AND TRUNC(sysdate) BETWEEN TRUNC(start_date_active)
AND TRUNC(nvl(end_date_active, sysdate + 1)));


(Check the SQLT attached to this article for reviewing the EXPLAIN PLAN for this specific SQL statement - e.g. sqlt_s6206.zip

Solution

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms