Introduction

It’s common to have multi-terabyte partitioned tables in an Oracle database these days. If you are not there yet but you’re heading that way, then you need to know about how to maintain statistics on large tables. I will cover this in a series of posts as follows:

• Part 1 (this post) – Concepts and implementation
• Part 2 – Incremental statistics and partition exchange loading
• Part 3 – New to Oracle Database Cloud Services (Oracle Database 12c Release 2)

There are some additional details and some overlap with an earlier Optimizer blog post, so you should check that out too.

[Read More]

A new white paper for the Oracle Optimizer is now available. Just go to the Query Optimization page in OTN and click the Optimizer with Oracle Database 12c link.

I've kept the content and format close to the way Maria organized it, so it will be familiar to you if you've read previous versions. The main difference is that I've reduced the number of scripts in the text to keep the paper down to about 30 pages, otherise it's too easy to hit 40+. As an alternative, I upload examples to GitHub.

Post comments to this blog if you want to give me feedback on the white paper's content. If you want more examples, let me know and I'll add suggestions to my to-do list. Of course, I intend to post more entries here (and scripts to GitHub) to cover things in more detail anyway.

Finally, in OTN you'll see a bunch of other white papers referenced in the Optimizer page. At the time of writing, these are for Oracle Database 12c Release 1. Needless to say, updates to those are on the way.

Nigel Bayliss.

Introduction

In the Exadata Express Cloud Service (Oracle Database 12c Release 2) we have changed the way optimizer adaptive features can be controlled. In this post, I'll present what has changed and give you guidance on how to you can choose what settings to use.

These changes are also relevant for Oracle Database 12c Release 1. If you want to know more about that, there's information at end of this post (or take a look at the OOW 2016 Update, below).

[Read More]

I hope those of you that made it to OOW 2016 had a great time. I can tell you that I definitely did, and I got to hear a bunch of really interesting scenarios and anecdotes from all over the world. It's really useful to get feedback like this - so thanks to everyone that came for a chat with me and the folks staffing the Optimizer and Statistics demo booth. I can assure you that your ideas and comments are heard and discussed with all members of the team.

You can get a copy of the slide deck I presented from the OOW session catalog.

For more information on Oracle Database 12c Release 1...

There is now a MOS note called Recommendations for Adaptive Features in Oracle Database 12c Release 1 (12.1) (Doc ID 2187449.1). Take a look, especially if you want the Optimizer adaptive features in Oracle Database 12c Release 1 to be controlled in a similar way to how they are controlled in the new Exadata Express Cloud Service. In the next post, there's a summary of the changes we made in this release.

It’s nearly that time of the year again: Oracle OpenWorld kicks off on September 18th in San Francisco. I’m hoping to meet some of you out there, but for those of you not going I intend to keep you up-to-date with the latest optimizer news by posting updates here during OOW week and more when the dust has settled a bit.

For those of you going, the Oracle Optimizer session is on Monday 19th, 1:45pm to 2:30pm in Moscone South—303. The title is Oracle Optimizer: Upgrading Without Pain [CON6450] and I'll be covering some important changes and enhancements we've made to make things easier for you.

Also, check out the panel session Optimizing SQL for Performance and Maintainability [CON6557] on Thursday 22nd, 1:15pm - 2:00 pm in Moscone South—103. The developer advocates hosting the panel, Chris and Connor, have asked me to join them so I'll be there as an official gatecrasher.

Please try and take the opportunity to come and meet the Oracle Optimizer developers at the Oracle Optimizer and Statistics demo ground booth (number 1633). You can ask them in person about all the new features they’ve added and the enhancements they’ve made. I’ll be at the booth on Tuesday afternoon, and probably on-and-off throughout the week.

Finally, my fellow product managers in the data warehousing and big data arena are presenting a bunch of sessions. Check them out in the full searchable OOW catalog. If you search using keywords like partitioning, warehousing, warehouse, parallel and analytics you’ll find them.

I received an email recently that demonstrated something the author considered strange when the init.ora parameter optimizer_feature_enable (OFE) is set in a database session. I thought I'd mention it here because the behavior he spotted is expected, but I don't think that it is entirely obvious.

Let's assume that you're logged into Oracle Database 12c. Now check the value of a hidden parameter applicable to this database version:

select ksppinm name,
       ksppstvl value
from   sys.x$ksppi x,
       sys.x$ksppcv y
where (x.indx = y.indx)
and   ksppinm = '_optimizer_aggr_groupby_elim';

