Monday Nov 16, 2015

UPDATE: _rowsets_enabled in Oracle Database 12c

Last week I did post this entry with a strong recommendation to disable _rowsets_enabled in Oracle Database

Today I can give you an update, more insight information and better workarounds.

Credits go to our DWH and Optimizer people (thanks to Hermann, Angela, Nigel and Mohammed).

When is the problem happening?

When a hash join operation receives rowsets from its right input but then produces one row at a time as output. This explains why one of the bugs had as potential workaround hash_join_enabled=false (and please don't use this as a w/a!!!).

Different Workarounds 

  • Set
    event = "10055 trace name context forever, level 2097152"
    in your spfile. This will disable rowsets only for the specific situation where the problem happens.

    An important comment from Angela if you intend to set this event via an ALTER SESSION command:
    "Note that if the event is set with an alter session, it will not take effect unless the query or queries are recompiled, such as by changing the query text (by adding spaces or comments) or by flushing the shared pool."

  • Set
    in your spfile. But this will switch off the entire feature, not only the particular situation where the problem happens.

  • Apply the fix for
    (as of Nov 16, 2015 in regression testing and not available yet) as soon as it is available.

Thanks again to the Optimizer folks for their quick reaction!!!


Tuesday Oct 06, 2015

New in Oracle 12c: _optimizer_gather_stats_on_load

Received an email from Roy last night with some performance issues a customer in the US encountered recently during their upgrade testing

One issue the customer encountered has to do with tons of parallel slaves creating a massive noise on the system when they are doing a CTAS (Create Table As Select) - and the same thing happens with an IAS (Insert Append Select).

What caused this change?

In this case the behavior change is well documented, even though not linked to the responsible underscore parameter. 

Quoting from the first paper: 

Online statistics gathering

In Oracle Database 12c, online statistics gathering “piggybacks” statistics gather as part of a direct-path data loading operation such as, create table as select (CTAS) and insert as select (IAS) operations. Gathering statistics as part of the data loading operation, means no additional full data scan is required to have statistics available immediately after the data is loaded.

The parameter controlling this change is not mentioned:

  • _optimizer_gather_stats_on_load

The default is TRUE since Oracle - the parameter or functionality did not exist before Oracle Database 12c.

Things to Know

The online stats gathering for IAS can happen only if the object you are loading data into is empty. You'll recognize the feature when you see a line saying OPTIMIZER STATISTICS GATHERING in the execution plan. Additionally you'll find STATS_ON_LOAD in the NOTES column of below query:


Consider now that first of all immediate stats gathering may not be desired as it will mean additional load to the system. Even though the feature has been designed to generate as little load as possible it's still doing something in the background. Hence there may be situations where you'd like to switch it off, e.g. with a hint:

insert /*+append NO_GATHER_OPTIMIZER_STATISTICS*/ into MYTAB select ...

And how about the number of parallel slaves creating the statistics? 

There's no easy answer to this based on the information I have at the moment - but I'd highly recommend this article about parallel execution in Oracle:


Tuesday Jul 28, 2015

Optimizer Issue in Oracle "Reduce Group By"

Wrong Query Results BugDBAs biggest fears I'd guess are Optimizer Wrong Query Results bugs as usually the optimizer does not write a message into the alert.log saying "Sorry, I was in a bad mood today ..."

The Oracle Database Optimizer is a complex piece - and in Oracle 12c it delivers great performance results. Plus (my personal experience when you know what to do) it is more predictable which I like a lot when changing databases from one to another release. But due to its complexity sometimes we see issues - and sometimes it is necessary to switch off tiny little pieces until a fix is available.

Roy just came across this one - and we believe it's worth to tell you about it. Again, our intention is only to prevent issues when upgrading or migrating to Oracle Database


An outer join query with a bind variable and a group by clause can produce wrong results in some cases.


 If all of the following match, you may be hitting this bug:
 - two or more subquery views are outer-joined on column C1
 - column C1 is specified on select list of top-most query block
 - column C1 is filtered on a bind value


 create table test1(c1 number(5),c2 varchar2(16));
 insert into test1 values(1,'3');

 variable num1 number
 execute :num1 :=1;

 -- Following query retuns wrong result(NULL), this should return 1.

 select V.c1 from
  (SELECT c1 FROM test1 GROUP BY c1) V,
  (SELECT c1 FROM test1 WHERE c2 = '1' GROUP BY c1) V2
 where  V.c1 = :num1
    and V.c1 = V2.c1(+);


alter session set "_optimizer_reduce_groupby_key" = false;

Please don't use the workaround:
alter session set optimizer_features_enable='';
as this will switch off other good optimizer features working very well.

More information:

See MOS Note:20634449.8 describing:
Bug 20634449 - Wrong results from OUTER JOIN with a bind variable and a GROUP BY clause in

As far as I can see there are no interim (one-off/single) patches available right now. 


Wednesday Oct 19, 2011

It's always the Optimizer, isn't it?! - Part 1

Wouldn't you agree?

My colleagues from the Optimizer Development might forgive me but a lot of people would say: "Everything went fine after upgrade - except for those tiny 3 queries!". And I know, you have tested a lot. I'd like to tell you about a few issues we've seen post upgrade with

  1. You have captured your plans in Oracle 10.2 pre-upgrade.
  2. You have moved these plans from your SQL Tuning Set into the SQL Plan Baseline in Oracle 11.2.
  3. But the optimizer doesn't pick them although you have:
    (a) ACCEPTED and
    (b) FIXED them.


It might be worth a try to set this underscore parameter and test again:


What does _optimizer_use_feedback mean?
You'd find some detailed information in our Upgrade Companion (MOS Note:785351.1):

Cardinality Feedback (Oracle Database 10g to 11g Change)
Cardinality feedback is an enhancement made to the Optimizer in Oracle Database 11g Release
2. Cardinality feedback compares cardinality estimates used to derive the plan with the actual
cardinality seen in the first execute. If the estimate is 2X off, the cursor is marked for hard parse
next time around. The cardinality information seen at first execute is supplied at the next hard
parse thus allowing the Optimizer an opportunity to improve on the plan now that it knows more
about the actual cardinality seen in the query. If cardinality feedback is used, it is displayed in the
section of the execution plan. Cardinality feedback works for predicates on tables, indexes and
group by clauses. It does not help for cardinality mis-estimates for joins. Feedback is not
persistent on disk, it resides in memory only. The Optimizer will need to "relearn" something if the
database is shutdown and restarted. (Related to _optimizer_use_feedback parameter.)

Besides that there were known issue in (Bug 8608703 - SubOptimal Execution Plan created by Cardinality Feedback (Doc ID 8608703.8) - Bug 9342979 - Suboptimal plan change with cardinatilty feedback (Doc ID 9342979.8)) - but they seem to be fixed with patch set and above.

While doing some research in our support portal (MOS) I accidentally found these interesting notes:

To be continued ...

Please don't take this as a general recommendation to set this underscore and switch off the functionality as this isn't a general recommendation!!! Be always careful with underscore/hidden parameters and always check back with Oracle Support - in most cases underscores slow down the upgrade and using underscores from previous releases has often a bad impact on performance!


Mike Dietrich - Oracle Mike Dietrich
Master Product Manager - Database Upgrade & Migrations - Oracle

Based in Germany. Interlink between customers/partners and the Upgrade Development. Running workshops between Arctic and Antartica. Assisting customers in their reference projects onsite and remotely. Connect via:

- -


« November 2015
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Viewlets and Videos
Workshop Map
x Oracle related Tech Blogs
This week on my Rega & Pono
Upgrade Reference Papers