проклятые NESTED LOOPS

нубик2011
Дата: 08.03.2011 19:22:05
не хочу сразу писать много букв и морочить занятым людям голову полным SQL-ем. Если попросите - напишу все, секретов нет, но может и так у кого-то будут идеи, т.к. в задаче ничего необычного нет.

Есть SELECT из одной большой таблицы (BUBTA2, 7 млн. записей) и кучи маленьких (NUBA, SAGRD от 200 до 2000 записей).
В нем центральная часть такая:
select
 sum(LB),count(*) from BUBTA2 a, NUBA f
where f.MUSAN       = a.MUSAN
  and f.MUSAW       = a.MUSAW
  and f.SNUBAL      = a.PAS
  and a.SU40ARTA    = 2
  and ( (f.MUSAN='ME01')) 
  and ( (f.MUSAW='MRR1')) 
 and ( (f.GARUA='VORFERT')) 
если ее выполняю- все работает замечательно и быстро. Индекс берет правильный, план разумный:
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE              |            |      1 |      1 |      1 |00:00:00.51 |    4589 |   1070 |
|   2 |   TABLE ACCESS BY INDEX ROWID| BUBTA2     |      1 |     70 |  40267 |00:00:00.61 |    4589 |   1070 |
|   3 |    NESTED LOOPS              |            |      1 |    279 |  40290 |00:00:00.28 |     384 |      0 |
|*  4 |     TABLE ACCESS FULL        | NUBA       |      1 |      4 |     22 |00:00:00.01 |      23 |      0 |
|*  5 |     INDEX RANGE SCAN         | IXULBUBTA2 |     22 |    273 |  40267 |00:00:00.01 |     361 |      0 |
--------------------------------------------------------------------------------------------------------------

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

   4 - filter(("F"."GARUA"=:SYS_B_3 AND "F"."MUSAW"=:SYS_B_2 AND "F"."MUSAN"=:SYS_B_1))
   5 - access("A"."MUSAN"=:SYS_B_1 AND "A"."MUSAW"=:SYS_B_2 AND "F"."SNUBAL"="A"."PAS" AND
              "A"."SU40ARTA"=:SYS_B_0)

теперь BUBTA2 соединяется с кучей маленьких табличек, причем на эти маленькие никаких дополнительных условий не накладывается (знаю, это глупо, но так было запрограммировано и быстро этого не изменить). Все соединения идут по индексированным полям в большой табличке и соответственно первичным ключам в маленьких. Статистики показывают: consistent gets растет с 400 до 170000, время выполнения увеличивается от долей секунды до 15-20 секунд. Может ничего с этим не сделать, тогда скажите и я смирюсь... Но план мне все-таки кажется каким-то не кошерным:
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------------
|*  1 |  FILTER                                 |               |      1 |        |     18 |00:00:04.92 |     167K|   4695 |
|   2 |   SORT ORDER BY                         |               |      1 |      1 |     18 |00:00:04.92 |     167K|   4695 |
|   3 |    HASH GROUP BY                        |               |      1 |      1 |     18 |00:00:04.92 |     167K|   4695 |
|   4 |     NESTED LOOPS                        |               |      1 |      1 |  13617 |00:00:07.03 |     167K|   4695 |
|   5 |      NESTED LOOPS                       |               |      1 |      1 |  13617 |00:00:06.96 |     153K|   4694 |
|   6 |       NESTED LOOPS OUTER                |               |      1 |      1 |  13617 |00:00:06.88 |     153K|   4694 |
|   7 |        NESTED LOOPS                     |               |      1 |      1 |  13617 |00:00:06.80 |     153K|   4694 |
|   8 |         NESTED LOOPS                    |               |      1 |      1 |  13617 |00:00:06.62 |     139K|   4694 |
|   9 |          NESTED LOOPS                   |               |      1 |      1 |  13618 |00:00:06.40 |     112K|   4688 |
|  10 |           NESTED LOOPS OUTER            |               |      1 |      1 |  31388 |00:00:08.30 |   98961 |   4686 |
|  11 |            NESTED LOOPS                 |               |      1 |      1 |  31388 |00:00:08.20 |   98876 |   4681 |
|* 12 |             HASH JOIN                   |               |      1 |      1 |  31388 |00:00:00.23 |    4710 |      0 |
|* 13 |              TABLE ACCESS BY INDEX ROWID| BUBTA2        |      1 |      8 |  31388 |00:00:00.06 |    4591 |      0 |
|  14 |               NESTED LOOPS              |               |      1 |     32 |  40290 |00:00:00.24 |     386 |      0 |
|  15 |                NESTED LOOPS             |               |      1 |      4 |     22 |00:00:00.01 |      25 |      0 |
|  16 |                 NESTED LOOPS            |               |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |
|* 17 |                  INDEX UNIQUE SCAN      | SYS_C0028313  |      1 |      1 |      1 |00:00:00.01 |       1 |      0 |
|* 18 |                  INDEX UNIQUE SCAN      | SYS_C0027420  |      1 |      1 |      1 |00:00:00.01 |       1 |      0 |
|* 19 |                 TABLE ACCESS FULL       | NUBA          |      1 |      4 |     22 |00:00:00.01 |      23 |      0 |
|* 20 |                INDEX RANGE SCAN         | IXULBUBTA2    |     22 |    276 |  40267 |00:00:00.01 |     361 |      0 |
|* 21 |              INDEX RANGE SCAN           | SYS_C0028339  |      1 |     11 |    112 |00:00:00.01 |     119 |      0 |
|  22 |               SORT AGGREGATE            |               |    112 |      1 |    112 |00:00:00.01 |     116 |      0 |
|* 23 |                INDEX RANGE SCAN         | SYS_C0028339  |    112 |      1 |    124 |00:00:00.01 |     116 |      0 |
|  24 |             TABLE ACCESS BY INDEX ROWID | TBARAV2       |  31388 |      1 |  31388 |00:00:03.95 |   94166 |   4681 |
|* 25 |              INDEX UNIQUE SCAN          | SYS_C00114566 |  31388 |      1 |  31388 |00:00:00.66 |   62778 |    247 |
|* 26 |            INDEX UNIQUE SCAN            | SYS_C0020522  |  31388 |      1 |     84 |00:00:00.06 |      85 |      5 |
|  27 |           TABLE ACCESS BY INDEX ROWID   | SAGRD         |  31388 |      1 |  13618 |00:00:00.17 |   13620 |      2 |
|* 28 |            INDEX UNIQUE SCAN            | SYS_C0020970  |  31388 |      1 |  13618 |00:00:00.08 |       2 |      0 |
|* 29 |          INDEX UNIQUE SCAN              | SYS_C00110097 |  13618 |      1 |  13617 |00:00:00.09 |   27238 |      6 |
|* 30 |         INDEX RANGE SCAN                | SYS_C0027614  |  13617 |      1 |  13617 |00:00:00.13 |   13725 |      0 |
|  31 |          SORT AGGREGATE                 |               |     21 |      1 |     21 |00:00:00.01 |      23 |      0 |
|  32 |           FIRST ROW                     |               |     21 |      1 |     21 |00:00:00.01 |      23 |      0 |
|* 33 |            INDEX RANGE SCAN (MIN/MAX)   | SYS_C0027614  |     21 |      1 |     21 |00:00:00.01 |      23 |      0 |
|* 34 |        INDEX UNIQUE SCAN                | SYS_C0020970  |  13617 |      1 |  13617 |00:00:00.04 |       2 |      0 |
|* 35 |       INDEX RANGE SCAN                  | IK1BBUBU      |  13617 |      1 |  13617 |00:00:00.06 |       2 |      0 |
|* 36 |      INDEX UNIQUE SCAN                  | SYS_C00110100 |  13617 |      1 |  13617 |00:00:00.05 |   13619 |      1 |
----------------------------------------------------------------------------------------------------------------------------
Вижу в нем центральную часть - ту же что и в первом случае, но почему так много A-Rows в NESTED LOOPS с маленькими табличками и с таким большим A-Time? Притом что все соединения идут по индексированным полям в польшой табличке и соответственно первичным ключам в маленьких. Может что-то делается принципиально неправильно? Подскажите плиз, не дайте помереть дураком... ORACLE 10.2.0.4
mayton
Дата: 08.03.2011 20:11:14
Без текста второго курсора как-то непонятно...

