Странные значения E-Rows, A-Rows.

dbms_photoshop
Дата: 12.03.2011 13:15:51
Привет!
Сразу замечу, что в посте три пункта, два из которых вопросы, третий - просто замечание. Может кому-то будет интересно.

1. Меня удивиляет, что в некоторых случаях TABLE ACCESS BY INDEX ROWID > INDEX UNIQUE SCAN для ожидаемого числа строк. На практике, на сколько мне известно может быть только TABLE ACCESS BY INDEX ROWID <= INDEX UNIQUE (и не только UNIQUE) SCAN.
На freelists.org была тема dbms_xplan: unexpected expected number of rows (E-Rows), но никто не ответил.
Видно что TABLE ACCESS BY INDEX ROWID E-Rows = общее число строк в таблице. Но почему? Ясно же, что там никогда не может быть больше одной строки.

2. При NESTED LOOPS удивило, что A-Rows больше фактического значения строк, получаемого при связанных циклах. Не понятно почему в нижеуказанном примере A-Rows = 53, а не 50?!

create table t01 as select rownum id, '*' name from dual connect by level <= 30;

Table created.

create unique index t01_i on t01 (id);

Index created.

create table t02 as select rownum id, mod(rownum,2)+1 id_id, '*' name from dual connect by level <= 50;

Table created.

create unique index t02_i1 on t02 (id);

Index created.

create index t02_i2 on t02 (id_id);

Index created.


BEGIN
    dbms_stats.gather_table_stats(USER,
                                  'T01',
                                  estimate_percent => NULL,
                                  method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
                                  cascade => TRUE);
    dbms_stats.gather_table_stats(USER,
                                  'T02',
                                  estimate_percent => NULL,
                                  method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
                                  cascade => TRUE);
END;
/

PL/SQL procedure successfully completed.


alter session set statistics_level = all;

Session altered.


select /*+ use_nl(t1 t2) */
t1.name||t2.name result
from t01 t1
join t02 t2 on t1.id = t2.id_id
where t2.id = 1;

RE
--
**


select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6ta7z4fp4wt16, child number 0
-------------------------------------
select /*+ use_nl(t1 t2) */ t1.name||t2.name result from t01 t1 join t02 t2 on t1.id =
t2.id_id where t2.id = 1

Plan hash value: 3664413308

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  NESTED LOOPS                |        |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T02    |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  3 |    INDEX UNIQUE SCAN         | T02_I1 |      1 |      1 |      1 |00:00:00.01 |       1 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T01    |      1 |     30 |      1 |00:00:00.01 |       2 |
|*  5 |    INDEX UNIQUE SCAN         | T01_I  |      1 |      1 |      1 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------

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

   3 - access("T2"."ID"=1)
   5 - access("T1"."ID"="T2"."ID_ID")


24 rows selected.


select /*+ use_nl(t1 t2) */
count(t2.id)
from t01 t1
join t02 t2 on t1.id = t2.id_id
where t1.id in (1,2);

COUNT(T2.ID)
------------
          50


select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  19nbc9kx8hkbs, child number 0
-------------------------------------
select /*+ use_nl(t1 t2) */ count(t2.id) from t01 t1 join t02 t2 on t1.id = t2.id_id
where t1.id in (1,2)

Plan hash value: 1463040450

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |      1 |00:00:00.01 |       5 |
|   1 |  SORT AGGREGATE              |        |      1 |      1 |      1 |00:00:00.01 |       5 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T02    |      1 |     25 |     50 |00:00:00.01 |       5 |
|   3 |    NESTED LOOPS              |        |      1 |     50 |     53 |00:00:00.01 |       4 |
|   4 |     INLIST ITERATOR          |        |      1 |        |      2 |00:00:00.01 |       2 |
|*  5 |      INDEX UNIQUE SCAN       | T01_I  |      2 |      2 |      2 |00:00:00.01 |       2 |
|*  6 |     INDEX RANGE SCAN         | T02_I2 |      2 |     25 |     50 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------

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

   5 - access(("T1"."ID"=1 OR "T1"."ID"=2))
   6 - access("T1"."ID"="T2"."ID_ID")
       filter(("T2"."ID_ID"=1 OR "T2"."ID_ID"=2))


26 rows selected.


select /*+ use_nl(t1 t2) */
count(t2.id)
from t01 t1
join t02 t2 on t1.id = t2.id_id
where t1.id between 1 and 2;

