Optimizer

Why is the fix for bug nnn not included in any RU?

Posted on September 8, 2026 by Optimizer Patch Recommendation 0

Christian asked this question today. Specifically, in his case for Bug 26749785 – PERF_DIAG: NEED TO HAVE MORE CONTROL IN DICTIONARY FOR AUTO_STATS_ADVISOR_TASK. Several of his customers suffer from this issue, and they repeatedly need to ask for backports or merge patches. I have seen this several times as well. Anyway, he is not only one who asked: Why is the fix for bug nnn not included in any RU?

Why is the fix for bug nnn not included in any RU?

Photo by Priscilla Du Preez 🇨🇦 on Unsplash

Inclusion criteria

In order to be able to include a bug fix into an MRP, CSPU or RU, it has to be …

Continue reading...

DBMS_OPTIM_BUNDLE – what it shows, and what oradiff lists

Posted on May 26, 2026 by Optimizer Oracle AI Database 26ai Oracle Database 19c ORAdiff Parameters 2

I blogged multiple times about DBMS_OPTIM_BUNDLE, the package to work with the Fix Control Persistence (FCP) framework in the database. And we also gave recommendations over the years that you need to test carefully when you are going to enable the fixes while patching. But it is a good practice do enable them when you do upgrade testing, and also when you start creating new databases in a new environment. Just make sure you keep the settings equal across your environments. The other day I received a question from Dennis asking about DBMS_OPTIM_BUNDLE – what it shows, and what oradiff …

Continue reading...

New interesting underscores for more than 255 _FIX_CONTROLs

Posted on April 22, 2025 by Differences Optimizer Parameters 6

I keep saying this – we have the best and most skilled customers. Christian Klose from my Franconian home region contacted me via LinkedIn and asked for some assistance with an SR. I confess, I replied way too late (information overflow). But the finding Christian had is great to know. There are new interesting underscores for more than 255 _FIX_CONTROLs.

Why don’t you see them in ORAdiff?

As usual, I went to ORAdiff and tried to find out anything about these new _fix_control parameters. But I couldn’t find any indication for what Christian …

Continue reading...

Oracle Optimizer Statistics Advisor in Oracle 19c

Posted on February 28, 2023 by Flaws and Pitfalls Optimizer Patch Recommendation 22

Christian Pfundtner pointed me to the fact that my old blog post about the Statistics Advisor from 2017 may not be up to date anymore. And he further pointed out that some of his clients still suffer from issues with the tool. Hence, let me refresh the topic with Oracle Optimizer Statistics Advisor in Oracle 19c. And thanks to Christian for the pointers.

 

Overview

At first, I won’t rewrite the entire blog post but instead focus on recent findings. Therefore, you may please want to read my previous blog post about the …

Continue reading...

DBMS_OPTIM_BUNDLE and Out-Of-Place Patching

Posted on August 12, 2021 by Flaws and Pitfalls Optimizer Patch Recommendation 10

Well, you see, this is most likely my special DBMS_OPTIM_BUNDLE week. And since I receive quite a number of questions, it may be good to discuss here about DBMS_OPTIM_BUNDLE and Out-Of-Place Patching?

DBMS_OPTIM_BUNDLE and Out-Of-Place Patching

Photo by Didssph on Unsplash

Out-of-place Patching

When you patch out-of-place with a new home – which is clearly our recommendation – you may see another tiny pitfall with DBMS_OPTIM_BUNDLE.

When you check DBA_DIRECTORIES, you will find two directories being related to DBMS_OPTIM_BUNDLE.

SQL> select directory_name, directory_path from dba_directories where directory_name like '%OPTIM%'

DIRECTORY_NAME	     DIRECTORY_PATH
-------------------- --------------------------------------------------
DBMS_OPTIM_LOGDIR    /u01/app/oracle/product/19/cfgtoollogs
DBMS_OPTIM_ADMINDIR  /u01/app/oracle/product/19/rdbms/admin

