Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 IFS вместо IFFS при DELETE  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2961
Наткнулся на забавный bug оптимизатора, не нашёл ничего похожего. Суть в следующем:
+ test
drop table t1;
drop table t2;
create table t1 as select level     id, lpad('z',100,'z') dat from dual connect by level <= 1000000 order by ora_hash(rownum);
create table t2 as select level-100 id, lpad('z',100,'z') dat from dual connect by level <= 1000000  order by ora_hash(rownum);
alter table t1 add constraint pk1 primary key (id);
alter table t2 add constraint pk2 primary key (id);

begin
  dbms_stats.gather_table_stats(user, 't1', estimate_percent => 100, method_opt => 'FOR ALL COLUMNS SIZE 1', cascade => true);
  dbms_stats.gather_table_stats(user, 't2', estimate_percent => 100, method_opt => 'FOR ALL COLUMNS SIZE 1', cascade => true);
end;
/

--ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

explain plan for 
delete from t1 where not exists(select 1 from t2 where t2.id=t1.id);
select * from table(dbms_xplan.display);

explain plan for 
delete /*+ index_ffs(t1 pk1) */ from t1 where not exists(select 1 from t2 where t2.id=t1.id);
select * from table(dbms_xplan.display);


SQL> explain plan for
  2  delete from t1 where not exists(select 1 from t2 where t2.id=t1.id);
 
Explained
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 1087844786 
---------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT       |      | 10000 |    97K|       |  4300   (1)| 00:00:52 |
|   1 |  DELETE                | T1   |       |       |       |            |          |
|*  2 |   HASH JOIN ANTI       |      | 10000 |    97K|    16M|  4300   (1)| 00:00:52 |
|   3 |    INDEX FULL SCAN     | PK1  |  1000K|  4882K|       |  2100   (1)| 00:00:26 |
|   4 |    INDEX FAST FULL SCAN| PK2  |  1000K|  4882K|       |   574   (2)| 00:00:07 |
--------------------------------------------------------------------------------------- 
Predicate Information (identified by operation id):
--------------------------------------------------- 
   2 - access("T2"."ID"="T1"."ID")
 
16 rows selected

SQL> explain plan for
  2  delete /*+ index_ffs(t1 pk1) */ from t1 where not exists(select 1 from t2 where t2.id=t1.id);
 
Explained
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 656462145 
---------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT       |      | 10000 |    97K|       |  2774   (2)| 00:00:34 |
|   1 |  DELETE                | T1   |       |       |       |            |          |
|*  2 |   HASH JOIN ANTI       |      | 10000 |    97K|    16M|  2774   (2)| 00:00:34 |
|   3 |    INDEX FAST FULL SCAN| PK1  |  1000K|  4882K|       |   574   (2)| 00:00:07 |
|   4 |    INDEX FAST FULL SCAN| PK2  |  1000K|  4882K|       |   574   (2)| 00:00:07 |
--------------------------------------------------------------------------------------- 
Predicate Information (identified by operation id):
--------------------------------------------------- 
   2 - access("T2"."ID"="T1"."ID")
 
16 rows selected

Из 10053 trace видно что ничего кроме FTS и IFS без хинта даже не рассматривается:
+ 10053
Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for T1[T1] 
  Table: T1  Alias: T1
    Card: Original: 1000000.000000  Rounded: 1000000  Computed: 1000000.00  Non Adjusted: 1000000.00
  Access Path: TableScan
    Cost:  4225.91  Resp: 4225.91  Degree: 0
      Cost_io: 4213.00  Cost_cpu: 260752635
      Resp_io: 4213.00  Resp_cpu: 260752635
  Access Path: index (FullScan)
    Index: PK1
    resc_io: 2089.00  resc_cpu: 214876688
    ix_sel: 1.000000  ix_sel_with_filters: 1.000000 
    Cost: 2099.64  Resp: 2099.64  Degree: 1
  Best:: AccessPath: IndexRange
  Index: PK1
         Cost: 2099.64  Degree: 1  Resp: 2099.64  Card: 1000000.00  Bytes: 0

А с хинтом /*+ index_ffs(t1 pk1) */ ничего кроме FTS и IFFS:
+ 10053 with hint
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for T1[T1] 
  Table: T1  Alias: T1
    Card: Original: 1000000.000000  Rounded: 1000000  Computed: 1000000.00  Non Adjusted: 1000000.00
  Access Path: index (index (FFS))
    Index: PK1
    resc_io: 567.00  resc_cpu: 134862445
    ix_sel: 0.000000  ix_sel_with_filters: 1.000000 
  Access Path: index (FFS)
    Cost:  573.68  Resp: 573.68  Degree: 1
      Cost_io: 567.00  Cost_cpu: 134862445
      Resp_io: 567.00  Resp_cpu: 134862445
  Best:: AccessPath: IndexFFS
  Index: PK1
         Cost: 573.68  Degree: 1  Resp: 573.68  Card: 1000000.00  Bytes: 0

Поведение одинаковое на 10.2.0.4, 11.1.0.7, 11.2.0.1 и 11.2.0.2
14 сен 11, 20:28    [11278020]     Ответить | Цитировать Сообщить модератору
 Re: IFS вместо IFFS при DELETE  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2961
А с хинтом /*+ index_ffs(t1 pk1) */ ничего кроме FTS и IFFS:

читать как
С хинтом /*+ index_ffs(t1 pk1) */ рассматривает IFFS:
14 сен 11, 20:31    [11278033]     Ответить | Цитировать Сообщить модератору
 Re: IFS вместо IFFS при DELETE  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2961
Забавно от версии к версии деградировала/улучшалась формула подсчёта кардинальности (точное значение 33):
+ test
drop table t1;
drop table t2;
create table t1 as select level    id from dual connect by level <= 100000 order by ora_hash(rownum);
create table t2 as select level-33 id from dual connect by level <= 100000  order by ora_hash(rownum);
alter table t1 add constraint pk1 primary key (id);
alter table t2 add constraint pk2 primary key (id);