P.S. Непоняяяяяятно... (с) Бабушка провинциального актёра
dbms_photoshop
Дата: 08.03.2011 20:16:40
нубик2011,
Ну ты же сам видишь, что E-Rows и A-Rows сильно отличаются.
Значит либо
1) у тебя скошенные (skew) некоторые распределения и кардинальности для связываемой переменной по которой был построен план и по которой ты показываешь сейчас сильно отличаются. В таком случае для 10-ки спасут литералы.
2) если же ты и так передаешь литералы, а не бинды, то просто напросто может быть устаревшая статистика.
Собери ее по схеме с параметрами
estimate_percent => null,method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY',cascade=>true
и снова выложи план так же как и сделал.
andreymx
Дата: 08.03.2011 20:21:01
SYS_B_0 - это следствие CURSOR SHARING ?
_Nikotin
Дата: 08.03.2011 22:43:20
/*+ LEADING(...) */ или /*+ ORDERED */ может помочь в крайнем случае
нубик2011
Дата: 09.03.2011 01:05:18
andreymx
SYS_B_0 - это следствие CURSOR SHARING ?


ну... наверное.. Я, как Вы наверное уже поняли, не большой специалист в оракле - так пытаюсь что-то оптимизировать по ходу дела - без глубоких познаний. Работаем глупым образом без связываемых переменных - в SQL стоят прямо литералы. Но cursor_sharing стоит на force (вроде это в 10-ке значение по умолчанию). От этого наверное и идут в плане SYS_B_0...

Или Вы что-то другое имели в виду?
нубик2011
Дата: 09.03.2011 01:11:57
_Nikotin
/*+ LEADING(...) */ или /*+ ORDERED */ может помочь в крайнем случае


Я почему-то думал, что они помогают в том случае если неправильно выбирается ведущая таблица. Но тут вроде оракел берет правильно: те же BUBTA2 и NUBA вначале и от них JOIN-нит остальные (маленькие) таблицы - этим JOINA-ам соответствует куча NESTED LOOPS. Ну я попробовал на всякий случай - не помогло :-/
_Nikotin
Дата: 09.03.2011 01:20:20
нубик2011,

Я не внимательно посмотрел, думал что проблема с этим. А так проблема в оценке кардинальности на 20 шаге.
wurdu
Дата: 09.03.2011 01:52:43
нубик2011, попробуй /*+ cardinality(f 100) */, т.е. увеличить кардинальность для NUBA.