[jlgottlieb]

Hi all, I just wanted to present some of our results with OutXmlProfiles to boost performance in case anyone finds this helpful.

I attended the OTM SIG User Conference in Philadelphia last month and Eric Rosenbloom did a presentation on OTM Performance. This is a big concern of ours and I'm always looking for ways to boost performance. In one case study, he talked about a situation where all normal tuning efforts did not find any problem areas. After much investigation it was found that during "slow" times, a great deal of integration was happening. The fix was to implement OutXmlProfiles for the integration. Since I am getting reports of "slow" times as well, I implemented OutXmlProfiles on two of our ExternalSystems.

Both of these ExternalSystems use the PlannedShipment element and the profile was set to MAX_ELEMENTS. This is good when you are doing development because you want to see all the available data. What we found was that we do not actually use most of the data in the GLOG XML.

We started out developing a OutXmlProfile using the MIN elements and adding the XPaths, but I ran into some problems with this where the XPATH that I added was not in the generated XML. We found that the easiest method was to use an XML_TEMPLATE, which is basically a skeleton XML with only the elements that you want. Then define your OutXmlProfile based on the XML_TEMPLATE.

Here are our results:

No. of sql calls: 338 (Full Xml) to 74 (ExtSys1), 86 (ExtSys2)
Size of XML 95-110K (Full Xml) to 34-40K (ExtSys1), 44-50K(ExtSys2)

I'm not sure how to quantify this properly, but we have seen %CPU on our database server drop by about 10-15% with these OutXmlProfiles implemented.

I am now looking at our RateInquiry and GenericQuery, both of which have an optional OutXmlProfile element in the request. Sadly the ShipmentQuery does not have an OutXmlProfile option. I've been trying to use the GenericQuery to get a Shipment Element returned, but no luck so far. If anyone has a suggestion, let me know.

I thought our results with OutXmlProfiles might be useful to some of you out there.