Re: Seeing more ORA-04031 errors in 12c

Yes, we have been through a somewhat similar process to you. For our case we found around 80% of queries in shared pool were related to Dynamic Sampling.
In our case, and maybe worth checking on your side, we have some applications not using bind variables.
To confirm this suggest to query v$sql grouping by plan_hash_value. In our case even setting _optimizer_ads_use_result_cache didn't resolve contention on results cache latch; from my testing there is additional lookup on AWR data for sql id that still uses results cache and latch. We have disabled ADS at schema level for those applications not using bind variables.
P

On 17 August 2016 at 04:41, Deeds, Dallas M <DEEDSD_at_nationwide.com> wrote:

> 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(_at_"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(_at_"innerQuery", INDEX_F
>
>
>

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