begin
  dbms_stats.gather_table_stats(user, 't1', estimate_percent => 100, method_opt => 'FOR ALL COLUMNS SIZE 1', cascade => true);
  dbms_stats.gather_table_stats(user, 't2', estimate_percent => 100, method_opt => 'FOR ALL COLUMNS SIZE 1', cascade => true);
end;
/

explain plan for 
select 1 from t1 where not exists(select 1 from t2 where t2.id=t1.id);
select * from table(dbms_xplan.display);

Результаты на различный версиях:
+ 10.2.0.4 - 33
Plan hash value: 3980330428
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    33 |   264 |    45  (16)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |      |    33 |   264 |    45  (16)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |   100K|   390K|    39   (3)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK2  | 99967 |   390K|     0   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("T2"."ID"="T1"."ID")

+ 11.1.0.7 - 99967
Plan hash value: 3980330428
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 99967 |   780K|    59  (23)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |      | 99967 |   780K|    59  (23)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |   100K|   390K|    47   (3)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK2  |    33 |   132 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("T2"."ID"="T1"."ID")

+ 11.2.0.1 и 11.2.0.2 - 1000
Plan hash value: 3980330428
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1000 | 10000 |    56  (18)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI |      |  1000 | 10000 |    56  (18)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |   100K|   488K|    47   (3)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK2  | 99967 |   488K|     0   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("T2"."ID"="T1"."ID")
14 сен 11, 20:55    [11278130]     Ответить | Цитировать Сообщить модератору
 Re: IFS вместо IFFS при DELETE  [new]
А.
Guest
воспроизводится,
только пример можно сделать проще
например как-то так
update t1 
   set id = 0 
 where id <> 0;
vs
update --+ index_ffs(t1)
       t1 
   set id = 0 
 where id <> 0;
15 сен 11, 01:54    [11278610]     Ответить | Цитировать Сообщить модератору
 Re: IFS вместо IFFS при DELETE  [new]
седой
Member

Откуда:
Сообщений: 33
_Nikotin
Наткнулся на забавный bug оптимизатора, не нашёл ничего похожего. Суть в следующем:


Да, похоже на баг.

Удаляем PK, создаем уникальный индекс

alter table t1 drop constraint pk1;

create unique index pk1 on t1(id);

Видим, что в 10053 по t1 рассматривается только 1 путь доступа

+ 10053 1 путь доступа
Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for T1[T1] 
  Table: T1  Alias: T1
    Card: Original: 100000.000000  Rounded: 100000  Computed: 100000.00  Non Adjusted: 100000.00
  Access Path: TableScan
    Cost:  1549.67  Resp: 1549.67  Degree: 0
      Cost_io: 1541.00  Cost_cpu: 25959896
      Resp_io: 1541.00  Resp_cpu: 25959896
  Best:: AccessPath: TableScan
         Cost: 1549.67  Degree: 1  Resp: 1549.67  Card: 100000.00  Bytes: 0

***************************************


Добавляем ограничение уникальности
alter table t1 modify  (id not null)

Появляется еще один путь доступа - index full scan

+ 10053 2 пути доступа
Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for T1[T1] 
  Table: T1  Alias: T1
    Card: Original: 100000.000000  Rounded: 100000  Computed: 100000.00  Non Adjusted: 100000.00
  Access Path: TableScan
    Cost:  1549.67  Resp: 1549.67  Degree: 0
      Cost_io: 1541.00  Cost_cpu: 25959896
      Resp_io: 1541.00  Resp_cpu: 25959896
  Access Path: index (FullScan)
    Index: PK1
    resc_io: 209.00  resc_cpu: 21488381
    ix_sel: 1.000000  ix_sel_with_filters: 1.000000 
    Cost: 216.18  Resp: 216.18  Degree: 1
  Best:: AccessPath: IndexRange
  Index: PK1
         Cost: 216.18  Degree: 1  Resp: 216.18  Card: 100000.00  Bytes: 0

***************************************


Но, к сожалению FFS в списке нет. Очевидно, работает какая-то эвристика. Точнее, не работает :)
15 сен 11, 10:40    [11279424]     Ответить | Цитировать Сообщить модератору
 Re: IFS вместо IFFS при DELETE  [new]
седой
Member

Откуда:
Сообщений: 33
_Nikotin
Забавно от версии к версии деградировала/улучшалась формула подсчёта кардинальности (точное значение 33):


Похоже на (еще один вид?) sanity check

В 10.2.0.4 видим корректировку кардинальности:

>>> adjusting AJ/SJ sel based on min/max ranges: jsel=min(1, 0.99967)
Anti Join Card:  33.00 = outer (100000.00) * (1 - sel (0.99967))
Join Card - Rounded: 33 Computed: 33.00

Здесь все относительно ясно - вычисляем селективность на основе максимальных и минимальных значений, типа в первой таблице макс значение id 1000, во второй 997, значит после anti-join останется 3 значения.

В 11.2.0.1 формула такая же

>>> adjusting AJ/SJ sel based on min/max ranges: jsel=min(1.000000, 0.999670)
Anti Join Card:  1000.000000 = outer (100000.000000) * (1 - sel (0.999670))
Join Card - Rounded: 1000 Computed: 1000.00

Но отчего-то итоговое значение получется не 33, а 1000 :) Видимо, из соображений sanity check функция sel (0.999670) возвращает 0.01, а не 0.00033 :)
15 сен 11, 11:01    [11279581]     Ответить | Цитировать Сообщить модератору
 Re: IFS вместо IFFS при DELETE  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2961
А.
воспроизводится,
только пример можно сделать проще
например как-то так

Да, спасибо, я плотно не разбирался, привёл пример с которым столкнулся.
Вывод по-видимому такой, что по умолчанию в качестве путей доступа к модифицируемой таблице рассматриваются только FTS и IRS(IFS), при этом не учитывается доступ к строке самой таблицы для модификации (учитываются столбцы упоминавшиеся в запросе):
+ test
explain plan for
update t1 set t1.id = 1 where t1.id <> 0;
select * from table(dbms_xplan.display('PLAN_TABLE', null, 'ALL'));

explain plan for
update t1 set t1.dat = '1' where t1.id <> 0;
select * from table(dbms_xplan.display('PLAN_TABLE', null, 'ALL'));

