Cardinality Feedback

True to form, Kerry Osborne has another excellent post, this time on Cardinality Feedback.

If the mark of a good post is that it sets you off thinking and investigating then he’s nailed it again.

I started to comment on his post, but it was getting long and messy and I thought it easy to make a comment with a post of my own. Apart from anything else, I always seem to cock up the formatting when making longer comments with examples, etc.

So, hopefully, you’ve read Kerry’s article and he asked where Oracle stored the opt_estimate fudge factor feedback that he could see in his trace and that came through when using a baseline created from the plan changed by cardinality feedback.

My initial reaction was that I thought they would be independent and so began this little investigation (which isn’t finished yet).

First tangent of the day … on the subject of OPT_ESTIMATE – it was my belief that this was one of the principal mechanisms used by sql plan baselines. I know it was very popular with standalone sql profiles. I’ve got a controlled number of baselines in my environment – a couple of hundred – none of them use OPT_ESTIMATE in SQLOBJ$DATA.COMP_DATA. Surprising.

Anyway, I thought in terms of having a standalone demo on Cardinality Feedback, why reinvent the wheel?

On his blog article on the subject, Tom Kyte has a standalone demo. Perfect I thought, I’ll reuse it.

And it goes a little something like this:

1. Create table and package:

create or replace type str2tblType as table of varchar2(30);
/
create or replace function str2tbl( 
  p_str in varchar2, 
  p_delim in varchar2 default ',' )
return str2tblType
PIPELINED
as
  l_str      long default p_str || p_delim;
  l_n        number;
begin
   loop
       l_n := instr( l_str, p_delim );
       exit when (nvl(l_n,0) = 0);
       pipe row ( ltrim(rtrim(substr(l_str,1,l_n-1))) );
       l_str := substr( l_str, l_n+1 );
   end loop;
   return;
end;
/

2. Set up variable in sql*plus and execute sql statement once, get plan

var in_list varchar2(255)
exec :in_list := 'DBMS_PIPE,DBMS_OUTPUT,UTL_FILE';

select * from TABLE(cast( str2tbl(:in_list ) as str2tblType) ) t;

select * from table(dbms_xplan.display_cursor);

Which produced the following plan:

SQL> select * from TABLE(cast( str2tbl(:in_list ) as str2tblType) ) t;

COLUMN_VALUE
------------------------------
DBMS_PIPE
DBMS_OUTPUT
UTL_FILE

3 rows selected.

SQL> 
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  d7bc3g2ccymkt, child number 0
-------------------------------------
select * from TABLE(cast( str2tbl(:in_list ) as str2tblType) ) t

Plan hash value: 2407808827

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |       |       |    34 (100)|          |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| STR2TBL |  8168 | 16336 |    34   (3)| 00:00:01 |
---------------------------------------------------------------------------------------------


13 rows selected.

SQL> 

Hopefully, no surprises there.

According to the original demo, if I rinse and repeat and should get some feedback kicking right in there.

SQL> select * from TABLE(cast( str2tbl(:in_list ) as str2tblType) ) t;

COLUMN_VALUE
------------------------------
DBMS_PIPE
DBMS_OUTPUT
UTL_FILE

3 rows selected.

SQL> 
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID  d7bc3g2ccymkt, child number 0
-------------------------------------
select * from TABLE(cast( str2tbl(:in_list ) as str2tblType) ) t

Plan hash value: 2407808827

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |       |       |    34 (100)|          |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| STR2TBL |  8168 | 16336 |    34   (3)| 00:00:01 |
---------------------------------------------------------------------------------------------


13 rows selected.

SQL> 

Oh!

Nope.

And doesn’t matter how often I repeated, same difference.

So, this probably the right time to mention version, right?

SQL> select * from v$version;

BANNER
------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

At this point, something I’d read by the Optimzer Development Group rang a bell:

For similar reasons, execution statistics for queries containing bind variables can be problematic. In cardinality feedback, we limit the feedback to portions of the plan whose estimates are not affected by bind variables. For instance, if a table has a filter comparing a column to a bind value, the cardinality of that table will not be used. However, cardinality feedback can still be used for cardinalities elsewhere in the plan.

Sounds relevant, doesn’t it?

Let’s substitute the bind with a literal.

So, we’re going to run this:

select * from TABLE(cast( str2tbl('DBMS_PIPE,DBMS_OUTPUT,UTL_FILE' ) as str2tblType) ) t;

select * from table(dbms_xplan.display_cursor);

which gave me:

