X

Insights into Statistics, Query Optimization and the Oracle Optimizer

  • 12c |
    March 1, 2017

Optimizer Adaptive Features and Upgrading to Oracle Database 12c Release 2 or Oracle Database 18c

Nigel Bayliss
Product Manager

Introduction

Here's a reminder of the changes we’ve made to the optimizer’s adaptive feature settings in Oracle Database 12c Release 2 onwards. The new default behavior is covered in an earlier post but I want to cover the various options available to you depending on what you're upgrading from.

Since the October 2017 database bundle patch, Oracle has included fixes for the adaptive features. This is covered in a more recent post. I have kept this post in place for those that are upgrading and have not applied the proactive bundle patches.

Scenario 1

Upgrading from Oracle Database 11g (or an earlier release)

Once you’ve upgraded the database to Oracle Database 12c Release 2 or Oracle Database 18c, use the default adaptive feature settings. To do this, simply don't include any adaptive feature parameters in your database's initialization parameter file. In other words, there's no need to set optimizer_adaptive_plans or optimizer_adaptive_statistics.

Keep it simple!

Scenario 2

Upgrading from Oracle Database 12c Release 1 where patches for bugs 21171382 and 22652097 have been applied.

These two patches enable an Oracle Database 12c Release 1 database to use the same adaptive feature settings as those in Oracle Database 12c Release 2 (see MOS note 2187449.1). There is no requirement to apply them before upgrading. They have been provided because some customers want to use the new adaptive parameter settings in Oracle Database 12c Release 1 production environments.

Oracle Database 12c Release 1 databases with these patches can be upgraded without changing any adaptive feature settings.That's it!

Alternatively, if you were not using the recommended defaults pre-upgrade and you want to use them post-upgrade, then:

  • Remove references to optimizer_adaptive_plans and optimizer_adaptive_statistics from the database initialization parameter file.
  • Ensure that the DBMS_STATS preference
    AUTO_STAT_EXTENSIONS is set to OFF using DBMS_STATS.SET_GLOBAL_PREFS.

Scenario 3

Upgrading from Oracle Database 12c Release 1 and patches for bugs 21171382 and 22652097 have not been applied.

If you disabled adaptive features in Oracle Database 12c Release 1 (by setting, for example, optimizer_adaptive_features to FALSE) you should use the new recommended defaults once you've upgraded. To do this, you will need to check your initialization parameter file as follows:

  • Remove references to the optimizer_adaptive_features parameter (it is obsolete in Oracle Database 12c Release 2 onwards).
  • Remove any fix control and hidden parameter settings that were used to disable various adaptive features. Fix controls like 12914055, 12914055 and 7452863 were typically used along with hidden parameters like _optimizer_dsdir_usage_control and _sql_plan_directive_mgmt_control.
  • There is no need to set optimizer_adaptive_plans and optimizer_adaptive_statistics because the default values are the recommended values. 

If adaptive features are enabled in your Oracle Database 12c Release 1 database and you want to continue to use these features in the same way once the database has been upgraded, then:

  • Remove references to optimizer_adaptive_features from the
    initialization file (it is obsolete in Oracle Database 12c Release 2 onwards).
  • Add optimizer_adaptive_statistics=TRUE to the initialization parameter file (and there's no need to set optimizer_adaptive_plans because the default is TRUE).
  • Set DBMS_STATS preference
    AUTO_STAT_EXTENSIONS to ON using DBMS_STATS.SET_GLOBAL_PREFS

Testing and SQL Plan Management

SQL execution plans are subject to change when a database is upgraded,so critical systems should be tested to make sure that there are no performance regressions. If comprehensive testing is not practical but there is a strong requirement to mitigate the risk of performance regression, then use SQL plan management (SPM). This feature allows you to keep the same execution plans post-upgrade as the ones you had pre-upgrade. It can do this without "freezing" SQL execution plans: SPM will identify, verify and enable SQL execution plans that perform better in the new release. In this way it is possible to take advantage of new optimizer features that yield improved performance without risking poor performance associated with regressed SQL execution plans.

There's more on SPM in the context of upgrades here and a four-part series here. You can of course use the blog's search facility (above) to find more.

If anything is not clear, please don't  hesistate to post a comment. Thanks.

Join the discussion

Comments ( 7 )
  • Foued Thursday, March 9, 2017

    Thank you for sharing this post.


  • guest Tuesday, April 11, 2017

    Why the 12C release2 explain plan function cannot reflect the Inmemory tables cost change ? A "table inmemory full access" cost in a query plan is the same as it is a normal full table scan ?


  • NigelBayliss Tuesday, April 11, 2017

    Hi -

    Regarding In-Memory, I'm not sure exactly what you mean. There information on costing here:

    https://blogs.oracle.com/In-Memory/entry/oracle_database_in_memory_the

    Regards,

    Nigel.


  • Richard Friday, May 12, 2017
    Thank you for your answer, my question is illustrated as the following:

    explain plan for select * from T10;

    SELECT STATEMENT ALL_ROW SCost: 6,829 Bytes: 175,999,680 Cardinality: 549,999
    1 TABLE ACCESS FULL TABLE T10 Cost: 6,829 Bytes: 175,999,680 Cardinality: 549,999

    alter table T10 inmemory;
    explain plan for select * from T10;

    Plan
    SELECT STATEMENT ALL_ROWSCost: 6,829 Bytes: 175,999,680 Cardinality: 549,999
    1 TABLE ACCESS INMEMORY FULL TABLE T10 Cost: 6,829 Bytes: 175,999,680 Cardinality: 549,999

    You can see the plan cost is not change, but the Table access for T10 method is changed to Inmemory full scan.

    In Oracle12C R1, the explain plan can estimate the IO cost and CPU cost without the need to populate the tables. In R2, I have to populate the table before I can get the cost estimation from Explain plan function, it is very time consuming if I want to test different combinations of table objects to see the inmemory impact to a SQL workload.

    Thanks,

    Richard
  • Nigel Bayliss Friday, May 12, 2017
    Hi Richard,

    It's theoretically possible to manipulate overall costing behavior using some hidden database parameters but they do not target individual tables. In Oracle Database 12c Release 2 you can use DBMS_STATS.SET_TABLE_STATS to set INMEM_IMCU_COUNT and INMEM_BLOCK_COUNT on a per-table basis. It is possible to manipulate the cost by setting these values but they won't be non-NULL unless the table has been altered to be in-mem. You could alter a table in-memory and then set these values immediately after that (to values that imply the table is fully populated in the IMCS). This would fool the optimizer into thinking that the relevant table is immediately fully resident. These stats weren't added to manipulate the optimizer like this, so your mileage will vary.

    Regards,
    Nigel.
  • Richard Saturday, May 13, 2017
    Nigel,

    Thank you for your response, it is very useful. I know IM_BLOCK_COUNT is roughly equal to the table's blocks count, but how about IM_IMCU_COUNT? How can I roughly estimate this number ?

    Thanks,

    Richard
  • Nigel Bayliss Wednesday, May 24, 2017
    Hi Richard, the number depends on the compression ratio - so there's no fit-for-all estimate. My advice is to try it for some of your own datasets and get a feel for it that way.
    Regards,
    Nigel.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services