Re: Seeing more ORA-04031 errors in 12c

Dallas,

Keep in mind that the shared_pool is not a single entity. It contains lots of caches, queues, buffers, etc. It might be helpful to find out where most of the space is being consumed to narrow down what is causing the problem. Chances are something is going to stick out.

select name, round(bytes/1024/1024) mb from v$sgastat where pool = 'shared pool' order by mb;

Seth Miller

On Tue, Aug 16, 2016 at 3:41 PM, 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