Subquery Anomaly

Here’s an oddity that appeared on the OTN database forum last night:

We have this query in our application which works fine in 9i but fails in 11gR2 (on Exadata) giving an “ORA-00937: not a single-group group function” error….

… The subquery is selecting a column and it doesn’t have a group by clause at all. I am not sure how is this even working in 9i. I always thought that on a simple query using an aggregate function (without any analytic functions / clause), we cannot select a column without having that column in the group by clause. So, how 11g behaves was not a surprise but surprised to see how 9i behaves. Can someone explain this behaviour?

The poster supplied the suspect query, and it certainly looked as if it should never have worked – but I took a guess that the optimizer was doing some sort of transformation that concealed the problem before the optimizer managed to see the error. The subquery was a little odd because it was doing something it didn’t need to do, and my was guess that the optimizer had recognised the option to simplify the query and the simplification had “accidentally” removed the error. This turned out to be correct, but my guess about exactly what had happened to hide the error was wrong.

Having created a hypothesis I couldn’t resist checking it this morning, so here’s the test case (don’t pay any attention to the actual data I’ve generated, it was a cut-n-paste from a script that I had previously used for something completely different):

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
;

begin
 	dbms_stats.gather_table_stats(
 		user,
 		't1',
 		method_opt => 'for all columns size 1'
	);

	dbms_stats.gather_table_stats(
		user,
		't2',
		method_opt => 'for all columns size 1'
	);
end;
/

explain plan for
select
	/*+ qb_name(main) */
	*
from t1
where (n2,n1) in (
	select /*+
			qb_name(subq)
			unnest
		*/
		max(t2.n2), t2.n1
	from t2
	where t2.n1 = t1.n1
)
;

You’ll notice, of course, that I don’t have a group by clause at all, so the presence of the t2.n1 in the select list should lead to Oracle error: “ORA-00937: not a single-group group function”.

In versions from 8i to 11.1.0.7, this query could run, and its execution plan looked looked like this:

----------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost  |
----------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |   200 | 45200 |    46 |
|*  1 |  HASH JOIN           |         |   200 | 45200 |    46 |
|   2 |   VIEW               | VW_SQ_1 |   200 |  7800 |    31 |
|   3 |    HASH GROUP BY     |         |   200 |  2400 |    31 |
|   4 |     TABLE ACCESS FULL| T2      |  3000 | 36000 |    14 |
|   5 |   TABLE ACCESS FULL  | T1      |  3000 |   547K|    14 |
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("N2"="MAX(T2.N2)" AND "N1"="N1" AND "ITEM_1"="T1"."N1")

Notice how the optimizer has produced an inline view (VW_SQ_1) from the subquery, using it to drive a hash join; notice how that inline view has an aggregation operation (HASH GROUP BY) in it. In effect the optimizer has rewritten my query like this:

select
	t1.*
from	(
		select
			distinct max(t2.n2) max_n2, t2.n1 item_1, t2.n1
		from	t2
		group by
			t2.n1
	)	vw_sq_1,
	t1
where
	t1.n2 = vw_sq_1.max_n2
and	t1.n1 = vw_sq_1.n1
and	t1.n1 = vw_sq_1.item_1
;

There’s a clue about why this succeeded in the 10053 trace file, which includes the lines:

"Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on query block SEL$1 (#1).
SU:   Checking validity of unnesting subquery SEL$2 (#2)
SU:   Passed validity checks.

Compared to the 11.2 lines:

Subquery Unnesting on query block MAIN (#1)SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on query block MAIN (#1).
SU:   Checking validity of unnesting subquery SUBQ (#2)
SU:     SU bypassed: Failed basic validity checks.
SU:   Validity checks failed.

Whatever check it was that Oracle introduced in 11.2 (maybe a check that the query block was inherently legal), unnesting failed – and if I add an /*+ no_unnest */ hint to the original subquery in the earlier versions of Oracle I get the expected ORA-00937.

The philosophical argument is left to the reader: was the original behaviour a bug, or is the new behaviour the bug ?

 

12c pq_replicate

Another day, another airport lounge – another quick note: one of the changes that appeared in 12c was a tweak to the “broadcast” distribution option of parallel queries. I mentioned this in a footnote to a longer article a couple of months ago; this note simply expands on that brief comment with an example. We’ll start with a simple two-table hash join – which I’ll first construct and demonstrate in 11.2.0.4:

create table t1
as
with generator as (
	select	--+ materialize
		rownum 	id
	from	all_objects
	where	rownum <= 3000
)
select
	rownum				n1,
	lpad(rownum,6,'0')		small_vc,
	lpad(rownum,200,'0')		padding
from
	generator	v1,
	generator	v2
where
	rownum <= 1000
;

create table t2
as
with generator as (
	select	--+ materialize
		rownum 	id
	from	all_objects
	where	rownum <= 3000
)
select
	1 + mod(rownum,10000)			n1,
	lpad(1 + mod(rownum,10000),6,'0')	small_vc,
	lpad(rownum,500,'0')			padding
from
	generator	v1,
	generator	v2
where
	rownum <= 20000 ;

-- collect stats, no histograms.

select
  	/*+
  		leading(t1 t2)
 		parallel(t1 2)
 		parallel(t2 2)
 		use_hash(t2)
 	*/
 	t1.padding,
 	t2.padding
from 	t1, t2
where	t2.n1 = t1.n1
and	t2.small_vc = t1.small_vc
;

-------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost  |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |  1000 |   707K|   135 |        |      |            |
|   1 |  PX COORDINATOR         |          |       |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10001 |  1000 |   707K|   135 |  Q1,01 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN            |          |  1000 |   707K|   135 |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE          |          |  1000 |   207K|     4 |  Q1,01 | PCWP |            |
|   5 |      PX SEND BROADCAST  | :TQ10000 |  1000 |   207K|     4 |  Q1,00 | P->P | BROADCAST  |
|   6 |       PX BLOCK ITERATOR |          |  1000 |   207K|     4 |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL| T1       |  1000 |   207K|     4 |  Q1,00 | PCWP |            |
|   8 |     PX BLOCK ITERATOR   |          | 20000 |     9M|   131 |  Q1,01 | PCWC |            |
|   9 |      TABLE ACCESS FULL  | T2       | 20000 |     9M|   131 |  Q1,01 | PCWP |            |
-------------------------------------------------------------------------------------------------

In this plan slave set 2 scans table t1 in parallel and broadcasts the result set to slave set 1 (lines 5 – 7). The significance of the broadcast option is that each slave in slave set 2 sends all the rows it has read to every slave in slave set 1. For a fairly large table with a high degree of parallelism this could be a lot of inter-process communication; the total number of rows passing through the PX message pool is “DOP x number of row filtered from t1″.

After a slave in slave set 1 has receive the whole of the t1 result set it builds an in-memory hash table and starts scanning rowid ranges (PX BLOCK ITERATOR) from table t2, probing the in-memory hash table to effect the join (lines 3,4, 8,9). Since each slave has a copy of the whole result set from t1 it can scan any chunk of t2 and handle the contents locally. Moreover, because slave set 1 isn’t reading its second input from a virtual table it is able to write its output immediately the virtual table (:TQ10001) that feeds the query coordinator with the result (lines 1,2) – we don’t have to do a “hash join buffered” operation and buffer the entire second input before starting to execute the join.

So how does 12c change things. With the same starting data and query, here’s the execution plan:

-----------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost  |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |  1000 |   707K|   135 |        |      |            |
|   1 |  PX COORDINATOR       |          |       |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM) | :TQ10000 |  1000 |   707K|   135 |  Q1,00 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN          |          |  1000 |   707K|   135 |  Q1,00 | PCWP |            |
|   4 |     TABLE ACCESS FULL | T1       |  1000 |   207K|     4 |  Q1,00 | PCWP |            |
|   5 |     PX BLOCK ITERATOR |          | 20000 |     9M|   131 |  Q1,00 | PCWC |            |
|   6 |      TABLE ACCESS FULL| T2       | 20000 |     9M|   131 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------

Notice, in particular, that we only have one virtual table (or table queue :TQ10000) rather than two – and that’s from a parallel query slave set to the query co-ordinator, parallel to serial; the query only uses one set of parallel query slaves. Until you run the query with rowsource execution statistics enabled and look at the output from v$pq_tqstat it’s not going to be immediately obvious what has happened, but we should see that somehow Oracle is no longer broadcasting the first table even though it’s still doing something in parallel with both tables.

The run-time statistics confirm that we’ve only used one set of slaves, and each slave in the slave set has scanned the whole of table t1. This means each slave can build the full hash table and then go on to read rowid ranges from table t2. We’ve managed to get the benefit of broadcasting t1 (every slave has the whole of t1 so we don’t have to scan and distribute the big table t2 through the PX message pool) but we haven’t had to clone it multiple times through the PX message pool.

Clearly there’s a trade-off here that Oracle Corp. has decided is worth considering. I’m guessing it’s biased towards Exadata where you might run queries with a very high degree of parallelism. In that case the overhead of task switching as large numbers of messages are passed around may (and this is pure supposition) be greater than the added cost of loading the table into the buffer cache (of each instance) and having each slave scan it from there. (Reminder – 11g introduced two “opposite” changed to tablescans: “serial direct reads” and “parallel in-memory scans”.)

There’s one little oddity in this replication – there’s a pair of hints: pq_replicate and no_pq_replicate to control the effect if you think the optimizer is making the wrong choice. I would have guessed that in my example the hint would read: /*+ pq_replicate(t1) */ as it’s table t1 that is read by every single slave. Strangely, though, this is what the outline section of the execution plan showed:

  /*+
      BEGIN_OUTLINE_DATA
      PQ_REPLICATE(@"SEL$1" "T2"@"SEL$1")
      PQ_DISTRIBUTE(@"SEL$1" "T2"@"SEL$1" BROADCAST NONE)
      USE_HASH(@"SEL$1" "T2"@"SEL$1")
      LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
      FULL(@"SEL$1" "T2"@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      OPT_PARAM('_optimizer_cost_model' 'io')
      DB_VERSION('12.1.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Notice how the hint specifies table t2, not table t1 !

Footnote

Here’s a little anomaly,  and a generic warning about “optimizer_features_enable”: I found that if I used the hint /*+ optimizer_features_enable(’11.2.0.4′) */ in 12c I could still get the pq_replicate() hint to work. Unfortunately there are a few places where the hint (or parameter) isn’t guaranteed to take the optimizer code backwards the full 100%.

Empty Hash

A little while ago I highlighted a special case with the MINUS operator (that one of the commentators extended to include the INTERSECT operator) relating to the way the second subquery would take place even if the first subquery produced no rows. I’ve since had an email from an Oracle employee letting me know that the developers looked at this case and decided that it wasn’t feasible to address it because – taking a wider view point – if the query were to run parallel they would need a mechanism that allowed some synchronisation between slaves so that every slave could find out that none of the slaves had received no rows from the first subquery, and this was going to lead to hanging problems.

The email reminded me that there’s another issue of the same kind that I discovered several years ago – I thought I’d written it up, but maybe it was on a newsgroup or forum somewhere, I can’t find it on my blog or old website). The problem can be demonstrated by this example:

create table t1
as
select
	rownum			id,
	mod(rownum,25)		rep_col,
	rpad('x',50)		padding
from
	all_objects
where
	rownum <= 3000
;

delete from t1 where rep_col = 12;

create table t2
as
select
	rownum			id,
	mod(rownum,25)		rep_col,
	rpad('x',50)		padding
from
	all_objects
where
	rownum <= 10000
;

-- collect stats, no histograms

select
	/*+
		leading(t1 t2)
		use_hash(t2)
		no_swap_join_inputs(t2)
		pq_distribute(t2, hash, hash)
	*/
	count(*)
from
	t1,t2
where
	t1.rep_col = 12
and	t2.id = t1.id
;

You’ll notice that I’ve created a data set for table t1 where the values 12 does not appear in column rep_col; so my query will return no rows. However, for the purposes of demonstration, I’ve hinted an execution path that will scan t1 and distribute it by hash, then scan t2 to distribute that by hash before doing the join. Here’s the plan – which I’ve generated with rowsource execution statistics enabled and pulled from memory after executing the query:

-------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name     | Starts | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |          |      1 |      1 |00:00:00.01 |       6 |      2 |
|   1 |  SORT AGGREGATE            |          |      1 |      1 |00:00:00.01 |       6 |      2 |
|   2 |   PX COORDINATOR           |          |      1 |      2 |00:00:00.01 |       6 |      2 |
|   3 |    PX SEND QC (RANDOM)     | :TQ10002 |      0 |      0 |00:00:00.01 |       0 |      0 |
|   4 |     SORT AGGREGATE         |          |      2 |      2 |00:00:00.01 |       0 |      0 |
|*  5 |      HASH JOIN             |          |      2 |      0 |00:00:00.01 |       0 |      0 |
|   6 |       JOIN FILTER CREATE   | :BF0000  |      2 |      0 |00:00:00.01 |       0 |      0 |
|   7 |        PX RECEIVE          |          |      2 |      0 |00:00:00.01 |       0 |      0 |
|   8 |         PX SEND HASH       | :TQ10000 |      0 |      0 |00:00:00.01 |       0 |      0 |
|   9 |          PX BLOCK ITERATOR |          |      2 |      0 |00:00:00.01 |      54 |     27 |
|* 10 |           TABLE ACCESS FULL| T1       |     27 |      0 |00:00:00.01 |      54 |     27 |
|  11 |       PX RECEIVE           |          |      0 |      0 |00:00:00.01 |       0 |      0 |
|  12 |        PX SEND HASH        | :TQ10001 |      0 |      0 |00:00:00.01 |       0 |      0 |
|  13 |         JOIN FILTER USE    | :BF0000  |      2 |      2 |00:00:00.01 |     116 |     87 |
|  14 |          PX BLOCK ITERATOR |          |      2 |      2 |00:00:00.01 |     116 |     87 |
|* 15 |           TABLE ACCESS FULL| T2       |     29 |      2 |00:00:00.01 |     116 |     87 |
-------------------------------------------------------------------------------------------------

Since this was 11.2.0.4 Oracle has used Bloom filtering to reduce the traffic between slave sets – but you can see that despite returning no rows from t1 (lines 7 – 10), Oracle still performed the parallel tablescan of t2 (lines 12 – 15). Thanks to the Bloom filter we don’t transfer 10,000 rows between slave sets, but we can see from the Buffers and Reads columns that we really did do the tablescan – and if we take a snapshot of instance activity we would have seen 10,000 rows fetched by tablescan at that point.

If I ran this query serially Oracle would stop after discovering that the first tablescan returned no rows – why bother scanning the probe table when the hash table is empty ? But because the query is running in parallel, a single slave that receives no data from the first tablescan cannot assume that every other slave in the same set has also received no data – there’s no cross-chat that allows the slaves to discover that every slave has no data – so the second scan goes ahead.

I was a little surprised by this when I first found it since I thought (from looking at some of the 1039x trace information) that the parallel slaves were telling the query coordinator how many rows they had acquired on each granule – which would allow the coordinator to spot the zero total. But it looks as if I was misinterpreting the trace.

On the plus side for this example – it’s probably the case that if zero is a possible volume of data returned by the query then the optimizer will have decided that it was going to get a “small” data set for the build table and therefore do a broadcast distribution – and if that happens the second tablescan won’t occur – as we see below (note the zero Reads, zero Buffers):

------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name     | Starts | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |      1 |      1 |00:00:00.01 |       6 |      2 |
|   1 |  SORT AGGREGATE           |          |      1 |      1 |00:00:00.01 |       6 |      2 |
|   2 |   PX COORDINATOR          |          |      1 |      2 |00:00:00.01 |       6 |      2 |
|   3 |    PX SEND QC (RANDOM)    | :TQ10001 |      0 |      0 |00:00:00.01 |       0 |      0 |
|   4 |     SORT AGGREGATE        |          |      2 |      2 |00:00:00.01 |       0 |      0 |
|*  5 |      HASH JOIN            |          |      2 |      0 |00:00:00.01 |       0 |      0 |
|   6 |       PX RECEIVE          |          |      2 |      0 |00:00:00.01 |       0 |      0 |
|   7 |        PX SEND BROADCAST  | :TQ10000 |      0 |      0 |00:00:00.01 |       0 |      0 |
|   8 |         PX BLOCK ITERATOR |          |      2 |      0 |00:00:00.01 |      54 |     27 |
|*  9 |          TABLE ACCESS FULL| T1       |     27 |      0 |00:00:00.01 |      54 |     27 |
|  10 |       PX BLOCK ITERATOR   |          |      0 |      0 |00:00:00.01 |       0 |      0 |
|* 11 |        TABLE ACCESS FULL  | T2       |      0 |      0 |00:00:00.01 |       0 |      0 |
------------------------------------------------------------------------------------------------

Unfortunately the same type of argument can’t be used to dismiss the minus/intersect cases.

 

Predicate Order

Common internet question: does the order of predicates in the where clause make a difference.
General answer: It shouldn’t, but sometimes it will thanks to defects in the optimizer.

There’s a nicely presented example on the OTN database forum where predicate order does matter (between 10.1.x.x and 11.1.0.7). Notnne particularly – there’s a script to recreate the issue; note, also, the significance of the predicate section of the execution plan.
It’s bug 6782665, fixed in 11.2.0.1

RAC Plans

Recently appeared on Mos – “Bug 18219084 : DIFFERENT EXECUTION PLAN ACROSS RAC INSTANCES”

Now, I’m not going to claim that the following applies to this particular case – but it’s perfectly reasonable to expect to see different plans for the same query on RAC, and it’s perfectly possible for the two different plans to have amazingly different performance characteristics; and in this particular case I can see an obvious reason why the two nodes could have different plans.

Here’s the query reported in the bug:

SELECT /*+ INDEX(C IDX3_GOD_USE_LOT)*/
   PATTERN_ID, STB_TIME
    FROM mfgdev.MTR_AUTO_GOD_AGENT_BT C
   WHERE 1 = 1
     AND EXISTS (SELECT /*+ INDEX(B IDX_MTR_STB_LOT_EQP)*/ 1
            FROM MFGDEV.MTR_STB_BTH B
           WHERE B.PATTERN_ID = C.PATTERN_ID
             AND B.STB_TIME = C.STB_TIME
             AND B.ACTUAL_START_TIME < SYSDATE
             AND EXISTS (SELECT /*+ INDEX(D CW_LOTID)*/
                   1
                    FROM F14DM.DM_CW_WIP_BT D
                   WHERE D.LOT_ID = B.LOT_ID
                     AND D.SS = 'BNKI'));

See the reference to “sysdate”. I can show you a system where you had a 15 minute window each day (until the problem was addressed) to optimize a particular query if you wanted a good execution plan; if you optimized it any other time of day you got a bad plan – and the problem was sysdate: it acts like a peeked bind variable.

Maybe, on this system, if you optimize the query at 1:00 am you get one plan, and at 2:00 am you get another – and if those two optimizations occur on different nodes you’ve just met the conditions of this bug report.

Here’s another thought to go with this query: apparently it’s caused enough problems in the past that someone’s written a couple of hints into the code. With three tables and two correlated subqueries in the code a total of three index() hints is not enough. If you’re going to hard-code hints into a query then take a look at the outline it generates when it does the right thing, and that will tell you about the 15 or so hints you’ve missed out. (Better still, consider generating an SQL Baseline from the hinted code and attaching it to the unhinted code.)

12c fixed subquery

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.

Minus

Here’s a little script to demonstrate an interesting observation that appeared in my email this morning (that’s morning Denver time):

create table t1
as
select * from all_objects where rownum = 1;

delete from t1;
commit;

create table t2
as
select * from all_objects where rownum <= 100000;

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

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

alter system flush buffer_cache;
alter session set events '10046 trace name context forever, level 8';

prompt  ======================
prompt  And now the test query
prompt  ======================

select * from t1
minus
select * from t2
;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
alter session set events '10046 trace name context off';

Clearly the first query block in the test query will return no rows, and since the MINUS operator returns rows from the first result set that do not appear in the second result set there is no need for Oracle to run the second query block. Well, guess what …

The ‘create where rownum = 1′ followed by ‘delete’ is a lazy workaround to avoid side effects of deferred segment creation so that you can run the script on any (recent) version of Oracle. The flush, combined with 10046 trace, allowed me to see waits that showed which objects Oracle scanned and when, and the display_cursor() was just icing on the cake.

I’ve checked 11.2.0.4 and 12.1.0.1, and both of them scan t1 first and then scan t2 unnecessarily.

This surprised me slightly given how smart the optimizer can be, but I guess it’s one of those boundary cases where the optimizer has just one strategy for an entire class of queries. I couldn’t think of any “legal” way to control the effect, but here’s the first dirty trick that came to my mind. If you’re sure that the first subquery is going to be cheap and you’re worried that the second subquery is expensive, you could do the following:

select v2.*
from
	(select * from t1 where rownum = 1)	v1,
	(
		select * from t1
		minus
		select * from t2
	)	v2
;

Introduce a spurious query to return one row from the first subquery and join it do the MINUS query. If the inline view doesn’t return any rows Oracle short-circuits the join, as shown by the following execution path with stats:

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |      1 |        |      0 |00:00:00.01 |       2 |     32 |       |       |          |
|   1 |  MERGE JOIN CARTESIAN  |      |      1 |      1 |      0 |00:00:00.01 |       2 |     32 |       |       |          |
|   2 |   VIEW                 |      |      1 |      1 |      0 |00:00:00.01 |       2 |     32 |       |       |          |
|*  3 |    COUNT STOPKEY       |      |      1 |        |      0 |00:00:00.01 |       2 |     32 |       |       |          |
|   4 |     TABLE ACCESS FULL  | T1   |      1 |      1 |      0 |00:00:00.01 |       2 |     32 |       |       |          |
|   5 |   BUFFER SORT          |      |      0 |      1 |      0 |00:00:00.01 |       0 |      0 | 73728 | 73728 |          |
|   6 |    VIEW                |      |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|   7 |     MINUS              |      |      0 |        |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|   8 |      SORT UNIQUE       |      |      0 |      1 |      0 |00:00:00.01 |       0 |      0 | 73728 | 73728 |          |
|   9 |       TABLE ACCESS FULL| T1   |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  10 |      SORT UNIQUE       |      |      0 |  70096 |      0 |00:00:00.01 |       0 |      0 | 73728 | 73728 |          |
|  11 |       TABLE ACCESS FULL| T2   |      0 |  70096 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(ROWNUM=1)

The only thing to watch out for is that the “rownum = 1″ doesn’t make the optimizer switch to an unsuitable “first_rows(1)” execution plan.

Optimisation

Here’s a recent request from the OTN database forum – how do you make this query go faster (tkprof output supplied):

 select a.rowid
   from  a, b
   where A.MARK IS NULL
     and a.cntry_code = b.cntry_code and b.dir_code='XX' and b.numb_type='XXX'
     and upper(Trim(replace(replace(replace(replace(replace(replace(replace(a.co_name,'*'),'&'),'-'),'/'),')'),'('),' '))) like
         upper(Trim(substr(replace(replace(replace(replace(replace(replace(replace(b.e_name,'*'),'&'),'-'),'/'),')'),'('),' '),1,25)))||'%';

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        3   3025.53    3260.11       8367       7950          0          31
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6   3025.54    3260.13       8367       7950          0          31

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 74

Rows     Row Source Operation
-------  ---------------------------------------------------
     31  HASH JOIN
 302790   INDEX FAST FULL SCAN OBJ#(39024) (object id 39024)   -- B 500,000 in the table
  55798   TABLE ACCESS FULL OBJ#(78942)                        -- A 175,000 in the table



-- and from some "explain plan" tool
SELECT STATEMENT  CHOOSE Cost: 333  Bytes: 52,355,940  Cardinality: 608,790 
3 HASH JOIN  Cost: 333  Bytes: 52,355,940  Cardinality: 608,790 
  1 INDEX FAST FULL SCAN UNIQUE B_PK Cost: 4  Bytes: 503,022  Cardinality: 12,898 
    2 TABLE ACCESS FULL A Cost: 215  Bytes: 3,150,034  Cardinality: 67,022

One thing you might note from the spartan tkprof output – this is an old version of Oracle (9.2.0.1 to be exact).

The first thing to do is note that most of the time is spent on the CPU – and maybe that multiply cascading replace() has something to do with it.  Now replace() and translate() are things I use so rarely that I usually get them wrong first time, but I think the predicate could be replaced by:

upper(translate(a.co_name, 'x*&-/)( ', 'x')) like upper(substr(translate(b.e_name, 'x*&-/)( ', 'x'),1,25))||'%'

Beyond making the code slightly less eye-boggling, though, I don’t think this is going to help much. Consider the information we have about the sizes of the rowsources involved.

If we can trust the tkprof row counts as being the complete output from the first execution of the statement (there seem to have been 2 in the trace file) – we selected 300,000 rows from one table and 56,000 rows from the other and then joined them with a hash join. A hash join requires equality predicates, and the only join predicate in the query that could be used is the one “a.cntry_code = b.cntry_code”.

Now, if cntry_code is short for “country code” we have a scaling problem: there are only about 600 countries in the world, so on average each row in the A table (56,000 rows acquired) is going to find roughly 500 rows in the B table (300,000 rows divided across 600 countries). So at run time the hash join will generate a rowsource of at least 56,000 * 500 = 28 Million rows; then Oracle is going to do that complicated bit of textual manipulation on two columns, compare them, and find that ultimately only 31 rows match !

So how can we do less work ?

If we’re lucky we can make the hash join much more efficient by thinking about what that nasty textual predicate means. We compare to see if one string looks like it’s starting with the first 25 characters of the other string – but if it does then the two strings have to be identical on the first 25 characters, and a hash join works with equality. So let’s just add in a new predicate to see what effect it has:

upper(substr(translate(a.co_name, 'x*&-/)( ', 'x'),1,25)) = upper(substr(translate(b.e_name, 'x*&-/)( ', 'x'),1,25))

I’ve made the suggestion on the forum – now I’m waiting to see if it has a beneficial effect (or whether I’ve made a silly mistake in my logic or guesswork)

Conditional SQL – 4

This is one of those posts where the investigation is left as an exercise – it’s not difficult, just something that will take a little time that I don’t have, and just might end up with me chasing half a dozen variations (so I’d rather not get sucked into looking too closely). It comes from an OTN question which ends up reporting this predicate:

WHERE ( LENGTH ( :b7) IS NULL OR
         UPPER (TRIM (CODFSC)) = UPPER (TRIM ( :b8)) or
         UPPER (TRIM (CODUIC)) = UPPER (TRIM ( :b9)))
       AND STATE = 0;

The three bind variables all hold the same value; there is a function-based index on upper(trim(codfsc)), and another on upper(trim(coduic)). The execution plan for this query is a full tablescan, but if you eliminate the first predicate Oracle can do a concatenation of two index range scans. This variation doesn’t surprise me, the optimizer’s ability to introduce concatenation is limited; however, I did wonder whether some small variation in the SQL would allow the optimizer to get just a little more clever.

Would you get concatenation if you changed the first predicate to (:b7 is null); if not, would a similar query that didn’t depend on function-based indexes do concatenation; if not is there any rewrite of this query that could do a tablescan ONLY for the case where :b7 was null ?

Demonstrations of any levels of success can be left in the comments if anyone’s interested. To get a fixed font that preserves space start the code with “sourcecode” and end with “/sourcecode” (removing the quotation marks and replacing them with square brackets).

Unnest Oddity

Here’s a little oddity I came across in 11.2.0.4 a few days ago – don’t worry too much about what the query is trying to do, or why it has been written the way I’ve done it, the only point I want to make is that I’ve got the same plan from two different strategies (according to the baseline/outline/hints), but the plans have a difference in cost.

Here’s the definition of my table and index, with query and execution plan:

create table trx(
	trx_xxx_id		number,
	trx_split_indicator	number,
	trx_latest_record_flag	varchar2(1),
	rep_firm_xxx_id		number,
	trx_external_ref	varchar2(30),
	trx_trade_date_time	date
);

create index trx_ui1 on trx(trx_xxx_id);

explain plan for
select
       /*+
		leading(vw_nso_1 tx1)
		use_nl(tx1)
		index_rs_asc(tx1 trx_ui1)
		pq_distribute(tx1 none broadcast)
		dynamic_sampling(0)
       */
	trx_xxx_id, trx_split_indicator,
	rep_firm_xxx_id, trx_external_ref,
	trx_trade_date_time
from
	trx tx1
where	(trx_trade_date_time,trx_xxx_id) in(
		select
			/*+
				full(tx2) parallel(tx2 2)
			--	unnest
				no_merge
				no_gby_pushdown
			*/
			trx_trade_date_time,max(trx_xxx_id)
		from	trx tx2
		where	trx_trade_date_time between to_date(:lower_date,'dd-mon-yyyy') and to_date(:upper_date,'dd-mon-yyyy')
		and	trx_latest_record_flag = 'Y'
		group by
			tx2.rep_firm_xxx_id, tx2.trx_external_ref, tx2.trx_trade_date_time
		having count(*) > 3
	);

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

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |          |     1 |    87 |     3  (34)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                 |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)           | :TQ10002 |     1 |    87 |     3  (34)| 00:00:01 |  Q1,02 | P->S | QC (RAND)  |
|   3 |    NESTED LOOPS                 |          |     1 |    87 |     3  (34)| 00:00:01 |  Q1,02 | PCWP |            |
|   4 |     NESTED LOOPS                |          |     1 |    87 |     3  (34)| 00:00:01 |  Q1,02 | PCWP |            |
|   5 |      VIEW                       | VW_NSO_1 |     1 |    22 |     3  (34)| 00:00:01 |  Q1,02 | PCWP |            |
|   6 |       HASH UNIQUE               |          |     1 |    54 |     3  (34)| 00:00:01 |  Q1,02 | PCWP |            |
|   7 |        PX RECEIVE               |          |     1 |    54 |     3  (34)| 00:00:01 |  Q1,02 | PCWP |            |
|   8 |         PX SEND HASH            | :TQ10001 |     1 |    54 |     3  (34)| 00:00:01 |  Q1,01 | P->P | HASH       |
|*  9 |          FILTER                 |          |       |       |            |          |  Q1,01 | PCWC |            |
|  10 |           HASH GROUP BY         |          |     1 |    54 |     3  (34)| 00:00:01 |  Q1,01 | PCWP |            |
|  11 |            PX RECEIVE           |          |     1 |    54 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|  12 |             PX SEND HASH        | :TQ10000 |     1 |    54 |     2   (0)| 00:00:01 |  Q1,00 | P->P | HASH       |
|* 13 |              FILTER             |          |       |       |            |          |  Q1,00 | PCWC |            |
|  14 |               PX BLOCK ITERATOR |          |     1 |    54 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|* 15 |                TABLE ACCESS FULL| TRX      |     1 |    54 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|* 16 |      INDEX RANGE SCAN           | TRX_UI1  |     1 |       |     0   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|* 17 |     TABLE ACCESS BY INDEX ROWID | TRX      |     1 |    65 |     0   (0)| 00:00:01 |  Q1,02 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH_AGGREGATION(@"SEL$683B0107")
      FULL(@"SEL$683B0107" "TX2"@"SEL$2")
      PQ_DISTRIBUTE(@"SEL$5DA710D3" "TX1"@"SEL$1" NONE BROADCAST)
      NLJ_BATCHING(@"SEL$5DA710D3" "TX1"@"SEL$1")
      USE_NL(@"SEL$5DA710D3" "TX1"@"SEL$1")
      LEADING(@"SEL$5DA710D3" "VW_NSO_1"@"SEL$5DA710D3" "TX1"@"SEL$1")
      INDEX(@"SEL$5DA710D3" "TX1"@"SEL$1" ("TRX"."TRX_XXX_ID"))
      NO_ACCESS(@"SEL$5DA710D3" "VW_NSO_1"@"SEL$5DA710D3")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      UNNEST(@"SEL$2" UNNEST_INNERJ_DISTINCT_VIEW)
      OUTLINE_LEAF(@"SEL$5DA710D3")
      OUTLINE_LEAF(@"SEL$683B0107")
      ALL_ROWS
      DB_VERSION('11.2.0.4')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   9 - filter(COUNT(*)>3)
  13 - filter(TO_DATE(:UPPER_DATE,'dd-mon-yyyy')>=TO_DATE(:LOWER_DATE,'dd-mon-yyyy'))
  15 - filter("TRX_LATEST_RECORD_FLAG"='Y' AND "TRX_TRADE_DATE_TIME">=TO_DATE(:LOWER_DATE,'dd-mon-yyyy') AND
              "TRX_TRADE_DATE_TIME"<=TO_DATE(:UPPER_DATE,'dd-mon-yyyy'))
  16 - access("TRX_XXX_ID"="MAX(TRX_XXX_ID)")
  17 - filter("TRX_TRADE_DATE_TIME"="TRX_TRADE_DATE_TIME")

