Fetch time reduce

[Laurel]

Hi Erman!
How are you ? Hope all is OK. :)
Question :
for performance  created MV. Sql execution lasts 5 min in DB
But the fetch to catch all rows 100K takes 1 Hour after that
Any hint how we can  improve fetch time ?
Tried to increase arraysize. Did not help much.
Thx!
Br,Laurel

[ErmanArslansOracleBlog]

Hi Laurel ,

Thanks :)

What kind of MV is that? ( based on some remote dblinks maybe?)
Reading LOBs may also increase the fetch time..
What about the network between the client/app and database? Does it have the specs to support that size of a fetch? I mean you fetch 100K rows ( Don't know the average row size but you may check that..)
I mean, check the network latency as well.. SDU & MTU tunables can also be revisited for that..
Troubleshooting Waits for 'SQL*Net message to client' and 'SQL*Net more data to client' Events from a Performance Perspective (Doc ID 1404526.1)

I give you some references blindly but they are okay, they are in the context.. But!, still you should analyze further -> Document 376442.1 How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues

Still 1 hour is very high.. I think you have some sqlnet roundtrips and context switches there and these may be the causes of that long fetch wait.
I don't know what client you use, but for sqlplus arraysize should do the work.. For java, there are other tunables for that as well..

Also take a look at -> https://tanelpoder.com/posts/reasons-why-select-star-is-bad-for-sql-performance/

and this one-> Row Prefetching and it's Impact on Logical Reads and Fetch Calls (Doc ID 1419023.1)