[jlgottlieb]

Hi all, I just wanted to present some of our results with OutXmlProfiles to boost performance in case any 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 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:
# of sql calls
338 74 86
size of Xml 95 - 110K 34-40K 44-50K
avg # pushes/day 1100 (*) 3800 (*)

With a little testing, I was able to determine that the full Glog XML for a PlannedShipment element was making 338 sql calls to generate. After implementing the OutXmlProfiles, the number of sql calls was reduced to 63 for one external system and to 78 for the other. The size of the XMLs were reduced from 95 - 110K to

[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.