As you can see (lines 5 to 15), the optimizer has unnested the IN subquery and done a parallel tablescan of the table to gather the distinct set of values for (trx_trade_date_time,trx_xxx_id); then it has done a nested loop (using the “doubled NL” strategy of 11g) to probe the TRX table by index.

The point I want to pick up is the subquery unnesting. If you look at the “Outline Data” part of the plan you will find the hint UNNEST(@”SEL$2″ UNNEST_INNERJ_DISTINCT_VIEW) – it’s the first version of Oracle where I’ve seen an unnest hint referencing what appears to be an explantory internal view name or mechanism (this doesn’t happen in 10.2.0.5 or 11.1.0.7). The interesting thing is this, when I did my first few experiments with the code I wanted to ensure that the subquery unnested so my very first test had included the unnest hint (commented out in SQL statement above) and when it had been there I had got exactly the same plan except the Outline Data had the following TWO hints:

• SEMI_TO_INNER(@”SEL$5DA710D3″ “VW_NSO_1″@”SEL$5DA710D3″)
• UNNEST(@”SEL$2″ UNNEST_SEMIJ_VIEW)

Again the unnest carries a descriptive view name, suggesting that the optimizer has changed an IN subquery to an EXISTS subquery, then unnested it to a semi-join. The previous descriptive name seems to suggest that the optimizer unnested the distinct view before merging it into a join. So we’ve got to the same position by following a different code path from the previous transformation. (Incidentally, the cost for this path was slightly higher than the cost for the other path).

