_optimizer_ignore_hints is now optimizer_ignore_hints

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

Additional Info for Adaptive Features Fixes in Oracle 12.1.0.2

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. Enabling Oracle 12.2 ADAPTIVE Features in Oracle 12.1.0.2 – BEFORE the patches got included in BPs Oracle 12.2 Adaptive Features…

DBMS_OPTIM_BUNDLE – only available in Oracle 12.1.0.2 – or not?

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: MOS Note:1942966.1: Exadata: DBMS_OPTIM Package Philippe Fierens’ blog post from October 2014 Purpose of DBMS_OPTIM_BUNDLE package in Oracle 12.1.0.2 The package got introduced to enable module bug fixes which cause…

Adpative Features Patches with Oracle Peoplesoft

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 ADAPTIVE Features of Oracle 12.2 in 12.1 The Adaptive Feature Patches I explained to him that he will have to request these specific two fixes unfortunately…

Enabling Oracle 12.2 ADAPTIVE Features in Oracle 12.1.0.2

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: https://mikedietrichde.com/2016/11/22/optimizer_adaptive_features-obsolete-in-oracle-12-2/ https://blogs.oracle.com/optimizer/entry/optimizer_adaptive_features_in_the 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 It recommends to…

OPTIMIZER_ADAPTIVE_FEATURES obsolete in Oracle 12.2

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 ADAPTIVE Features of Oracle 12.2 in 12.1     –Mike .

UPDATE: _rowsets_enabled in Oracle Database 12c

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 why one…

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

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  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”

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

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 12c…

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 from release 10.1…

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. 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 might be…