Oracle Scratchpad

December 16, 2013

Unnest Oddity

Filed under: Execution plans,Hints,Oracle,subqueries — Jonathan Lewis @ 6:56 pm BST Dec 16,2013

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.

 

3 Comments »

  1. The main thing I took away from this is that it’s possible to comment out optimizer hints! I didn’t think you could since it’s already within a comment.

    Comment by Jason Bucata — December 17, 2013 @ 1:01 am BST Dec 17,2013 | Reply

  2. Jonathan,

    The unnest hint naming does not happen even in 11.2.0.3. I was a little bit curious if it won’t change implicit query block names. Fortunately, it seems all QB names are same as in 11.2.0.4.

    Comment by Pavol Babel — December 17, 2013 @ 2:53 am BST Dec 17,2013 | Reply

    • Pavol,

      I think the query block names would be dependent only on the resultant view VW_NSO_1 – possibly the “name” in the hint is more like an directive to use a particular strategy.

      Comment by Jonathan Lewis — December 17, 2013 @ 1:05 pm BST Dec 17,2013 | 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,266 other followers