Hints again

A recent posting on OTN came up with a potentially interesting problem – it started roughly like this:

I have two queries like this:

select * from emp where dept_id=10 and emp_id=15;
select * from emp where dept_id=10 and emp_id=16;

When I run them separately I get the execution plan I want, but when I run a union of the two the plans change.

This, of course, is extremely unlikely – even if we assume that the two queries are more complex than the text shown. On the other hand you might, after a little thought, come up with the idea that perhaps the optimizer had done something really clever like join factorization (moving a join that’s common to the two parts of the UNION from inside to outside the UNION), or maybe there’s some really new trick the optimizer had played because a UNION ultimately requires a SORT UNIQUE, and the optimizer had chosen a different path that returned the data from each part of the UNION in sorted order to decrease the cost of that final sort.

In fact it turned out to be a lot simpler than that. The query looked more like this:

select
	/*+
		index(@qb_view_a t1)
		index(@qb_view_b t1)
	*/
	*
from
	t2, qb_view
where
	t2.n1 = 10
and	qb_view.n2  = t2.n2
union
select
	/*+
		index(@qb_view_a t1)
		index(@qb_view_b t1)
	*/
	*
from
	t2, qb_view
where
	t2.n1 = 12                    -- the real code referenced an alternative column here.
and	qb_view.n2  = t2.n2
;

Of particular note is the fact that it’s a join, the join involves a view (guessing from the names in the FROM clause) and there are hints that reference query block names for query blocks that don’t exist – but perhaps are present inside the view. So what does the view look like.

create or replace view qb_view
as
select /*+ qb_name(qb_view_a) */ * from t1
union all
select /*+ qb_name(qb_view_b) */ * from t1
;

It’s a union all view – and the two query blocks named from the outside query are the two halves of the inner union.

Here’s an important thought – it’s quite easy to get Oracle to do what you want in a simple query (at least in the short term) by sticking in a few hints – especially if you create and reference query block names; but when you start compounding queries by combining bits of code that currently do what you want, you may find that Oracle introduces extra query blocks during transformation, and perhaps some of the query blocks you’ve referenced originally cease to exist, so the hints no longer apply.

Let’s look at the execution plan – including the ALIAS and OUTLINE sections – for the final query, and compare it with the execution plan for just one of the two pieces; starting with the single piece first:

explain plan for
select
	/*+
		index(@qb_view_a t1)
		index(@qb_view_b t1)
	*/
	*
from
	t2, qb_view
where
	t2.n1 = 10
and	qb_view.n2  = t2.n2
;

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

--------------------------------------------------
| Id  | Operation                      | Name    |
--------------------------------------------------
|   0 | SELECT STATEMENT               |         |
|   1 |  HASH JOIN                     |         |
|   2 |   TABLE ACCESS FULL            | T2      |
|   3 |   VIEW                         | QB_VIEW |
|   4 |    UNION-ALL                   |         |
|   5 |     TABLE ACCESS BY INDEX ROWID| T1      |
|   6 |      INDEX FULL SCAN           | T1_I1   |
|   7 |     TABLE ACCESS BY INDEX ROWID| T1      |
|   8 |      INDEX FULL SCAN           | T1_I1   |
--------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1     / T2@SEL$1
   3 - SET$1     / QB_VIEW@SEL$1
   4 - SET$1
   5 - QB_VIEW_A / T1@QB_VIEW_A
   6 - QB_VIEW_A / T1@QB_VIEW_A
   7 - QB_VIEW_B / T1@QB_VIEW_B
   8 - QB_VIEW_B / T1@QB_VIEW_B

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"QB_VIEW_A" "T1"@"QB_VIEW_A" ("T1"."N1"))
      INDEX(@"QB_VIEW_B" "T1"@"QB_VIEW_B" ("T1"."N1"))
      USE_HASH(@"SEL$1" "QB_VIEW"@"SEL$1")
      LEADING(@"SEL$1" "T2"@"SEL$1" "QB_VIEW"@"SEL$1")
      NO_ACCESS(@"SEL$1" "QB_VIEW"@"SEL$1")
      FULL(@"SEL$1" "T2"@"SEL$1")
      OUTLINE(@"QB_VIEW_B")
      OUTLINE(@"QB_VIEW_A")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"QB_VIEW_B")
      OUTLINE_LEAF(@"QB_VIEW_A")
      ALL_ROWS
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