You see the “19” in the …

Continue reading...

DBMS_OPTIM_BUNDLE and disabling another fix

Posted on August 11, 2021 by Optimizer Patch Recommendation 8

Yes, DBMS_OPTIM_BUNDLE week it is. As I received so many good questions and input after my blog post whether you should enable _fix_controls with DBMS_OPTIM_BUNDLE or not, now I can easily fill the rest of the week with new content. Today it will be about a customer’s question regarding DBMS_OPTIM_BUNDLE and disabling another fix.

Disabling another fix

The setup is pretty simple. Stefan wanted to disable a different fix while he had all fixes from the most recent RU enabled on purpose. Sounds simple but has a dangerous pitfall.

This is what …

Continue reading...

Should you enable _fix_controls with DBMS_OPTIM_BUNDLE?

Posted on August 10, 2021 by Optimizer Patch Recommendation 21

Today, Paul mailed me asking a question we internally discussed many times in the past weeks: Should you enable _fix_controls with DBMS_OPTIM_BUNDLE? And there is no easy simple answer. But I can give a recommendation based on my experience from many projects.

DBMS_OPTIM_BUNDLE – a quick recap

Just in case you haven’t dealt with DBMS_OPTIM_BUNDLE recently, here is a quick recap.

With below procedure, you can list the available potentially behavior changing optimizer fixes in the current patch bundle – and you can turn them on or off at will:

SQL> set serveroutput on;
… Continue reading...

ORA-1422 and ORA-6512 from SYS.DBMS_STATS in Post Upgrade

Posted on May 4, 2021 by Optimizer Patch Recommendation 3

Ernst Leber sent me an email today. He hit an error at a customer upgrading to Oracle 19.9.0 on Exadata with AutoUpgrade. ORA-1422 and ORA-6512 from SYS.DBMS_STATS in Post Upgrade were signaled – and he better flashed back to the Guaranteed Restore Point. He found even a MOS note but still had questions. So time to blog about it in case you hit this error sequence as well.

What happens, and when does it happen?

At first, this is not an AutoUpgrade issue. You may see the same error stack in the post upgrade …

Continue reading...

You may need a one-off for DBMS_OPTIM_BUNDLE in 19.10.0

Posted on February 1, 2021 by Optimizer Patch Recommendation 22

It’s been a while since I blogged about the DBMS_OPTIM_BUNDLE – and to me, the package is still sort of a mystery. Ideally you can least and enable or disable behavior changing optimizer fixes per RU. And in Oracle 19.10.0, you will find a new surprise. So You may need a one-off for DBMS_OPTIM_BUNDLE in 19.10.0.

You may need a one-off for DBMS_OPTIM_BUNDLE in 19.10.0

Photo by Jonas Denil on Unsplash

Quick Recap

Just in case you haven’t dealt with DBMS_OPTIM_BUNDLE yet or for a while, a quick recap.

With this procedure, you can list the available patches to turn on or off at will:

SQL> set serveroutput
… Continue reading...

Do you love unexpected surprises? SYS_AUTO_STS in Oracle 19.7.0

Posted on May 28, 2020 by Behavior Change Optimizer 42

Last week I recorded seminars – and I wanted to garnish the Performance part with demo recordings from our Hands-On Lab. I used the Hands-On Lab instructions we published. And while I edited a recording, I realized that there was something new I haven’t seen before. Do you love unexpected surprises? SYS_AUTO_STS in Oracle 19.7.0? Of course you do …!

Do you love unexpected surprises? SYS_AUTO_STS in Oracle 19.7.0

Photo by Pete Wright on Unsplash

What happened?

In my environment I run load against my database, I collect statements from AWR and from Cursor Cache into two separate SQL Tuning Sets (STS). And then I upgrade my …

Continue reading...

_optimizer_ignore_hints is now optimizer_ignore_hints

