Oracle Scratchpad

December 8, 2013

12c Subqueries

Filed under: 12c,CBO,Execution plans,Oracle,subqueries — Jonathan Lewis @ 11:32 am BST Dec 8,2013

When you upgrade you often find that some little detail (of the optimizer) that didn’t receive a lot of attention in the “New Features” manuals introduces a few dramatic changes in execution plans. Here’s one example of a detail that is likely to catch a few unlucky people. We start with a very simple table which is just and id column with some padding, and then show the effect of a change in the handling of “constant subqueries”. Here’s my data set:


create table t1
as
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		level <= 1e5
)
select
	rownum			id,
	lpad(rownum,6)		small_vc,
	rpad('x',100,'x')	padding
from
	generator
;

begin
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T1',
		method_opt 	 => 'for all columns size 1'
	);
end;
/

alter table t1 add constraint t1_pk primary key (id);

Now I’m going to select all the rows for a couple of ranges of IDs, showing you the execution plan for each case – but you’ll notice that I’ve tried to “hide” the ranges from the optimizer by putting them into a “select from dual”. How does Oracle 12c cope ?

select
	*
from	t1
where	id between (select 10001 from dual)
	   and     (select 20000 from dual)
;

---------------------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       | 10001 |  1103K|   188   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    | 10001 |  1103K|   188   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | T1_PK | 10001 |       |    22   (0)| 00:00:01 |
|   3 |    FAST DUAL                        |       |     1 |       |     2   (0)| 00:00:01 |
|   4 |    FAST DUAL                        |       |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID">= (SELECT 10001 FROM "SYS"."DUAL" "DUAL") AND "ID"<= (SELECT
              20000 FROM "SYS"."DUAL" "DUAL"))

select
	*
from	t1
where	id between (select 10001 from dual)
	   and     (select 90000 from dual)
;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 80002 |  8828K|   218   (4)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   | 80002 |  8828K|   214   (4)| 00:00:01 |
|   2 |   FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |
|   3 |   FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID">= (SELECT 10001 FROM "SYS"."DUAL" "DUAL") AND "ID"<=
              (SELECT 90000 FROM "SYS"."DUAL" "DUAL"))

Notice how 12c had managed to see constant values, even though they are hidden inside subqueries, and produced appropriate plans as the ranges change. Why does this matter ? Here’s the plan for the second query when you set the optimizer_features_enable back to 11.2.0.3:


select
	/*+ optimizer_features_enable('11.2.0.3') */
	*
from	t1
where	id between (select 10001 from dual)
	   and     (select 90000 from dual)
;

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   250 | 28250 |    14   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |   250 | 28250 |    10   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_PK |   450 |       |     2   (0)| 00:00:01 |
|   3 |    FAST DUAL                |       |     1 |       |     2   (0)| 00:00:01 |
|   4 |    FAST DUAL                |       |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID">= (SELECT 10001 FROM "SYS"."DUAL" "DUAL") AND "ID"<=
              (SELECT 90000 FROM "SYS"."DUAL" "DUAL"))

In 11.2.0.3 Oracle produces an estimate of 450 index entries and 250 rows – even for the 80,000 row case. These are the basic “selectivity guesses” for “unknown value”, and show the odd inconsistency that the optimizer displays between index guesses and table guesses.

If you’ve used this “hide the value” type of code, watch out for plan changes on the upgrade.

Footnote:
This change actually appears in the upgrade from 11.2.0.3 to 11.2.0.4 – but I’ve labelled it in the 12c upgrade list because I’m guessing it was introduced in 12.1 and backported to 11.2.0.4 as so many other little details appear to have been. There is a fix-control to stop the effect if you really have to (check with Oracle Support first, of course): ‘alter session set “_fix_control”=’11813257:0″;

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 4,015 other followers