Oracle Scratchpad

February 6, 2014

12c fixed subquery

Filed under: 12c,Execution plans,Oracle,subqueries — Jonathan Lewis @ 2:25 pm BST Feb 6,2014

Here’s a simple little demonstration of an enhancement to the optimizer in 12c that may result in some interesting changes in execution plans as cardinality estimates change from “guesses” to accurate estimates.


create table t1
as
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		level <= 1e4
)
select
	rownum				id,
	trunc(sysdate) + rownum		d1
from
	generator	v1
;

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

set autotrace traceonly explain

select * from t1 where id > (select 9900 from dual);

set autotrace off

Since there are no indexes in sight the execution plan has to be a tablescan. The interesting thing, though, is the optimizer’s prediction for the number of rows returned. If you look at the code you can work out that the actual result set should be 100; but Oracle has used a standard “guess” of 5% for predicates of the form “column greater than (scalar subquery)”, and sure enough, here’s the plan for 11.2.0.3


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   500 |  2000 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   500 |  2000 |     3   (0)| 00:00:01 |
|   2 |   FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"> (SELECT 9900 FROM "SYS"."DUAL" "DUAL"))

However, after upgrading to 11.2.0.4, the numbers change:

12c plan - shows correct cardinality estimate
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 |   400 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   100 |   400 |     3   (0)| 00:00:01 |
|   2 |   FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"> (SELECT 9900 FROM "SYS"."DUAL" "DUAL"))

Shades of precompute_subquery – it looks like the optimizer recognizes that the subquery has to return a constant, and extracts that constant for use in the plan. Strangely, though, I can’t see any sign in the 10053 trace file of the subquery value being used until the plan just magically appears with the right estimate.

6 Comments »

  1. I encountered some developers recently using this kind of scalar subquery as a way of “fooling” the optimiser into giving a lower cost estimate (and choosing to use their precious index). I had to explain who was really being fooled.

    Just to clarify, is this new behaviour observed in 12c or 11.2.0.4 (or both)?

    Comment by Dan Jankowski — February 6, 2014 @ 2:40 pm BST Feb 6,2014 | Reply

    • Dan,
      The new behaviour appears in 11.2.0.4 and 12.1.0.1
      Co-incidentally this morning’s “hot topics” email from Metalink included this:
      Bug 18153127 : SUBOPTIMAL EXECUTION PLAN OF INSERT…SELECT OF MV COMPLETE REFRESH IN 11.2.0.4

      Glancing through the bug, there’s a change of plan on upgrading from 11.2.0.3 to 11.2.0.4 which is a classic “switch from hash join A->B to nested loop B->A”; which often means a change in the cardinality estimate of A (from large to small).

      But there’s something that looks suspiciously like a single row subquery in there which might mean that A has got an “no longer guessed” estimate. (I wouldn’t be convinced without seeing the query, outline, and predicate sections, though, as the plan suggests the subquery is called through a FILTER, rather than being pushed, as in my example, which I had thought was a necessity for this change to appear.)

      Comment by Jonathan Lewis — February 6, 2014 @ 3:10 pm BST Feb 6,2014 | Reply

  2. This is Bug #11813257, controllable with _fix_control

    Comment by Timur Akhmadeev — February 6, 2014 @ 5:33 pm BST Feb 6,2014 | Reply

    • Timur,

      Thanks for digging that one out.

      I have to say, I’ve never viewed it as a bug, just the natural consequence of the optimizer’s standard approach to handling “column range-based-operator (subquery)” – so I was thinking of the 12.1 behaviour as one of the many little enhancements that have appeared over the years.

      How long will it be, I wonder, before we find a bug note on MoS that says getting the correct cardinality estimate is undesirable behaviour ? ;)

      Comment by Jonathan Lewis — February 6, 2014 @ 5:46 pm BST Feb 6,2014 | Reply

  3. […] been about 8 months since I posted a little note about a “notable change in behaviour” of the optimizer when dealing with […]

    Pingback by 12c Fixed Subquery | Oracle Scratchpad — September 29, 2014 @ 4:18 pm BST Sep 29,2014 | Reply


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

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,267 other followers