How to imporve performace of an insert when a table has so many indexes in Oracle?

Question

We have a table having about 100 indexes on it. So when I try to insert a high number of rows into this table, it takes too much time to perform the insert. I tried the PARALLEL and APPEND hints but did not help much.

Is there any other ways to improve the insert performance in such situations? (I don't want to disable and then enable the triggers)

Answer 1

Use the explain plan to ensure that you are using the append and parallel hints correctly - there are many ways for those hints can go wrong.

Here's an example of a good explain plan for large data warehouse statements:

create table test1(a number);

explain plan for
insert /*+ append parallel enable_parallel_dml */ into test1
select 1 from test1;

select * from table(dbms_xplan.display);


Plan hash value: 1209398148
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |          |     1 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                    |          |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)              | :TQ10000 |     1 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)| TEST1    |       |            |          |  Q1,00 | PCWP |            |
|   4 |     OPTIMIZER STATISTICS GATHERING |          |     1 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR             |          |     1 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL            | TEST1    |     1 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
 
   0 -  STATEMENT
         U -  parallel
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - automatic DOP: Computed Degree of Parallelism is 2

For good parallel direct-path performance, these are the main things to look for in the explain plan:

1. Ensure direct path writes are used. "LOAD AS SELECT" means the append hint worked, "INSERT CONVENTIONAL" means the hint was not used. There are many possible reasons why direct path cannot be used, the most common of which is that parallel DML is not enabled, which is what the third hint does. (Before 12c, you had to run alter session enable parallel dml.)
2. Ensure parallelism is used for both reads and writes. There should be a "PX" operation both before and after the "LOAD AS SELECT". If there's not an operation before, then the writing is not done in parallel.
3. Ensure the degree of parallelism is correct. The explain plan will tell you the requested degree of parallelism. The DOP is hard to get right and is affected by many factors. If your DOP seems wrong, use this checklist to look for possible problems.

If you're still having problems, use a SQL monitoring report to find information about the actual execution plan, rows, times, and wait events. Generating the report is usually as easy as select dbms_sqltune.report_sql_monitor('your SQL_ID') from dual. If you post the results here someone can probably find a way to improve the performance.