COUNT(T2.ID)
------------
          50


select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  cr6upf70fxtfw, child number 0
-------------------------------------
select /*+ use_nl(t1 t2) */ count(t2.id) from t01 t1 join t02 t2 on t1.id = t2.id_id
where t1.id between 1 and 2

Plan hash value: 2465331157

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  SORT AGGREGATE              |        |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T02    |      1 |     17 |     50 |00:00:00.01 |       4 |
|   3 |    NESTED LOOPS              |        |      1 |     34 |     53 |00:00:00.01 |       3 |
|*  4 |     INDEX RANGE SCAN         | T01_I  |      1 |      2 |      2 |00:00:00.01 |       1 |
|*  5 |     INDEX RANGE SCAN         | T02_I2 |      2 |     25 |     50 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------

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

   4 - access("T1"."ID">=1 AND "T1"."ID"<=2)
   5 - access("T1"."ID"="T2"."ID_ID")
       filter(("T2"."ID_ID">=1 AND "T2"."ID_ID"<=2))


25 rows selected.

3. Это уже не вопрос, а замечание.
В комментариях к Troubleshooting Bad Execution Plans Tanel Poder обсуждал с Greg Rahn как же все таки связаны E-Rows, A-Rows при связанных циклах.
Ответ был таков:
Greg Rahn
with NJ joins the value for the inner row source E-Rows represents the per iteration value. Thus one must multiply the number of rows for a single iteration times the number of iterations. The A-Rows value is the cumulative for the given operation.

То есть A-Rows = E-Rows * Starts.
Я бы уточнил, что это так, но для случаев, когда не предшествует INLIST ITERATOR.
Естественно речь про 'ALLSTATS LAST'.

Все вышесказанное продемонстрировано в тест кейсе (10.2.0.5.0).
Благодарю за отклики.
_Nikotin
Дата: 12.03.2011 23:48:29
1. Значение в плане берется из Single Table Cardinality Estimation, для расчетов оно не используется.
2. Это ошибка при сборе статистики выполнения. В 11 исправлен план - нет висящего в воздухе TABLE ACCESS BY INDEX ROWID.
3. Комментарий Greg Rahn относится к отличию A-rows и E-rows для inner row source. Никакой связи вида "A-Rows = E-Rows * Starts" нет.
+ test
drop table t01;
drop table t02;

create table t01 as select rownum id, '*' name from dual connect by level <= 30;

create unique index t01_i on t01 (id);

create table t02 as select rownum id, mod(rownum,3)+1 id_id, '*' name from dual connect by level <= 50;

create unique index t02_i1 on t02 (id);

create index t02_i2 on t02 (id_id);


BEGIN
    dbms_stats.gather_table_stats(USER,
                                  'T01',
                                  estimate_percent => NULL,
                                  method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
                                  cascade => TRUE);
    dbms_stats.gather_table_stats(USER,
                                  'T02',
                                  estimate_percent => NULL,
                                  method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
                                  cascade => TRUE);
END;
/

select /*+ gather_plan_statistics leading(t1) use_nl(t1 t2) */ *
from t01 t1
join t02 t2 on t1.id = t2.id_id
where t1.id in (1,2);
  
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
+ result
SQL_ID  6t1sgcux62sym, child number 0
-------------------------------------
select /*+ gather_plan_statistics leading(t1) use_nl(t1 t2) */ * from t01 t1 join t02 t2 
on t1.id = t2.id_id where t1.id in (1,2)
 
Plan hash value: 960182074
 
---------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID   | T02    |      1 |     11 |     33 |00:00:00.01 |      13 |
|   2 |   NESTED LOOPS                 |        |      1 |     22 |     36 |00:00:00.01 |       9 |
|   3 |    INLIST ITERATOR             |        |      1 |        |      2 |00:00:00.01 |       4 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T01    |      2 |      2 |      2 |00:00:00.01 |       4 |
|*  5 |      INDEX UNIQUE SCAN         | T01_I  |      2 |      2 |      2 |00:00:00.01 |       2 |
|*  6 |    INDEX RANGE SCAN            | T02_I2 |      2 |     11 |     33 |00:00:00.01 |       5 |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - access(("T1"."ID"=1 OR "T1"."ID"=2))
   6 - access("T1"."ID"="T2"."ID_ID")
       filter(("T2"."ID_ID"=1 OR "T2"."ID_ID"=2))
