Analyze This – 2

If you’ve run the scripts from Analyze This, I hope you found that the query gave you two different execution plans. This is my output from the test (with a little cosmetic tidying):

=============
Using Analyze
=============

Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 10000 |  3964K|       |   344 |
|*  1 |  HASH JOIN         |      | 10000 |  3964K|  3506K|   344 |
|   2 |   TABLE ACCESS FULL| T1   | 10000 |  1982K|       |    74 |
|   3 |   TABLE ACCESS FULL| T2   | 10000 |  1982K|       |    70 |
-------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."ID")

================
Using dbms_stats
================

Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 10000 |  4189K|       |   360 |
|*  1 |  HASH JOIN         |      | 10000 |  4189K|  3662K|   360 |
|   2 |   TABLE ACCESS FULL| T2   | 10000 |  2001K|       |    70 |
|   3 |   TABLE ACCESS FULL| T1   | 10000 |  2187K|       |    74 |
-------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."ID")

The order of the tables in the hash join has changed, and the cost has changed because I switched from using the analyze command to the dbms_stats package. And this was on an example that had no indexes, and used a compute / 100% sample. So we can’t blame the change in plan on differences in the statistics the two calls were supposed to collect.

What’s happened ? Take a close look at the column and row statistics we get from the two commands.

Using Analyze:

select
        table_name, avg_row_len
from    user_tables
where   table_name in ('T1','T2')
order by
        table_name
;

TABLE_NAME           AVG_ROW_LEN
-------------------- -----------
T1                           332
T2                           313

select
        table_name, column_name, avg_col_len
from
        user_tab_columns
where
        table_name in ('T1','T2')
order by
        table_name, column_id
;

TABLE_NAME           COLUMN_NAME          AVG_COL_LEN
-------------------- -------------------- -----------
T1                   ID                             3
                     N1                             3
                     V00                           10
                     V01                           10
                     V02                           10
                     V03                           10
                     V04                           10
                     V05                           10
                     V06                           10
                     V07                           10
                     V08                           10
                     V09                           10
                     V10                           10
                     V11                           10
                     V12                           10
                     V13                           10
                     V14                           10
                     V15                           10
                     V16                           10
                     V17                           10
                     V18                           10
                     V19                           10
                     PADDING                      100

T2                   ID                             3
                     N1                             3
                     V100                         200
                     PADDING                      100

Using dbms_stats:

TABLE_NAME           AVG_ROW_LEN
-------------------- -----------
T1                           328
T2                           309

TABLE_NAME           COLUMN_NAME          AVG_COL_LEN
-------------------- -------------------- -----------
T1                   ID                             4
                     N1                             4
                     V00                           11
                     V01                           11
                     V02                           11
                     V03                           11
                     V04                           11
                     V05                           11
                     V06                           11
                     V07                           11
                     V08                           11
                     V09                           11
                     V10                           11
                     V11                           11
                     V12                           11
                     V13                           11
                     V14                           11
                     V15                           11
                     V16                           11
                     V17                           11
                     V18                           11
                     V19                           11
                     PADDING                      101

T2                   ID                             4
                     N1                             4
                     V100                         201
                     PADDING                      101

The avg_col_len includes the length byte(s) for dbms_stats, but not for analyze.

Conversely, the avg_row_len includes the in-line row overhead (flag byte, lock byte, column count) for analyze but not for dbms_stats. (The row lengths for dbms_stats are one less than they should be in this report, but that’s a detail that’s been corrected in 11g.)

Since some of the optimizer arithmetic uses the avg_row_len, and some of it uses the avg_col_len, you can find that changing your statistics collection method can actually result in changes of execution plans in borderline cases.

Of course, you should have switched to using the dbms_stats package to collect statistics a few years ago – but not everyone did, and some people found they had to switch back fairly promptly because too many things seemed to go wrong … for no obvious reason.

Footnote:

I’ve just rediscovered another note I’ve written about the difference between dbms_stats and analyze, and the effect that migrated or chained rows can have on plans if you vary your usage.

Footnote 2:

A side point in this particular case is the demonstration that in a hash join the optimizer chooses the table that returns the smaller data volume (i.e. it’s looking at bytes not rows) as the build (first child) table even if the cost of acquiring that data is larger than the cost of acquiring the data from the probe (second child) table. This would have been a little more obvious if I’d generated the v00 column in table t1 as lpad(rownum,5,’0′) in which case the plan from the analyze call would have shown t1 returning 1933K bytes instead of 1982K bytes.