11.2.0 or 22.214.171.124? If not .4, possible a bug-fix could resolve your issue. Seems like an internal memory leak given it appears to slow down as the volume increases. Can you better clarify what your two images relate to and why you think it is the call to .transform? A re-write may get you around the issue, or it may not, given I don't know either.
Thank you for your reply.
The two images are snips from PL/SQL hierarchical profiler runs, if you click on the images they pop up and one can see the full image, I had not realised they had been 'truncated' a bit but I think they show that the more documents we have a higher percentage of the run time happens in xmltransform.
A colleague also by using a more traditional debug method and logging times at what point the code was executing also had the same result.
We will run the jobs again and our DBA's will monitor for your suggestion.
Do you have a stand-alone test case that can be ran and shows the slowdown over time? I'd be curious to see the test case and see if it happens on 12.1 or on livesql.oracle.com. It would also be something you could take to Oracle as well.
We we are able to run a stand alone test case and on Oracle 11 and 12 Enterprise there appears to be a 'memory leak' with repeated calls to xmltype transform. We have raised an SR with Oracle and provided a script to be ran.
Thank you for your initial reply to my question. You pointed us in the correct direction. Appreciated.
I will update question/post when we receive a reply for the SR.