RE: Seeing more ORA-04031 errors in 12c

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 16 Aug 2016 21:54:10 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901503387BA_at_exmbx05.thus.corp>

You get dynamic sampling in 12c as the third level response to adaptive execution plans.

First you get a plan, then you get a new plan using statistics feedback, then you get an SQL Plan Directive generated because of the in-memory statistics feedback (visible in v$sql_reoptimization_hints), and the directive(s) generally tells Oracle to do dymamic sampling.

Check dba_sql_plan_directives to see how many you've got. You may find you have to disable the adaptive feature and delete all the directives - the latter through the dbms_spd package.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Deeds, Dallas M [DEEDSD_at_nationwide.com] Sent: 16 August 2016 21:41
To: oracle-l_at_freelists.org
Subject: Seeing more ORA-04031 errors in 12c

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

Received on Tue Aug 16 2016 - 23:54:10 CEST