At some stage I ought to look at the 10053 trace to see if there is any significant difference in the way the optimiser arrives at these two paths in case there are circumstances where it might make a real difference to performance – but this is another case where I’m going to leave further investigation to anyone who’s interested. The key concern here, of course, is that there’s a slight chance that some SQL running with an explicit unnest hint (or a stored outline with an unnest hint) in an earlier version of Oracle may change its behaviour because of a change in the definition of the hint.

The only other thing I’ve done with this example is to check the plans under 12c – where only the first version of the Outline Data appeared whether or not I supplied the hint. Whether this is due to an optimizer code change or simply a coincidence of costing I don’t yet know.

 

Subquery

How not to write subqueries:

AND     sal.ticket_airline || sal.ticket_number NOT IN (
                SELECT sub.dsd_airline || sub.dsd_ticket_number
                FROM   ...
        )

If there had been any opportunity for the optimizer to do something clever with the NOT IN, you’ve just made sure it can’t happen. On top of that you may find that you don’t get the right results – consider the following cut-n-paste:

SQL> select user from dual where 1 || 23 = 12 || 3;

USER
------------------------------
TEST_USER

1 row selected.

Sometimes people simply forget that you can have multiple columns in subqueries (or in “IN Lists”) – so it’s perfectly valid to write the subquery as:

AND     (sal.ticket_airline, sal.ticket_number) NOT IN (
                SELECT sub.dsd_airline, sub.dsd_ticket_number
                FROM   ...
        )

It’s quite likely that Oracle will actually turn this into a NOT EXISTS, or ANTI-JOIN, of course. But if it doesn’t do something nice you could try doing a manual rewrite – provided it is actually logically equivalent:

AND     not exists (
                select  null
                from    ....
                where   sub.dsd_airline    = sal.ticket_airline
                and     sub.dsd_ticket_number = sal.ticket_number
        )

Remember: NOT IN may not translate to NOT EXISTS – see also this.

Parallel Execution – 3

It’s finally time to take a close look at the parallel versions of the execution plan I produced a little while ago for a four-table hash join. In this note I’ll examine the broadcast parallel distribution. First, here’s a list of the hints I’m going to use to get the effect I want:

	/*+
		leading(t4 t1 t2 t3)
		full(t4) parallel(t4, 2)
		use_hash(t1) swap_join_inputs(t1) pq_distribute(t1 none broadcast)
		full(t1) parallel(t1, 2)
		use_hash(t2) swap_join_inputs(t2) pq_distribute(t2 none broadcast)
		full(t2) parallel(t2, 2)
		use_hash(t3) swap_join_inputs(t3) pq_distribute(t3 none broadcast)
		full(t3) parallel(t3, 2)
		monitor
	*/

