0

One of my developers is trying to perform insert into a table by selecting from a table.

Below is the query that was run. The query takes extremely long time for execution (4hrs odd).

Number of records on the table where it is getting imported is 1.2 million.

Database - Oracle 12c on Linux with archivelog mode on.

insert  /*+ parallel(24) */  into data1.frido_dev select  /*+ parallel(24) */  *
from DATA_MAIN;

How can the insert of such bulk volumes be optimized? Can we use append? Will that be helpful?

I'm not good at tuning queries, would be great if someone helps me out here.

Improve this question
4
  • Do you really need to specify parallel for insert? each px thread gets its own extent for the segment. If you have indexes/FK to validate etc your performance will be penalized. Same with triggers on the table where you are inserting into, that would slow you down.
    – Raj
    Mar 23, 2017 at 11:36
  • Does the DB has so many CPU's that parallel(24) makes sense?
    – Wernfried Domscheit
    Mar 23, 2017 at 13:40
  • Did you try APPEND hint?
    – Wernfried Domscheit
    Mar 23, 2017 at 13:42
  • Be very careful specifying a number of parallel processes for a task. You can easily end up exceeding the maximum number of parallel query slaves allowed and then you will just end up with processes waiting for a slave to become available. (particularly as you have picked what appears to be a high degree)
    – BriteSponge
    Mar 23, 2017 at 15:15

1 Answer 1

Reset to default
3

Unless you have a special case, an INSERT with 1.2 million rows should be completed in seconds or minutes at most.

Yes, direct-path insert may significantly reduce execution time.

The above hint in INSERT has no effect without the necessary prerequisites.

Enable Parallel DML Mode

A DML statement can be parallelized only if you have explicitly enabled parallel DML in the session or in the SQL statement. To enable this mode in a session, run the following SQL statement:

ALTER SESSION ENABLE PARALLEL DML;

To enable parallel DML mode in a specific SQL statement, include the ENABLE_PARALLEL_DML SQL hint. For example:

INSERT /*+ ENABLE_PARALLEL_DML */ …

This mode is required because parallel DML and serial DML have different locking, transaction, and disk space requirements and parallel DML is disabled for a session by default.

When parallel DML is disabled, no DML is executed in parallel even if the PARALLEL hint is used.

Improve this answer
2
  • Thanks, but in your view what's the best way to do bulk insert in this case if the records are 10 million plus.
    – tesla747
    Mar 22, 2017 at 17:51
  • 1
    @tesla747 Parallel direct-path load is great. But first of all, I would investigate the cause for the slowness before tuning without a clue. For example use SQL trace. Maybe you have some Basicfile LOBs that completely disable parallel DML and slow down reading, or the table is changing constanly, and because of that, the database has to use undo to reconstruct a transactionally consistent set of blocks, and for that it falls back to single block I/O from multi block I/O. But we do not know this from the information you shared with us.
    – Balazs Papp
    Mar 22, 2017 at 21:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.