не хочу сразу писать много букв и морочить занятым людям голову полным 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