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?
Here are the results I got when optimizer_dynamic_sampling = 2:
SQL> select count(*), SUBSTR(sql_text,1,180)
2 from v$sql
3 group by SUBSTR(sql_text,1,180)
4 having count(*) > 1500
5 order by count(*) desc;
COUNT(*) SUBSTR(SQL_TEXT,1,180)
----------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
14141 SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune
no_monitoring optimizer_features_enable(default) no_parallel
result_cache(snapshot=3600) */ SUM(C1) FROM (SELECT /*+ qb_name
12263 SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune
no_monitoring optimizer_features_enable(default) no_parallel
result_cache(snapshot=3600) OPT_ESTIMATE(@"innerQuery", TABLE,
1915 SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune
no_monitoring optimizer_features_enable(default) no_parallel
result_cache(snapshot=3600) OPT_ESTIMATE(@"innerQuery", INDEX_F