+ plan for set t1.id = 1
Plan hash value: 2374501924
 
-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | UPDATE STATEMENT |      |   999K|  4882K|  2102   (1)| 00:00:01 |
|   1 |  UPDATE          | T1   |       |       |            |          |
|*  2 |   INDEX FULL SCAN| PK1  |   999K|  4882K|  2102   (1)| 00:00:01 |
-------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - UPD$1
   2 - UPD$1 / T1@UPD$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("ID"<>0)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   2 - (upd=2; cmp=2; cpy=2) "T1".ROWID[ROWID,10], "T1"."ID"[NUMBER,22]

+ plan for set t1.dat = '1'
Plan hash value: 2927627013
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |      |   999K|   101M|  4229   (1)| 00:00:01 |
|   1 |  UPDATE            | T1   |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   999K|   101M|  4229   (1)| 00:00:01 |
---------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - UPD$1
   2 - UPD$1 / T1@UPD$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("ID"<>0)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   2 - (upd=3; cmp=2) "T1".ROWID[ROWID,10], "T1"."ID"[NUMBER,22], "T1"."DAT"[VARCHAR2,100]


седой
Добавляем ограничение уникальности
alter table t1 modify  (id not null)
Появляется еще один путь доступа - index full scan

Почему его не было без not null очевидно :)

седой
Но отчего-то итоговое значение получется не 33, а 1000 :) Видимо, из соображений sanity check функция sel (0.999670) возвращает 0.01, а не 0.00033 :)

Похоже на правду, в 11.2 берется большее из 0.01 и подсчитанной селективности:
+ test
drop table t1;
drop table t2;
create table t1 as select level    id from dual connect by level <= 3200 order by ora_hash(rownum);
create table t2 as select level-33 id from dual connect by level <= 3200  order by ora_hash(rownum);
alter table t1 add constraint pk1 primary key (id);
alter table t2 add constraint pk2 primary key (id);

begin
  dbms_stats.gather_table_stats(user, 't1', estimate_percent => 100, method_opt => 'FOR ALL COLUMNS SIZE 1', cascade => true);
  dbms_stats.gather_table_stats(user, 't2', estimate_percent => 100, method_opt => 'FOR ALL COLUMNS SIZE 1', cascade => true);
end;
/

explain plan for 
delete from t1 where id is not null and not exists(select 1 from t2 where t2.id=t1.id);
select * from table(dbms_xplan.display);

/* |   0 | DELETE STATEMENT    |      |    33 |   264 |     4   (0)| 00:00:01 | */

drop table t1;
drop table t2;
create table t1 as select level    id from dual connect by level <= 3400 order by ora_hash(rownum);
create table t2 as select level-33 id from dual connect by level <= 3400  order by ora_hash(rownum);
alter table t1 add constraint pk1 primary key (id);
alter table t2 add constraint pk2 primary key (id);

begin
  dbms_stats.gather_table_stats(user, 't1', estimate_percent => 100, method_opt => 'FOR ALL COLUMNS SIZE 1', cascade => true);
  dbms_stats.gather_table_stats(user, 't2', estimate_percent => 100, method_opt => 'FOR ALL COLUMNS SIZE 1', cascade => true);
end;
/

explain plan for 
delete from t1 where id is not null and not exists(select 1 from t2 where t2.id=t1.id);
select * from table(dbms_xplan.display);

/* |   0 | DELETE STATEMENT    |      |    34 |   272 |     4   (0)| 00:00:01 | */
15 сен 11, 19:56    [11283740]     Ответить | Цитировать Сообщить модератору
 Re: IFS вместо IFFS при DELETE  [new]
Alexander Anokhin
Member

Откуда: Хабаровск
Сообщений: 425
Издалека, конечно, похоже на CBO багу.
Я тут порыл немного, здесь чуть больше деталей.


SQL> select * from v$version where rownum = 1;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

SQL> create table t as select * from all_objects;
 
Table created

SQL> create index i on t(object_id);
 
Index created

SQL> exec dbms_stats.gather_table_stats(user, 'T', estimate_percent => 100, cascade => true);
 
PL/SQL procedure successfully completed

SQL> explain plan for
  2    delete
  3      from t
  4     where object_id <> 0;
 
Explained
 
SQL> @plan
 
-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | DELETE STATEMENT |      | 65919 |   321K|   149   (2)| 00:00:02 |
|   1 |  DELETE          | T    |       |       |            |          |
|*  2 |   INDEX FULL SCAN| I    | 65919 |   321K|   149   (2)| 00:00:02 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID"<>0)

Index Full Scan.

Попробуем Index Fast Full Scan
SQL> explain plan for
  2    delete --+ index_ffs(t i)
  3      from t
  4     where object_id <> 0;
 
Explained
 
SQL> @plan;

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | DELETE STATEMENT      |      | 65919 |   321K|    35   (3)| 00:00:01 |
|   1 |  DELETE               | T    |       |       |            |          |
|*  2 |   INDEX FAST FULL SCAN| I    | 65919 |   321K|    35   (3)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID"<>0)



Стоимость IFFS = 35, тогда как стоимость IFS = 149, но Оракл выбирает IFS.
Попробуем запустить оба стейтмента.
SQL> begin
  2  
  3      dbms_monitor.session_trace_enable;
  4  
  5      delete
  6        from t
  7       where object_id <> 0;
  8  
  9      rollback;
 10  
 11      delete --+ index_ffs(t i)
 12        from t
 13       where object_id <> 0;
 14  
 15      rollback;
 16  
 17  end;
 18  /
 
PL/SQL procedure successfully completed


DELETE FROM T 
WHERE
 OBJECT_ID <> 0


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      2.15       3.16       2135        155      77120       65920
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      2.15       3.16       2135        155      77120       65920

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 9511     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  T (cr=155 pr=2135 pw=0 time=0 us)
  65920   INDEX FULL SCAN I (cr=147 pr=0 pw=0 time=0 us cost=149 size=329595 card=65919)(object id 81755)

....

