Reports 2017-11-18T12:27:27+00:00

INV Item Import Performance

Description
Categories: DBA, Enginatics, Manufacturing
Performance of the inventory item import (INCOIN) concurrent program by looking at the number of items created and the run time of the corresponding concurrent request.
If the number of items processed per second is decreasing with increasing total items processed, then the interface SQLs are most likely using a wrong non selective index on mtl_system_items_interface.

select
x.request_id,
x.user_name,
x.actual_start_date,
x.actual_completion_date,
x.seconds,
xxen_util.time(x.seconds) time,
x.item_count,
x.total_item_count,
round(x.item_count/xxen_util.zero_to_null(x.seconds),3) items_per_second,
round(x.total_item_count/xxen_util.zero_to_null(x.seconds),2) total_items_per_second,
x.min_creation_date
from
(
select distinct
msib.request_id,
xxen_util.user_name(msib.created_by) user_name,
fcr.actual_start_date,
fcr.actual_completion_date,
(nvl(fcr.actual_completion_date,sysdate)-fcr.actual_start_date)*86400 seconds,
count(distinct msib.inventory_item_id) over (partition by msib.created_by, msib.request_id) item_count,
count(*) over (partition by msib.created_by, msib.request_id) total_item_count,
min(msib.creation_date) over (partition by msib.created_by, msib.request_id) min_creation_date
from
mtl_system_items_b msib,
fnd_concurrent_requests fcr
where
1=1 and
msib.request_id=fcr.request_id
) x
where
2=2
order by
x.request_id desc

Parameter Name SQL text Validation
Past Days
msib.creation_date>=sysdate-:past_days
Number
Min Total Item Count
x.total_item_count>=:min_total_item_count
Number