Optimizer

Is it always the Optimizer? Should you disable Group By Elimination in 12c?

I wouldn’t say it’s always the optimizer – but sometimes one or two tiny little things are broken making it necessary to turn off new functionality for a while.

Please don’t misinterpret this posting!
As far as I see (and I really do work with customers!) I’d state the Oracle Database 12.1.0.2 Optimizer is more stable, faster and predictable compared to 11.2.0.x. Our Optimizer Architects and Developers have done a great job. But with all the complexity involved sometimes it takes a few fixes or incarnations until a great new feature really matures. The Group-By-Elimination feature in Oracle Database …

Continue reading...

Avoid Poor Performance and Wrong Results in 11.2.0.4

One of the best notes in MOS got refreshed last night for Oracle Database 11.2.0.4. It has not only information about performance bugs but also about the worst and meanest category of issues, wrong query result bugs. Those are the ones I call the “sleeping beauties” as you as a DBA usually won’t recognize them. It is recognized at the application level when the result of a report is incorrect or instead of 4 rows just 3 rows get delivered in the output. Therefore you should be highly aware of those and add this note to your

Continue reading...

Why you shouldn’t set OPTIMIZER_FEATURES_ENABLE

Roy today answered an interesting question on one of our internal mailing lists. And this reminds me to pick up that piece of information as we see this quite often on customer sites, especially after upgrades or migrations. People set OPTIMIZER_FEATURES_ENABLE (OFE) to revert the optimizer’s behaviour to another pre-current release. That’s what a lot of people think this parameter does.

But in fact this is not true. Even though our documentation states it:

OPTIMIZER_FEATURES_ENABLE acts as an umbrella parameter for enabling a series of optimizer features based on an Oracle release number. For example, if you upgrade your database

Continue reading...

New Slides – and a discussion about Dictionary Statistics

First of all we have just upoaded a new version of the Upgrade and Migration Workshop slides with some added information. So please feel free to download them from here.The slides have one new interesting information which lead to a discussion I’ve had in the past days with a very large customer regarding their upgrades – and internally on the mailing list targeting an EBS database upgrade from Oracle 10.2 to Oracle 11.2.

Why are we creating dictionary statistics during upgrade?

I’d believe this forced dictionary statistics creation got introduced with the desupport of the Rule Based Optimizer in …

Continue reading...

Wrong statistics in AUX_STATS$ might puzzle the optimizer

We do recommend the creation of System Statistics under certain circumstances for quite a long time. Since Oracle 9i the optimizer works with a CPU and IO cost based model. And in order to give the optimizer some knowledge about the IO subsystem’s performance and throughput – once System Statistics are collected – they’ll get stored in AUX_STATS$. For this purpose in the old Oracle 9i days some default values had been defined – and you’ll still find those defaults in Oracle Database 11g Release 2 in AUX_STATS$. But these old values don’t reflect the performance …

Continue reading...

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 11.2.0.2.

  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.

Why?

It …

Continue reading...

Optimizer patch recommendation for Oracle 11.1.0.7

Thanks to Thomas Kempkens from Oracle’s ACS Support who brought this to my attention:

This will just apply to 11.1.0.6 and 11.1.0.7 customers:
It is possible to get an ORA-979 for a query containing a subquery with an aggregate and no group by.
This issue is known as bug7189491 (non-public) – there are one-offs (single patches) available on top of 11.1.0.7 for Linux (32/64bit) and Solaris.
If you’ll still get an ORA-979 then you might set _optimizer_group_by_placement to false

You’ll find further information in Note:8260094…

Continue reading...