DELETE --+ index_ffs(t i)
 FROM T WHERE OBJECT_ID <> 0

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      3.42       4.61       2697        165     210070       65920
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      3.42       4.61       2697        165     210070       65920

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 9511     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  T (cr=165 pr=2697 pw=0 time=0 us)
  65920   INDEX FAST FULL SCAN I (cr=153 pr=0 pw=0 time=0 us cost=35 size=329595 card=65919)(object id 81755)

77120 current gets в случае Index Full Scan и 210070 current gets в случае Index Fast Full Scan.

Ниже выдержка из redo dump (частично это можно увидеть и в undo changes dump, event 10221):
IFS case
REDO RECORD - Thread:1 RBA: 0x00002c.0000c7af.0118 LEN: 0x0ff8 VLD: 0x01
SCN: 0x09d8.6f140f23 SUBSCN:  1 12/20/2011 18:13:04
CHANGE #1 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x09d8.6f140f21 SEQ:  1 OP:5.2 ENC:0
ktudh redo: slt: 0x001f sqn: 0x00000000 flg: 0x000a siz: 3380 fbi: 0
            uba: 0x00c82c4b.0392.01    pxid:  0x0000.000.00000000
CHANGE #2 TYP:1 CLS:36 AFN:3 DBA:0x00c82c4b OBJ:4294967295 SCN:0x09d8.6f140f22 SEQ:  1 OP:5.1 ENC:0
ktudb redo: siz: 3380 spc: 1238 flg: 0x000a seq: 0x0392 rec: 0x01
            xid:  0x000a.01f.00014438
ktubu redo: slt: 31 rci: 0 opc: 10.22 objn: 81755 objd: 81755 tsn: 5
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00c82c4a
index undo for leaf key operations
KTB Redo
op: 0x04  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0xffff.000.00000000 uba: 0x00000000.0000.00
                      flg: C---    lkc:  0     scn: 0x09d8.6f13d63f
Dump kdilk : itl=2, kdxlkflg=0x25 sdc=0 indexid=0x140281a block=0x0140281c
(kdxlre): restore leaf row (clear leaf delete flags)
number of keys: 255
key sizes:
 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10
 10 10 10 10 10 10 10 10 10 10 10 10 10 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11
 11 11 11 11 11 11 11 11 11 11 11 11 10 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11
key :(2705):
 02 c1 03 06 01 40 27 2b 00 1a 02 c1 04 06 01 40 27 2b 00 04 02 c1 05 06 01
 40 27 2b 00 1f 02 c1 06 06 01 40 27 2b 00 16 02 c1 07 06 01 40 27 2b 00 03
 02 c1 08 06 01 40 27 2b 00 12 02 c1 09 06 01 40 27 2b 00 2e 02 c1 0a 06 01
 40 27 2b 00 20 02 c1 0b 06 01 40 27 2b 00 37 02 c1 0c 06 01 40 27 2b 00 02
...
selflock: (32):
 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 00 00 00 00 00 00 00
bitmap: (32):
 ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff
 ff ff ff ff ff ff ff
CHANGE #3 TYP:0 CLS: 1 AFN:5 DBA:0x0140281c OBJ:81755 SCN:0x09d8.6f140727 SEQ:232 OP:10.4 ENC:0
index redo (kdxlde):  delete leaf row
KTB Redo
op: 0x01  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x000a.01f.00014438    uba: 0x00c82c4b.0392.01
REDO: ARRAY / -- / --
itl: 2, sno: 0, row size 3725
number of keys: 255
slots:
 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49
 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 12
 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190
 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254


IFFS case
REDO RECORD - Thread:1 RBA: 0x00002e.00000005.0020 LEN: 0x00e8 VLD: 0x01
SCN: 0x09d8.6f141196 SUBSCN:  1 12/20/2011 18:20:45
CHANGE #1 TYP:0 CLS:36 AFN:3 DBA:0x00c82d1f OBJ:4294967295 SCN:0x09d8.6f141195 SEQ:  1 OP:5.1 ENC:0
ktudb redo: siz: 100 spc: 3982 flg: 0x0022 seq: 0x0392 rec: 0x1d
            xid:  0x000a.019.00014416
ktubu redo: slt: 25 rci: 28 opc: 10.22 objn: 81755 objd: 81755 tsn: 5
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
index undo for leaf key operations
KTB Redo
op: 0x04  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0xffff.000.00000000 uba: 0x00000000.0000.00
                      flg: C---    lkc:  0     scn: 0x09d8.6f13d63f
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x140281a block=0x0140281c
(kdxlre): restore leaf row (clear leaf delete flags)
key :(10):  02 c1 03 06 01 40 27 2b 00 1a
CHANGE #2 TYP:0 CLS: 1 AFN:5 DBA:0x0140281c OBJ:81755 SCN:0x09d8.6f140fbb SEQ:  2 OP:10.4 ENC:0
index redo (kdxlde):  delete leaf row
KTB Redo
op: 0x01  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x000a.019.00014416    uba: 0x00c82d1f.0392.1d
REDO: SINGLE / -- / --
itl: 2, sno: 0, row size 14

...

REDO RECORD - Thread:1 RBA: 0x00002e.00000006.007c LEN: 0x00d0 VLD: 0x01
SCN: 0x09d8.6f141196 SUBSCN:  3 12/20/2011 18:20:45
CHANGE #1 TYP:0 CLS:36 AFN:3 DBA:0x00c82d1f OBJ:4294967295 SCN:0x09d8.6f141196 SEQ:  2 OP:5.1 ENC:0
ktudb redo: siz: 84 spc: 3662 flg: 0x0022 seq: 0x0392 rec: 0x1f
            xid:  0x000a.019.00014416
ktubu redo: slt: 25 rci: 30 opc: 10.22 objn: 81755 objd: 81755 tsn: 5
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
index undo for leaf key operations
KTB Redo
op: 0x02  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C  uba: 0x00c82d1f.0392.1d
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x140281a block=0x0140281c
(kdxlre): restore leaf row (clear leaf delete flags)
key :(10):  02 c1 04 06 01 40 27 2b 00 04
CHANGE #2 TYP:0 CLS: 1 AFN:5 DBA:0x0140281c OBJ:81755 SCN:0x09d8.6f141196 SEQ:  1 OP:10.4 ENC:0
index redo (kdxlde):  delete leaf row
KTB Redo
op: 0x02  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C  uba: 0x00c82d1f.0392.1f
REDO: SINGLE / -- / --
itl: 2, sno: 1, row size 14