The plan shows us that we have used an index to access table t1 (with an index full scan) in both halves of the QB_VIEW’s union all; and the alias section shows us that we have a table t1 in a query block name qb_view_a, and the outline section shows that we have a hint applied in that query block that directs the optimizer to use an index on that table: INDEX(@”QB_VIEW_A” “T1″@”QB_VIEW_A” (“T1″.”N1″)); and we can see the same strategy appearing for a table t1 in query block qb_view_b. By the way, I checked the plan without the hints, and the optimizer chose to do full tablescans on t1 – so the hints were actually having an effect.

So what happens when we check the plan for the UNION of the two variants of the query:

------------------------------------------
| Id  | Operation              | Name    |
------------------------------------------
|   0 | SELECT STATEMENT       |         |
|   1 |  SORT UNIQUE           |         |
|   2 |   UNION-ALL            |         |
|   3 |    HASH JOIN           |         |
|   4 |     TABLE ACCESS FULL  | T2      |
|   5 |     VIEW               | QB_VIEW |
|   6 |      UNION-ALL         |         |
|   7 |       TABLE ACCESS FULL| T1      |
|   8 |       TABLE ACCESS FULL| T1      |
|   9 |    HASH JOIN           |         |
|  10 |     TABLE ACCESS FULL  | T2      |
|  11 |     VIEW               | QB_VIEW |
|  12 |      UNION-ALL         |         |
|  13 |       TABLE ACCESS FULL| T1      |
|  14 |       TABLE ACCESS FULL| T1      |
------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SET$1
   3 - SEL$1
   4 - SEL$1 / T2@SEL$1
   5 - SET$2 / QB_VIEW@SEL$1
   6 - SET$2
   7 - SEL$2 / T1@SEL$2
   8 - SEL$3 / T1@SEL$3
   9 - SEL$4
  10 - SEL$4 / T2@SEL$4
  11 - SET$3 / QB_VIEW@SEL$4
  12 - SET$3
  13 - SEL$5 / T1@SEL$5
  14 - SEL$6 / T1@SEL$6

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$5" "T1"@"SEL$5")
      FULL(@"SEL$6" "T1"@"SEL$6")
      FULL(@"SEL$2" "T1"@"SEL$2")
      FULL(@"SEL$3" "T1"@"SEL$3")
      USE_HASH(@"SEL$1" "QB_VIEW"@"SEL$1")
      LEADING(@"SEL$1" "T2"@"SEL$1" "QB_VIEW"@"SEL$1")
      NO_ACCESS(@"SEL$1" "QB_VIEW"@"SEL$1")
      FULL(@"SEL$1" "T2"@"SEL$1")
      USE_HASH(@"SEL$4" "QB_VIEW"@"SEL$4")
      LEADING(@"SEL$4" "T2"@"SEL$4" "QB_VIEW"@"SEL$4")
      NO_ACCESS(@"SEL$4" "QB_VIEW"@"SEL$4")
      FULL(@"SEL$4" "T2"@"SEL$4")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"SEL$4")
      OUTLINE_LEAF(@"SET$3")
      OUTLINE_LEAF(@"SEL$6")
      OUTLINE_LEAF(@"SEL$5")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SET$2")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$2")
      ALL_ROWS
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

The plan is completely different. We’ve lost the index full scans and we’ve reverted to the tablescans that we would have got from an unhinted query. When you look at the outline you can see why – the query blocks qb_view_a and qb_view_b have disappeared – so the hints are no longer valid. As you can see we now have four occurrences of table t1, but as the alias section shows they come from query blocks sel$2, sel$3, sel$5 and sel$6).

Is this a bug ? I don’t think so. When the optimizer produces the outline information (which can be stored as an SQL Baseline in 11g) it’s producing a set of hints that will be applied at the outermost query block, with hints that point, as necessary, to inner query blocks; this means you can’t use the same query block name twice in a query or the optimizer wouldn’t be able to identify which query block a hint was supposed to apply to. So Oracle has eliminated duplicate query block names and replaced them with the standard internally generated ones – the user’s hints no longer apply.

Footnote: Checking the clock – it took me about 15 minutes to create a simplified model based on the information available on OTN: it’s taken me 75 minutes to describe what I did and what I learned as a result. With a little practice you can get very good at creating models that help you to identify and solve problems very quickly.

How to hint

Here’s a live example demonstrating a point I’ve often made – you have to be very detailed in your hinting or Oracle will find a way to obey your hints and do the wrong thing.  A recent posting on the OTN database forum gave use the following query and execution plan:

SELECT
	ERO.DVC_EVT_ID,
	E.DVC_EVT_DTTM
FROM D1_DVC_EVT E,
     D1_DVC_EVT_REL_OBJ ERO
