_disable_streams_pool_auto_tuning
Posted By admin On 07.05.20In his 22 years at Oracle, Tom Kyte started the Ask Tom question and answer site and was the site's sole proprietor for almost 15 years. In his spare time, he also wrote Expert Oracle Database Architecture (Apress, 2005, 2010, 2014) and Effective Oracle by Design (Oracle Press, 2003), among other books. Purpose: The 'undocumented' underscore parameters are parameters officially undocumented and unsupported by Oracle but that may be utilized, after receiving permission from Oracle Support to address specific issues such as bugs.
Apr 19, 2013 If you don’t use the automatic SGA (i.e. Set the sgatarget=0) - something I frequently do - and don’t use Streams, you probably have set the parameter streamspoolsize to 0 or not set it at all, since you reckon that the pool is used for Streams alone and therefore would be irrelevant in your environment wasting memory.
Synchronous I/O | |
Buffered I/O | asynch |
directIO |
Our daily expdp backup job became abnormally and it would last more than one day, while in the past it just took about 40 minutes.
From the v$session view I found the expdp related processes were waiting for event 'Streams AQ: enqueue blocked on low memory', and I also found the export speed was very very slow even the exported tables were empty.
Food cooking game download. ,764000000,62,'Tilting Point','Hello Fatma, we sincerely apologize for the delay. Our team is constantly working on bringing out an exciting game location for our players.
Searched and got below notes:
EXPDP And IMPDP Slow Performance In 11gR2 and 12cR1 And Waits On Streams AQ: Enqueue Blocked On Low Memory (Doc ID 1596645.1)
The key information I got was this: If the size of the streams_pool is being modified, then this can result in excessive waits for 'Streams AQ: enqueue blocked on low memory'.
Every occurrence for this event causes a 1 minutes delay.
And the suggested actions could be easily executed were:
- Explicitly set the streams_pool_size to a fixed (large enough) value, 150 Mb or 300 Mb if needed. We had 40G SGA, so I updated it to 500M.
- Alter system set '_disable_streams_pool_auto_tuning'=TRUE. We modified it and when I checked and found the default value was TRUE, so in fact no need to modify it.
Three patches were mentioned and I found all of them already were included in the latest PSU, so maybe I just need to restart the instance to observe the result.
I could not restart it as it is a production database, so I tried to find more and got below notes:
Datapump Expdp Or Impdp Slowdown Due To Frequent Waits On ”Streams AQ: Enqueue Blocked On Low Memory' (Doc ID 2386566.1)
Two important information:
- Select shrink_phase_knlasg from X$KNLASG; Will return 1 if the stream pool is shrinking and return 0 if such action is finished.
- Alter system set events 'immediate trace name mman_create_def_request level 6'. Will force to complete the stream pool shrink.
I run the statement in step 1 and did get value 1. I was surprised as we set the stream pool to 500M one day before and we still in the shrink action.
So I run statement in step 2 to complete it and I did begin to get value 0.
I checked the wait event after that and I could not find such wait event, so I guessed the issue should be fixed. I tailed the log of the expdp job and found it finished already!