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?