Posted on June 25, 2018 by Optimizer Parameters 3

I’m refreshing our Hands-On Lab for Oracle Database 18c. And as part of a performance evaluation I wanted to set an underscore parameter to ignore all hints my load test tool sets. But then I learned: This does not work anymore as _optimizer_ignore_hints is now optimizer_ignore_hints. We made the underscore obsolete in Oracle Database 18c.

_optimizer_ignore_hints is now optimizer_ignore_hints

_optimizer_ignore_hints is now optimizer_ignore_hints

Actually in Oracle Database 12.2.0.1 you can find a long list of obsolete parameters here:

To be precise: We obsoleted159 parameters. In addition, in Oracle Database 18c we added an additional …

Continue reading...

DBMS_OPTIM_BUNDLE Package in Oracle 12.2 and 18c

Posted on March 8, 2018 by Optimizer 13

Yes, I did blog about the DBMS_OPTIM_BUNDLE package in earlier Oracle releases before. And I thought (and it was documented this way) that it will disappear in Oracle 12.2 and newer. But in fact you’ll find the DBMS_OPTIM_BUNDLE package in Oracle 12.2 and 18c. Most likely …

This is a very interesting case and you may see also my previous blog posts:

DBMS_OPTIM_BUNDLE Package in Oracle 12.2 and 18c

First of …

Continue reading...

Additional Info for Adaptive Features Fixes in Oracle 12.1.0.2

Posted on January 18, 2018 by Optimizer Patch Recommendation 0

Additional Info for Adaptive Features Fixes in Oracle 12.1.0.2I blogged about the Adaptive Features fixes in the past several times. But following some of the comments readers had I believe there’s some additional info for Adaptive Feature Fixes in Oracle 12.1.0.2 necessary.

What happened so far?

We delivered the most important fixes not only for adaptive features but only for dynamic sampling and some other things with the Database Bundle Patch in October 2017 for Oracle Database 12.1.0.2. The fixes got delivered on MS Windows a bit earlier.

… Continue reading...

Fixes for Adaptive Features are included in Oracle 12.1.0.2 October 2017 BP

Posted on October 19, 2017 by Optimizer Patch Recommendation 26

Finally … the two most important fixes for Adaptive Features are included in Oracle 12.1.0.2 October 2017 BP (Bundle Patch). I’m absolutely happy about this. Almost all of my customer and many folks outside I’m dealing with on a regular basis had to request and wait for merge patches over and over again. And we all knew – also based on the experience of the Real World Performance Team – that these two patches are so important to stabilize Oracle 12.1.0.2 production systems.

You may find this ALERT on MyOracle Support:

MOS Note 2289719.1
ALERT: Oracle Recommended Adaptive Feature Configuration …

Continue reading...

Oracle 12.2 Adaptive Features are enabled in Oracle 12.1.0.2 Windows BPs

Posted on September 20, 2017 by Optimizer Patch Recommendation 2

Oracle 12.2 Adaptive Features are enabled in Oracle 12.1.0.2 Windows BPsOracle 12.2 adaptive features are enabled in Oracle 12.1.0.2 Windows BPs already, This is quite a surprise to me. But thanks to Michael Pfautz. He alerted me today.as he was wondering why his database didn’t start up anymore after he applied the August 31 Bundle Patch on Windows. It complained about the parameter optimizer_adaptive_features in the SPFILE. Michael had to remove this parameter before he could startup his database again.

Oracle 12.2 Adaptive Features are enabled in Oracle 12.1.0.2 Windows BPs

We recommend to add two very important parameters to Oracle Database 12.1.0.2 installations.

Enabling Oracle 12.2 Adaptive Features …

Continue reading...

DBMS_OPTIM_BUNDLE – only available in Oracle 12.1.0.2 – or not?

Posted on July 25, 2017 by Optimizer Patch Recommendation 2

