How to Overcome Performance Related Issues in CMRO when AHL Operation Key Flexfields are Involved

(Doc ID 971748.1)

Last updated on JANUARY 29, 2018

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***


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.

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.


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