CODEWITHSUNDEEP
oracle-databaseprocedurepartitioning
Sparsh
Sparsh

Reputation: 39

Oracle partition

I have one table which has 100 partitions, And I have procedure which reads the data partition wise. Suppose

P0 is 1 partition if I execute procedure 

 exec xyz('P0');

it takes 3 minute to execute for 500k rows.

But when I create 100 sessions and run parallely something like in different different sessions

exec xyz('P1');
exec xyz('P2');
exec xyz('P3');

It takes 1 hr for 50M rows can anyone explain why its happening.

Upvotes: 1

Views: 72

Answers (1)

Lajos Arpad
Lajos Arpad

Reputation: 76591

As far as I understand, your expectation is that the time needed for 100 partitions would be 100 * the time needed for a single partition, which is 100 * 3 minutes = 300 minutes = 5 hours. Instead of that, the whole task takes only 1 hour and 50 minutes.

The first question is whether the size of the partitions is equal. Maybe there are small partitions, which could explain the behavior you just described.

Also, the execution time on a given partition highly depends on what your stored procedure actually doing. For instance it might contain large selections, filling a lot of data into memory (which takes a lot of time) in some partitions, while the other partitions are executing it in a much quicker fashion, as records are filtered out before writing their content into memory.

Also, the stored procedure might contain write operations (insert, update, delete), which are very time consuming, especially if they affect many records. There is a high chance that the affected records' distribution among your partitions is uneven and therefore the execution time in different partitions might be different.

For example, if you have roughly 500k records in each partition and your stored procedure selects records by a criteria and does a given update for the items matching the criteria, then there is a high chance that the number of found and affected records will be different in the different partitions.

Or the partitions are distributed among servers with different lags or different properties, which affects the total time. Or the servers are doing some other jobs as well, which will increase execution time in a pseudo-random manner.

Upvotes: 1

Related Questions