Привет!
Сразу замечу, что в посте три пункта, два из которых вопросы, третий - просто замечание. Может кому-то будет интересно.
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).
Благодарю за отклики.