DBMS_OPTIM_BUNDLE - only available in Oracle 12.1.0.2The DBMS_OPTIM_BUNDLE package got introduced with the April 2014 Exadata 11.2.0.4 Bundle Patch. And it got introduced for all Oracle 12.1.0.2 databases with the April 2017 Bundle Patch (BP). DBMS_OPTIM_BUNDLE is only available in Oracle 12.1.0.2.

Addition March 6, 2018:
See the updated blog post about DBMS_OPTIM_BUNDLE available in Oracle 12.2 and 18c onwards.
https://mikedietrichde.com/2018/03/08/dbms_optim_bundle-package-oracle-12-2-18c/

For more information on this package on Exadata in Oracle 11.2.0.4 you may look at:

Purpose of DBMS_OPTIM_BUNDLE package in Oracle 12.1.0.2

The package got introduced to enable module bug fixes which …

Continue reading...

Adpative Features Patches with Oracle Peoplesoft

Posted on July 6, 2017 by Optimizer Patch Recommendation 7

Adaptive Feature Patches with Oracle Peoplesoft - Accenture Enkitec's ExperienceI’ve met David Kurtz, an Oracle ACE Director working for Accenture’s Enkitec division in the UK earlier this year on the OUGN Conference‘s boat trip from Oslo to Kiel and return. We set across the table at dinner. And of course we started discussing “Oracle”. David went to my talk about “How to ensure Performance Stability” before. He did ask me questions about the adaptive features patches I was recommending in my talk:

Enabling Oracle 12.2 ADAPTIVE Features in Oracle 12.1.0.2

The Adaptive Feature Patches

I explained to him that he will have to request these specific two …

Continue reading...

Adaptive Execution Plans – not available in Oracle SE2

Posted on June 12, 2017 by Optimizer 2

I received an interesting question on the blog the other day:

“Are Adaptive Plans an Enterprise Edition only feature?”

What does the License Guide say?

The Oracle Database 12.2 License Guide says:

Adaptive - EE only

Adaptive Features: Enterprise Edition only – from the Oracle 12.2 License Guide

What does www.oracle.com say?

This is the link the customer came across – also saying that Adaptive Execution Plans are an Enterprise Edition only feature:

Adaptive Plans - EE only

Adaptive Execution Plans – Enterprise Edition only – from www.oracle.com

What does this mean for SE2?

That is the question Adam had. Does this impact SE2? Will I get an error …

Continue reading...

Enabling Oracle 12.2 ADAPTIVE Features in Oracle 12.1.0.2

Posted on November 23, 2016 by Optimizer Oracle Database 12c Parameters 10

Oracle Database 12.2 introduces the new split-up adaptive parameters, OPTIMIZER_ADAPTIVE_PLANS and OPTIMITER_ADAPTIVE_STATISTICS. And the key trigger to cure potential and known performance issues in Oracle Database 12.1.0.2 is enabling Oracle 12.2 ADAPTIVE Features in Oracle 12.1.0.2

For more information please see:

Enabling Oracle 12.2 ADAPTIVE Features in Oracle 12.1.0.2

But Oracle Database 12.2 on-premises is not out yet – so what should you do when upgrading to Oracle Database 12.1 – or struggling with some of the “adaptive” features in Oracle 12.1?

… Continue reading...

OPTIMIZER_ADAPTIVE_FEATURES obsolete in Oracle 12.2

Posted on November 22, 2016 by Optimizer Oracle Database 12.2.0.1 Parameters 10

The Oracle Database 12.1 parameter OPTIMIZER_ADAPTIVE_FEATURES has been made OBSOLETE (i.e. must be removed from the SPFILE when upgrading) in Oracle Database 12.2.

It gets replaced with two parameters of whom one is enabled, the other one is disabled by default:

  • OPTIMIZER_ADAPTIVE_PLANS=TRUE by default
  • OPTIMIZER_ADAPTIVE_STATISTICS=FALSE by default