Как можно видеть, в случае IFS Оракл умеет делать изменения в индексном блоке пачками (ARRAY), тогда как в IFFS случае изменения идут по одной записи. Таким образом, вывод: вероятно это не баг, а фича - возможно Оракл не рассматривает IFFS потому что знает, что сможет сделать изменения с IFS более оптимально.

Стоит отметить, что это работает не всегда, например в случае ниже
  delete 
    from t
   where object_id <> 100 
     and rownum < 999999999999999;
изменения будут как и в случае IFFS - по одной записи.
20 дек 11, 18:57    [11799297]     Ответить | Цитировать Сообщить модератору
 Re: IFS вместо IFFS при DELETE  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2961
Alexander Anokhin,

Это по мотивам последнего поста Льюиса :) ?
20 дек 11, 19:03    [11799318]     Ответить | Цитировать Сообщить модератору
 Re: IFS вместо IFFS при DELETE  [new]
Alexander Anokhin
Member

Откуда: Хабаровск
Сообщений: 425
Не, совпадение. У меня этот пост несколько недель в драфтах висел.
20 дек 11, 19:04    [11799324]     Ответить | Цитировать Сообщить модератору
 Re: IFS вместо IFFS при DELETE  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2961
Alexander Anokhin,

Тогда почему он рассматривает и выбирает сам FTS ?

drop table t1;
create table t1 as select level id, lpad('z',10,'z') dat from dual connect by level <= 10000 order by ora_hash(rownum);
alter table t1 add constraint pk1 primary key (id);

begin
  dbms_stats.gather_table_stats(user, 't1', estimate_percent => 100, method_opt => 'FOR ALL COLUMNS SIZE 1', cascade => true);
end;
/

-- 1
delete from t1 where id <> 0;

-- 2
-- delete /*+ index(t1 pk1) */ from t1 where id <> 0;


В первом случае будет FTS и REDO: SINGLE как и в случае и IFFS.
Аналогично REDO 4581832 против 2691804 при IFS.
Но время 2.9 сек против 4.6 при этом.

P.S. В обоих случаях будет KDO Op code: DRP.
20 дек 11, 20:44    [11799705]     Ответить | Цитировать Сообщить модератору
 Re: IFS вместо IFFS при DELETE  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2961
_Nikotin
Но время 2.9 сек против 4.6 при этом.

Теперь не повторяется время стало наоборот при IFS ~2сек при FTS ~4сек.
20 дек 11, 20:53    [11799735]     Ответить | Цитировать Сообщить модератору
 Re: IFS вместо IFFS при DELETE  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2961
Alexander Anokhin
изменения будут как и в случае IFFS - по одной записи.

В любом случае, эту фича с IFS я вижу только в простых запросах. Для тех что были в первом сообщении в обоих случаях будет REDO: SINGLE, и IFFS соотвественно выигрывает по скорости.
20 дек 11, 23:00    [11800027]     Ответить | Цитировать Сообщить модератору
 Re: IFS вместо IFFS при DELETE  [new]
Alexander Anokhin
Member

Откуда: Хабаровск
Сообщений: 425
_Nikotin
Alexander Anokhin,

Тогда почему он рассматривает и выбирает сам FTS ?

Согласен, если это фича и ожидаемое поведение, то немного странно, что FTS рассматривается, а IFFS нет. С другой стороны не могли же они FTS исключить ;-)
Возможно, что это все таки бага.

К слову, стоит отметить очевидный факт, index full scan будет выгоднее, только если удаляется существенная доля строк.

_Nikotin
P.S. В обоих случаях будет KDO Op code: DRP.

Что ты имел ввиду? DRP - delete row piece, во всех трех случаях (FTS, IFFS, IFS) эта составляющая не меняется.
Меняется способ и количество вызовов изменения листовых блоков индекса.

Причем с точки зрения logical I/O перед каждым db block get листового блока для удаления строки из идекса, запрашивается бранч блок.
Таким образом current gets в случаях FTS, IFFS больше примерно на на количество подлежащих удалению строк умноженных на два.
21 дек 11, 01:01    [11800269]     Ответить | Цитировать Сообщить модератору
 Re: IFS вместо IFFS при DELETE  [new]
Alexander Anokhin
Member

Откуда: Хабаровск
Сообщений: 425
_Nikotin
Alexander Anokhin
изменения будут как и в случае IFFS - по одной записи.

В любом случае, эту фича с IFS я вижу только в простых запросах. Для тех что были в первом сообщении в обоих случаях будет REDO: SINGLE, и IFFS соотвественно выигрывает по скорости.



На вскидку проверил в первом примере из первого поста - там эта оптимизация прекрасно работает.
Работает если только IFS по индексу из удаляемой таблицы.

SQL> delete /*+ index(t1 pk1) index_ffs(t2@sel$1 pk2) */ from t1 where not exists(select 1 from t2 where t2.id=t1.id);

100 rows deleted


---------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT       |      |   999K|  9764K|       |  4239   (2)| 00:00:51 |
|   1 |  DELETE                | T1   |       |       |       |            |          |
|*  2 |   HASH JOIN ANTI       |      |   999K|  9764K|    16M|  4239   (2)| 00:00:51 |
|   3 |    INDEX FULL SCAN     | PK1  |  1000K|  4882K|       |  2115   (2)| 00:00:26 |
|   4 |    INDEX FAST FULL SCAN| PK2  |  1000K|  4882K|       |   474   (4)| 00:00:06 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T2"."ID"="T1"."ID")

redo
REDO RECORD - Thread:1 RBA: 0x000030.0000004b.0148 LEN: 0x0238 VLD: 0x01
SCN: 0x09d8.6f152a02 SUBSCN: 23 12/21/2011 15:19:21
CHANGE #1 TYP:0 CLS:36 AFN:3 DBA:0x00c8915c OBJ:4294967295 SCN:0x09d8.6f152a02 SEQ: 22 OP:5.1 ENC:0
ktudb redo: siz: 384 spc: 3706 flg: 0x0022 seq: 0x03a8 rec: 0x17
            xid:  0x000a.016.000144f6  
