Убить CBO

Andrew Max
Дата: 01.08.2005 15:42:01
Ситуация – проще некуда. Имеюся две таблицы, из одной из них необходимо удалить записи по условию IN, в котором участвует вторая таблица.
Заполняем таблицы данными, создаем первичные ключи и собираем статистику:

SQL> create table t1 (g varchar2(20), code int, dp int, flag int);

Таблица создана.

SQL> insert /*+ APPEND */ into t1
  2  select 'STRING', rownum, 0, 0
  3   from all_objects
  4   where rownum <= 1000;

1000 строк создано.

SQL> commit;

Фиксация обновлений завершена.

SQL> alter table t1
  2   add constraint pk_t1 primary key (g, code, dp);

Таблица изменена.

SQL> create table t2 (code int, gcode varchar2(20), rcode int, alevel int);

Таблица создана.

SQL> insert /*+ APPEND */ into t2
  2   select t1.code, 'SOME DATA', t0.rn, 1
  3    from t1,
  4    (select rownum rn
  5      from all_objects
  6      where rownum <= 200
  7    ) t0;

200000 строк создано.

SQL> commit;

Фиксация обновлений завершена.

SQL> alter table t2
  2   add constraint pk_t2 primary key (code, gcode, rcode);

Таблица изменена.

SQL> begin
  2   dbms_stats.gather_table_stats
  3    (ownname => user, tabname => 'T1',
  4     method_opt => 'FOR ALL COLUMNS SIZE AUTO',cascade => true);
  5   dbms_stats.gather_table_stats
  6    (ownname => user, tabname => 'T2',
  7     method_opt => 'FOR ALL COLUMNS SIZE AUTO',cascade => true);
  8  end;
  9  /

Процедура PL/SQL успешно завершена.

Теперь – суть задачи. Необходимо удалить из таблицы t2 все строки, для которых в таблице t1 поле g равно ‘STRING’, поле t1.code равно t2.code, а поле t1.flg равно нулю. По тестовым данным видно, что в этом случае будут удалены все строки.

Запрос будет простейший:
SQL> set autotrace on
SQL> set timing on
SQL>
SQL> delete from t2
  2   where t2.code in
  3   (
  4    select t1.code
  5     from t1
  6     where t1.g = 'STRING' and t1.flag = 0
  7   );

200000 строк удалено.

Затрач.время: 00:00:06.00

План выполнения
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=CHOOSE (Cost=979 Card=200000 Bytes=6200000)
   1    0   DELETE OF 'T2'
   2    1     HASH JOIN (SEMI) (Cost=979 Card=200000 Bytes=6200000)
   3    2       TABLE ACCESS (FULL) OF 'T2' (Cost=378 Card=200000 Bytes=3600000)
   4    2       TABLE ACCESS (FULL) OF 'T1' (Cost=5 Card=1000 Bytes=13000)




Статистика
----------------------------------------------------------
        466  recursive calls
     815546  db block gets
        945  consistent gets
          2  physical reads
   92492964  redo size
        632  bytes sent via SQL*Net to client
        610  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
     200000  rows processed

Теперь посмотрим, как справится с задачей RBO:
SQL> rollback;

Откат завершен.

Затрач.время: 00:00:04.07
SQL> delete /*+ RULE */ from t2
  2   where t2.code in
  3   (
  4    select t1.code
  5     from t1
  6     where t1.g = 'STRING' and t1.flag = 0
  7   );

200000 строк удалено.

Затрач.время: 00:00:03.06

План выполнения
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=HINT: RULE
   1    0   DELETE OF 'T2'
   2    1     NESTED LOOPS
   3    2       VIEW OF 'VW_NSO_1'
   4    3         SORT (UNIQUE)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'T1'
   6    5             INDEX (RANGE SCAN) OF 'PK_T1' (UNIQUE)
   7    2       INDEX (RANGE SCAN) OF 'PK_T2' (UNIQUE)




Статистика
----------------------------------------------------------
        360  recursive calls
     216648  db block gets
       2953  consistent gets
          0  physical reads
   56626960  redo size
        633  bytes sent via SQL*Net to client
        622  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
     200000  rows processed

Хм... Мало того, что мы с RBO получили время, меньшее в два раза – количество db block gets, есле верить статистике, меньше в три с лишним раза. В два раза меньше генерируется redo. Правда, consistent gets побольше будет, чем в случае CBO. Однако, складывается ощущение, что запрос с хинтом RULE работает лучше.

Но я недолюбливаю хинты. Хотелось бы понять, чего не хватает оптимизатору? Что я мог упустить?

Спасибо.
dmitrysk
Дата: 01.08.2005 15:56:54
Встречал подобную ситуацию, в случае когда не собрана системная статистика (dbms_stats.gather_system_stats)
Andrew Max
Дата: 01.08.2005 16:00:45
dmitrysk
Встречал подобную ситуацию, в случае когда не собрана системная статистика (dbms_stats.gather_system_stats)


SQL> connect sys
Введите пароль:
Соединено.
SQL>
SQL> select * from aux_stats$;

