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
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Deeds, Dallas M [DEEDSD@xxxxxxxxxxxxxx]
Sent: 16 August 2016 21:41
To: oracle-l@xxxxxxxxxxxxx
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(@"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