NAME                            VALUE
=============================   =====
_optimizer_aggr_groupby_elim    TRUE

Next, set OFE to 11.2.0.4:

alter session set optimizer_features_enable = '11.2.0.4';

And you will then see this:

NAME                            VALUE
=============================   =====
_optimizer_aggr_groupby_elim    FALSE

That's not surprising - we have turned off an Oracle Database 12c feature by setting OFE down to 11.2.0.4. You can probably guess that the use of hidden parameters is not something I normally recommend, but there are circumstances where you might want to set them. Now, check this out:

alter session set "_optimizer_aggr_groupby_elim"=true;
alter session set optimizer_features_enable = '11.2.0.4';

NAME                            VALUE
=============================   =====
_optimizer_aggr_groupby_elim    TRUE

This time we see "TRUE", and it's because we do not cascade OFE to a parameter that has been changed in the session.

Hints are different because they override session settings. The group-by and aggregation elimination will not be available to the query in this example:

alter session set "_optimizer_aggr_groupby_elim"=true;

SELECT /*+ optimizer_features_enable('11.2.0.4') */ ...

Prior to Oracle Database 12c, global temporary tables (GTTs) shared statistics between sessions, so each GTT had one set of statistics visible to all sessions using it. Of course, gathering statistics on GTTs can be very useful because it will help the optimizer find better execution plans, but this advantage was sometimes tempered by the fact that multiple session would see the same set of stats. To resolve this dilemma, Oracle Database 12c included the ability to create session-private statistics, allowing sessions to create and use their own set of statistics for each GTT. This new behavior is controlled by a DBMS_STATS preference GLOBAL_TEMP_TABLE_STATS, which can be set to SHARED or SESSION. 

It is important to remember that the default in Oracle Database 12c is now SESSION, and that this has some implications if you're upgrading to this release. Firstly, you should consider whether your database application depends on SHARED GTT statistics. For example, you might have an ETL job that consist of multiple processes, and perhaps one process uses a database session to gather statistics on a GTT for other sessions to make use of later on. If your database applications are upgraded without taking into account the new default, then workloads relying on shared statistics won't see statistics on GTTs where they had seen them before. Dynamic sampling might kick in where it hadn't before (depending on your optimizer_dynamic_sampling level), and this could result in new or sub-optimal execution plans. So - if you're experiencing SQL execution plan degredations in Oracle Database 12c after an upgrade, check to see if any of the problem queries are associated with GTTs.