SNAME                          PNAME                               PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ----------------
SYSSTATS_INFO                  STATUS                                    COMPLETED
SYSSTATS_INFO                  DSTART                                    12-24-2004 19:22
SYSSTATS_INFO                  DSTOP                                     12-24-2004 20:26
SYSSTATS_INFO                  FLAGS                                   1
SYSSTATS_MAIN                  SREADTIM                             ,556
SYSSTATS_MAIN                  MREADTIM                            2,754
SYSSTATS_MAIN                  CPUSPEED                              771
SYSSTATS_MAIN                  MBRC                                   13
SYSSTATS_MAIN                  MAXTHR                           49090560
SYSSTATS_MAIN                  SLAVETHR                               -1

10 строк выбрано.

Да, кстати, забыл в первый пост включить:
SQL> select * from v$version where rownum = 1;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
Ааз
Дата: 01.08.2005 16:47:44
Привет

db_file_multiblock_read_count
optimizer_index_caching
optimizer_index_cost_adj

в студию. Еще поставь event 10053, когда без хинта - глянь, какю цену CBO для плана с NESTED LOOPS высчитал.

Всего
--
Andrei Kriushin (Oracle8/8i/9i OCP DBA), RDTEX J.S.C.
Disclaimer: Opinions are of my own and not necessar(-il)y...
Apex
Дата: 01.08.2005 17:01:34
Так ты ж все строки удаляешь...
Andrew Max
Дата: 01.08.2005 17:03:27
Ааз
Привет...

Доброго дня.

SQL> show parameter optimizer_index

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
SQL>
SQL> show parameter db_file_multi

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     16

Трейс с включенным event 10053 сегодня впервые сделал, читать пока непривычно, вот сижу, пытаюсь вникнуть.
С nested loops - вот это ниже, вроде бы, оно?
...
Join order[2]:  T1[T1]#1  T2[T2]#0
    SORT resource      Sort statistics
      Sort width:           75 Area size:      268288 Max Area size:    13421568   Degree: 1
      Blocks to Sort:        4 Row size:           25 Rows:       1000
      Initial runs:          1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0
      Total CPU sort cost: 877680
      Total Temp space used: 0
Now joining: T2[T2]#0 *******
NL Join
  Outer table: cost: 7  cdn: 1000  rcz: 13  resp:  7
  Inner table: T2
    Access path: tsc  Resc: 378
    Join:  Resc:  377663  Resp:  377663
  Access path: index (index-only)
      Index: PK_T2
  TABLE: T2
      RSC_CPU: 55093   RSC_IO: 2
  IX_SEL:  1.0000e-003  TB_SEL:  1.0000e-003
    Join:  resc: 2135  resp: 2135
  Best NL cost: 2136  resp: 2135
Join cardinality:  200000 = outer (1000) * inner (200000) * sel (1.0000e-003)  [flag=0]
...
Apex
Дата: 01.08.2005 17:04:02
Apex
Так ты ж все строки удаляешь...

Хотя откуда об этом знать ораклу?..
----
Собрал статистику по схеме - планы стали одинаковы.
Andrew Max
Дата: 01.08.2005 17:04:42
Apex
Так ты ж все строки удаляешь...

Я знаю.
Это частный случай. Возможно, с другими данными будут удаляться не все.
Мне интересно, почему CBO выбирает такой план...
Apex
Дата: 01.08.2005 17:06:35
Andrew Max

Мне интересно, почему CBO выбирает такой план...

Мне тоже, я еще пробовал в дополнении к dbms_stats.gather_table_stats по индесам статистику собирать - не помогло. А вот по схеме - помогло.
Andrew Max
Дата: 01.08.2005 17:20:17
Apex
...я еще пробовал в дополнении к dbms_stats.gather_table_stats по индесам статистику собирать - не помогло. А вот по схеме - помогло.

Не знаю... мне такой шаманский прием не помог:
SQL> begin
  2   dbms_stats.gather_schema_stats(
  3    ownname => user,
  4    method_opt => 'FOR ALL COLUMNS SIZE AUTO',
  5    cascade => true);
  6  end;
  7  /

Процедура PL/SQL успешно завершена.

SQL> explain plan for
  2  delete from t2
  3   where t2.code in
  4   (
  5    select t1.code
  6     from t1
  7     where t1.g = 'STRING' and t1.flag = 0
  8   );

Объяснено.

SQL> @utlxpls

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------

---------------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes |TempSpc| Cost (%CPU)|
---------------------------------------------------------------------------------
|   0 | DELETE STATEMENT     |             |   200K|  6054K|       |   979  (19)|
|   1 |  DELETE              | T2          |       |       |       |            |
|*  2 |   HASH JOIN SEMI     |             |   200K|  6054K|  5864K|   979  (19)|
|   3 |    TABLE ACCESS FULL | T2          |   200K|  3515K|       |   378  (27)|
|*  4 |    TABLE ACCESS FULL | T1          |  1000 | 13000 |       |     5  (20)|
---------------------------------------------------------------------------------

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

   2 - access("T2"."CODE"="T1"."CODE")
   4 - filter("T1"."G"='STRING' AND "T1"."FLAG"=0)

16 строк выбрано.