My Oracle Support Banner

Very Slow Response Time For Item Search In iProcurement (Doc ID 1292406.1)

Last updated on MAY 23, 2018

Applies to:

Oracle iProcurement - Version 12.0.0 to 12.2 [Release 12 to 12.2]
Information in this document applies to any platform.
***Checked for relevance on 21-Sep-2012***

Symptoms

In iProcurement R12
Users experience unusually slow response time for item search. Item search takes approximately 2 - 3 minutes to complete.

Expected Behavior

Item search take a few seconds to complete.

Steps To Reproduce

1. Log in and select iProcurement.
2. On shop page, search for item.


Trace may show any of the following (or similar) as having slow performance

SELECT /*+ DYNAMIC_SAMPLING(0) INDEX(i) */         TOKEN_FIRST,TOKEN_LAST,
 TOKEN_COUNT,ROWID  
FROM
"ICX"."DR$ICX_CAT_ITEMSCTXDESC_HDRS$I" i  WHERE TOKEN_TEXT = :word AND
 TOKEN_TYPE = :wtype  ORDER BY TOKEN_TEXT, TOKEN_TYPE, TOKEN_FIRST

 

SELECT  *  
FROM
( SELECT bq.relevance_score Relevance,'l#' || bq.inventory_item_id || '#' ||
bq.po_line_id ||'#' || bq.req_template_name || '#'
||bq.req_template_line_num || '#' || :ORG_ID_KEY1 ItemKey,bq.source_type
SourceType,DECODE(bq.source_type,'TEMPLATE',
decode(least(length(bq.req_template_name) , 20),20,
substr(bq.req_template_name,1,17) ||'...',bq.req_template_name),
'INTERNAL_TEMPLATE',decode(least(length(bq.req_template_name) , 20),20,
substr(bq.req_template_name,1,17) ||'...',bq.req_template_name),'QUOTATION',
ICX_CAT_UTIL_PVT.get_message('ICX_CAT_QUOTATION_SOURCE','NUMBER',
bq.document_number) ,'BLANKET',
ICX_CAT_UTIL_PVT.get_message('ICX_CAT_BLANKET_SOURCE','NUMBER',
bq.document_number),'GLOBAL_BLANKET',
ICX_CAT_UTIL_PVT.get_message('ICX_CAT_BLANKET_SOURCE','NUMBER',
bq.document_number), null) SOURCE,bq.inventory_item_id ItemId,
nvl(avtlp.description, bq.description) DESCRIPTION,
DECODE(LEAST(LENGTH(decode(1,1, nvl(avtlp.description, bq.description))),25)
, 25, SUBSTR(decode(1,1, nvl(avtlp.description, bq.description)),1, 22)
||'...', decode(1,1, nvl(avtlp.description, bq.description)))
TruncatedDescription,avtlp.long_description LONG_DESCRIPTION,
DECODE(LEAST(LENGTH(avtlp.long_description), 180), 180,
SUBSTR(avtlp.long_description, 1, 177)||'...', avtlp.long_description)
TruncatedLongDescription,msikfv.concatenated_segments INTERNAL_ITEM_NUM,
decode(bq.item_revision, '-2', to_char(null), bq.item_revision)
ITEM_REVISION,bq.po_header_id PoHeaderId,decode(bq.merged_source_type,
'REQ_TEMPLATE',NVL(bq.req_template_po_line_id,-2), bq.po_line_id) PoLineId,
bq.document_number DocumentNumber,bq.line_num DocumentLineNumber,
bq.req_template_name ReqTemplateName,bq.req_template_line_num
ReqTemplateLineNum,decode(bq.order_type_lookup_code, 'AMOUNT', 1,
decode(bq.allow_price_override_flag, 'Y', 1, 0)) AllowPriceOverrideFlag,
bq.not_to_exceed_price AmountLimit,decode(bq.order_type_lookup_code, 'FIXED
PRICE', 1, 0) IsFixedPrice,decode(bq.order_type_lookup_code, 'QUANTITY', 0,
1) IsFixedPriceOrAmountBased,decode(bq.order_type_lookup_code, 'AMOUNT', 0,
decode(bq.allow_price_override_flag, 'Y', 0, 1)) IsAmountReadOnly,
decode(bq.order_type_lookup_code, 'QUANTITY', 1, 0) IsQuantityBased,
nvl(bq.line_type_id, :GOODS_LINE_TYPE_KEY1) LineTypeId,bq.po_category_id
PurchasingCategoryId,bq.ip_category_id ShoppingCategoryId,
mck.concatenated_segments PURCHASING_CATEGORY,bq.ip_category_name
SHOPPING_CATEGORY,muomtl.uom_code UomCode,muomtl.unit_of_measure UomForCart,
muomtl.unit_of_measure_tl UOM,nvl(bq.suggested_quantity, 1) Quantity,
decode(bq.merged_source_type, 'MASTER_ITEM', decode(:ALLOWED_ITEM_TYPE_KEY1,
 'INTERNAL', to_number(null), bq.unit_price), bq.unit_price) PRICE,
decode(bq.order_type_lookup_code, 'AMOUNT', to_number(null), 'FIXED PRICE',
to_number(null), decode(bq.currency_code, :FUNC_CURRENCY_KEY1,
decode(bq.merged_source_type, 'MASTER_ITEM', decode(:ALLOWED_ITEM_TYPE_KEY2,
 'INTERNAL', to_number(null), bq.unit_price), bq.unit_price),
icx_cat_util_pvt.convert_amount(bq.currency_code, :FUNC_CURRENCY_KEY2,
decode(bq.currency_code, :FUNC_CURRENCY_KEY3, to_date(null),
decode(bq.global_agreement_flag, 'Y', trunc(sysdate), bq.rate_date)),
decode(bq.currency_code, :FUNC_CURRENCY_KEY4, to_char(null),
decode(bq.global_agreement_flag, 'Y', psp.default_rate_type, bq.rate_type)),
 decode(bq.currency_code, :FUNC_CURRENCY_KEY5, to_number(null),
decode(bq.global_agreement_flag, 'Y', to_number(null), bq.rate)),
decode(bq.merged_source_type, 'MASTER_ITEM', decode(:ALLOWED_ITEM_TYPE_KEY3,
 'INTERNAL', to_number(null), bq.unit_price), bq.unit_price))))
FUNCTIONAL_PRICE,decode(bq.order_type_lookup_code, 'AMOUNT', 1, bq.amount)
Amount,decode(bq.order_type_lookup_code, 'FIXED PRICE',
decode(bq.currency_code, :FUNC_CURRENCY_KEY6,
decode(bq.order_type_lookup_code, 'AMOUNT', 1, bq.amount),
icx_cat_util_pvt.convert_amount(bq.currency_code, :FUNC_CURRENCY_KEY7,
decode(bq.currency_code, :FUNC_CURRENCY_KEY8, to_date(null),
decode(bq.global_agreement_flag, 'Y', trunc(sysdate), bq.rate_date)),
decode(bq.currency_code, :FUNC_CURRENCY_KEY9, to_char(null),
decode(bq.global_agreement_flag, 'Y', psp.default_rate_type, bq.rate_type)),
 decode(bq.currency_code, :FUNC_CURRENCY_KEY10, to_number(null),
decode(bq.global_agreement_flag, 'Y', to_number(null), bq.rate)),
decode(bq.order_type_lookup_code, 'AMOUNT', 1, bq.amount))), to_number(null)
) FunctionalAmount,bq.currency_code CURRENCY,:FUNC_CURRENCY_KEY12
FUNCTIONAL_CURRENCY,decode(bq.currency_code, :FUNC_CURRENCY_KEY13,
to_char(null), decode(bq.global_agreement_flag, 'Y', psp.default_rate_type,
bq.rate_type)) RateType,decode(bq.currency_code, :FUNC_CURRENCY_KEY14,
to_date(null), decode(bq.global_agreement_flag, 'Y', trunc(sysdate),
bq.rate_date)) RateDate,decode(decode(bq.currency_code,
:FUNC_CURRENCY_KEY15, to_char(null), decode(bq.global_agreement_flag, 'Y',
psp.default_rate_type, bq.rate_type)), 'User', decode(bq.currency_code,
:FUNC_CURRENCY_KEY16, to_number(null), decode(bq.global_agreement_flag, 'Y',
 to_number(null), bq.rate)), decode(bq.currency_code, :FUNC_CURRENCY_KEY17,
to_number(null), ICX_CAT_UTIL_PVT.get_rate(bq.currency_code,
:FUNC_CURRENCY_KEY18, decode(bq.currency_code, :FUNC_CURRENCY_KEY19,
to_date(null),decode(bq.global_agreement_flag, 'Y', trunc(sysdate),
bq.rate_date)), decode(bq.currency_code, :FUNC_CURRENCY_KEY20, to_char(null)
, decode(bq.global_agreement_flag, 'Y', psp.default_rate_type, bq.rate_type)
)))) Rate,decode(bq.supplier_id, -2, to_number(null), bq.supplier_id)
SupplierId,bq.supplier_site_id SupplierSiteId,bq.supplier_contact_id
SupplierContactId,'N' NewSupplierFlag,bq.supplier SUPPLIER,
pvs.vendor_site_code SUPPLIER_SITE,to_char(null) SupplierContactName,
to_char(null) SupplierContactPhone,to_char(null) SupplierContactFax,
to_char(null) SupplierContactEmail,decode(bq.supplier_part_num, '##NULL##',
to_char(null), bq.supplier_part_num) SUPPLIER_PART_NUM,
decode(bq.supplier_part_auxid, '##NULL##', to_char(null),
bq.supplier_part_auxid) SUPPLIER_PART_AUXID,avtlp.manufacturer MANUFACTURER,
av.manufacturer_part_num MANUFACTURER_PART_NUM,bq.buyer_id BuyerId,
bq.rfq_required_flag RfqRequiredFlag,bq.negotiated_by_preparer_flag
NegotiatedByPreparerFlag,to_number(null) NonCatTemplateId,to_char(null)
Attribute1,to_char(null) Attribute2,to_char(null) Attribute3,to_char(null)
attribute4,to_char(null) Attribute5,to_char(null) attribute6,to_char(null)
Attribute7,to_char(null) Attribute8,to_char(null) Attribute9,to_char(null)
Attribute10,to_char(null) Attribute11,to_char(null) Attribute12,
to_char(null) Attribute13,to_char(null) Attribute14,to_char(null)
Attribute15,av.thumbnail_image THUMBNAIL_IMAGE,av.picture PICTURE,
avtlp.alias ALIAS,avtlp.comments COMMENTS,av.availability AVAILABILITY,
av.lead_time LEAD_TIME,av.supplier_url SUPPLIER_URL,av.manufacturer_url
MANUFACTURER_URL,av.attachment_url ATTACHMENT_URL,bq.is_item_available
IsItemAvailable,decode(bq.is_item_available, 1, 0, 1) IsItemNotAvailable,
decode(nvl(bq.req_template_name, '-2'), '-2', 0, 1) IsReqTemplate,
DECODE(bq.source_type, 'MASTER_ITEM', DECODE(bq.item_type, 'PURCHASE', 0, 1)
, 'INTERNAL_TEMPLATE', 1, DECODE(msikfv.internal_order_enabled_flag, 'Y', 1,
 0)) IsInternallyOrderable,DECODE(bq.source_type, 'MASTER_ITEM',
DECODE(bq.item_type, 'INTERNAL', 0, 1), 'INTERNAL_TEMPLATE', 0, 1)
IsPurchasable,DECODE(bq.source_type, 'MASTER_ITEM', bq.item_type,
'INTERNAL_TEMPLATE', 'INTERNAL', DECODE(msikfv.internal_order_enabled_flag,
'Y', 'BOTH', 'PURCHASE')) ItemType,bq.favorite_list_id FavoriteListId,
bq.favorite_list_line_id FavoriteListLineId,av.UNSPSC UNSPSC,to_number(null)
 HazardClassId,'CATALOG' CatalogType,'INTERNAL' CatalogSource,
to_number(null) ContentZoneId, decode(substr(lower(THUMBNAIL_IMAGE), 1, 7),
'http://', 0, decode(substr(lower(THUMBNAIL_IMAGE), 1, 8), 'https://', 0, 1)
) AS IsThumbnailFile,av.TEXT_BASE_ATTRIBUTE1 TEXT_BASE_ATTRIBUTE1 FROM
(SELECT ctxh.inventory_item_id, ctxh.po_line_id, ctxh.req_template_name,
ctxh.req_template_line_num, ctxh.org_id, ctxh.language, ctxh.source_type,
ctxh.purchasing_org_id, ctxh.ip_category_id, ctxh.ip_category_name,
ctxh.po_category_id, ctxh.owning_org_id, ctxh.supplier_id,
ctxh.supplier_part_num, ctxh.supplier_part_auxid, ctxh.supplier_site_id,
ctxh.item_type, ctxh.item_revision, ctxh.po_header_id, ctxh.document_number,
 ctxh.line_num, ctxh.allow_price_override_flag, ctxh.not_to_exceed_price,
ctxh.line_type_id, ctxh.unit_meas_lookup_code, ctxh.unit_price, ctxh.amount,
 nvl(ctxh.currency_code, :FUNC_CURRENCY_KEY11) currency_code,
ctxh.rate_type, ctxh.rate_date, ctxh.rate, ctxh.buyer_id,
ctxh.supplier_contact_id, ctxh.rfq_required_flag,
ctxh.negotiated_by_preparer_flag, ctxh.description, ctxh.supplier,
ctxh.req_template_po_line_id, ctxh.order_type_lookup_code,
ctxh.merged_source_type, ctxh.global_agreement_flag,
ctxh.suggested_quantity, score(1) relevance_score, 1 as is_item_available,  
to_number(null) favorite_list_id, to_number(null) favorite_list_line_id ,
count(*) over (partition by ctxh.inventory_item_id, ctxh.org_id,
ctxh.language) as source_count FROM icx_cat_items_ctx_hdrs_tlp ctxh WHERE
contains(ctxh.ctx_desc, :INTERMEDIA_KEY1, 1) > 0 AND
ICX_CAT_UTIL_PVT.is_item_valid_for_search( ctxh.source_type,
ctxh.po_line_id, ctxh.req_template_name, ctxh.req_template_line_num,
ctxh.po_category_id, ctxh.org_id) = 1 ) bq , po_system_parameters_all psp,
mtl_units_of_measure_tl muomtl, icx_cat_attribute_values av,
icx_cat_attribute_values_tlp avtlp, po_vendor_sites_all pvs,
mtl_categories_kfv mck, mtl_system_items_kfv msikfv  WHERE (bq.source_count
= 1 OR bq.source_type <> 'MASTER_ITEM') AND psp.org_id = :ORG_ID_KEY2 AND
bq.unit_meas_lookup_code = muomtl.unit_of_measure(+) AND bq.language =
muomtl.language(+) AND bq.inventory_item_id = av.inventory_item_id(+) AND
bq.owning_org_id = av.org_id(+) AND bq.po_line_id = av.po_line_id(+) AND
bq.req_template_name = av.req_template_name(+) AND bq.req_template_line_num
= av.req_template_line_num(+) AND bq.inventory_item_id =
avtlp.inventory_item_id(+) AND bq.owning_org_id = avtlp.org_id(+) AND
bq.po_line_id = avtlp.po_line_id(+) AND bq.req_template_name =
avtlp.req_template_name(+) AND bq.req_template_line_num =
avtlp.req_template_line_num(+) AND bq.language = avtlp.language(+) AND
bq.supplier_site_id = pvs.vendor_site_id(+) AND bq.po_category_id =
mck.category_id(+) AND bq.inventory_item_id = msikfv.inventory_item_id(+)
AND msikfv.organization_id(+) = :INV_ORG_ID_KEY1 ) bvoq

 

Cause

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
Symptoms
Cause
Solution
References


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