SQL> select * from TABLE(cast( str2tbl('DBMS_PIPE,DBMS_OUTPUT,UTL_FILE' ) as str2tblType) ) t;

COLUMN_VALUE
------------------------------
DBMS_PIPE
DBMS_OUTPUT
UTL_FILE

3 rows selected.

SQL> 
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID  8udfh089hyh6d, child number 0
-------------------------------------
select * from TABLE(cast( str2tbl('DBMS_PIPE,DBMS_OUTPUT,UTL_FILE' ) as
str2tblType) ) t

Plan hash value: 2407808827

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |       |       |    34 (100)|          |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| STR2TBL |  8168 | 16336 |    34   (3)| 00:00:01 |
---------------------------------------------------------------------------------------------


14 rows selected.

And again

SQL> select * from TABLE(cast( str2tbl('DBMS_PIPE,DBMS_OUTPUT,UTL_FILE' ) as str2tblType) ) t;

COLUMN_VALUE
------------------------------
DBMS_PIPE
DBMS_OUTPUT
UTL_FILE

3 rows selected.

SQL> 
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID  8udfh089hyh6d, child number 1
-------------------------------------
select * from TABLE(cast( str2tbl('DBMS_PIPE,DBMS_OUTPUT,UTL_FILE' ) as
str2tblType) ) t

Plan hash value: 2407808827

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |       |       |    33 (100)|          |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| STR2TBL |     6 |    12 |    33   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Note
-----
   - cardinality feedback used for this statement


18 rows selected.

SQL> 

So, cardinality feedback successfully demoed with cardinality estimates reduced from the blocksize-related default for a collection to something altogether more accurate.

Now we need to move on to a case where these improved cardinality estimates actually affect the plan.

Fortunately, Tom’s demo does just that with a new table:

create table data  as
select * from all_objects;

create index data_idx on data(object_name);

exec dbms_stats.gather_table_stats( user, 'DATA' );

And a new statement:

with T as
( select /*+ find_me */ 
         distinct * 
  from   TABLE(cast( str2tbl( 'DBMS_PIPE,DBMS_OUTPUT,UTL_FILE' ) as str2tblType) ) t 
  where rownum > 0 )
select * from data, t 
where data.object_name = t.column_value;

select * from table(dbms_xplan.display_cursor(format=>'-bytes'));

which gives the following (actual sql output snipped):

SQL> with T as
  2  ( select /*+ find_me */
  3           distinct *
  4    from   TABLE(cast( str2tbl( 'DBMS_PIPE,DBMS_OUTPUT,UTL_FILE' ) as str2tblType) ) t
  5    where rownum > 0 )
  6  select * from data, t
  7  where data.object_name = t.column_value;

.......

5 rows selected.

