Re: Seeing more ORA-04031 errors in 12c

  • From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 18 Aug 2016 22:51:55 -0400

On 08/16/2016 05:54 PM, Jonathan Lewis wrote:


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
@jloracle
------------------------------------------------------------------------


This is a huge change from 11G, where setting dynamic_sampling meant to cardinality feedback. However, from what I see, this enormously complicated optimizer doesn't produce much better results than the much simpler optimizer in 11G, without most popular values and with only 254 endpoints in histograms. Hopefully, 12.2, which will be announced on OOW, will do a better job with the optimizer.
Regards


--
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 08-2016