Large joins may use full scans and hash joins. If your tables are large enough, this will fill up your process working memory and start spilling to your temp-tablespace. At that time a few important effects come into play:
There seems to be a bug related to expression based (function based) indexes when the optimizer creates a plan for inserts over a database link. The symptom is ORA-00904: … : invalid identifier.
If you, like me, have stumbeled into trouble with the Scheduler not running jobs, you might realize that there are several ways to disable the Oracle scheduler. The parameter job_queue_processes is just one of them.
The more cunning one is that there’s a parameter, SCHEDULER_DISABLED, visible in the view dba_scheduler_global_attribute.
Lately I’ve been working a lot with large bulk-loads of data between Oracle databases. The loading is done over db-links and we needed to speed up some of the loads since parallel DML is not supported in distributed transactions.