For oracle 12.1.0.2,
we are processing clob table with 1.2 Million records about 800 mb as sys_refcursor. This job is taking almost an hour.
Oracle trace show it is doing 4 million waits for “SQL*Net message from client" which suggests client is pulling LOB across the server one at a time and processing it. Is they any way we could improve performance for it?
We are using default for InitialLOBFetchSize and FetchSize. Would it help to change these values?
From the documentation, I did not understand what to set for FetchSize
https://docs.oracle.com/cd/E63277_01/win.121/e63268/featData.htm#ODPNT304
" Note that the RowSize value does not change due to the data length in each individual column. Instead, the RowSize value is determined strictly from the metadata information of the database table(s) that the SELECT statement is executed against."
Does this mean rowSize of CLOB column would be 4 GB as max size of CLOB is 4 GB. So to fetch multiple rows I have to put FetchSize in multiple of 4 GB?
Thanks in advance. I am not quite familiar with odb.net