SYMPTOMS
- The import with DataPump of tables into an Oracle11g Release 11.2.0.4 database takes almost 45 minutes, while the same import into Oracle10g Release 10.2.0.4 only take 2 minutes.
The slow Import Data Pump performance problem might also reproduce in other Oracle11g Releases (like: 11.2.0.3) or Oracle12c Release 12.1.0.1. The problem also reproduces when running an Export Data Pump job from such a release database.
PROBLEM:
--------
1. Clear description of the problem encountered
Any attempt to reduce the current size of streams pools hangs
for a while (up to several minutes)
SQL> select * from v$sgastat where pool ='streams pool';
POOL NAME BYTES
------------ -------------------------- ----------
streams pool free memory 16777216
SQL> alter system set streams_pool_size=15M scope=memory;
Even if the streams is free and not allocated the session
is blocked:
"Streams AQ: enqueue blocked on low memory"
After the wait is finished (confirmed by "System altered") the size
of streams pool has not changed.
SQL> alter system set streams_pool_size=15M scope=memory;
System altered.
SQL> select * from v$sgastat where pool ='streams pool';
POOL NAME BYTES
------------ -------------------------- ----------
streams pool free memory 16777216
2. Pertinent configuration information (MTS/OPS/distributed/etc)
No special configuration.
3. Indication of the frequency and predictability of the problem
Permanently repdroducible.
4. Sequence of events leading to the problem
SQL> alter system set streams_pool_size={new size} scope=memory;
5. Technical impact on the customer. Include persistent after effects.
Memory can't be released to provide more free memory to other
pools such as shared pool.
DIAGNOSTIC ANALYSIS:
--------------------
Session #1:
-----------
-- Set client info
exec DBMS_APPLICATION_INFO.SET_CLIENT_INFO ('TC1');
-- Check current size of streams pool
select * from v$sgastat where pool ='streams pool';
POOL NAME BYTES
------------ -------------------------- ----------
streams pool free memory 16777216
-- Now Reduce streams pool
alter system set streams_pool_size=15M scope=memory;
==> Session #1 hangs.
Session #2:
-----------
set linesize 150
col spid form a10
select p.spid, s.program, s.event
from v$process p, v$session s
where s.PADDR = p.ADDR
and sid in (select sid from v$session where client_info='TC1');
SPID PROGRAM EVENT
----- ------------------------------
-----------------------------------------
4878 sqlplus@vm-rschorr (TNS V1-V3) Streams AQ: enqueue blocked on low
memory
SQL> oradebug setospid 4878
Oracle pid: 29, Unix process pid: 4878, image: oracle@vm-rschorr (TNS V1-V3)
SQL> oradebug current_sql
<none>
SQL> oradebug short_stack
ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1876<-sspuser()+112<-__sighandler()<-se
1<-knla_set_shrink_start()+159<-ksmc_streams_pool_dyn_cbk()+106<-kspptval()+28
66<-kspset0()+2528<-kkyasy()+15733<-kksExecuteCommand()+2453<-opiexe()+33474<-
kpoal8()+2219<-opiodr()+916<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiod
r()+916<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+252<-main(
)+201<-__libc_start_main()+253<-_start()+36
SQL> select * from v$sgastat where pool ='streams pool';
POOL NAME BYTES
------------ -------------------------- ----------
streams pool free memory 16777216
col "Hidden Parameter" form a30
col "Parameter Value" form a20
select ksppinm as "Hidden Parameter",
ksppstvl as "Parameter Value"
from x$ksppi join x$ksppcv using (indx)
where regexp_like(ksppinm, '__streams_pool_size');
Hidden Parameter Parameter Value
------------------------------ --------------------
__streams_pool_size 16777216
SUMMARY
-------
The streams pool size does not changed.
WORKAROUND:
-----------
None.
RELATED BUGS:
-------------
-
REPRODUCIBILITY:
----------------
Reproducible on 11.2.0.3. and 12.1.0.1.0
TEST CASE:
----------
STACK TRACE:
------------
-
SUPPORTING INFORMATION:
-----------------------
-
24 HOUR CONTACT INFORMATION FOR P1 BUGS:
----------------------------------------
-
DIAL-IN INFORMATION:
--------------------
-
IMPACT DATE:
------------
-
*** 08/27/13 01:00 am ***
TEST CASE
--------------
- 2 sqlplus sessions recommended:
Session #1:
Tries to reduce the current size of streams pool.
Session #2
Monitors session#1.
This will confirm: "Streams AQ: enqueue blocked on low memory"
SESSION #1:
-----------
connect / as sysdba
-- Set client info
exec DBMS_APPLICATION_INFO.SET_CLIENT_INFO ('TC1');
-- Check current size of streams pool
select * from v$sgastat where pool ='streams pool';
-- Now Reduce streams pool
alter system set streams_pool_size=15M scope=memory;
==> Session #1 hangs.
SESSION #2:
-----------
connect / as sysdba
-- Checking session state (while session #1 hangs)
set linesize 150
col spid form a10
select p.spid, s.program, s.event
from v$process p, v$session s
where s.PADDR = p.ADDR
and sid in (select sid from v$session where client_info='TC1');
==> wait event: "Streams AQ: enqueue blocked on low memory"
-- Control the streams pool size
select * from v$sgastat where pool ='streams pool';
==> Streams Pool size does not change.
*** 08/27/13 01:04 am *** (CHG: Sta->16)
*** 08/27/13 01:05 am ***
*** 08/27/13 01:05 am ***
*** 08/28/13 05:49 pm ***
*** 08/29/13 08:28 am ***
*** 10/18/13 03:09 am ***
*** 10/18/13 04:06 pm *** (CHG: Sta->11)
*** 10/18/13 04:06 pm ***
*** 10/18/13 04:07 pm ***
*** 10/18/13 04:07 pm ***
*** 11/26/13 12:36 am ***
*** 11/26/13 06:57 am ***
*** 12/10/13 12:37 am ***
*** 12/18/13 02:37 am ***
*** 12/18/13 08:30 am ***
*** 02/06/14 04:02 pm ***
*** 02/15/14 09:36 am ***
*** 03/14/14 07:29 am ***
*** 03/16/14 06:30 pm ***
*** 04/14/14 02:48 am ***
*** 04/23/14 01:46 pm ***
*** 04/24/14 03:25 am ***
*** 04/25/14 12:08 am ***
*** 05/15/14 11:11 pm ***
*** 06/18/14 12:13 pm ***
*** 07/22/14 06:04 pm ***
*** 07/29/14 04:10 am ***
*** 07/29/14 06:26 am ***
*** 07/31/14 07:27 am ***
*** 07/31/14 10:12 am ***
*** 07/31/14 10:13 am ***
*** 08/03/14 11:40 pm ***
*** 08/04/14 02:39 am ***
*** 08/13/14 03:42 am ***
*** 08/13/14 03:42 am ***
*** 08/13/14 06:13 am ***
*** 08/13/14 12:19 pm ***
*** 08/19/14 02:32 am ***
*** 09/03/14 03:04 am ***
*** 09/08/14 01:15 am ***
*** 09/11/14 11:06 am ***
*** 09/24/14 03:02 am ***
*** 09/25/14 07:43 am ***
*** 10/02/14 07:17 am ***
*** 10/15/14 09:05 pm ***
*** 10/23/14 06:43 am ***
*** 11/03/14 12:20 am ***
*** 11/05/14 01:44 am ***
*** 11/05/14 08:30 am ***
*** 11/05/14 08:44 am ***
SQL> select * from v$sgastat where pool ='streams pool';
POOL NAME BYTES
------------ -------------------------- ----------
streams pool free memory 159383552
SQL> set timing on
SQL> set time on
08:35:30 SQL> alter system set streams_pool_size=15M scope=memory;
System altered.
Elapsed: 00:05:00.40
08:40:49 SQL> select * from v$sgastat where pool ='streams pool';
POOL NAME BYTES
------------ -------------------------- ----------
streams pool free memory 16777216
Elapsed: 00:00:00.01
08:42:17 SQL> show parameter stream
NAME TYPE VALUE
----------------------------- --------------- ---------------------
streams_pool_size big integer 16M
*** 11/05/14 08:46 am ***
*** 11/06/14 12:37 am ***
*** 11/06/14 12:44 am ***
*** 11/06/14 04:44 am ***
*** 11/11/14 11:51 am ***
*** 11/12/14 12:36 am ***
*** 11/13/14 01:05 pm ***
*** 11/28/14 06:14 am ***
*** 12/01/14 01:49 pm ***
*** 12/03/14 01:10 am ***
*** 12/03/14 02:18 am ***
*** 12/10/14 07:22 am ***
*** 12/13/14 09:26 pm ***
*** 12/29/14 06:29 am ***
*** 01/06/15 06:43 am ***
*** 01/07/15 08:31 am ***
*** 01/08/15 06:25 am ***
*** 01/13/15 03:16 pm ***
*** 01/14/15 09:57 am ***
*** 01/14/15 11:30 am ***
*** 01/15/15 04:14 am ***
*** 01/15/15 06:28 am ***
*** 01/15/15 08:59 pm ***
*** 01/28/15 02:46 pm ***
*** 01/29/15 10:55 am ***
*** 01/30/15 06:28 am ***
*** 01/30/15 09:27 am ***
*** 01/30/15 11:51 am ***
*** 02/02/15 07:22 am *** ESCALATED
*** 02/02/15 08:20 am ***
*** 02/02/15 06:54 pm ***
*** 02/02/15 06:55 pm ***
*** 02/02/15 06:55 pm ***
*** 02/03/15 09:49 am ***
*** 02/03/15 09:51 am ***
*** 02/04/15 05:21 am ***
*** 02/11/15 06:16 am ***
*** 02/12/15 08:19 am ***
*** 02/16/15 12:02 am ***
*** 02/16/15 12:34 am ***
*** 02/19/15 03:08 pm ***
*** 02/20/15 01:42 am ***
*** 02/24/15 04:07 am ***
*** 02/25/15 03:48 am ***
*** 02/25/15 06:00 am ***
*** 02/27/15 02:16 am ***
*** 03/04/15 02:06 pm ***
*** 03/06/15 04:00 am ***
*** 03/06/15 04:37 am ***
*** 03/06/15 10:05 am ***
*** 03/09/15 11:44 pm ***
*** 03/10/15 11:48 am ***
*** 03/10/15 11:48 am ***
RELEASE NOTES:
]] Decreasing the memory size results in stalling
*** 03/10/15 11:48 am *** (ADD: Impact/Symptom->HANG - SINGLE ASM PR...)
*** 03/10/15 11:48 am ***
SOLUTION
1. As a temporary workaround, you can restart the database, and re-run the Data Pump job. The problem usually reproduces again after some database uptime.
- OR -
2. For a more permanent workaround, explicitly set the streams_pool_size to a fixed (large enough) value, e.g. 150 Mb (or 300 Mb if needed) that will be used as a minimum value, e.g.:
CONNECT / as sysdba
ALTER SYSTEM SET streams_pool_size=150m SCOPE=both;
And re-run the import or Export or Import Data Pump job.
If you cannot modify the STREAMS_POOL_SIZE dynamically, then you need to set the value in the spfile, and restart the database. Note that you may have to increase your SGA_TARGET or MEMORY_TARGET also.
CONNECT / as sysdba
ALTER SYSTEM SET streams_pool_size=150m SCOPE=spfile;
SHUTDOWN IMMEDIATE
STARTUP
NOTE:
Instead of starting a new job, you can suspend the job and resume the same job after setting streams pool, restart of instance and setting the under score parameter.
This will save time when facing the issue in the middle of a Datapump operation.
-- OR --
3. Apply interim Patch 17365043 if available for your platform and Oracle version, and re-run the Export Data Pump job.
NOTE:
If the problem is not fixed after implementing one of the above solutions, a fix for unpublished Bug 24560906 must be also installed before reporting the issue to Oracle Support.
Possible solutions for unpublished Bug 24560906 are:
1. In addition to setting a minimum value for STREAMS_POOL_SIZE to avoid Bug 17365043, set next parameter as a workaround for unpublished Bug 24560906:
alter system set "_disable_streams_pool_auto_tuning"=TRUE;
SHUTDOWN IMMEDIATE
STARTUP
-- OR --
2. Apply interim Patch 24560906 if available for your platform and Oracle version, and re-run the Export Data Pump job.