Search

Top 60 Oracle Blogs

Recent comments

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.