Surely this is simply because the input to the test is an XMLTYPE (the result of his test query).
- The call to CLOB2FILE requires an implicit conversion from XMLTYPE to CLOB and then simply serialising the CLOB (a large string already in XML format)
- WRITETOFILE is making an implicit conversion from XMLTYPE to DOMDOCUMENT - it is constructing a DOM tree and then walking it to produce the serialised output. Here's the signature for WRITETOFILE:
Building that tree is a major overhead - in this case - though obviously it wouldn't be if you actually needed to navigate around the tree adding/moving/updating or pruning nodes and branches.
doc IN DOMDOCUMENT,
fileName IN VARCHAR2,
charset IN VARCHAR2);
Marco has continued the story here, in great detail and at great length (tkprof listings and all). He's particularly interested in file size; I'd be more interested in performance impact of the various steps in his operations. I will try to reproduce his tests and summarise those soon.