it would be great if anyone can help me to achieve this or maybe know a simpler way to load data from different views in one table parallel (at the same time)
Since you asked it this way, what I'm suggesting is a simpler way to load data from multiple views concurrently than the method you are trying to use. That's what you asked, that's what I'm suggesting. First in a comment, and now in an answer in order to better demonstrate.
insert /*+ enable_parallel_dml parallel(8) */ into mytable
select * from view_1
union all
select * from view_2
union all
select * from view_3
union all
select * from view_4
union all
select * from view_5
union all
select * from view_6
union all
select * from view_7
The above is one statement, submitted as a unit. Mission accomplished: it loads the table from the various views all at once. A lot simpler than using a PL/SQL engine to achieve parallelism.
Explanation: whatever "degree of parallelism" (DOP) you request (here 8), assuming the instance allows it and you have the CPU resources available, it will allocate twice that many parallel slave processes (two teams of the specified DOP each) which will divide up the work load at the block range level of the underling tables - a lot more granular, and therefore a lot more powerful, than dividing it up by the high-level individual view. It also parallelizes the joins and sort operations they might involve as well.
Further, this also enables "pdml" (parallel DML) which means not only the view queries are parallelized, but the block formatting of the segment being written to (the insert step itself). Space will be allocated above the "high water mark" (HWM), thereby bypassing freespace bitmap lookups and vastly reducing undo writes, as well as postponing index maintenance to the end so indexes don't impact the load step. This is the fastest, most efficient and simplest method of inserting data from multiple sources into a single table all at once.
Furthermore, since 12c, Oracle can run the blocks of a UNION ALL
set concurrently, allocating separate parallel slaves to each so that each executes at the same time, rather than one after the other. Initially that required a special hint (pq_concurrent_union
) but later versions make this the default - but only when it is beneficial to do so, which is mainly when your set involves distributed queries (to a remote database over a dblink). For most local operations, concurrent union is still possible but not really advantageous, as the point is to move the data the quickest, and that's already being achieved by the low-level parallelism of Oracle PQ/PX out of the box.
Simpler + faster = better.