Insert query optimisation – Oracle

index-tuningoracleoracle-11g-r2oracle-12cperformanceperformance-tuning

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.

Best Answer

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.

Related Solutions

Tuning read-intensive queries – Oracle

Using a different blocksize would probably be the last thing to try, and only if there is a good reason to do so. A good reason might include

The majority of queries do full table scans that happen to retrieve several blocks from disk at once
The table includes large objects (LOBs) that are bigger than 8k

But in general, I would say a larger blocksize won't help for most environments; and for those that need it, it would be a minor improvement. Derek's comment above has good things to look at; if you post the query and the explain plan, we can provide better advice.

Based on the query and explain plan you provided, you may want to try a composite index; the order of the columns below is assuming there are fewer unique values for MATCH_CODE_DATE_OF_BIRTH than for MATCH_CODE_INDIVIDUAL:

create index IXE6_MATCH_DATE_IND 
on FSC_ENTITY_WATCH_LIST_DIM (
    MATCH_CODE_DATE_OF_BIRTH,
    MATCH_CODE_INDIVIDUAL
    );

Also, are all the bind variables always populated with a value, or are sometimes some of them null? IE: if :V9 and :V10 are often null, you might be able to do something to your query like

...
OR
(
  :v9 is not null and :v10 is not null and
  "MATCH_CODE_DATE_OF_BIRTH"  = :V9
        AND "MATCH_CODE_INDIVIDUAL" = :V10
)

Oracle – Global Statistics Preference NO_INVALIDATE

Simply put, they are the parsed SQL statements in the shared pool related to the table. When they are invalidated, they need to be hard parsed again the next time they run.

A typical use case for this: the database generates a suboptimal execution plan because of stale statistics on a table. The database fails to recognize this mistake on subsequent executions, but you notice it and decide to gather statistics. With NO_INVALIDATE => true, this will not have any effect on the SQL with poor performance, the database will continue to use the old, suboptimal plan. That is why you should gather statistics with the NO_INVALIDATE => false option in such cases.

Best Answer

Related Solutions

Tuning read-intensive queries – Oracle

Oracle – Global Statistics Preference NO_INVALIDATE

Related Question