If you compare this set of hints to the hints I used for the serial plan you’ll see that the change involves two features:

I’ve added a parallel(alias, 2) for each table to the full(alias) hint – this simply ensures that the full tablescans I’ve demanded should operate as parallel tablescans – then I’ve added a pq_distribute(alias none broadcast) hint to the use_hash(alias) and swap_join_inputs(alias) pair of hints – this completes the specification of a hash join when running parallel: not only do we have to specify the join method, and which of the two data sources should be treated as the build (‘first’)table and which the probe (‘second’) table, we also have to specify the method a slave set uses to pass data on to the next. In combination the set of hash-related hints give the following instructions to the optimizer:

“when you get to the point in the code path for ‘now joining {alias}’, you should do a hash join, using {‘alias’} as the build table and the current result set as the probe table; {‘alias’} should be broadcast to every slave, but the current result set does not need distribution”

This (with a number of blank lines inserted) is the resulting execution plan from 11.2.0.4:

-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost  |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |       |       |   345 |        |      |            |
|   1 |  SORT AGGREGATE             |          |     1 |    38 |       |        |      |            |
|   2 |   PX COORDINATOR            |          |       |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)      | :TQ10003 |     1 |    38 |       |  Q1,03 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE          |          |     1 |    38 |       |  Q1,03 | PCWP |            |
|*  5 |      HASH JOIN              |          |    26 |   988 |   345 |  Q1,03 | PCWP |            |
|   6 |       PX RECEIVE            |          |     3 |    18 |     2 |  Q1,03 | PCWP |            |