ktubu redo: slt: 22 rci: 22 opc: 10.22 objn: 81836 objd: 81836 tsn: 5
Undo type:  Regular undo       Undo type:  Last buffer split:  No 
Tablespace Undo:  No 
             0x00000000
index undo for leaf key operations
KTB Redo 
op: 0x04  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0xffff.000.00000000 uba: 0x00000000.0000.00
                      flg: C---    lkc:  0     scn: 0x09d8.6f152636
Dump kdilk : itl=2, kdxlkflg=0x25 sdc=0 indexid=0x140331a block=0x014196d3
(kdxlre): restore leaf row (clear leaf delete flags)
number of keys: 21 
key sizes:
 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5
key :(105): 
 04 c3 64 64 02 04 c3 64 64 03 04 c3 64 64 04 04 c3 64 64 05 04 c3 64 64 06
 04 c3 64 64 07 04 c3 64 64 08 04 c3 64 64 09 04 c3 64 64 0a 04 c3 64 64 0b
 04 c3 64 64 0c 04 c3 64 64 0d 04 c3 64 64 0e 04 c3 64 64 0f 04 c3 64 64 10
 04 c3 64 64 11 04 c3 64 64 12 04 c3 64 64 13 04 c3 64 64 14 04 c3 64 64 15
 04 c3 64 64 16
keydata/bitmap: (126): 
 01 41 1b 4d 00 20 01 41 35 54 00 08 01 41 34 22 00 00 01 41 36 77 00 0d 01
 41 19 a9 00 0a 01 41 3e cc 00 23 01 41 46 6b 00 15 01 41 11 29 00 2b 01 41
 18 7f 00 0d 01 41 28 6c 00 00 01 41 26 73 00 1a 01 41 35 82 00 28 01 41 14
 4b 00 38 01 41 4c 33 00 12 01 41 11 39 00 3b 01 41 38 42 00 35 01 41 12 15
 00 2c 01 41 18 e7 00 06 01 41 30 fa 00 3c 01 41 3b a8 00 1e 01 41 49 70 00
 12
selflock: (3):  00 00 00
bitmap: (3):  ff ff ff
CHANGE #2 TYP:0 CLS: 1 AFN:5 DBA:0x014196d3 OBJ:81836 SCN:0x09d8.6f1529f2 SEQ: 21 OP:10.4 ENC:0
index redo (kdxlde):  delete leaf row
KTB Redo 
op: 0x01  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x000a.016.000144f6    uba: 0x00c8915c.03a8.17
REDO: ARRAY / -- / -- 
itl: 2, sno: 458, row size 315
number of keys: 21
slots: 
 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478
 
REDO RECORD - Thread:1 RBA: 0x000030.0000004c.0190 LEN: 0x05ac VLD: 0x01
SCN: 0x09d8.6f152a02 SUBSCN: 24 12/21/2011 15:19:21
CHANGE #1 TYP:0 CLS:36 AFN:3 DBA:0x00c8915c OBJ:4294967295 SCN:0x09d8.6f152a02 SEQ: 23 OP:5.1 ENC:0
ktudb redo: siz: 1152 spc: 3320 flg: 0x0022 seq: 0x03a8 rec: 0x18
            xid:  0x000a.016.000144f6  
ktubu redo: slt: 22 rci: 23 opc: 10.22 objn: 81836 objd: 81836 tsn: 5
Undo type:  Regular undo       Undo type:  Last buffer split:  No 
Tablespace Undo:  No 
             0x00000000
index undo for leaf key operations
KTB Redo 
op: 0x04  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0xffff.000.00000000 uba: 0x00000000.0000.00
                      flg: C---    lkc:  0     scn: 0x09d8.6f152636
Dump kdilk : itl=2, kdxlkflg=0x25 sdc=0 indexid=0x140331a block=0x014196d4
(kdxlre): restore leaf row (clear leaf delete flags)
number of keys: 79 
key sizes:
 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5...
key :(393): 
 04 c3 64 64 17 04 c3 64 64 18 04 c3 64 64 19 04 c3 64 64 1a 04 c3 64 64 1b
 04 c3 64 64 1c 04 c3 64 64 1d 04 c3 64 64 1e 04 c3 64 64 1f 04 c3 64 64 20
...
keydata/bitmap: (474): 
 01 41 2f a9 00 1f 01 41 3f f2 00 0d 01 41 14 17 00 22 01 41 1e cd 00 24 01
 41 15 3d 00 10 01 41 21 4d 00 11 01 41 46 67 00 3b 01 41 37 3c 00 37 01 41
...
selflock: (10):  00 00 00 00 00 00 00 00 00 00
bitmap: (10):  ff ff ff ff ff ff ff ff ff ff
CHANGE #2 TYP:0 CLS: 1 AFN:5 DBA:0x014196d4 OBJ:81836 SCN:0x09d8.6f1529f2 SEQ: 79 OP:10.4 ENC:0
index redo (kdxlde):  delete leaf row
KTB Redo 
op: 0x01  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x000a.016.000144f6    uba: 0x00c8915c.03a8.18
REDO: ARRAY / -- / -- 
itl: 2, sno: 0, row size 1183
number of keys: 79
slots: 
 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 
...
END OF REDO DUMP


dtracelio
============================ Summary ============================
object_id    data_object_id  function   mode_held  where    count
81836        81836           kcbgcur    2          258      1
81836        81836           kcbgtcr               1047     1
81836        81836           kcbgcur    1          816      2
81836        81836           kcbget     1          820      2
81836        81836           kcbget     2          820      2
81836        81836           kcbgtcr               1048     2
81837        81837           kcbgtcr               644      2
0            -1              kcbgcur    2          10       3
0            -1              kcbgcur    2          38       4
81837        81837           kcbgtcr               645      8
81834        81834           kcbgcur    2          952      100
81836        81836           kcbgtcr               814      2087
81837        81837           kcbgtcr               872      2092


