STS, OFE and SPM

That’s SQL Tuning Sets, optimizer_features_enable, and SQL Plan Management.

There’s a recent post on OTN describing an issue when using SQL Tuning Sets to enforce plan stability when upgrading from 10.2.0.3 to 11.2.0.3 – it doesn’t always work. Here’s a very simple model to demonstrate the type of thing that can happen (the tables are cloned from a completely different demo, so don’t ask why I picked the data they hold):

create table t1
as
select
	trunc((rownum-1)/15)	n1,
	trunc((rownum-1)/15)	n2,
	rpad(rownum,180)	v1
from all_objects
where rownum <= 3000
;

create table t2
as
select
	mod(rownum,200)		n1,
	mod(rownum,200)		n2,
	rpad(rownum,180)	v1
from all_objects
where rownum <= 3000
;

create index t1_i1 on t1(n1);
create index t2_i1 on t2(n1);

execute	dbms_stats.gather_table_stats(user,'t1');
execute	dbms_stats.gather_table_stats(user,'t2');

alter session set optimizer_features_enable = '10.2.0.3';

explain plan for
select
	/*+ ordered use_nl(t1) index(t1) */
	t2.n1, t1.n2
from
	t2,t1
where
	t2.n2 = 45
and	t2.n1 = t1.n1
;

set pagesize 60
set linesize 132
set trimspool on

select * from table(dbms_xplan.display(null,null,'outline'));

You’ll notice I’ve included a directive to set the optimizer_features_enable back to 10.2.0.3. If I run this test on both 10.2.0.3 (real) and 11.2.0.3 (with ofe set) I get the same plans but slightly different outline data.

From 10.2.0.3
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   225 |  3600 |    35   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |    15 |   120 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |       |   225 |  3600 |    35   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL        | T2    |    15 |   120 |     5   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | T1_I1 |    15 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      USE_NL(@"SEL$1" "T1"@"SEL$1")
      LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N1"))
      FULL(@"SEL$1" "T2"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

From 11.2.0.3
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   225 |  3600 |    54   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |    15 |   120 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |       |   225 |  3600 |    54   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL        | T2    |    15 |   120 |    24   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | T1_I1 |    15 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      NLJ_PREFETCH(@"SEL$1" "T1"@"SEL$1")
      USE_NL(@"SEL$1" "T1"@"SEL$1")
      LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N1"))
      FULL(@"SEL$1" "T2"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Apart from the appearance of the db_version() hint in 11.2.0.3 the most important difference in the outline data is the hint nlj_prefetch() hint. 11g introduced a new mechanism for nested loop joins called NLJ Batching, at the same time introducing two new hints to allow the optimizer to specify which mechanism a plan should use, prefetch (nlj_prefetch()) or batching (nlj_batching()). Since 10g only does prefetching it doesn’t have (or need) a hint to specify the mechanism.

The outline section data from a plan is basically what Oracle stores as an SQL Plan Baseline – so if I use the approved method to turn the 10g outline data above into the 11g SQL Plan Baseline what’s going to happen to the execution plan when I run the query in the default 11g environment ? It’s easy to demonstrate (at least in this case) by simply cutting and pasting the entire 10g outline into the original SQL statement and generating its plan under 11g; here’s the result:

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |   225 |  3600 |    54   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |       |   225 |  3600 |    54   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL          | T2    |    15 |   120 |    24   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| T1    |    15 |   120 |     2   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | T1_I1 |    15 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      USE_NL(@"SEL$1" "T1"@"SEL$1")
      LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."N1"))
      FULL(@"SEL$1" "T2"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

The plan has changed. I was slightly surprised to see in this case that the optimizer used neithor NLJ prefetching, nor NLJ batching, it went all the way back to the traditional nested loop mechanism.

If I had actually captured the original outline hints as an SQL Baseline the optimizer would have found the baseline in the data dictionary, generated this new plan from it, discovered that the plan hash value for this plan didn’t match the plan hash value for the stored plan, and re-optimized the query from scratch - potentially producing a totally different execution plan.

In my example 10g and 11g both wanted to use the nlj_prefetch mechanism when pushed into the nested loop join. 10g, of course, doesn’t have a relevant hint, so I got lucky that 11g wanted to do what 10g had done. In the case of the OP on the OTN forum 11g had decided that it preferred nlj_batching over nlj_prefetch when  attempting to apply the baseline, so the plan hash values didn’t match and the optimizer became free to choose a completely different plan.

Without looking very carefully through all the hints available to 11g I can’t decide whether there are other similar cases to worry about – but if you see 11g ignoring SQL Plan Baselines that have been generated from 10g, then look for operations where 11g has two options (and perhaps two hints) for performing a given operation where 10g has only one option, and perhaps that’s where the problem lies.

Footnotes:

In the case of the OP’s example, a possible workaround could start by setting the hidden parameter “_nlj_batching_enabled”=0; obviously this shouldn’t be done on a production system without approval of Oracle Support, and it’s never a desirable strategy to change a global parameter to fix a local problem – so I’d prefer to set the parameter in a session and generate a new SQL Plan Baseline that would then (probably) include either the nlj_prefetch() hint, or maybe it would turn out to be the no_nlj_batching() hint.

To see what plan (and hints) the SQL Plan Baseline would have generated, the OP enabled SPM tracing using the new event mechanism, in this case:

alter session set events 'trace[RDBMS.SQL_Plan_Management.*]';

-- run, or explain the query here

alter session set events 'trace[RDBMS.SQL_Plan_Management.*] off'; 

ACS

You’ve probably heard about adaptive cursor sharing, and possibly you’ve wondered why you haven’t seen it happening very often on your production systems (assuming you’re running a modern version of Oracle). Here’s a little bug/fix that may explain the non-appearance.

MOS Doc ID 9532657.8 Adaptive cursor sharing ignores SELECTs which are not fully fetched.

This bug is confirmed in 11.2.0.1, and fixed in 11.2.0.3. The problem is that the ACS code doesn’t process the statistical information from the cursor unless the cursor reaches “end of fetch” – i.e. if you don’t select all the data in your query, Oracle doesn’t consider the statistics of that execution when deciding whether or not to re-optimise a statement.

It’s quite possible, of course, for an OLTP system, and particularly a web-based system, to execute a number of that allow the user to fetch data one “page” at a time, and stop before fetching all the data – so this bug (or limitation, perhaps) means that some critical statements in your application may never be re-optimized. If this is the case, and you know that you have some such statements that should generate multiple plans, then you could add the hint /*+ bind_aware */ to the SQL.

Upgrade woes: as ever, when a bug is fixed, it’s possible that a few people will suffer from unfortunate side-effects. In the case of this bug, Oracle may start to re-optimize and generated multiple child cursors for SQL statements that (from your perspective) didn’t need the extra work. If you’re very unlucky this may have an undesirable impact on execution performance, and library cache activity.

Thanks to Leonid Roodnitsky for sending me a note about this bug after attending one of my tutorial days last month.

Index Upgrades

Listening to a presentation by Paul Matuszyk on extended statistics yesterday, I learned something that I should have spotted ages ago. Here’s a little demo script to introduce the point:

create table t1
as
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		level <= 1000)
select
	rownum			id,
	mod(rownum,100)		n1,
	mod(rownum,100)		n2,
	mod(rownum,100)		n3,
	lpad(rownum,10,'0')	small_vc,
	rpad('x',100)		padding