|   7 |        PX SEND BROADCAST    | :TQ10000 |     3 |    18 |     2 |  Q1,00 | P->P | BROADCAST  |
|   8 |         PX BLOCK ITERATOR   |          |     3 |    18 |     2 |  Q1,00 | PCWC |            |
|*  9 |          TABLE ACCESS FULL  | T3       |     3 |    18 |     2 |  Q1,00 | PCWP |            |

|* 10 |       HASH JOIN             |          |   612 | 19584 |   343 |  Q1,03 | PCWP |            |
|  11 |        PX RECEIVE           |          |     3 |    18 |     2 |  Q1,03 | PCWP |            |

|  12 |         PX SEND BROADCAST   | :TQ10001 |     3 |    18 |     2 |  Q1,01 | P->P | BROADCAST  |
|  13 |          PX BLOCK ITERATOR  |          |     3 |    18 |     2 |  Q1,01 | PCWC |            |
|* 14 |           TABLE ACCESS FULL | T2       |     3 |    18 |     2 |  Q1,01 | PCWP |            |

|* 15 |        HASH JOIN            |          | 14491 |   367K|   341 |  Q1,03 | PCWP |            |
|  16 |         PX RECEIVE          |          |     3 |    18 |     2 |  Q1,03 | PCWP |            |

|  17 |          PX SEND BROADCAST  | :TQ10002 |     3 |    18 |     2 |  Q1,02 | P->P | BROADCAST  |
|  18 |           PX BLOCK ITERATOR |          |     3 |    18 |     2 |  Q1,02 | PCWC |            |
|* 19 |            TABLE ACCESS FULL| T1       |     3 |    18 |     2 |  Q1,02 | PCWP |            |

|  20 |         PX BLOCK ITERATOR   |          |   343K|  6699K|   339 |  Q1,03 | PCWC |            |
|* 21 |          TABLE ACCESS FULL  | T4       |   343K|  6699K|   339 |  Q1,03 | PCWP |            |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("T3"."ID"="T4"."ID3")
   9 - access(:Z>=:Z AND :Z<=:Z)
       filter((TO_NUMBER("T3"."SMALL_VC")=1 OR TO_NUMBER("T3"."SMALL_VC")=2 OR
               TO_NUMBER("T3"."SMALL_VC")=3))
   10 - access("T2"."ID"="T4"."ID2")
   14 - access(:Z>=:Z AND :Z<=:Z)
        filter((TO_NUMBER("T2"."SMALL_VC")=1 OR TO_NUMBER("T2"."SMALL_VC")=2 OR
               TO_NUMBER("T2"."SMALL_VC")=3))
   15 - access("T1"."ID"="T4"."ID1")
   19 - access(:Z>=:Z AND :Z<=:Z)
        filter((TO_NUMBER("T1"."SMALL_VC")=1 OR TO_NUMBER("T1"."SMALL_VC")=2 OR
               TO_NUMBER("T1"."SMALL_VC")=3))
   21 - access(:Z>=:Z AND :Z<=:Z)
       filter(SYS_OP_BLOOM_FILTER_LIST(SYS_OP_BLOOM_FILTER(:BF0000,"T4"."ID1"),SYS_OP_BLOOM_F
              ILTER(:BF0000,"T4"."ID2"),SYS_OP_BLOOM_FILTER(:BF0000,"T4"."ID3")))

The serial plan had 9 lines, this plan has 22 – which makes it a little more intimidating.

Each “TABLE ACCESS FULL” is the child of a “PX BLOCK ITERATOR” – telling us that we are doing parallel tablescans, breaking each table into “rowid ranges” (called granules – you’ll frequently see 13 granules per slave, though very large objects can have more) and sharing the pieces out between the set of slaves.

There are pairs of “PX SEND / PX RECEIVE” showing us where one set of parallel slaves passes rows to the next set of slaves. In this case we can see that (most of) our sending slave sets are using “PX SEND BROADCAST”, echoed as “BROADCAST” in the PQ Distrib column. Taking line 17 as an example, we get a clue about how slave sets communicate – the Name column references something called “:TQ10002″, this is a “table queue (TQ)” or virtual table; to send data, one set of slaves writes to a virtual table, the other set of slaves reads that virtual table.

A key point to reading parallel execution plans is that the names of the virtual tables reflect the order in which they are written to, so in this case we know that the virtual table in line 7 (:TQ10000) is populated before the virtual table in line 12 (:TQ10001) which is populated before the virtual table in line 17 (:TQ10002) which is populated before the virtual table in line 3 (:TQ10003).

In fact the plan shows us quite a lot about each of the virtual tables – the Name column gives us the name of a virtual table, the TQ column with a little cosmetic massage highlights the lines of the plan that populate that virtual table. So we can see that :TQ10000 at line 7 is populated by a “PX BLOCK ITERATOR” (line 8) which implements the parallelism of the “TABLE SCAN FULL” (line 9). Following this pattern we can see that the plan shows us doing parallel tablescans of T3, T2, and T1 in that order, populating virtual tables :TQ10000, :TQ10001, and :TQ10002 in that order.

It is not immediately obvious (until you’ve had a little practice) how the two sets of slaves are interacting as this goes on – but if you now look for the lines that populate virtual table :TQ10003 you will see that we have a single activity which drains all three of the previous virtual tables AND does a parallel tablescan of T4. (Check the TQ column of lines 5,6, 10/11, 15/16, 20/21 to see all the inputs to :TQ10003)

Putting the pieces together: there is a single slave set that operates a hash join at line 5 by reading a virtual table :TQ1000 and building an in-memory hash table; after which it reads a second virtual table :TQ10001 and builds a second in-memory hash table, then it reads a third virtual table :TQ10002 building a final in-memory hash table, before reading the real table T4 (using rowid ranges) which it uses to probe each of the three in-memory hash tables in turn. This plan is really the closest equivalent of the original serial execution plan that we could get.

