Purchasing Retrieve Time From OTL process Performance issue (Doc ID 1911075.1)

Last updated on JULY 15, 2016

Applies to:

Oracle Time and Labor - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, Retrievals Issues

ACTUAL BEHAVIOR
---------------
Running Purchasing "Retrieve Time from OTL every day 5 times ( for 5 operating units). Each request takes a long time to complete.

This query is responsible for 90% of the CPU time and 75% of the elapsed time.

The problem caused by this particular section:

( select 1 from hxc_time_attribute_usages usage,
  hxc_time_attributes att
  where usage.time_building_Block_id = detail_block.time_building_block_id AND
  usage.time_building_block_ovn = detail_block.object_version_number
  and
  att.time_attribute_id = usage.time_attribute_id and
  att.ATTRIBUTE2 IN (SELECT TO_CHAR(pol.po_line_id)
  FROM po_lines pol
  WHERE pol.order_type_lookup_code in ('RATE','FIXED PRICE')) and att.bld_blk_info_type_id = 63 )


The bottom line is that in a system that has many PO_LINES_ALL records and this statement  causes a performance issue


EXPECTED BEHAVIOR
-----------------------
Expecting performance problem to be solved.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run Purchasing Retrieval Time and Labor process
2. Takes very long time
3. Performance issue

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, user run "Retrieve Time from OTL every day 5 times ( for 5 operating units). Each request takes a long time to complete.

Cause

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