Nigel Bayliss, our “Optimizer” Product Manager has blogged about it already with way more detailed insights.

But as Oracle Database 12.2 on-premises is not available how should you deal with this feature in Oracle Database 12.1?

Enabling Oracle 12.2 ADAPTIVE Features in Oracle 12.1.0.2

 …

Continue reading...

Gather Fixed Objects Stats in PDBs as well?

Posted on October 21, 2016 by Optimizer Single-/Multitenant Statistics 9

Yesterday I received an interesting customer question:

“Do we have to gather fixed objects stats inside a PDB as well or only in the CDB$ROOT?”

The customer told me he couldn’t find anything in the documentation. Neither could I. And I did check also the usual suspects this morning “Best Practices for Gathering Statistics” – but it does not say anything about PDBs and CDBs.

Therefore I did a short test:

  • Create a PDB in my Oracle 12.1.0.2 CDB: 
    SQL> create pluggable database PDB3
     admin user adm identified by adm
     file_name_convert=( '/u02/oradata/CDB1/pdbseed', '/u02/oradata/CDB1/pdb3');
  • Open the PDB: 
    SQL>
… Continue reading...

_rowsets_enabled – Apply patch and use the default

Posted on October 11, 2016 by Flaws and Pitfalls Optimizer Oracle Database 12c Parameters Patch Recommendation 2

I while back I blogged about issues with “rowsets“, a new Oracle 12c feature which unfortunately had two known wrong result (WQR) bugs:

Switch off “_rowsets_enabled” in Oracle Database 12c

UPDATE: _rowsets_enabled in Oracle Database 12c

What does “rowsets” actually mean?

I’d like to thank Sankar, our Development manager for providing this explanation which sheds some light on how important this feature actually is:

“Rowsets is a SQL execution performance enhancement introduced in Oracle RDBMS release 12.1 and further extended in a future release of the Oracle Database.

Prior to 12.1, data processing in the SQL layer were

… Continue reading...

Global Temporary Tables – Change in Oracle 12c

Posted on July 7, 2016 by Behavior Change Optimizer Oracle Database 12c 0

A few weeks back I was copied on an email conversation about a important change with Global Temporary Tables (GTT) in Oracle Database 12c.

Something you need to be aware of when using GTTs in Oracle Database 12.1.0.2:
Prior to this release GTTs shared statistics between sessions. Statistics were SHARED between different sessions. Now since Oracle Database 12c this is different by default – statistics on GTTs are visible to the SESSION only.

This can be changed and altered of course. And there are pros and cons. But as I’m not an optimizer expert I’m glad that …

Continue reading...

What happened to the blog post about “12c parameters”?

Posted on February 25, 2016 by Optimizer Parameters Performance Tuning 3

Best Practice Hint

Two weeks ago I published a blog post about Parameter Recommendations for Oracle Database 12.1.0.2. And I took it down a day later. Why that?

I’ve got a lot of input from external sources for the “Parameter” blog post. And I’d like to thank everybody who contributed to it, especially Oracle ACE Ludovico Caldara.

Generally there was a bit of a misunderstanding internally about whether we should “advertise” underscore parameters to cure some misbehavior of the database. In 99% of all cases I’d agree that underscores are not a good solution – especially when it comes …

Continue reading...

UPDATE: _rowsets_enabled in Oracle Database 12c

Posted on November 16, 2015 by Flaws and Pitfalls Optimizer Oracle Database 12c Parameters 9

Please find a recent update here:

_rowsets_enabled – Apply patch and use the default

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

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 …

Continue reading...
Mike Dietrich
Oracle Vice President of Product Management and Development for Database Upgrade, Cloud Migrations and Patching writing just another blog.

Opinions certainly are my own as is this page.

Archives

Categories

Upgrade @YouTube

Subscribe via Email

Join 11.1K other subscribers