SQL> 
SQL> select * from table(dbms_xplan.display_cursor(format=>'-bytes'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  c4m3t9jwvs3ht, child number 0
-------------------------------------
with T as ( select /*+ find_me */          distinct *   from
TABLE(cast( str2tbl( 'DBMS_PIPE,DBMS_OUTPUT,UTL_FILE' ) as str2tblType)
) t   where rownum > 0 ) select * from data, t where data.object_name =
t.column_value

Plan hash value: 892089582

------------------------------------------------------------------------------------------
| Id  | Operation                              | Name    | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |         |       |   318 (100)|          |
|*  1 |  HASH JOIN                             |         | 15014 |   318   (5)| 00:00:02 |
|   2 |   VIEW                                 |         |  8168 |    36   (9)| 00:00:01 |
|   3 |    HASH UNIQUE                         |         |  8168 |    36   (9)| 00:00:01 |
|   4 |     COUNT                              |         |       |            |          |
|*  5 |      FILTER                            |         |       |            |          |
|   6 |       COLLECTION ITERATOR PICKLER FETCH| STR2TBL |  8168 |    34   (3)| 00:00:01 |
|   7 |   TABLE ACCESS FULL                    | DATA    | 58781 |   279   (3)| 00:00:02 |
------------------------------------------------------------------------------------------

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

   1 - access("DATA"."OBJECT_NAME"="T"."COLUMN_VALUE")
   5 - filter(ROWNUM>0)


28 rows selected.

SQL> 

As expected, no cardinality feedback yet, collection estimate back to default.

And I’m just going to monitor what’s in V$SQL:

SQL> select sql_id, child_number, plan_hash_value, executions, is_shareable, sql_plan_baseline 
  2  from v$sql 
  3  where sql_id = 'c4m3t9jwvs3ht';

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS I SQL_PLAN_BASELINE
------------- ------------ --------------- ---------- - ------------------------------
c4m3t9jwvs3ht            0       892089582          1 Y

1 row selected.

SQL> 

Now re-run:

SQL> with T as
  2  ( select /*+ find_me */ 
  3           distinct * 
  4    from   TABLE(cast( str2tbl( 'DBMS_PIPE,DBMS_OUTPUT,UTL_FILE' ) as str2tblType) ) t 
  5    where rownum > 0 )
  6  select * from data, t 
  7  where data.object_name = t.column_value;

...............

5 rows selected.

SQL> 
SQL> select * from table(dbms_xplan.display_cursor(format=>'-bytes'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  c4m3t9jwvs3ht, child number 1
-------------------------------------
with T as ( select /*+ find_me */          distinct *   from
TABLE(cast( str2tbl( 'DBMS_PIPE,DBMS_OUTPUT,UTL_FILE' ) as str2tblType)
) t   where rownum > 0 ) select * from data, t where data.object_name =
t.column_value

Plan hash value: 3947981921

--------------------------------------------------------------------------------------------
| Id  | Operation                               | Name     | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |          |       |    43 (100)|          |
|   1 |  NESTED LOOPS                           |          |       |            |          |
|   2 |   NESTED LOOPS                          |          |     6 |    43   (3)| 00:00:01 |
|   3 |    VIEW                                 |          |     3 |    34   (3)| 00:00:01 |
|   4 |     HASH UNIQUE                         |          |     3 |    34   (3)| 00:00:01 |
|   5 |      COUNT                              |          |       |            |          |
|*  6 |       FILTER                            |          |       |            |          |
|   7 |        COLLECTION ITERATOR PICKLER FETCH| STR2TBL  |     6 |    33   (0)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN                     | DATA_IDX |     2 |     2   (0)| 00:00:01 |
|   9 |   TABLE ACCESS BY INDEX ROWID           | DATA     |     2 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   6 - filter(ROWNUM>0)
   8 - access("DATA"."OBJECT_NAME"="T"."COLUMN_VALUE")

Note
-----
   - cardinality feedback used for this statement


34 rows selected.

SQL> 

And in V$SQL:

SQL> select sql_id, child_number, plan_hash_value, executions, is_shareable, sql_plan_baseline 
  2  from v$sql 
  3  where sql_id = 'c4m3t9jwvs3ht';

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS I SQL_PLAN_BASELINE
------------- ------------ --------------- ---------- - ------------------------------
c4m3t9jwvs3ht            0       892089582          1 N
c4m3t9jwvs3ht            1      3947981921          1 Y

2 rows selected.

SQL> 

So, cardinality feedback has come to the rescue, adjusted our estimates and this time we’ve got a better plan for the numbers concerned with NESTED LOOPS rather than a HASH JOIN.

What happens if, as Kerry did, I create a baseline for this improved statement using his create_baseline.sql script?

SQL> @create_baseline
Enter value for sql_id: c4m3t9jwvs3ht
Enter value for plan_hash_value: 3947981921
Enter value for fixed (NO): 
Enter value for enabled (YES): 
Enter value for plan_name (ID_sqlid_planhashvalue): my_cf_baseline
old  16: l_sql_id := '&&sql_id';
new  16: l_sql_id := 'c4m3t9jwvs3ht';
old  17: l_plan_hash_value := to_number('&&plan_hash_value');
new  17: l_plan_hash_value := to_number('3947981921');
old  18: l_fixed := '&&fixed';
new  18: l_fixed := 'NO';
old  19: l_enabled := '&&enabled';
new  19: l_enabled := 'YES';
old  40:     decode('&&plan_name','X0X0X0X0','SQLID_'||'&&sql_id'||'_'||'&&plan_hash_value','&&plan_
new  40:     decode('my_cf_baseline','X0X0X0X0','SQLID_'||'c4m3t9jwvs3ht'||'_'||'3947981921','my_cf_
Baseline MY_CF_BASELINE created.
SQL> 

And now I re-run the statement:

SQL> with T as
  2  ( select /*+ find_me */ 
  3           distinct * 
  4    from   TABLE(cast( str2tbl( 'DBMS_PIPE,DBMS_OUTPUT,UTL_FILE' ) as str2tblType) ) t 
  5    where rownum > 0 )
  6  select * from data, t 
  7  where data.object_name = t.column_value;

....

5 rows selected.

SQL> 
SQL> select * from table(dbms_xplan.display_cursor(format=>'-bytes'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  c4m3t9jwvs3ht, child number 2

An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

NOTE: cannot fetch plan for SQL_ID: c4m3t9jwvs3ht, CHILD_NUMBER: 2
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)


8 rows selected.

SQL> 

Oh dear. Can we just ignore that and come back to it at a later date? Let’s stick a pin in it, as they say.

I’ll just try again:

SQL> with T as
  2  ( select /*+ find_me */ 
  3           distinct * 
  4    from   TABLE(cast( str2tbl( 'DBMS_PIPE,DBMS_OUTPUT,UTL_FILE' ) as str2tblType) ) t 
  5    where rownum > 0 )
  6  select * from data, t 
  7  where data.object_name = t.column_value;

........

5 rows selected.

SQL> 
SQL> select * from table(dbms_xplan.display_cursor(format=>'-bytes'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  c4m3t9jwvs3ht, child number 2
-------------------------------------
with T as ( select /*+ find_me */          distinct *   from
TABLE(cast( str2tbl( 'DBMS_PIPE,DBMS_OUTPUT,UTL_FILE' ) as str2tblType)
) t   where rownum > 0 ) select * from data, t where data.object_name =
t.column_value

Plan hash value: 3947981921

--------------------------------------------------------------------------------------------
| Id  | Operation                               | Name     | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |          |       | 24591 (100)|          |
|   1 |  NESTED LOOPS                           |          |       |            |          |
|   2 |   NESTED LOOPS                          |          | 15014 | 24591   (1)| 00:02:03 |
|   3 |    VIEW                                 |          |  8168 |    36   (9)| 00:00:01 |
|   4 |     HASH UNIQUE                         |          |  8168 |    36   (9)| 00:00:01 |
|   5 |      COUNT                              |          |       |            |          |
|*  6 |       FILTER                            |          |       |            |          |
|   7 |        COLLECTION ITERATOR PICKLER FETCH| STR2TBL  |  8168 |    34   (3)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN                     | DATA_IDX |     2 |     2   (0)| 00:00:01 |
|   9 |   TABLE ACCESS BY INDEX ROWID           | DATA     |     2 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   6 - filter(ROWNUM>0)
   8 - access("DATA"."OBJECT_NAME"="T"."COLUMN_VALUE")

Note
-----
   - SQL plan baseline SQL_PLAN_6w2j9bx7xvu6h5268a54a used for this statement


34 rows selected.

SQL> 

And what do we have in V$SQL?

SQL> select sql_id, child_number, plan_hash_value, executions, is_shareable, sql_plan_baseline 
  2  from v$sql 
  3  where sql_id = 'c4m3t9jwvs3ht';

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS I SQL_PLAN_BASELINE
------------- ------------ --------------- ---------- - ------------------------------
c4m3t9jwvs3ht            0       892089582          1 N
c4m3t9jwvs3ht            2      3947981921          1 Y SQL_PLAN_6w2j9bx7xvu6h5268a54a

2 rows selected.

SQL> 

So we can see that the baseline worked.

We can also see that the baseline, in this example, operated independently of the cardinality feedback so whilst we got our nice plan from the more accurate cardinality, we got the estimates from the default estimate of the collection.

Which is not exactly what I expected.

From Kerry’s post, he managed to get a statement using both a baseline and cardinality feedback as illustrated by his dbms_xplan output of:

Note
-----
   - SQL plan baseline SQLID_0CM4R08VJ075R_1255158658 used for this statement
   - cardinality feedback used for this statement

This is what I expected to get and then I was going to flush the shared pool in the hope of having a baseline and the original non-feedback estimates.

But no matter how many times I re-ran the statement I couldn’t get the cardinality feedback to kick back in.

So, unfortunately, I got to that end result without the all-important bit in the middle. Which is a bit weird. And warrants further investigation at some point.

I thought maybe the MONITOR hint might be the key difference to getting feedback working with baselines but not according to what I’ve tried so far. I couldn’t get the two features to work together.

But the bottom line is that I think this shows that the baseline did not use anything to maintain that those nice accurate estimates from cardinality feedback, only the end result plan.

And that’s backed up by the set of hints in the baseline which doesn’t include any OPT_ESTIMATE.

Suffice to say that this is really fledgling functionality and, as such, you expect subtle and no-so-subtle tweaks along the way as illustrated by the possible change of behaviour with bind variables when comparing my results above with the ones in Tom’s original article.

About these ads

One Response to Cardinality Feedback

  1. Pingback: Sql Plan Mangement(SPM) and Adaptive Cursor Sharing(ACS) : My résumé | Mohamed Houri’s Oracle Notes

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.

Join 69 other followers

%d bloggers like this: