While helping with migration of one Retail production system, I need to export certain schemas by using Data Pump utility.
As the database is pretty small (about 3TB), I’ve just executed expdp command and expected it will finish within 2 to 3 hours.
When I checked in the evening, on my surprise, Data Pump job was still running.
After monitoring the Data Pump session, soon I’ve realized what is going on.
Almost 94% of time was spent on the “Streams AQ: enqueue blocked on low memory” wait event.
After digging, Oracle MOS portal helped me in this case.
In database version 184.108.40.206, Oracle has implemented a new algorithm for modifying components of the SGA memory, which has issue while changing the size of the stream pool size.
You can see on the picture below how it look like.
To resolve the problem, there are several workarounds, but this is what is working in my case.
select value from v$parameter where name like '%stream%'; 0 select current_size/1024/1024 as MB from v$sga_dynamic_components where component = 'streams pool'; 128 MB
I’ve changed the minimum size of the stream pool to 256MB by executing the following command:
alter system set streams_pool_size=256m scope=both sid = '*';
This value is dependent of the number of parallel processes (parameter parallel=4 in my case).
If your database is very busy, you’ll have to do the following:
alter system set streams_pool_size=256m scope=spfile sid = '*';
and restart the database.
If you’ll still have the same wait event even after increasing minimum amount of the stream pool size, you’ll have to execute the following command:
alter system set "_disable_streams_pool_auto_tuning"=TRUE scope=spfile sid = '*';
and bounce the database.
After increasing the value and disabling auto tuning, performance of the Data Pump were back to normal.
The same database export that I have to kill after 24 hours, now takes only 16.5 minutes with parallel degree of 4.