wurdu
Дата: 13.03.2011 00:41:24
_Nikotin
2. Это ошибка при сборе статистики выполнения. В 11 исправлен план - нет висящего в воздухе TABLE ACCESS BY INDEX ROWID.
Не совсем понятно, что имеется в виду. Вполне адекватный план для NLJ. В 11g появился Batching Nested Loop Join, соответственно план может быть другим. При отключении /*+ NO_NLJ_BATCHING(..)*/ получим тот же план.
Original and New Implementation for Nested Loop Joins
_Nikotin
Дата: 13.03.2011 00:57:39
wurdu
В 11g появился Batching Nested Loop Join, соответственно план может быть другим.

Да, я ошибся.
_Nikotin
Дата: 13.03.2011 01:17:59
wurdu,

Но тем не менее, при старом варианте без дополнительного NLJ, A-rows для NLJ = 1 + сумма A-rows для inner и outer. Это похоже на ошибку сбора статистики.
wurdu
Дата: 13.03.2011 01:34:18
_Nikotin
wurdu,

Но тем не менее, при старом варианте без дополнительного NLJ, A-rows для NLJ = 1 + сумма A-rows для inner и outer. Это похоже на ошибку сбора статистики.
Похоже. Tanel Poder достаточно глубоко объяснил механизмы подсчета: Advanced Oracle Troubleshooting Guide, Part 6: Understanding Oracle execution plans with os_explain. Похоже, проблема в QUERY EXECUTION STATISTICS wrapper.
wurdu
Дата: 13.03.2011 06:15:24
Первый случай связан с уникальностью индекса t01_i. С не уникальным индексом rows будут показаны правильно. При уникальном индексе оптимизатор использует другой алгоритм, т.к. он знает что будет одна строка. Join cardinality рассчитывается как
Join Card: 1.000000 = = outer (1.000000) * inner (1.000000) * sel (0.033333) с уникальным и
Join Card: 1.000000 = = outer (1.000000) * inner (30.000000) * sel (0.033333) с не уникальным.
Если мы даже "испортим" статистику
exec dbms_stats.set_column_stats(
        ownname        => user,
        tabname        => 't01',
        colname        => 'id',
        distcnt        => 1,
        density        => 1
    ); 
, с не уникальным:
Join Card:  30.000000 = = outer (1.000000) * inner (30.000000) * sel (1.000000)
Best so far:  Table#: 0  cost: 1.0004  card: 1.0000  bytes: 8
              Table#: 1  cost: 2.0014  card: 30.0000  bytes: 390
С уникальным
Join Card:  1.000000 = = outer (1.000000) * inner (1.000000) * sel (1.000000)
Best so far:  Table#: 0  cost: 1.0004  card: 1.0000  bytes: 8
              Table#: 1  cost: 2.0009  card: 1.0000  bytes: 13
Т.е. это, конечно, баг с отображением rows в плане, но внутри оптимизатор считает все правильно.
dbms_photoshop
Дата: 14.03.2011 00:46:50
_Nikotin, wurdu, спасибо, что приняли участие!
Сейчас у меня голова совершенно не соображает, чтоб думать об этих вещах, но все переварю на светлую голову и дискуссия продолжится.
С _Nikotin я так точно готов поспорить. :)
_Nikotin
Дата: 14.03.2011 00:55:00
dbms_photoshop,

Если по поводу 3 - то давай :)
Alexander Anokhin
Дата: 14.03.2011 12:48:53
dbms_photoshop
Привет!
Сразу замечу, что в посте три пункта, два из которых вопросы, третий - просто замечание. Может кому-то будет интересно.

1. Меня удивиляет, что в некоторых случаях TABLE ACCESS BY INDEX ROWID > INDEX UNIQUE SCAN для ожидаемого числа строк. На практике, на сколько мне известно может быть только TABLE ACCESS BY INDEX ROWID <= INDEX UNIQUE (и не только UNIQUE) SCAN.
...


Иногда Оракл считает селективности/кардинальности индексного сканирования и родительского table access по разному.
http://alexanderanokhin.wordpress.com/2011/02/08/cbo-defaults/
http://alexanderanokhin.wordpress.com/2011/02/15/jokes-of-the-cbo-with-local-indexes/