WHERE
	ERO.MAINT_OBJ_CD = 'D1-DEVICE'
AND	ERO.PK_VALUE1 = :H1
AND	ERO.DVC_EVT_ID = E.DVC_EVT_ID
AND	E.DVC_EVT_TYPE_CD IN (
		'END-GSMLOWLEVEL-EXCP-SEV-1',
		'STR-GSMLOWLEVEL-EXCP-SEV-1'
	)
ORDER BY
	E.DVC_EVT_DTTM DESC

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name       | Starts | Cost (%CPU)| Pstart| Pstop | A-Rows |   A-Time   | Buffers |Reads  |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |            |      1 |  3196 (100)|       |       |    134 |00:00:13.85 |    3195 |  2136 |
|   1 |  SORT ORDER BY                       |            |      1 |  3196   (1)|       |       |    134 |00:00:13.85 |    3195 |  2136 |
|   2 |   NESTED LOOPS                       |            |      1 |            |       |       |    134 |00:00:13.85 |    3195 |  2136 |
|   3 |    NESTED LOOPS                      |            |      1 |  3195   (1)|       |       |   1059 |00:00:07.77 |    2138 |  1197 |
|*  4 |     INDEX RANGE SCAN                 | TEST1      |      1 |    30   (0)|       |       |   1059 |00:00:00.07 |      11 |    11 |
|   5 |     PARTITION RANGE ITERATOR         |            |   1059 |     1   (0)|   KEY |   KEY |   1059 |00:00:07.69 |    2127 |  1186 |
|*  6 |      INDEX UNIQUE SCAN               | D1T400P0   |   1059 |     1   (0)|   KEY |   KEY |   1059 |00:00:07.67 |    2127 |  1186 |
|*  7 |    TABLE ACCESS BY GLOBAL INDEX ROWID| D1_DVC_EVT |   1059 |     2   (0)| ROWID | ROWID |    134 |00:00:06.08 |    1057 |   939 |
-----------------------------------------------------------------------------------------------------------------------------------------

You’ll notice that something close to half the time spent came from the table access in line 7 (This is 11g, and we have a plan which shows the “double nested loop” of an index access followed by a table access – for each rowid returned in line 3 (totalling 7.77 seconds) we access the table through the nested loop driven by line 2 which totals 13.85 seconds).

After a little chat, the suggestion arose to introduce an index that avoided the table access – it’s doing a fairly large amount of random I/O, and we might be able to run the query roughly twice as fast if we didn’t visit it. So the DBA set up a suitable test index (called test2) on the D1_DVC_EVT table, and found that the optimizer didn’t use it (perhaps because the index was larger then the alternative, perhaps because the clustering_factor was much bigger) – so he added a hint to the code: /*+ index (e test2) */ which made Oracle use the index to produce the following plan:

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Starts | Cost (%CPU)| Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |      1 | 98415 (100)|       |       |    134 |00:04:11.82 |     100K|  96848 |
|   1 |  SORT ORDER BY              |       |      1 | 98415   (1)|       |       |    134 |00:04:11.82 |     100K|  96848 |
|*  2 |   HASH JOIN                 |       |      1 | 98414   (1)|       |       |    134 |00:04:11.82 |     100K|  96848 |
|*  3 |    INDEX RANGE SCAN         | TEST1 |      1 |    30   (0)|       |       |   1059 |00:00:00.01 |      11 |      0 |
|   4 |    PARTITION RANGE ALL      |       |      1 | 98249   (1)|1048575|     1 |   7566K|00:03:34.58 |     100K|  96848 |
|   5 |     PARTITION RANGE SUBQUERY|       |    287 | 98249   (1)|KEY(SQ)|KEY(SQ)|   7566K|00:03:10.87 |     100K|  96848 |
|*  6 |      INDEX FULL SCAN        | TEST2 |   2296 | 98249   (1)|1048575|     1 |   7566K|00:02:45.47 |   97412 |  96848 |
----------------------------------------------------------------------------------------------------------------------------

Unfortunately, although Oracle obeyed the hint – it had to, since it was legal and in-context – it didn’t take the path the DBA expected.

When you hint, you have to make it impossible for Oracle find any path you don’t want, and that can take a lot of hints. In this case the DBA simply wanted to use the same nested loop path that he’d originally seen, but using the new index instead. To get the path safely he needed at least 4 hints: one to specify the join order, one to specify the join method, and one for each table to specify the access method. In this case:

/*+
        leading(ero e)
        use_nl(ero e)
        index(ero test1)
        index(e test2)
*/

