Oracle Performance Storyteller MERGE

Interpreting Parallel MERGE Statement

This post is inspired by one of my colleagues, who had small difficulty while interpreting parallel MERGE execution plan.

This is the step-by-step explanation I gave him.

1. Create tables.

2. Would this MERGE statement run in parallel?

The answer is NO. As you notice from the operation #2, the MERGE part itself runs in serial. It is SELECT part that runs in parallel.

3. The reason would be parallel dml not enabled. Let’s see what happens if we have parallel dml enabled.

Still, the MERGE part runs serially.

4. Maybe parallel merge needs more specific HINT, which is quite natural because the MERGE statement has two tables involved. Let me make the PARALLE hint more specific.

Now, the MERGE part really runs in parallel!

5. One more thing you should be careful about. Let me create an index on the target table t1 and see what happens on the plan.

Now we have a new operation called INDEX MAINTENANCE and need one more table queue(TQ10004) to parallelize index maintenance itself. But why do we have index maintenance here?

• Parallel MERGE is composed of parallel update and parallel insert.
• Parallel insert is direct path insert.
• Indexes need to be maintained after direct path insert.

Oracle needs to maintain the indexes after direct path insert completes. This is controlled by _idl_conventional_index_maintenance hidden parameter, which you’d never have a chance to modify.

The INDEX MAINTENANCE operation does not disappear even when the index is disabled, but I believe that Oracle would skip the maintenance for the disabled indexes in runtime.