As each slave in the slave set generates a result row from the three consecutive hash joins it keeps a running aggregate (the query is a simple count(col), remember) so that it can eventually forward a single row to the query co-ordinator. That’s what’s going into virtual table :TQ10003 at line 3. The query co-ordinator (the end-user’s shadow process) reads all the rows from the virtual table and aggregates them to the final result that will be sent to the end-user.

There’s a little more that I can say about this execution plan – but I think that’s enough for a first dose; I’ll add an update to this article in a couple of days covering a few details about the virtual tables, parallel execution message size, and Bloom filters.

Footnote:

As often happens, a newer version of Oracle produces a more informative representation of the plan; in this case 12c shows operation lines for Bloom filter creation and usage.  It also happened, in this case, to bypass the redundant broadcasting of the small tables by doing all the scans and joins inside a single set of slaves – so every slave scanned every block of the previously broadcast tables, a strategy that you can force with the 12c /*+ pq_replicate(alias) */ hint and block with the /*+ no_pq_replicate(alias) */ hint.

12c Subqueries

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.

Interesting Plan

A recent question on the OTN database forum included an execution plan that prompted one reader to ask: “but where has the existence subquery gone?” Here’s the original question showing the query, and here’s the later response showing the plan that prompted the question.

There were three possible reasons why that question may have been posed:

1. first, of course, is that Oracle may have transformed the subquery into a join (“existence” often turns into a “semi-join”, and sometimes – depending on uniqueness constraints – a simple join). In this case the OP had ensure that this didn’t happen by adding a hint to the code.
2. second is that the execution plan was extracted from memory (v$sql_plan), and when there is a defect in the code to populate v$sql_plan that results in the actual text of subqueries getting lost, so a line in the predicate section of the plan could read something like:  “AND     IS NOT NULL”, with a small gap where you’re expecting to see the text of a subquery.
3. third is that we often expect to see a FILTER operation (i.e. separate line) in the execution plan with two children, the first being the data access operation and the second being the start of the subquery plan (see below). However, in this case, Oracle had “pushed” the subquery (thanks to a hint), and in these circumstances the FILTER operation often disappears introducing a small distortion in the shape of the execution plan that makes it easy to mis-interpret the plan. In this case line 11 of the plan visits a table to collect some rows, and line 12 (indented one step from line 11) is the start of the existence subquery.

I came close to writing a complete blog post about this problem with reading execution plans – then decided to check if I had mentioned it before. I have: twice: originally in 2010, and then again in 2012.

“Classic” filter subquery shape:

select from child where exists (select from parent join grandparent)

-------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |     1 |  1627 |   595K|
|*  1 |  FILTER             |             |       |       |       |
|   2 |   TABLE ACCESS FULL | CHILD       | 10000 |    15M|   381 |
|*  3 |   HASH JOIN         |             |     1 |    22 |   119 |
|*  4 |    TABLE ACCESS FULL| PARENT      |     1 |    11 |    78 |
|   5 |    TABLE ACCESS FULL| GRANDPARENT |  1000 | 11000 |    40 |
-------------------------------------------------------------------

Same query with “pushed” subquery.

-------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |   500 |   794K|   500 |
|*  1 |  TABLE ACCESS FULL  | CHILD       |   500 |   794K|   381 |
|*  2 |   HASH JOIN         |             |     1 |    22 |   119 |
|*  3 |    TABLE ACCESS FULL| PARENT      |     1 |    11 |    78 |
|   4 |    TABLE ACCESS FULL| GRANDPARENT |  1000 | 11000 |    40 |
-------------------------------------------------------------------

The first version of the plan can be interpreted by what I have taken to calling the “first child first, recursive descent” method.
To interpret the second plan you actually need to look at the predicate section to discover that lines 2-4 are a subquery, and then you have to check whether they form a filter subquery or an access subquery.

Footnote: there are a few special cases for serial execution plans where the recursive descent method doesn’t give you the right answer – and it’s almost certain that if you use it on parallel execution plans you’ll come to the wrong conclusion about the order of operation; however OEM (and Toad etc., etc….) still use this method for displaying the order of operation.

Outline hassle

Here’s the output I got from a 10.2.0.5 system after generating a stored outline on a query – then dropping the index that was referenced by the stored outline and creating an alternative index. Spot the problem:

Index dropped.

Index created.

-- run query
-- call to dbms_xplan.display_cursor()

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  bwkujqpbbanq4, child number 0
-------------------------------------
select * from t1 where n1 = :b1 and n1 + n2 = :"SYS_B_0"

Plan hash value: 3299499710

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |       |       |     1 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1   |     1 |   118 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_F |    27 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      OPT_PARAM('optimizer_index_cost_adj' 65)
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" "T1_F")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N1"=:B1)
   2 - access("T1"."SYS_NC00004$"=:SYS_B_0)

Note
-----
   - outline "SYS_OUTLINE_13110414022564008" used for this statement

According to the note the optimizer used an outline to optimize this query – which is odd since I’ve done something that made the outline invalid. Perhaps there’s another outline available, so let’s check:

SQL>select * from user_outline_hints;

NAME                                 NODE      STAGE   JOIN_POS HINT
------------------------------ ---------- ---------- ---------- --------------------------------------------------
SYS_OUTLINE_13110414022564008           1          1          1 INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."N1"))
SYS_OUTLINE_13110414022564008           1          1          0 OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_13110414022564008           1          1          0 FIRST_ROWS(10)
SYS_OUTLINE_13110414022564008           1          1          0 OPT_PARAM('optimizer_dynamic_sampling' 0)
SYS_OUTLINE_13110414022564008           1          1          0 OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
SYS_OUTLINE_13110414022564008           1          1          0 IGNORE_OPTIM_EMBEDDED_HINTS

No. The outline “used” was the outline that specified the now-dropped index.

Is this a bug ? After a few minutes of thinking it was, I decided it probably wasn’t. Technically if you “use” that outline you will get that plan; it’s just bad luck that the outline can’t reproduce the original plan. I suspect that if I read the documentation I will find that this is, indeed, expected behaviour.

SQL Baselines, of course, are much fussier – if a baseline doesn’t reproduce the original plan Oracle is aware of that fact – my initial response to this oddity with outlines was simply applying baseline thinking to old technology.