ANBOB™

To avoid “STREAMS AQ: ENQUEUE BLOCKED ON LOW MEMORY” error, you must set the STREAMS_POOL_SIZE value returned by the result set of the following query.

select 'ALTER SYSTEM SET STREAMS_POOL_SIZE='||(max(to_number(trim(c.ksppstvl)))+67108864)||' SCOPE=SPFILE;'
 from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c 
where a.indx = b.indx and a.indx = c.indx 
and lower(a.ksppinm) in ('__streams_pool_size','streams_pool_size');

• PARALLEL_FORCE_LOCAL=TRUE

In a RAC environment, it can improve the speed of Data Pump API-based operations and avoid some Parallel DML bugs at the same time.

•  CLUSTER=N

In a RAC environment, it can improve the speed of Data Pump API-based operations.

• On partition table

You try to DataPump import (impdp) a partitioned table into an empty schema using PARALLEL > 1. There are DataPump Worker processes waiting for an exclusive table lock which is indicated by event ‘enq: TM – contention’.

By default, DataPump uses the APPEND hint to force direct loads of the data, since direct loads are faster than conventional loads. One of the reasons direct load is faster is that it locks the table from updates from other sessions. In our case, there are multiple import jobs trying to load data into the same table. When one job gets the lock to do a direct load into a table, all other import jobs are locked out from loading that table until the current job has finished the its current load.

DataPump import (impdp) internally uses a statement (obtained with event 10046) similar to:

INSERT /*+ APPEND PARALLEL("TAB_LP",1)+*/ INTO RELATIONAL("<SCHEMA_NAME>"."<TABLE_NAME>" NOT XMLTYPE) ("<COL1>", "<COL2>", "<COL3>")
   SELECT "<COL1>", "<COL2>", "<COL3>"
    FROM "ET$0095000D0001" KU$

A way to force real partition-level load in parallel (INSERT /*+ APPEND PARALLEL(“<TABLE_NAME>”,4)+*/ INTO RELATIONAL(“<SCHEMA_NAME>”.”<TABLE_NAME>” ) PARTITION(XXX) … SELECT … FROM ET$) was introduced in 12.2.0.1 via ER Bug 9590164 : TABLE_EXISTS_ACTION=TRUNCATE|APPEND ON PARTITIONED TABLE =>  SERIAL IMPORT.
– This enhancement was implemented in 12.2.0.1, with the parameter DATA_OPTIONS=TRUST_EXISTING_TABLE_PARTITIONS.

Note:

There is an enhancement in datapump in version 12.2.0.1 that creates a way to force real partition-level load in parallel

(See the following note:   DataPump Import (IMPDP) In Parallel Is Slow When Importing Partitioned Tables (Doc ID 1681444.1) )

ACCESS_METHOD=INSERT_AS_SELECT maybe faster.

Export all of the data in one DataPump job using PARALLEL. The DataPump import will still load one partition at a time, but if the partition partitions are large enough, they will be loaded in parallel.

  Use the DATA_OPTIONS=DISABLE_APPEND_HINT option on import. This will cause the loads to conventional load instead of direct load, but it will allow multiple import jobs to load the same table simultaneously.