Improve Performance of Multi Thread Inserts

I have created a PL/SQL procedure that splits up a calculation workload into 8 different threads. Each thread loops over a subset some parameters, queries an additional table for input based on these parameters, and finally does some simple math using these values. This results in some output which I insert into an output table using INSERT INTO with an APPEND hint. For each iteration in these threads, the resulting dataset to be inserted is around 60 rows by 10 columns of mostly FLOAT(70) level data.

Looking at ALL_SCHEDULER_JOB_RUN_DETAILS I noticed the CPU time and Run duration were ~5 min and ~25 min respectively for each thread.

I have a few questions here:

1. Is this ratio of Run time to CPU time typical given the operations I am doing?
2. Is there a cleaner/ more efficient way to insert into the output table? Is it possible that there is some sort of collision happening when each thread attempts to append?
3. Would it be faster to have each thread insert into its own output table and then UNION the result into one table at the end? (not worried about lack of space)

Happy to hear other suggestions!

*** Edit: ***

Given;

Each row in param table dictates a calculation that must be done using values from data table (there are also multiple calc to be done columns but will just use one for below example)

Param table

Data table

Output

• Data table cannot be mutated

Each iteration will give me the output that must be stored somewhere. Right now I use INSERT APPEND into my output table. Would it make more sense to let each thread have it's own staging table and then combine once all calculations have run?

First, to answer your specific question about append: you cannot have multiple sessions concurrently insert in direct path (append hint) into the same segment. Direct path takes out an exclusive lock on the segment which prevents any other session from performing DML on it. Therefore, if you want a multithreaded job, you must either (a) remove the append hint, or (b) use a different table for each thread and UNION ALL their results in your final query/extract, or (c) list partition the table by thread number and use extended partition naming to isolate the appropriate partition.

Example of (c) would be:

Thread #1: INSERT /*+ append */ INTO outputable PARTITION (thread_1)...

Thread #2: INSERT /*+ append */ INTO outputable PARTITION (thread_2)...

Where thread_1, thread_2, etc.. are the names of the partitions the table is created with.

If you do this, then each thread is taking an exclusive lock out on only the segment underlying the partition it is working with, not the other partitions. This allows for multiple sessions doing this work on the same logical table.

However, you said you are inserting only 60 rows... that is a very tiny amount, far too little to justify using direct path (append). If you continue working with such small number of rows, forget the above and just remove the append hint entirely. You are also wasting a lot of space as each append operation will allocate a minimum of one extent, and 60 rows will almost certainly not fill that extent. It also won't ever reuse space freed up by deletes so, if you use deletes rather truncates to clear the table, it will cause the table to grow in size out of control. I typically wouldn't even dream of using append until we're talking about at least tens of thousands of rows per insert, and even then only if I never remove data or it's a work table that I truncate on a regular basis.

Other considerations:

2. If you have overhead of connecting, querying control tables, etc... then why do only 60 rows in each one? Consider vastly increasing the quantity of rows each thread operates on, to improve the efficiency ratio of work done vs. overhead.

3. Consider whether you should be using PL/SQL at all. Many problems can be solved in pure SQL with much more efficiency than a PL/SQL-based solution. You may not need an output table at all. Just query your data and fetch to your client normally.

4. Before turning to multithreading solutions you write yourself, if you do need the CPU power of more than one session you should always try to achieve that with Oracle's native parallel query. With PDML (enable_parallel_dml hint) you can extend the parallelism to the insert operation itself. But your row counts are so small I don't even think that is appropriate. I don't start thinking about parallel anything until my row counts are in the hundreds of thousands or more.

5. Beyond what I've said above, perf problems could be caused by many things. You can't debug them by looking at all_scheduler_job_run_details. You'll need instead to use ASH or other tools that probe the wait interface to show you how your sessions are spending their time and on what. That will pinpoint the precise bottleneck. Until that information is examined, all folks can do is just guess at the issue.

My hunch is that removing the append hint is the answer to your question, but that even after having done so, you probably have a basic SQL tuning or functionality issue that if solved will eliminate the need entirely for this multithreading approach or even using PL/SQL at all.