XQuery Profiling using Altova XMLSpy

Many of us may be working on XQuery/XSL Transformations in our OSB/BPEL projects. We might have often wondered for years as how we can check the performance of our XQuery resources at design time.

Oracle doesn’t ship any OOTB XQuery profiling tool. However we can use Altova XMLSpy for profiling our XQueries and evaluating their performance.

Here is a quick solution that we can use if needed.

I am using an existing complex XQuery Transformation that converts requests coming from one source system to another in XMLSpy.

Open the .xq/.xquery file in Altova XMLSpy.

Replace the last two lines with

xf:XFormXQ(doc('c:\ CreateSalesOrderRequest.xml')//CreateSalesOrderEBM)

Here we are directly providing an input to the XQuery. Well also place a sample request xml at the location provided so that the engine can pick the input file up.

Now click on the XSL/XQuery tab in Alto XMLSpy and check ‘Enable XSLT/XQuery Profiling’

Also enable XQuery Debugger in case you would want to set debug points.

That’s it. Now click on ALT+F10 or the XQuery Execution icon to execute the XQuery

What we see is that the XQuery is executed and we can see the response in a different pane. We can also see the detailed execution statistics for the XQuery in the profiling report.

The report can be drilled down to the narrowest element and we can gather statistics like Hit Count/ Execution Time etc. This could be of great importance if we need to find any performance bottlenecks.

Here is a sample output from the execution

Also now we get to see the execution report generated by the profiler. The report can be drilled down to the narrowest element and we can gather statistics like Hit Count/Execution Time etc. This could be of great importance if we need to find any performance bottlenecks.



You can also either create an OOTB chart from the profiling data or simply export it as XML.

Viola. Now you can pin to the deepest dungeon in your XQuery to figure out which construct is taking the maximum time to execute.

Important Consideration

One important note is that only standard XQuery functions will compile within Altova XMLSpy. If you have custom Oracle/BEA functions inside your XQuery i would suggest you comment those out.

All the files used in this demonstration can be found here at this link.