Sokrates on Oracle

A Restriction of the Cardinality Hint

Posted by Matthias Rogel on 17. January 2014

Here is a restriction of the cardinality hint in conjunction with the materialize-hint ( note: both are undocumented but sometimes of great use ):
we cannot tell the optimizer in the outer query ( the one that uses the materialized subquery ) about the cardinality of the materialization, this can only – and then not always – be done within the materializing query.


Update 21/01/2014.
Randolf Geist shows in this comment that this is not true and gives techniques how to achive this.

The example to show that is stolen from Tom Kyte’s Presentation S13961_Best_Practices_for_Managing_Optimizer_Statistics_Short.pptx from ukoug 2013.zip:

sokrates@12.1 > create type str2tbltype is table of varchar2(100);
  2  /

Type created.

sokrates@12.1 > create function str2tbl( p_str in varchar2 ) return str2tblType
  as
  l_str   long default p_str || ',';
  l_n	     number;
  l_data    str2tblType := str2tblType();
  begin
  loop
  l_n := instr( l_str, ',' );
  exit when (nvl(l_n,0) = 0);
 l_data.extend;
 l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
 l_str := substr( l_str, l_n+1 );
 end loop;
 return l_data;
 end;
  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16  /

Function created.

sokrates@12.1 > create table t as select object_id, object_name from dba_objects;

Table created.

sokrates@12.1 > create index t_idx on t( object_name );

Index created.

sokrates@12.1 > exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

sokrates@12.1 > variable in_list varchar2(100)
sokrates@12.1 > exec :in_list := 'DBMS_OUTPUT,UTL_FILE,DBMS_PIPE'

PL/SQL procedure successfully completed.

sokrates@12.1 > select count(*) from table(cast( str2tbl( :in_list) as str2tblType) ) t;

  COUNT(*)
----------
	 3

The optimizer does know nothing about the cardinality of this “table(cast( str2tbl( :in_list) as str2tblType) )”.
A clever human could prove that the cardinality of this “table” can never exceed 33.000, so humans sometimes are more clever than the optimizer in estimating cardinalities. In our example, we want to tell the optimizer that the cardinality of this table is approximately 10, which will influence the execution plan of a select which joins this table to a real table.

Without cardinality-hint, the optimizer uses a default cardinality and chooses the wrong hash join:

sokrates@12.1 > with data as
( select *
   from table(cast( str2tbl( :in_list) as str2tblType) ) t
)
select t.object_id, t.object_name
  from data, t
 where t.object_name = data.column_value
  2    3    4    5    6    7    8  /

Execution Plan
----------------------------------------------------------
Plan hash value: 386533642

----------------------------------------------------------------------------------------------
| Id  | Operation			   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		   |	     | 14005 |	 437K|	 154   (1)| 00:00:01 |
|*  1 |  HASH JOIN			   |	     | 14005 |	 437K|	 154   (1)| 00:00:01 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| STR2TBL |	8168 | 16336 |	  29   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL		   | T	     | 90964 |	2664K|	 124   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T"."OBJECT_NAME"=VALUE(KOKBF$))

When we tell the optimizer via cardinality hint the correct magnitude of the real cardinality, the right access path “nested loops and index” is used

sokrates@12.1 > with data as
( select /*+cardinality(t, 10) */ *
   from table(cast( str2tbl( :in_list) as str2tblType) ) t
)
select t.object_id, t.object_name
  from data, t
 where t.object_name = data.column_value
  2    3    4    5    6    7    8  /

Execution Plan
----------------------------------------------------------
Plan hash value: 2392632293