SQL> select object_id, object_name from user_objects where object_id in (81834, 81836, 81837);
 
 OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------
     81834 T1
     81836 PK1
     81837 PK2


SQL> delete /*+ index_ffs(t1 pk1) index(t2@sel$1 pk2) */ from t1 where not exists(select 1 from t2 where t2.id=t1.id);

100 rows deleted


---------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT       |      |   999K|  9764K|       |  4239   (2)| 00:00:51 |
|   1 |  DELETE                | T1   |       |       |       |            |          |
|*  2 |   HASH JOIN ANTI       |      |   999K|  9764K|    16M|  4239   (2)| 00:00:51 |
|   3 |    INDEX FAST FULL SCAN| PK1  |  1000K|  4882K|       |   474   (4)| 00:00:06 |
|   4 |    INDEX FULL SCAN     | PK2  |  1000K|  4882K|       |  2115   (2)| 00:00:26 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T2"."ID"="T1"."ID")

redo
REDO RECORD - Thread:1 RBA: 0x000032.00000005.0018 LEN: 0x00f0 VLD: 0x01
SCN: 0x09d8.6f152adb SUBSCN:  2 12/21/2011 15:21:31
CHANGE #1 TYP:0 CLS:36 AFN:3 DBA:0x00c8915f OBJ:4294967295 SCN:0x09d8.6f152adb SEQ:  1 OP:5.1 ENC:0
ktudb redo: siz: 104 spc: 5872 flg: 0x0022 seq: 0x03a8 rec: 0x0e
            xid:  0x000a.004.00014514  
ktubu redo: slt: 4 rci: 13 opc: 10.22 objn: 81836 objd: 81836 tsn: 5
Undo type:  Regular undo       Undo type:  Last buffer split:  No 
Tablespace Undo:  No 
             0x00000000
index undo for leaf key operations
KTB Redo 
op: 0x04  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0xffff.000.00000000 uba: 0x00000000.0000.00
                      flg: C---    lkc:  0     scn: 0x09d8.6f152636
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x140331a block=0x014196d4
(kdxlre): restore leaf row (clear leaf delete flags)
key :(5):  04 c3 64 64 1b
keydata/bitmap: (6):  01 41 15 3d 00 10
CHANGE #2 TYP:0 CLS: 1 AFN:5 DBA:0x014196d4 OBJ:81836 SCN:0x09d8.6f152a30 SEQ:  1 OP:10.4 ENC:0
index redo (kdxlde):  delete leaf row
KTB Redo 
op: 0x01  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x000a.004.00014514    uba: 0x00c8915f.03a8.0e
REDO: SINGLE / -- / -- 
itl: 2, sno: 4, row size 15


dtracelio
============================ Summary ============================
object_id    data_object_id  function   mode_held  where    count
81836        81836           kcbgcur    2          258      1
81837        81837           kcbgtcr               1047     1
81836        81836           kcbgtcr               644      2
81837        81837           kcbgtcr               1048     2
0            -1              kcbgcur    2          10       3
0            -1              kcbgcur    2          38       4
81836        81836           kcbgtcr               645      8
81834        81834           kcbgcur    2          952      100
81836        81836           kcbgcur    1          816      100
81836        81836           kcbget     1          820      100
81836        81836           kcbget     2          820      100
81837        81837           kcbgtcr               814      2087
81836        81836           kcbgtcr               872      2092
21 дек 11, 12:40    [11802107]     Ответить | Цитировать Сообщить модератору
 Re: IFS вместо IFFS при DELETE  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2961
Alexander Anokhin
_Nikotin
P.S. В обоих случаях будет KDO Op code: DRP.

Что ты имел ввиду? DRP - delete row piece, во всех трех случаях (FTS, IFFS, IFS) эта составляющая не меняется.
Меняется способ и количество вызовов изменения листовых блоков индекса.

Имел в виду, что array update из поста Льюиcа и redo: array никак не связаны кроме слова array :) - при array update изменяется KDO Op code с URP на "21", а здесь этот механизм остаётся.

Alexander Anokhin
На вскидку проверил в первом примере из первого поста - там эта оптимизация прекрасно работает.
Работает если только IFS по индексу из удаляемой таблицы.

Проверил на немного другом примере, здесь действительно работает. Пример приведу чуть позже.
21 дек 11, 13:32    [11802651]     Ответить | Цитировать Сообщить модератору
 Re: IFS вместо IFFS при DELETE  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2961
_Nikotin
Проверил на немного другом примере, здесь действительно работает. Пример приведу чуть позже.

Разобрался, я для скорости изменил столбец dat на lpad('z',10,'z'), что привело к FTS в плане без хинтов.
21 дек 11, 16:48    [11804609]     Ответить | Цитировать Сообщить модератору
 Re: IFS вместо IFFS при DELETE  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2961
Льюис отправил сюда tuning-updates
21 дек 11, 19:46    [11805678]     Ответить | Цитировать Сообщить модератору
 Re: IFS вместо IFFS при DELETE  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2961
По поводу исходного примера с отсутствием FFS.

Джонатан предполагает что исходная проблема возникает из-за особого условия в коде оптимизатора.

a) Стоимость удаления фактически считается как стоимость получения rowid удаляемых строк.
b) IFFS может быть выбран оптимизатором только когда все стобцы из запроса входят в индекс.
c) Есть некий код, определяющий могут ли потребности запроса быть удовлетворены только из индекса.

Догадка состоит в том, что код из части (с) обрабатывает DELETE statement как "требующий посещения таблицы", перед тем как код, преобразующий DELETE решит что это эквивалентно "выбору rowid".


Тот факт что без хинта FFS даже не рассматривается оптимизатором следует из этого предположения.

Один код решает какие пути доступа рассматривать, и исключает IFFS автоматически потому как распознаёт что delete обращается к таблице, поэтому он не появляется в списке доступных. А другой, несмотря на это, следует хинту и рассматривает FFS безоговорочно - и этот код не включает в себя проверку как в первом коде. Конечный результат: FFS не может появиться без хинта, но если использовать хинт то FFS происхожит и имеет меньшую стоимость, и действительно выполняется.