Once you’ve hinted some SQL and got it working the safe thing to do, in 11g, is to check the outline section of the actual execution plan to see if you’ve missed any important hints and then, if you can’t change the production code, attach the SQL Baseline from your hinted code to the SQL text from the original. (See – for example: http://jonathanlewis.wordpress.com/2011/01/12/fake-baselines/ )

It’s hard to create a full set of hints by hand – and I often see hinted SQL in production systems where the plan that appears happens to be the right one but it’s not the only plan that could be derived from the hints. So my 11g mantra for hinting is this: “if you can hint it, baseline it”.

 

Dynamic Sampling – 2

I’ve written about dynamic sampling in the past, but here’s a little wrinkle that’s easy to miss. How do you get the optimizer to work out the correct cardinality for a query like (the table creation statement follows the query):

select	count(*)
from	t1
where	n1 = n2
;

create table t1
as
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		level <= 1e4
)
select
	mod(rownum, 1000)	n1,
	mod(rownum, 1000)	n2
from
	generator	v1,
	generator	v2
where
	rownum <= 1e6 ; 

If you’re running 11g and can changed the code there are a couple of easy options – adding a virtual column, or applying extended stats and then modifying the SQL accordingly would be appropriate.

 -- Virtual Column 
alter table t1 add (
 	n3	generated always as ( case n1 when n2 then 1 end) virtual 
) 
; 

execute dbms_stats.gather_table_stats(user,'t1',method_opt=>'for columns n3 size 1')

-- Extended Stats

begin
	dbms_output.put_line(
		dbms_stats.create_extended_stats(
			ownname		=> user,
			tabname		=> 'T1',
			extension	=> '(case n1  when n2 then 1 else null end)'
		)
	);

	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T1',
		block_sample 	 => true,
		method_opt 	 => 'for columns (case n1  when n2 then 1 else null end) size 1'
	);
end;
/

select	count(*)
from	t1
where	(case n1 when n2 then 1 else null end)= 1
;

If you can’t change the SQL statement, there’s always the option for bypassing the problem by fixing a suitable execution plan with an SQL Baseline, of course. Alternatively, if you can think of the right hint you could create an “SQL Patch” for the statement – but what hint might be appropriate ? I’ll answer that question in a minute.

Here’s another option, though: get Oracle to use dynamic sampling. (You probably guessed that from the title of the post.) So which level would you use to make this work ? Left to its own devices, Oracle would calculate the selectivity of the predicate n1 = n2 as the smaller of the two separate predicates “n1 = unknown” and “n2 = unknown”. So you might hope that level 3 (Oracle is “guessing”) or level 4 (more than one predicate on a single table) might be appropriate. It’s the latter that works. If you execute “alter session set optimizer_dynamic_sampling=4;” before executing this query, Oracle will sample the table before optimising.

The method works, but can you apply it ? Possibly not, if you’re not allowed to inject any extra SQL anywhere – after all, you probably don’t want to set the parameter at the system level (spfile or init.ora) because it may affect lots of other queries – introducing more work because of the sample, and then risking unexpected changes in execution plans. Setting the parameter for a session is often no better. And this brings me back to the SQL Patch approach – if you don’t want to create a baseline for the query then perhaps a patch with the hint /*+ opt_param(‘optimizer_dynamic_sampling’ 4) */ will do the trick. Don’t forget all the doubling of single quotes that you’ll need, though (this is the code fragment I used):

begin
	sys.dbms_sqldiag_internal.i_create_patch(
		sql_text	=>
'
select
	count(*)
from	t1
where	n1 = n2
',
		hint_text	=> 'opt_param(''optimizer_dynamic_sampling'' 4)'

	);
end;
/

For more analysis and commentary on the SQL Patch mechanism, you might like to read Dominic Brooks’ mini-series:

•  part 1
• part 2
• part 3
• part 4

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'; 

Subquery Factoring

I have a small collection of postings where I’ve described anomalies or limitations in subquery factoring (the “with subquery”, or Common Table Expression (CTE) to give it the official ANSI name). Here’s another example of Oracle’s code not behaving consistently. You may recognise the basic query from yesterday’s example of logical tuning – so I won’t reprint the code to generate the data sets. This examples in this note were created on 11.2.0.2 – we start with a simple query and its execution plan:

Logical tuning

Here’s a model of a problem I solved quite recently at a client site. The client’s query was much more complex and the volume of data much larger, but this tiny, two table, example is sufficient to demonstrate the key principle. (Originally I thought I’d have to use three tables to model the problem, which is why you may find my choice of table names a little odd). I ran this example on 11.2.0.2 – which was the client version:

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:

