Optimizer

Oracle Optimizer Statistics Advisor in Oracle 19c

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.

Oracle Optimizer Statistics Advisor in Oracle 19c

Photo by Tobias Keller on Unsplash

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 …

Should you enable _fix_controls with DBMS_OPTIM_BUNDLE?

Posted on August 10, 2021 by Mike.Dietrich Optimizer Patch Recommendation 15

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.

Should you enable _fix_controls with DBMS_OPTIM_BUNDLE?

Photo by paolo candelo on Unsplash

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;

You may need a one-off for DBMS_OPTIM_BUNDLE in 19.10.0

Posted on February 1, 2021 by Mike.Dietrich 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

Do you love unexpected surprises? SYS_AUTO_STS in Oracle 19.7.0

Posted on May 28, 2020 by Mike.Dietrich 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 …

_optimizer_ignore_hints is now optimizer_ignore_hints

Posted on June 25, 2018 by Mike.Dietrich 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

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

Obsolete Parameters in Oracle Database 12.2.0.1 (Nov 10, 2016)

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

Additional Info for Adaptive Features Fixes in Oracle 12.1.0.2

Posted on January 18, 2018 by Mike.Dietrich Optimizer Patch Recommendation 0

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

DBMS_OPTIM_BUNDLE – only available in Oracle 12.1.0.2 – or not?

Posted on July 25, 2017 by Mike.Dietrich Optimizer Patch Recommendation 2

DBMS_OPTIM_BUNDLE - only available in Oracle 12.1.0.2 The 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 …

Adpative Features Patches with Oracle Peoplesoft

Posted on July 6, 2017 by Mike.Dietrich Optimizer Patch Recommendation 7

Adaptive Feature Patches with Oracle Peoplesoft - Accenture Enkitec's Experience I’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 …

Enabling Oracle 12.2 ADAPTIVE Features in Oracle 12.1.0.2

Posted on November 23, 2016 by Mike.Dietrich 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?

MOS Note: 2187449.1
Oracle Highly Recommended Adaptive Feature Configuration Parameter Settings for 12.1.0.2.0

OPTIMIZER_ADAPTIVE_FEATURES obsolete in Oracle 12.2

Posted on November 22, 2016 by Mike.Dietrich 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.

https://blogs.oracle.com/optimizer/entry/optimizer_adaptive_features_in_the

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

…

UPDATE: _rowsets_enabled in Oracle Database 12c

Posted on November 16, 2015 by Mike.Dietrich 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:

Nov 10, 2015:
Switch off _rowsets_enabled in Oracle Database 12c

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 …

Things to consider BEFORE upgrading to Oracle 12.1.0.2 to AVOID poor performance and wrong results

Posted on July 30, 2015 by Mike.Dietrich Flaws and Pitfalls Optimizer 8

Finally it got published on MyOracle Support (MOS) portal – one of the most helpful notes when we prep for an upgrade or a migration – and potentially the one with the longest title in MOS:

MOS Note: 2034610.1
Things to consider BEFORE upgrading to Oracle Database 12.1.0.2 to AVOID poor performance and wrong results

Avoid Poor Performance and Wrong Results when upgrading to 12.1.0.2

One important note regarding these “Things to Consider” notes:
They will only list issues where either a fix or a proven workaround is available. They don’t list all open issues nor do they recommend every given underscore which may cure one or the other misbehavior.…

Optimizer Issue in Oracle 12.0.1.2: “Reduce Group By”

Posted on July 28, 2015 by Mike.Dietrich Flaws and Pitfalls Optimizer Oracle Database 12c Parameters 4

DBAs 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 …

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

Posted on February 13, 2015 by Mike.Dietrich Flaws and Pitfalls Optimizer Oracle Database 12c Parameters 3

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 …

Why you shouldn’t set OPTIMIZER_FEATURES_ENABLE

Posted on December 16, 2013 by Mike.Dietrich Optimizer Parameters 6

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

It’s always the Optimizer, isn’t it?! – Part 1

Posted on October 19, 2011 by Mike.Dietrich Optimizer Oracle Database 11g 0

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.

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

Why?

It …

Optimizer

Oracle Optimizer Statistics Advisor in Oracle 19c

Overview

Should you enable _fix_controls with DBMS_OPTIM_BUNDLE?

DBMS_OPTIM_BUNDLE – a quick recap

You may need a one-off for DBMS_OPTIM_BUNDLE in 19.10.0

Quick Recap

Do you love unexpected surprises? SYS_AUTO_STS in Oracle 19.7.0

What happened?

_optimizer_ignore_hints is now optimizer_ignore_hints

_optimizer_ignore_hints is now optimizer_ignore_hints

Additional Info for Adaptive Features Fixes in Oracle 12.1.0.2

What happened so far?

DBMS_OPTIM_BUNDLE – only available in Oracle 12.1.0.2 – or not?

Purpose of DBMS_OPTIM_BUNDLE package in Oracle 12.1.0.2

Adpative Features Patches with Oracle Peoplesoft

The Adaptive Feature Patches

Enabling Oracle 12.2 ADAPTIVE Features in Oracle 12.1.0.2

Enabling Oracle 12.2 ADAPTIVE Features in Oracle 12.1.0.2

OPTIMIZER_ADAPTIVE_FEATURES obsolete in Oracle 12.2

UPDATE: _rowsets_enabled in Oracle Database 12c

When is the problem happening?

Things to consider BEFORE upgrading to Oracle 12.1.0.2 to AVOID poor performance and wrong results

Optimizer Issue in Oracle 12.0.1.2: “Reduce Group By”

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

Why you shouldn’t set OPTIMIZER_FEATURES_ENABLE

It’s always the Optimizer, isn’t it?! – Part 1

Archives

Categories

Upgrade @YouTube

RSS Feed for Upgrade your Database – NOW!

Subscribe via Email

Recent Posts

Follow me on Twitter