Database Reference
In-Depth Information
And that's it. We're ready to process the data in parallel, letting Oracle figure out based on the resources available
what the most appropriate degree of parallelism is:
EODA@ORA12CR1> alter session enable parallel dml;
Session altered.
EODA@ORA12CR1> insert /*+ append */
2 into t2(id,text,session_id)
3 select *
4 from table(parallel_pipelined
5 (CURSOR(select /*+ parallel(t1) */ *
6 from t1 )
7 ))
8 /
17914 rows created.
EODA@ORA12CR1> commit;
Commit complete.
Just to see what happened here, we can query the newly inserted data out and group by
SESSION_ID
to see how
many parallel execution servers were used and how many rows each one processed:
EODA@ORA12CR1> select session_id, count(*)
2 from t2
3 group by session_id;
SESSION_ID COUNT(*)
---------- ----------
198 2166
11 2569
13 2493
185 1865
95 2613
17 2377
256 2331
103 1500
8 rows selected.
Apparently, we used eight parallel execution servers for the
SELECT
component of this parallel operation, and
each one processed about 2,000 records each.
As you can see, Oracle parallelized our process, but we underwent a fairly radical rewrite of our process. This is a
long way from the original implementation. So, while Oracle can process our routine in parallel, we may well not have
any routines that are coded to be parallelized. If a rather large rewrite of your procedure is not feasible, you may well
be interested in the next implementation: DIY parallelism.
Do-It-Yourself Parallelism
Say we have that same process as in the preceding section: the serial, simple procedure. We cannot afford a rather
extensive rewrite of the implementation, but we would like to execute it in parallel. What can we do?