Quiz Night

Browsing a little history recently I came across a note I’d written about the new-style index hint. In that note I claimed that:

… the index has to start with the columns (product_group, id) in that order – with preference given to an exact match, otherwise using the lowest cost index that starts the right way.

On reading this statement I suddenly realised that I hadn’t actually proved (to myself, even) that if I had the indexes (product_group, id) and (product_group, id, other_col) then a two-column hint forced Oracle to use the two column index in all (legal) circumstances.

So, tonight’s quiz – are there any edge cases, and what easy ways can you think of to prove (or disprove) the claim for the general case.

Footnote: you don’t have to demonstrate the method, just a brief outline of the idea will be sufficient.

Index Hints

A new form of index hint appeared in 10g – and it’s becoming more common to see it in production code; instead of naming indexes in index hints, we describe them. Consider the following hint (expressed in two ways, first as it appeared in the outline section of an execution plan, then cosmetically adjusted to look more like the way you would write it in your SQL):

INDEX(@"SEL$1" "PRD"@"SEL$1" ("PRODUCTS"."PRODUCT_GROUP" "PRODUCTS"."ID"))
index(@sel$1 prd@sel$1(product_group  id))

Hinting

As I’ve often pointed out, this blog isn’t AskTom, or the OTN forum, so I don’t expect to have people asking me to solve their problems; neither do I answer email questions about specific problems. Occasionally, though, questions do appear that are worth a little public airing, and one of these came in by email a couple of weeks ago. The question is longer than the answer I sent, my contribution to the exchange doesn’t start until the heading: “My Reply”.

How to hint – 1

Here’s a quick tutorial in hinting, promped by a question on the OTN database forum.
The OP has a hash semi-join and Oracle appears to be ignoring a hint to use a nested loop:

Ignoring hints

Yes, finally, really ignoring hints – but it’s a sort of bug, of course.

Thanks to Timur Akhmadeev for telling us about bug 8432870 (you’ll need an account on MOS for the link) in his reply to Charles Hooper’s recent post.

In the upgrade from 9i to 10g there was a change in the “hint parser”. If you put a valid SQL keyword inside the hint delimiters (the note says /*+  */ but doesn’t mention the –+ alternative for specifying a hint, thought it’s probably still true there) when the keyword is not a valid hint – for example the word NOLOGGING which I have seen people use as if it were a hint – then Oracle will ignore all the hints.

Earlier versions of Oracle simply noticed that you had embedded something that wasn’t a valid hint, but that didn’t stop the parser from reading the rest of the hints correctly.

If the invalid hint is not a valid SQL keyword then there are no nasty side effects.

This might explain why I ran into an odd problem a little while ago when I added a comment to my  hint list and found that the hints stopped working. I can’t remember the exact details any more but I think my comment was something along the lines of: “Do not … because …”, and this broke the hints until I changed it to “Don’t … because …”.

[Further reading on "ignoring hints"]

 

Quiz Night

I have four simple (non-partitioned, non-clustered, not views, not object type – really I’m not trying to be cunning or devious here) heap tables, and write a query that joins them:

select
	/*+
		leading(t1 t2 t3 t4)
		use_hash(t2) use_hash(t3) use_hash(t4)
	*/
	count(t1.small_vc),
	count(t2.small_vc),
	count(t3.small_vc),
	count(t4.small_vc)
from
	t1,
	t2,
	t3,
	t4
where
	t2.id2 = t1.id1
and	t3.id3 = t2.id2
and	t4.id4 = t3.id3
;

ANSI – argh

I’m not keen on ANSI standard SQL – even though it is, technically, the strategic option and even though you have to use it for full outer joins and partitioned outer joins.

One reason for disliking it is that it “separates join predicates from filter predicates” – a reason often given in praise of the syntax which, to my mind, claims a spurious distinction and introduces a mechanism that makes it harder to keep mental track of what’s going to happen as you walk  through the join order. (I have to admit that I was temporarily fooled into thinking it was quite a nice idea – in an abstract sort of way.)

Collection Costs

Here’s an extract from an execution plan I found on a client site recently. I’ve collapsed lines 5 to 42 into a single line representing the rowsource produced by a fairly messy execution plan, leaving just the last three stages of execution on view. Each of three operations joins the same collection variable (using the table() operator) to the row source – once through a hash join, then twice more (joining to two other columns) through nested loop outer joins:

The resulting estimates of row counts and costs are quite entertaining and, fortunately, not very accurate: