Hi,

Caveat : never used DBMS_PARALLEL_EXECUTE (DPE), but thought about it some. We use our own batching and AQ frameworks. Also some experience with parallel pipelining (aka Oracle forking).

> assuming you haven't limited the number of parallel processes).
> Try with a much smaller number of chunks. Like 10.

Weird. I'd have thought creating lots of chunks (of 1000 rows each or so) and setting a reasonable parallel degree (say 16) would be the regular way to drive DPE? That is never let it flood the system with running all chunks in parallel. That would have the machine flailing spastically.

I guess this might also be related to the recommendation to commit rarely. 10 chunks = 10 commits. If so I'm still not convinced if this is sustainable in a mixed OLTP/batch system. Keeping locks so long doesn't sound right.

Running 10 (or 16) chunks AND commiting every 1000 rows or so might be possible (basically the way our pl/sql batch frameworks work). There's still the possibility the workload is not entirely even so the total running time is determined by the biggest/slowest chunk. With smaller chunks and X workers (basically our java and AQ frameworks) all workers fill finish at about the same time and thus the machine is used more evenly and the batch finishes faster.

regards,

Hi,

> few parallel chunks (N)

Mmmh. There is maybe a different use of chunk than what I'm thinking of in play. I would use three numbers :

X number of rows to process (say 1 million)
Y number of chunks (with chunksize 10.000 that would be 100)
Z number of parallel processes (say 16, parallel_level parameter to run_task, lets call them workers)

So 16 workers start processing the first 16 chunks. As each finishes it picks the next unprocessed chunk. Ends when the last chunk is processed. Here each would process about 7 chunks one after another.
The runtime for 16 chunks (70.000 rows or so) and and 16 workers would be about the same if the processing is perfectly uniform. If it isn't the slowest chunk problem appears. With smaller chunks no worker falls idle prematurely.
Typical case : using simple chunking with say id-ranges of 10000 each when not all IDs in each range exist or some are filtered out by some logic.
This can be addressed (for example PRECENTILE_CONT over the actual filtered statement) but with smaller chunking you wouldn't need to. Our own frameworks use the actual rows as input so don't have the problem of uneven effective chunk sizes. But their code is usually that much more complex than a simple INSERT as to make the processing time uneven even so.

That brings another question to the fore :
> I am having a data movement activity from one table to another table.
Why not do this with a simple parallel INSERT AS SELECT? Unless there are good reasons against nothing can beat that.

regards,

As Chris stated, it's crucial to know whether there is OLTP activity at the same time as this huge batch job.

- Is the target table being modified at the same time? This is the worst case, since the batch and OLTP can slow each other up, or there can be "lost updates".
- Is the source table being modified at the same time? If so, using DBMS_PARALLEL_EXECUTE with multiple commits can cause inconsistent data to be copied to the target.
- Is there other activity that does not touch the two tables? If so, the batch job should not use all the CPU/memory/IO resources.

Next question: can you create chunks based on something other than ROWID? That may take more time than the actual copying.
If both tables are partitioned in the same way, why not make each partition a chunk?

Now some general remarks.

You should not mix two types of parallelism:
either do an INSERT...AS SELECT in parallel
or do DBMS_PARALLEL_EXECUTE with each process running serially.

With DBMS_PARALLEL_EXECUTE, there can be more chunks than processes. Each process can do several chunks, but there will be one commit per chunk.

With answers to the two questions above, there will be a much better chance to provide appropriate advice.

Best regards,
Stew

Hi,

Ok. But I don't see how you assume that this trickle feed is slower than the big chunk version. I think it can only be faster (if the workload is anything but perfectly even). Both utilise the system the same (16 parallel processes in this case) but the smaller chunks never underutilize it towards the end.

If you were to "unleash" the small chunk version it would run all 100 in parallel. I think this would bring the system to a standstill so is not an option.

Whatever intermediate unleashing you choose (say 32 workers) because the system can take it can equally be used for either version.

regards,

Hi,

> You should aim to process the same number of rows in each chunk, right?