from
	generator	v1,
	generator	v2
where
	rownum <= 1000000;

create index t1_i1 on t1(n1, n2, n3);

-- collect stats, no histograms.

Subquery Factoring

When I wrote a note last week about the fixes to the subquery factoring optimizer code in 11.2.0.3, I finished with a comment about having more to say on the test case if I materialized the subquery. Today’s the day to talk about it. As a reminder, here’s the query, but with the /*+ materialize */ hint in place:

Upgrade Argh

Time for another of those little surprises that catch you out after the upgrade.
Take a look at this “Top N” from a standard AWR report, from an instance running 11.2.0.2

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
direct path read                  3,464,056       6,593      2   33.5 User I/O
DB CPU                                            3,503          17.8
db flash cache single block ph    2,293,604       3,008      1   15.3 User I/O
db file sequential read             200,779       2,294     11   11.6 User I/O
enq: TC - contention                     82       1,571  19158    8.0 Other

System Stats

A quick collation – and warning – for 11.2

• MOS (Metalink): Bug 9842771 - Wrong SREADTIM and MREADTIM statistics in AUX_STATS$ [ID 9842771.8] (needs MOS account)
• Comment from Sokrates in a Charles Hooper blog
• Blog item by Christian Antognini
• Blog item by Randolf Geist

Bottom line – be careful about what you do with system stats on 11.2

Footnote: the MOS link is a search string  producing a list of references. I set it up like that because one of the articles referencing the bug is called “Things to consider before upgrade to 11.2.0.2″ and it’s worth reading.

Addendum: one of the people on the two-day course I’ve just run in Berlin sent me a link for a quick note on how to set your own values for the system stats if you hit this bug. It’s actually quite a reasonable thing to do whether or not you hit the bug given the way that gathering the stats can produce unsuitable figures anyway:  setting system stats. (I’ve also added their company blog to the links on the right, they have a number interesting items and post fairly regularly.)

audsid

Here’s an example of how the passing of time can allow a problem to creep up on you.

A recent client had a pair of logon/logoff database triggers to capture some information that wasn’t available in the normal audit trail, and they had been using these triggers successfully for many years, but one day they realised that the amount of redo generated per hour had become rather large, and had actually been quite bad and getting worse over the last few months for no apparent reason. (You’ve heard this one before … “honest, guv, nothing has changed”).

Upgrades

Here’s a link to a truly ambitious document on Metalink (if you’re allowed to log on):

Doc ID 421191.1: Complete checklist for manual upgrades of Oracle databases from any version to any version on any platform

(Actually it only starts at v6 – but I don’t think there are many systems still running v5 and earlier).

Upgrades

I posted a summary of page hits a little while ago when the WordPress statistics hit 750,000 page views, and commented at the time that a couple of the most popular items seemed to be related to the types of issue you get when you upgrade to 10g.

It occurred to me this morning that I could take a closer look at the stats, because WordPress has a page which gives a fairly comprehensive break down of the views made over time of each page. I’ve repeated the top three from the previous list and added one more, with a brief comment about their popularity over time:

• Cartesian merge join – always popular, but a bit of a jump from Mar 2008
• Bind Variables – no significant change since original publication
• dbms_xplan in 10g - sharp jump from Mar 2008
• Not In – very sharp jump from Mar 2008

It would be very easy to read too much into these numbers – but one of the side effects of an upgrade to 10g is the impact of “Cost Based Query Transformation” and the clever (sometimes too clever) things it does to execution plans; and three of the top four articles are likely to be of interest to people seeing these side effects.  On the other hand, 10g doesn’t really do anything dramatically different with bind variable issues and the views for that topic haven’t changed.

Would it be reasonable to suggest that this might be an indication of a surge of people migrating to 10g in the March time-frame ?