Tunning Autoconfig

Subject: Autoconfig taking more than 1 hr Doc ID: Note: Last Revision Date: 06-JUN-2009

Type: PROBLEM Status:

In this Document Symptoms Cause Solution References

This document is under testing. So, Please don’t implement in on the production directly. Without testing it on the TEST environment. By : Rahul Gupta Path InfoTech Limited Client Site : L.G. Electronics , Greater Noida

Applies to: Oracle Application Object Library - Version: 11.5.10 Database Version: 9.2.0.5

Symptoms : Autoconfig is taking more than 1 hr on DB/Concurrent Node

Cause : txkWfClone.sh is taking more than 1hr . Query ( mentioned below ) called up by the concerned script takes around 65 minutes and thus result the overall autoconfig timings to more than 1 hr .

UPDATE WF_ITEM_ATTRIBUTE_VALUES WIAV SET WIAV.TEXT_VALUE = REPLACE(WIAV.TEXT_VALUE,:B1,:B2) WHERE (WIAV.ITEM_TYPE, WIAV.NAME) = (SELECT WIA.ITEM_TYPE, WIA.NAME FROM WF_ITEM_ATTRIBUTES WIA WHERE WIA.TYPE = 'URL' AND WIA.ITEM_TYPE = WIAV.ITEM_TYPE AND WIA.NAME = WIAV.NAME) AND WIAV.TEXT_VALUE IS NOT NULL AND INSTR(WIAV.TEXT_VALUE, :B1) > 0

Size of the table “WF_ITEM_ATTRIBUTE_VALUES” is around 30G and the full table scan of it is taking a large amount of time . Since the cardinality of the columns involves is very low , thus the optimizer is also not picking up the composite index ( Index name : WF_ITEM_ATTRIBUTE_VALUES_PK) .

Solution / Workaround : Use the parallel to speed up the full table access. Following line should be changed in the package WF_CLONE from:

update set where

and and

WF_ITEM_ATTRIBUTE_VALUES wiav wiav.text_value = replace(wiav.text_value,source_web_agent,target_web_agent) (wiav.item_type, wiav.name) = (select wia.item_type, wia.name from WF_ITEM_ATTRIBUTES wia where wia.type = 'URL' and wia.item_type = wiav.item_type and wia.name = wiav.name) wiav.text_value is not null instr(wiav.text_value,source_web_agent) > 0 ;

to update set where

and and

/*+ full(wiav) parallel(wiav,20) */ WF_ITEM_ATTRIBUTE_VALUES wiav wiav.text_value = replace(wiav.text_value,source_web_agent,target_web_agent) (wiav.item_type, wiav.name) = (select wia.item_type, wia.name from WF_ITEM_ATTRIBUTES wia where wia.type = 'URL' and wia.item_type = wiav.item_type and wia.name = wiav.name) wiav.text_value is not null instr(wiav.text_value,source_web_agent) > 0 ;

Also check the MAX_PARALLEL_SERVERS on your site .After making the following changes , overall time of autoconfig will reduce to 10~12 minutes ( Previously it was taking more than 1 hr )

References BUG 5503192 - TXKWFCLONE.SH IS TAKING A HUGE AMOUNT OF TIME DURING AUTOCONFIG 298550.1 - Troubleshooting Workflow Data Growth Issues SR Number - 7371131.992