Why do you think the issue is with XMLAgg?
Try with the following table definition instead
CREATE TABLE tmp_test_xml ( acc_ID NUMBER(12), CUS_DTLS XMLTYPE -- changed storage. Defaults to SECUREFILE BINARY XML in your version )
and also remove the XMLSERIALIZE from your SQL statement as well.
The performance degradation you show reads like a memory leak, so just testing to see if it is in the conversion from an XMLType to a CLOB. You could also open a SR with Oracle Support on the issue as well as they would have better insight.
BINGO !! Thanks Jason !!!
Yes probably the conversion from an XMLType to a CLOB was the culprit.!!
XMLAGG was innocent here ...
I removed the XMLSERIALIZE and changed the destination table column type to xmltype.. and it worked like a charm...
1 million records loaded in less than 5 mins !!!