Re: How do I speed up processing the data from a database link?

"Jaap W. van Dijk" <j.w.vandijk_at_hetnet.nl> wrote in message news:1148906298.513352.188100_at_j33g2000cwa.googlegroups.com...
> Jonathan,
>
> I've finally come around to testing your suggestion. Loading the table
> now took 54 minutes, as opposed to 32 minutes in case of reading over
> the link, but there were other processes busy executing this time. The
> wait event distribution looks as follows:
>
> log file switch completion
> number: 46
> time waited: 2.89 s
>
> free buffer waits
> number: 254
> time waited: 13.64 s
>
> db file sequential read (a tracked the p1/p2 values for some, the
> target table was read!)
> number: 9872
> time waited: 28.57 s
>
> db file scattered read
> number: 7827
> time waited: 111.33 s
>
> CPU used by this session
> total time: 1841.19 s (value when reading over the link:1496.86 s)
>
> Reading the target table now takes 111 seconds. This used to be 57
> seconds, possibly due to the other processes running?
>
> As you predicted, CPU increases. Would this mean that enlarging the SDU
> could decrease the CPU consumption for reading the remote table?
>
> Jaap.
>

Not quite sure what these results relate to. I think my suggestion was to emulate the distributed CTAS in the local system by
using a pl/sql loop on the local system to select and insert a few rows at a time. The intention was to show that it was the nature of the small-array effect that was causing the main difference in time, due to extra CPU.

I think you are telling me that the results agree with my prediction - but you can't be absolutely certain because of other processes now going on.

If that is the case, then increasing the 'remote array size' by increasing the SDU (and TDU if it still applies) should help.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html