С этими гипотезами соласуется и то, что для IOT прекрасно используется FFS, так как нет необходимости посещать таблицу по rowid.

drop table t purge;
create table t (a number primary key) organization index;
explain plan for delete t where a <> 0;
select * from table(dbms_xplan.display);

--------------------------------------------------------------------------------------------

| Id  | Operation             | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT      |                    |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  DELETE               | T                  |       |       |            |          |
|*  2 |   INDEX FAST FULL SCAN| SYS_IOT_TOP_855110 |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
25 дек 11, 01:24    [11821490]     Ответить | Цитировать Сообщить модератору
 Re: IFS вместо IFFS при DELETE  [new]
Alexander Anokhin
Member

Откуда: Хабаровск
Сообщений: 425
_Nikotin
Льюис отправил сюда tuning-updates

Интересно, не читал этого поста ранее. Спасибо.

_Nikotin
По поводу исходного примера с отсутствием FFS.

Джонатан предполагает что исходная проблема возникает из-за особого условия в коде оптимизатора.

a) Стоимость удаления фактически считается как стоимость получения rowid удаляемых строк.
b) IFFS может быть выбран оптимизатором только когда все стобцы из запроса входят в индекс.
c) Есть некий код, определяющий могут ли потребности запроса быть удовлетворены только из индекса.

Догадка состоит в том, что код из части (с) обрабатывает DELETE statement как "требующий посещения таблицы", перед тем как код, преобразующий DELETE решит что это эквивалентно "выбору rowid".


Тот факт что без хинта FFS даже не рассматривается оптимизатором следует из этого предположения.

Один код решает какие пути доступа рассматривать, и исключает IFFS автоматически потому как распознаёт что delete обращается к таблице, поэтому он не появляется в списке доступных. А другой, несмотря на это, следует хинту и рассматривает FFS безоговорочно - и этот код не включает в себя проверку как в первом коде. Конечный результат: FFS не может появиться без хинта, но если использовать хинт то FFS происхожит и имеет меньшую стоимость, и действительно выполняется.




Да, возможно и так.


_Nikotin
С этими гипотезами соласуется и то, что для IOT прекрасно используется FFS, так как нет необходимости посещать таблицу по rowid.

drop table t purge;
create table t (a number primary key) organization index;
explain plan for delete t where a <> 0;
select * from table(dbms_xplan.display);

--------------------------------------------------------------------------------------------

| Id  | Operation             | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT      |                    |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  DELETE               | T                  |       |       |            |          |
|*  2 |   INDEX FAST FULL SCAN| SYS_IOT_TOP_855110 |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Это не самый удачный пример. Здесь в IFS случае не будет ARRAY оптимизации.
Количество I/O будет минимально в обоих случаях (IFFS & IFS), за счет многократных посещений блока.

Если, например, в таблицу добавить столбец и на него повесить индекс, вот тогда по этому новому индексу будет ARRAY оптимизация redo/undo.
По индексу SYS_IOT_TOP_855110 ее по прежнему не будет.
Тогда IFS будет выгоднее, но в этом случае CBO будет рассматривать и выбирать IFFS!
Но опять же это все гадание по косвенным признакам, можно было бы также предположить, что это фича, а для IOT эта фича отключена по тем причинам, по которым я назвал пример не самым удачным.
Лично я больше склоняюсь к тому, что это больше баг, чем так задумано.

Стоит отметить отдельно сказанное выше: при IFS/IRS обсуждаемая ARRAY оптимизация будет происходить для всех индексов, учавствующих в DML, а не только тот по которому IFS.
Это важно.
Как я уже писал выше на каждое изменение leaf блока запрашиваются все бранчи выше (точнее я писал что запрашивается бранч, это было для индекса с blevel = 1).
Если на таблице 10 индексов, и каждый с blevel=4, то при удалении 100000 строк будет в случаях FTS & IFFS примерно 100000*(4+1)*10 ~ 5 000 000 current gets по индексам.
С оптимизацией это количество (а следовательно латчи, cpu, etc) будет в разы меньше.
27 дек 11, 03:18    [11830482]     Ответить | Цитировать Сообщить модератору
 Re: IFS вместо IFFS при DELETE  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2961
Alexander Anokhin,

Alexander Anokhin
Тогда IFS будет выгоднее, но в этом случае CBO будет рассматривать и выбирать IFFS!

Именно потому что не нужен доступ к таблице он и рассматривает IFFS по PK.
ARRAY оптимизация и рассмотрение или нет IFFS оптимизатором не связанные напрямую вещи.
27 дек 11, 09:48    [11830891]     Ответить | Цитировать Сообщить модератору
 Re: IFS вместо IFFS при DELETE  [new]
Alexander Anokhin
Member

Откуда: Хабаровск
Сообщений: 425
_Nikotin
ARRAY оптимизация и рассмотрение или нет IFFS оптимизатором не связанные напрямую вещи.

Тоже так считаю, хотя и есть некая ненулевая вероятность, что это сделано намеренно.
Это не было поинтом моего поста, если ты об этом.

Но я так считаю, не столько потому что какие-то косвенные доказательства можно найти в примерах с IOT, в которых тем более этой оптимизации нет без дополнтельных условий.
А потому, что стоимость/эффективность её состоит в рассмотрении всех, учавствующих в DML, индексов таблицы. Очевидно, что в текущих версиях этого нет.
+ example

Например, есть таблица с реверсивным индексом. При IFS доступе redo/undo у него будет SINGLE.
Оптимизатор выбирает IFS. Для этого индекса это бесмысленно.
Но представим, что на таблице еще 15 не реверсивных b-tree индексов. Совсем другое дело.
Напомню, ARRAY оптимизация будет для всех учавствующих индексов (т.е. кроме тех, как например в UPDATE колонка не менялась, индекс не меняется).

Поэтому намеренное убирание IFFS из рассмотрения в текущей рассматриваемой ветке вариантов доступа, как путь к обсуждаемой оптимизации, было бы очень топорно ;-)
28 дек 11, 00:48    [11836298]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить