Re: Seeing more ORA-04031 errors in 12c

Hi Dallas,
in addition to what Jonathan said.

Please do not set optimizer_dynamic_sampling=0, if you want to disable dynamic sampling due to SQL Plan directives (by the way you can see that these queries are related to SQL Plan directives due to "result_cache(snapshot=3600)" hint).

Franck Pachot has written a nice summary how to disable each adaptive feature on a more granular level: http://blog.dbi-services.com/sql-plan-directive-disabling-usage-and-column-groups/

FYI: The shared pool structure has also changed with 12c to avoid unnecessary ORA-04031 errors. For more details please check MOS ID #1675470.1 or dump heap 2.

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher Homepage: http://www.soocs.de
Twitter: _at_OracleSK

> "Deeds, Dallas M" <DEEDSD_at_nationwide.com> hat am 16. August 2016 um 22:41 geschrieben:
>
> I have several databases that we have rehosted from 11.2 to 12c (12.1.0.2.2 through the current PSU) on Redhat Linux suddenly start getting 4031
> errors. We have increased SGA size (nearly doubling, in some cases, to 8 GB) and still get the errors, they just take longer to show up. The
> shared_pool continues to want to grow. Nothing else had changed - just created a new 12c database, imported all of the data, and pointed the app at
> the new database.
>
> I have noticed duplicate SQL statements and found tons of queries with /* DS_SVC */ /*+ dynamic_sampling… in the query text. By tons, I mean
> 15,000 in one instance, 29,000 of them in another. I read a Pythian blog by Vyacheslav Rasskazov; he encountered more aggressive dynamic sampling
> in 12c. I think I am seeing another facet of it – our queries don’t use parallel query like his did.
>
> Suspecting that all of these statements were filling up the shared_pool, I used the sledgehammer approach (since none of our app queries appear to
> rely on dynamic sampling): I set optimizer_dynamic_sampling = 0 (from the default setting of 2). The problem appears to be resolved – no more
> DS_SVC queries flooding the shared_pool, and no more ORA-04031 errors. So far, at least.
>
> Is anyone else seeing extremely aggressive dynamic sampling/statistics in 12c, and is it causing ORA-04031 errors for you as well?

--
http://www.freelists.org/webpage/oracle-l