CLOB performance issue
When selecting from a CLOB on a remote database the performance is very slow. When casting the same CLOB to a varchar2 (or multiple varchar2s), the performance is much better. Please explain why and provide suggestions for workaround to the issue.
After researching further it appears that the number of round trips is always approximately 2x the number of rows returned. Appears there is a fetch of the lob locator as well as a fetch of the data itself, which both result in network round trip (as opposed to simply filling a packet with as much data as will fit). So, that explains the why. Still curious whether or not there are any enhancements in 11G to improve the performance.
After researching further it appears that the number of round trips is always approximately 2x the number of rows returned. Appears there is a fetch of the lob locator as well as a fetch of the data itself, which both result in network round trip (as opposed to simply filling a packet with as much data as will fit). So, that explains the why. Still curious whether or not there are any enhancements in 11G to improve the performance.
0