What should you do about this? Firstly, you can use the following query to see if GTT preferences match your expectations (you'll need to modify it to include your own application schema names):

select owner,
       table_name,
       dbms_stats.get_prefs('GLOBAL_TEMP_TABLE_STATS',owner,table_name) 
                 gtt_stats_preference
from   dba_tables
where  temporary = 'Y'
and    owner in ('my_application_owner')
and    duration = 'SYS$SESSION'
order  by owner,table_name;

Note that the predicate on the duration column is used to identify GTTs created with "on commit preserve rows".

If you want to be very selective, it is easy to change preferences on individual GTTs using a PL/SQL procedure similar to this. Just adjust the query to select the GTTs you want to change:

declare
  cursor c1 is
     select owner,
            table_name
     from   dba_tables
     where  temporary = 'Y'
     and    duration = 'SYS$SESSION'
     and    owner in ('my_application_owner');
begin
   for r in c1
   loop
      dbms_stats.set_table_prefs(r.owner,r.table_name,
                                 'GLOBAL_TEMP_TABLE_STATS','SHARED');
   end loop;
end;
/

You can use a bigger and easier switch to set the preference at the global, database and schema-level too. For example:

exec dbms_stats.set_global_prefs('GLOBAL_TEMP_TABLE_STATS','SHARED');
exec dbms_stats.set_schema_prefs('my_application_owner','GLOBAL_TEMP_TABLE_STATS','SHARED');

There is an "upgrade gotcha" you need to be aware of. If you have upgraded to Oracle Database 12c and are using optimizer_features_enable (OFE) set to a pre-12.1.0.1 version (let's say, for the sake of argument, it is '11.2.0.4') then be aware that the Oracle Optimizer will not "see" session private statistics.

You can see this in action if you do something like this:

-- Set dynamic sampling to the default value to help us indentify when stats are missing
alter session set optimizer_dynamic_sampling = 2;
-- Create a GTT
create global temporary table gtt1 (id number(10)) on commit preserve rows;
insert into gtt1 values (10);
commit;
-- Just to make sure you're using the default preference value, "SESSION":
exec dbms_stats.set_table_prefs(user,'GTT1','GLOBAL_TEMP_TABLE_STATS','SESSION')
-- Gather stats 
exec dbms_stats.gather_table_stats(user,'gtt1'); 

Execute a query on the GTT:

set autotrace on
select * from gtt1 where id = 1;

The execution plan will include the following comment in the "Note" section:

Global temporary table session private statistics used

If you now do the same with OFE set to 11.2.0.4:

alter session set optimizer_features_enable = '11.2.0.4';
set autotrace on
select * from gtt1 where id = 1;

You will see the following note in the execution plan, where the use of dynamic sampling indicates that the session-private statistics are not being seen:

dynamic statistics used: dynamic sampling (level=2)

Note also, that if you do this:

exec dbms_stats.set_table_prefs(user,'GTT1','GLOBAL_TEMP_TABLE_STATS','SESSION')
alter session set optimizer_features_enable = '11.2.0.4';
exec dbms_stats.gather_table_stats(user,'gtt1');

...then you will gather session private statistics, which will not be seen by queries executed against the GTT if OFE remains set prior to 12.1.0.1.

Conclusions

• If you're setting OFE prior to 12.1.0.1 and you have "on commit preserve rows" GTTs, you will probably want to change your GTT table preferences to SHARED so that statistics will be created and seen as expected.
• If you are upgrading to Oracle Database 12c or developing new functionality on this release, you need to consider whether this change of behavior is relevant to your application. Do you have GTTs created with "on commit preserve rows" and does the application gather statistics on those tables? Has your application been modified to accommodate the new default? If not, you might need to change your GTT table preferences to SHARED.

Each time we release a new version of the Oracle Database we publish a white paper that covers what's in the new in the Oracle Optimizer. It's pretty comprehensive and it covers all of the headline features, but it doesn't go deep down in amongst the dragons.What if you want a closer look a what's changed in the Oracle Optimizer between releases? That's what this post is about.

You'll know that there's a database parameter called optimizer_features_enable (OFE). It can be used to restrict which Optimizer features are enabled in your database. You'll also know that you shouldn't use it like a magic wand, but it is very useful for the trick I have in mind here.

I've put together a script for you to check out. It's not earth-shatteringly new (as you'll realize if you take a look around the Internet) but I think that you'll find it useful. The principle behind its operation is pretty simple: it captures parameters and configuration settings for two different OFE values (11.2.0.4 and 12.1.0.2, by default). Finally, it displays a comparison between the two sets of results. I think that the output is very interesting. I hope you think so too.

Introduction

In December I posted an entry on the subject of SQL plan management (SPM). It was inspired by questions raised at Oracle Open World 2015 and since then I’ve been talking to more customers about it, particularly in the context of upgrades. As you might guess, I get a lot of questions on the topic of how to use SPM in practice, so it seems like a good time to collate my answers and give you some pointers on how to get the best from the feature. If you're not familiar with SPM, there’s a four-part blog series to get you up to speed.

I’m going to make the assumption that your system is a suitable candidate for implementing SPM. This implies that you have a finite set of repeatable queries (or at least a finite set of critical queries). In SPM terminology, a repeatable query is one that is executed multiple times without the query text being modified, so this will make it practical and worthwhile to capture and stabilize its execution plan. This usually means that the application will need to use bind variables and not incorporate literal values in query strings (unless cursor sharing is being used to address that). If your application does use literal values for some critical queries, consider using SQL profiles with the “force_match” parameter.

Choosing between a strategic or tactical approach

SPM has been designed so that it can be used strategically. In other words, it will stabilize the execution plans of all your repeatable SQL statements and prevent them from encountering performance regression. If you need to avoid regression at all costs then a strategic, “SQL-plan-baseline-everything” approach is often the most appropriate choice. You should definitely consider creating SQL plan baselines for all queries if you are upgrading and want to continue to use the same SQL execution plans. After upgrade, you can evolve SQL plan baselines once the system is established, taking advantage of new optimizations and better execution plans in an incremental and verified way.

SPM can be implemented in a more tactical manner; using it to target a subset of your workload. This is appropriate if you are confident that you can identify a core set of critical queries and you can be sure that regressions outside this set are unlikely to significantly harm your service levels.

Using SPM proactively or reactively

You might have seen the following image in our documentation; comparing SPM with SQL profiles:

You can see that SPM is usually used proactively to prevent the use of suboptimal plans, whereas SQL Profiles are usually used reactively to correct problems once they have occurred. But don’t assume that this guidance is cast in stone. If you’ve read the earlier SPM series you’ll know that you can use SPM to “repair” queries that have regressed. In my experience, many DBAs use SPM reactively to correct individual queries, avoiding the need to change the application in any way (something that every DBA wants to have in their tool-box).

For the rest of this post, I’ll assume that SPM is being used proactively and that there are SQL plan baselines for all (or a significant part) of your workload.

When to capture SQL plan baselines

Capture queries from your workload when the system is performing well so that, by definition, all of the captured SQL execution plans will be “good” execution plans. SPM auto capture is a very popular choice with DBAs and it's particularly easy to use, but be aware that it will add some overhead to your workload because the execution plans are captured and stored at (hard) parse time. This isn't usually a problem because hard parse rates are generally low of course, but if you're concerned about it you can always capture in bulk from the cursor cache or from SQL tuning sets at any time. Make sure that you create accepted SQL plan baselines for every captured query and if a query has multiple execution plans (because of adaptive cursor sharing) go ahead and accept them all. This approach requires little manual intervention and there is no need to micro-manage which queries to accept.

SQL plan baseline capture is an ongoing-process, but Oracle recommends that you don’t switch on auto-capture indefinitely. Instead, capture new queries when changes have been made to applications and workloads. Bulk capture (from the cursor cache or from SQL tuning sets) is a good on-going approach too. For all types of capture, SQL plan baselines will be created without requiring you to figure out which ones are new and which ones already exist – SPM takes care of that for you.

If you are upgrading a critical system to Oracle Database 12c, then this is a perfect time to consider creating SQL plan baselines for all your queries. You can do this using the pre or post-upgraded system (see here for details).

Implementing SPM and backing out SQL plan baselines

There is always risk associated with making changes to a production system, and change management procedures will usually demand that you have a back-out procedure. It is reasonable to consider what would happen if you add or enable a large number of SQL plan baselines and you encounter problems of some kind. To help you in these circumstances, the DBA_SQL_PLAN_BASELINES view includes timestamps for creation and modification (as well as the origin of the SQL plan baseline). You can use this information to identify recently added and changed SQL plan baselines. The DROP_SQL_PLAN_BASELINE and ALTER_SQL_PLAN_BASELINE procedures are very efficient so, for example, most systems will be capable of enabling and disabling hundreds of SQL plan baselines per second.

Fixing SQL plan baselines

Individual SQL plan baselines can be fixed. This means that new plans will not be captured automatically for SQL statements that have fixed SQL plan baselines (although you can still load new plans manually and make them fixed if you want to). Be aware that if a SQL plan baseline is fixed for a particular query, but other non-accepted plans in the SQL plan history were previously captured (for this query) then these plan history entries will be subject to evolution. I have heard of DBAs encountering this, but it is a reare scenario - it is more usual to fix all baselines for a given SQL statement if you were considering fixing at all.

Some DBAs use SPM very tactically, creating and fixing SQL plan baselines for a carefully chosen set of queries with the intention of never evolving them. However, before fixing SQL plan baselines, consider the possibility of using a slightly different approach. Instead, don't fix them, but allow SPM to capture alternative plans in the SQL plan history. The trick then is to use the SPM auto evolve task to report on the new SQL execution plans without accepting them by default. Like this:

DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
    task_name => 'SYS_AUTO_SPM_EVOLVE_TASK'
,   parameter => 'ACCEPT_PLANS'
,   value     => 'FALSE'
);

Now you have the option to view the evolve task report at any time and experiment with new plans, accepting the better ones individually and manually if you prefer.

How and when to evolve

If SQL plan baselines have been comprehensively captured for a representative workload, the number of new plans identified is usually very low. In this case, the motivation to evolve plans will also be low. Nevertheless, evolution is still worthwhile to take into account new plans that are generated by change (such as changes to underlying schema objects, application changes or configuration changes).

In Oracle Database 12c, the evolve process is fully automated and enabled by default. We want you to be confident of using SPM with very little manual intervention, but I know that automation has surprised a number of DBAs that had originally intended to implement evolution down-the-line. If you want to postpone evolution for whatever reason, then the best approach is usually to set the evolve auto task parameter ACCEPT_PLANS to FALSE (see above). New excution plans will be verified and you can view the auto task report at any time to identify improvements and then be as selective as you like about which ones to accept using the evolve API on a case-by-case basis. For example:

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE( … sql_handle=>'...', plan_name=>'...' …);

When upgrading a database, a strategic SPM approach will enable you to carry forward your existing SQL execution plans into the new release. Once the new environment is established, it is likely that you will capture a large number of new execution plans in the SQL plan history. After all, the Optimizer will be different. The motivation to evolve baselines is likely to be stronger because it makes good sense to make full use of the most up-to-date Optimizer but you might want it to verify that it is better (and find out why if it’s not). For this scenario, it is not unreasonable to use ACCEPT_PLANS = FALSE to help you identify improved SQL execution plans to evolve individually. This will give you the opportunity to gain trust in evolution before you set ACCEPT_PLANS to TRUE.

Troubleshooting

Systems are rarely static. Schema changes or environment changes can sometimes make it impossible to continue to use a previously-valid execution plan defined in a SQL plan baseline. For example, if a SQL plan baseline specifies an index range scan but the corresponding index has been dropped, then it will become impossible for the SQL plan baseline excution plan to be used. This will not cause the corresponding SQL statement to fail; there is no error condition or error message, but it can mean that there will be a SQL statement that is no longer “protected” by a SQL plan baseline so it could conceivably suffer from a performance regression. If evolve is used frequently then this scenario can be avoided to a large extent because plan changes are captured in the SQL plan history where they can be verified and accepted when necessary.

I have seen cases where a customer evolves their SQL plan history infrequently, and subsequently some SQL statements have stopped matching their intended SQL plan baselines without being noticed. Also, this can sometimes happen because of a bug or where there has been a schema change without the DBAs realizing that it could make some execution plans in SQL plan baselines non-viable.  Fortunately, it is not difficult to identify queries in the SQL cursor cache that have a corresponding SQL plan baseline but (for whatever reason) it is not being successfully matched and used. Take a look at the utility scripts I’ve uploaded to GitHub – and in particular “nomatch.sql” shows you how it’s possible to identify SQL statements in the cursor cache that are failing to match their SQL plan baselines. As usual, if you have any suggestions on how I could improve the scripts, just let me know in the comments.

Click for more posts on SPM...

I get a fair number of questions on query transformations, and it’s especially true at the moment because we’re in the middle of the Oracle Database 12c Release 2 beta program. Sometimes people notice “something missing” or unusual in an execution plan and realize that a transformation is happening. For example, Join Elimination (thanks, Christian) can take you by surprise if you notice that a table in your query doesn’t appear in the SQL execution plan at all (and neither do any of its indexes).

[Read More]

Those of you that made it to the demo grounds at Oracle Open World this year (BTW - it’s still 2015 - just) will have had the chance to chat with the Oracle developers and throw lots of difficult questions at them! For everyone in the Optimizer development team it was a great opportunity to get lots of interaction and feedback, so thanks to all of you that took the time to come along and talk to us. We’re not all lucky enough to get a trip out to San Francisco, so I’ve been collating the main topics that came up to steer the subject matter of the next few blog posts. In this way I hope that we can all benefit from these interactions.

I can tell you right away that the number one demo ground question at OOW 2015 was …drum roll… “How can I reduce the risk of query regression when I upgrade to Oracle Database 12c?”. Well, maybe not those words exactly, but words to that effect. There is quite a lot of information out there on this topic, but people seem to struggle to find it… so we put our heads together and we realized that we should publish a 20,000ft view of this topic with pointers down into the detail. That’s the purpose of this post and, for the sake of brevity, I’m going to make the assumption that you are upgrading your Enterprise Edition database from Oracle Database 11g Release 2 to Oracle Database 12c.

The bottom line is this: if you want to mitigate the risk of query regression when you upgrade to Oracle Database 12c, then use SQL Plan Management (SPM). This is the recommended approach for the vast majority of systems out there, where the most critical SQL statements are reusable or, in other words, they are executed more than once.

Here are a couple of common scenarios:

Scenario#1	You want to use all Oracle Database 12c Optimizer features right away, but you need to “repair” any regressed queries quickly and with minimum effort.
Scenario#2	You’re upgrading and want to keep your “tried-and-tested”, Oracle Database 11g execution plans. Nevertheless, you do not want your application to be frozen in time: you want to evolve and use improved execution plans that are available in the new release, and you need to do this in a proven and controlled way.

Scenario 1

This is something you’ll want to think about before your production system goes live, particularly if you have not been able to test a realistic workload on all of your production data. It’s also very relevant if you are running a benchmark or proof of concept, where the time that’s available to resolve problem queries can be pretty limited (I’m using some understatement there!).

Ideally you will have captured SQL plan baselines before you’ve upgraded, because then you’ll have a set of “good” execution plans at-the-ready. It isn’t absolutely necessity to do this, though. As long as you can reproduce or find an example of a good plan, then this can be used to create a SQL plan baseline on-demand. For example, you may find a better plan:

• By running the problem query in a pre-upgrade environment (remembering that you can export and import SQL plan baselines to copy them between databases).
• Inside an existing SQL Tuning Set (STS).
• By reproducing the good plan in the post-upgrade environment using (for example) “alter session set optimizer_features_enabled = 11…”, adjusting other Optimizer database parameters or by using hints. Yes, setting this parameter to an older version will give you the plan of the older version; that’s the whole purpose of it (and if it doesn’t work for you then it usually means that there’s a bug).

The next step is the particularly clever part, but I get the impression that a lot of Oracle experts don’t know that it’s even possible. When you’ve found a good plan and captured the details in a SQL plan baseline, you can use SPM to associate it with a regressed SQL statement without having to change the existing query or the existing application code. For details, take a look in the section, “Creating an accepted plan by modifying the SQL text” in an earlier Optimizer blog entry and also page 24 of SQL Plan Management with Oracle Database 12c. In both cases, an improved SQL execution plan is found using a hint. This plan is associated with a regressed SQL statement so that, in future, the better plan is used.

Scenario 2

You should capture SQL Plan Baselines in your Oracle Database 11g environment and export them so that they can be imported into the upgraded database. If you are upgrading in-place, then existing SQL plan baselines will be available without the need to export and import them. If you neglected to capture baselines in the pre-upgrade environment, then you still have the option to capture 11.2 execution plans in an Oracle Database 12c environment by executing your queries in a session where the database parameter optimizer_features_enabled is set to “11.2.0.4” (or whatever version you like).

Once SQL plan baselines are established in the upgraded database, you will enjoy plan stability while you get to know your system in the critical early days after the upgrade. Once you are happy with your shiny new database, you can evolve SQL plan baselines either automatically or at your own pace. Then you will gain advantage of all the new Optimizer features available in Oracle Database 12c.

Licensing

SQL Plan Management is an Oracle Database Enterprise Edition (EE) feature (you can see this here). You don’t need to buy an additional license to use SPM on EE. Optionally, you might choose to use SPM with SQL Tuning Sets (STS). If you do, then you will need to have purchased the Oracle Tuning Pack for Oracle Database (PDF) in adition to EE because STS requires this pack.

Top Tip

Whenever you plan to upgrade, check out Oracle’s Database Upgrade Blog. It’s full of really great information and it will hook you up with the latest advice at the finest level of detail. For example, here are some useful specifics on SPM to get you started.

So now is the time to upgrade, unless you’re a retailer like Amazon who’s heating up its systems for the big Christmas push, or perhaps you’re heading into your end-of-year financial reporting period. Nevertheless, even for you, the “now” is pretty close…

Oracle OpenWorld 2015 is nearly here! Come and see our session on Monday, entitled The Next Generation of the Oracle Optimizer...and How You Can Use It NOW [CON8740]. The session will introduce you to the latest enhancements Oracle has made to make Optimizer statistics more comprehensive and accurate than ever before. You'll get some pointers to the latest best practice too.

If you want to get the deepest insight into the Optimizer features available in the latest generation of the Oracle Database, come and talk to the developers themselves in the demo grounds. They'll be in Moscone South, pod SLD-042 (you'll see it if you search for "optimizer" here).

Have a great Oracle OpenWorld 2015!

OK. There’s a new baseline, but it isn’t accepted (the value “NO” appears in the accepted column). This is exactly what SPM is supposed to do: we continue to use accepted and "approved" plans until we have verified or chosen to use alternatives. What is the new baseline plan in this case?

Now, re-run the query:

You’ve probably come across this situation many times: the Oracle Optimizer must choose between a full table scan or an index range scan depending on predicate selectivity. The ability to change the execution plan based on the value of bind variables is called adaptive cursor sharing. If you’ve not come across that, then you’ll find it useful to check out the section on this topic in the Database SQL Tuning Guide.