Aim, yes. Succeed not always (with DPE, maybe lack of knowledge on my part, because we haven't actually used it). Our frameworks have that part down. But still they exhibit uneven processing speed when the work per row is not constant.

This comes only into play if there is some complex processing involved. Say each row is a customer order. Most are small but some are resalers -> skew possible. Small chunks can adjust to this.

> There's overhead every time you start a new chunk

Yeah. I was thinking of our cases where the processing dominates the runtime. The chunk size would be determined by locking time limits. I.e. how many rows per say 10 seconds or at most a minute. Maybe be 10.000 or may be only 10 if each has lots to do. Also again we don't use DPE and I have misgivings about its chunk startup capabilities. For the framework side I would expect Oracle to "do its best". For the app side I would probably try to use JSON-strings with all 1000 IDs listed instead of the ID BETWEEN X AND Y scheme so processing can start right away.

> Unless you're doing something really funky

So yes I was in the funky camp. Non-funky -> MASS-DML.

There can of course still be many use cases where MASS-DML is too big and the logic small enough to go the big-chunk way. In particular could well be the case for the OP.

As I said I think once you have any Z for what the system can take both big and small chunk can use Z. Big will win if the logic is small enough to allow even processing. Small if its not, by enough to compensate for the startup overhead and then some.

regards,

Having the same number of rows does not necessarily mean "even processing".

If one chunk has 10,000 rows in 10,000 different blocks, that requires much more processing than if they are in 100 different blocks.

That is one reason why chunking by blocks can be preferable, provided we can process all the rows in the block at the same time.

If there are several chunks per process, "even processing" is easier to get since the first process to finish a chunk will start the next available chunk.

To be clear, "chunking by blocks" means setting the parameter "by_row" to FALSE in the CREATE_CHUNKS_BY_ROWID procedure.

Regards,
Stew

Hi,
To make it simpler, i would like to tell you below details.
I have Table_1 and Table_2 of same structure.
Table_1 get around 2-3 Million data everyday and Table_2 have data in Billions.
Now i want to move Table_1 data to Table_2 everyday.
I am doing Insert into Table_2 and Delete from Table_1.
</>
dbms_parallel_execute.create_chunks_by_rowid(task_name => 'MY_TASK',
table_owner => SYS_CONTEXT('USERENV','SESSION_USER'),
table_name => 'TABLE_1',
by_row => true,
chunk_size => 10000);

dbms_parallel_execute.run_task(task_name => 'MY_TASK',
sql_stmt => 'BEGIN PR(:start_id, :end_id); END;',
language_flag => DBMS_SQL.NATIVE,
parallel_level => 20);

</>

Now the challenge is TABLE_1 is partitioned and it is creating chunks based on Table Partitions.
Now, when i simply do insert and delete it will take 3-4 hours as other processes are also there.
And, this is the reason i want to do it with DBMS_PARALLEL_EXECUTE.
Also, it will not create any data issues and data will not be duplicate in table_2 or data in table_1 will be dropped first and then deleted.
Please i want your help in finding another way.
What i think is i can try it with CHUNKS_BY_SQL which could decrease the number of Chunks.
And i don't have 100,000 CPU's.. At one time it will run (parallel_level => 20) 20 parallel jobs and as it will finish processing in one job it will pick next chunk.
And number of chunks monitored from DBA_PARALLEL_EXECUTE_CHUNKS.

insert into table2
  select * from table1 where...

alter table table1 (drop|truncate) partition ...

Hi,

> I have Table_1 and Table_2 of same structure.
> Table_1 get around 2-3 Million data everyday and Table_2 have data in Billions.
> Now i want to move Table_1 data to Table_2 everyday.

That does sound like a potential case for partition exchange. Requires some sophisticated setup. In particular table_2 must have a compatible partitioning structure to table_1 and the rows to move must reside in a partition (or multiples) of their own and not be mixed with rows that should not be moved. At least the system already uses partitioning so its available and some knowledge can be assumed.

regards,

Hi Chris,

I can't truncate the partition as there is other data in same partition which i don't want to delete.
So i have to use Delete.
And insert into table_2 i am doing but using Parallel Chunks.

As Table_2 is huge and Table_1 also have Millions rows to process.
So, it is taking huge amount of time in doing so.
And same action on same time is running on several sessions with different conditions in where clause.

Hi Chris,

Partitions are there for several other reasons, but here i want to delete some data.

Example. Partition done on Column A, and below is data.

A B
--------
A 1
A 2
A 3
A 4
A 5
A 6
A 7

Now in above data if i want to delete B<=5.
So i cant truncate the partition.

And i have noticed it is taking from view DBMS_PARALLEL_EXECUTE_EXTENTS and creating the number of chunks.
Is there any way we can reduce the number of Chunks.

hi, I also encountered the same problem. No matter how I set the two parameters of chunk_size and parallel_level, there are tens of thousands of chunks. Later I found that after collecting the statistics of the table, the chunks decreased as the chunk_size increased. When it increases to chunk_size=100000, the chunk has dropped from tens of thousands to more than 200. I don't know if this is the case for you, but I hope it can be helpful to you.