Many thanks to Jonathan, Stefan, and Franck. I will be investigating this in
much more detail, using the information you provided. We did have a serious
result cache issue in one of these systems, bad enough that Oracle Support had
us turn off _optimizer_ads_use_result_cache.
@Jonathan, the one database we are currently seeing things behaving the worst
has 4,707 SQL plan directives. The display_cursor output for one nasty
statement noted that 2 directives were used, so I will work on unwinding that.
Again, thanks for the insights!
-----Original Message-----
From: Stefan Koehler [mailto:contact@xxxxxxxx] ;
Sent: Wednesday, August 17, 2016 02:16
To: Deeds, Dallas M <DEEDSD@xxxxxxxxxxxxxx>; oracle-l@xxxxxxxxxxxxx
Subject: 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: @OracleSK
"Deeds, Dallas M" <DEEDSD@xxxxxxxxxxxxxx> 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?