My Oracle Support Banner

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

Last updated on MAY 31, 2019

Applies to:

Oracle Complex Maintenance, Repair, & Overhaul - Version 12.1 and later
Information in this document applies to any platform.


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 = <route_id>
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)));

The following article can be used to generate such a report:
<Note: 215187.1> - SQLT (SQLTXPLAIN) - Enhanced Explain Plan

Setup considerations:

Setting up Operation Flexfield contains the following:

Validate Existence of Operation Flexfield
1. Login into the forms environment with Application Developer Responsibility.
2. Navigate to Flexfield > Key > Segments.
3. Query for Flexfield Title 'AHL Operation'. Query should return the record

Add segments to this flexfield
1. Click the Segments button.
2. Enter Records for Operation Flexfield Segment.

Create Value Sets to be used by Flexfield Segments
1. Click the Value Set button.
2. Define Value Set Name.
3. Define List Type as 'List of Values'.
4. Define Security Type as 'No Security'.
5. Define Format Type as 'Char'.
6. Define Validation Type as 'Independent'.
7. Update Flexfield Segments with the Value Sets.

Create values for Value Sets
1. Navigate to Flexfield > Key > Values.
2. Find the Value Sets for the System Flexfield.
3. Add the desired values to the value Set.

Note: Segment Separator and the exact order of the SEGMENTs that comprise the Key Flexfield are important. This will be explained later.


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

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