-----------------------------------------------------------------------------------------------
| Id  | Operation			    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |	      |    17 |   544 |    59	(0)| 00:00:01 |
|   1 |  NESTED LOOPS			    |	      |       |       | 	   |	      |
|   2 |   NESTED LOOPS			    |	      |    17 |   544 |    59	(0)| 00:00:01 |
|   3 |    COLLECTION ITERATOR PICKLER FETCH| STR2TBL |    10 |    20 |    29	(0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN		    | T_IDX   |     2 |       |     2	(0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID	    | T       |     2 |    60 |     3	(0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T"."OBJECT_NAME"=VALUE(KOKBF$))

Note, that we can “pull this hint into the outer query” and still get the exact same execution plan ( even the same plan hash value ):

sokrates@12.1 > with data as
( select *
   from table(cast( str2tbl( :in_list) as str2tblType) ) t
)
select /*+cardinality(data, 10) */ t.object_id, t.object_name
  from data, t
 where t.object_name = data.column_value
  2    3    4    5    6    7    8  /

Execution Plan
----------------------------------------------------------
Plan hash value: 2392632293

-----------------------------------------------------------------------------------------------
| Id  | Operation			    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |	      |    17 |   544 |    59	(0)| 00:00:01 |
|   1 |  NESTED LOOPS			    |	      |       |       | 	   |	      |
|   2 |   NESTED LOOPS			    |	      |    17 |   544 |    59	(0)| 00:00:01 |
|   3 |    COLLECTION ITERATOR PICKLER FETCH| STR2TBL |    10 |    20 |    29	(0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN		    | T_IDX   |     2 |       |     2	(0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID	    | T       |     2 |    60 |     3	(0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T"."OBJECT_NAME"=VALUE(KOKBF$))

This behaviour changes when we materialize the inner query.
First we hint the cardinality in the materializing query:

sokrates@12.1 > with data as
( select /*+materialize cardinality(t, 10) */*
   from table(cast( str2tbl( :in_list) as str2tblType) ) t
)
select t.object_id, t.object_name
  from data, t
 where t.object_name = data.column_value
  2    3    4    5    6    7    8  /

Execution Plan
----------------------------------------------------------
Plan hash value: 2115576147

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name			| Rows	| Bytes | Cost (%CPU)| Time	|
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |				|    17 |  1394 |    61   (0)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION	    |				|	|	|	     |		|
|   2 |   LOAD AS SELECT		    | SYS_TEMP_0FD9D666D_268859 |	|	|	     |		|
|   3 |    COLLECTION ITERATOR PICKLER FETCH| STR2TBL			|    10 |    20 |    29   (0)| 00:00:01 |
|   4 |   NESTED LOOPS			    |				|	|	|	     |		|
|   5 |    NESTED LOOPS 		    |				|    17 |  1394 |    32   (0)| 00:00:01 |
|   6 |     VIEW			    |				|    10 |   520 |     2   (0)| 00:00:01 |
|   7 |      TABLE ACCESS FULL		    | SYS_TEMP_0FD9D666D_268859 |    10 |    20 |     2   (0)| 00:00:01 |
|*  8 |     INDEX RANGE SCAN		    | T_IDX			|     2 |	|     2   (0)| 00:00:01 |
|   9 |    TABLE ACCESS BY INDEX ROWID	    | T 			|     2 |    60 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   8 - access("T"."OBJECT_NAME"="DATA"."COLUMN_VALUE")

Still the right plan.

This changes when we pull the cardinality-hint into the outer query:

sokrates@12.1 > with data as
( select /*+materialize */ *
   from table(cast( str2tbl( :in_list) as str2tblType) ) t
)
select /*+cardinality(data, 10) */ t.object_id, t.object_name
  from data, t
 where t.object_name = data.column_value
  2    3    4    5    6    7    8  /

Execution Plan
----------------------------------------------------------
Plan hash value: 4042153407

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name			| Rows	| Bytes | Cost (%CPU)| Time	|
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |				| 14005 |  1121K|   157   (1)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION	    |				|	|	|	     |		|
|   2 |   LOAD AS SELECT		    | SYS_TEMP_0FD9D666F_268859 |	|	|	     |		|
|   3 |    COLLECTION ITERATOR PICKLER FETCH| STR2TBL			|  8168 | 16336 |    29   (0)| 00:00:01 |
|*  4 |   HASH JOIN			    |				| 14005 |  1121K|   128   (1)| 00:00:01 |
|   5 |    VIEW 			    |				|  8168 |   414K|     3   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL		    | SYS_TEMP_0FD9D666F_268859 |  8168 | 16336 |     3   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL		    | T 			| 90964 |  2664K|   124   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T"."OBJECT_NAME"="DATA"."COLUMN_VALUE")

The cardinality-hint was ignored and the wrong plan was chosen.

This is a pity because when the materialized subquery is more complicated than in this example ( think of a multi-join where the developer just “knows” the magnitude of the result set for some reason the optimizer cannot be aware of – there are always such cases -), we have no chance in helping the optimizer to find the “right” plan just via cardinality hint.

About these ads

4 Responses to “A Restriction of the Cardinality Hint”

  1. Hi Matthias,

    for the sake of completeness: the use of the (similar and also undocumented) hint opt_estimate brings the same result.

  2. … and that’s not a big surprise since cardinality is translated into opt_estimate according to a 10053 trace. The CBO trace is quite verbose in 12c and contains an explicit hint section (Dumping Hints at the end of the file) and clearly shows the the cardinality adjustion is not considered for the last query.

  3. Yes, it’s a pity with the TEMP TABLE TRANSFORMATION, and there are actually other side effects with its current implementation, like that FILTER on the materialized temp table sometimes don’t get pushed to the TABLE ACCESS operator, but only get filtered on a VIEW operator after reading all rows from the table, which is bad in particular in Exadata environments where cell offloading of such a filter isn’t possible that way.

    Just for completeness, there *are ways* how the CARDINALITY hint could still be applied. Two ideas:

    The following just wraps the materialized data set into another non-mergeable view layer:

    with data as
    ( select /*+ materialize */ *
    from table(cast( str2tbl( :in_list) as str2tblType) ) t
    )
    select t.object_id, t.object_name
    from (select /*+ no_merge cardinality(10) */ * from data) data, t
    where t.object_name = data.column_value;

    This one gets the query block and the alias from the “Query Block” section of DBMS_XPLAN.DISPLAY* (the internal transformation calls these row sources always T1, T2 etc., but you could of course use the full alias from the section, in this case T1@SEL$AE95C8DF):

    with data as
    ( select /*+ materialize */ *
    from table(cast( str2tbl( :in_list) as str2tblType) ) t
    )
    select /*+ cardinality(@SEL$AE95C8DF t1 10) */ t.object_id, t.object_name
    from data, t
    where t.object_name = data.column_value;

    Randolf

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

 
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: