Есть подзпарос такого типа

SELECT X FROM DB1_TRANSFER_A1
WHERE
(DEPT IN('900000000000437A','900000000000436E','90000000000040DF','9000000000004372'))
AND (PRODUCT IN(SELECT T40044916.PRODUCT as P40049295 FROM TMP_PRODUCT_LIST T40044916))
AND (PARTY IN(SELECT T40044917.PARTY as P40049296 FROM TMP_PARTY_LIST T40044917))

Две последние таблицы темповые, и без индексов, но сейчас речь не об этом.

План следующий

| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 51 | 8 (13)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN | | 1 | 12 | 5 (20)| 00:00:01 |
| 4 | SORT UNIQUE | | 1 | 6 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | TMP_PARTY_LIST | 1 | 6 | 2 (0)| 00:00:01 |
| 6 | BUFFER SORT | | 1 | 6 | 3 (34)| 00:00:01 |
| 7 | SORT UNIQUE | | 1 | 6 | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | TMP_PRODUCT_LIST | 1 | 6 | 2 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | DB1_TRANSFER_A1_BY_PARTY | 1 | | 2 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID| DB1_TRANSFER_A1 | 1 | 39 | 3 (0)| 00:00:01 |

То есть оптимизатор перемножает между собой две темповые таблицы, друг с другом совсем не связанные, одна из которых может быть и 5 миллионов, а вторая порядка 1000 или больше, а потом из всего этого выбирает.
В результате полный запрос может выполняться 20 часов.

301 NESTED LOOPS (cr=2670751934 pr=2135 pw=11755 time=37606200 us)
2420700469 NESTED LOOPS (cr=260164832 pr=1379 pw=11755 time=1652934272 us cost=7 size=51 card=1)
149093213 MERGE JOIN CARTESIAN (cr=37783 pr=1350 pw=11755 time=68828528 us cost=5 size=12 card=1)
127105 SORT UNIQUE (cr=37775 pr=1350 pw=11755 time=696514 us cost=2 size=6 card=1)
5965168 TABLE ACCESS FULL TMP_PARTY_LIST (cr=37775 pr=0 pw=0 time=1978618 us cost=2 size=6 card=1)
149093213 BUFFER SORT (cr=8 pr=0 pw=0 time=46564896 us cost=3 size=6 card=1)
1173 SORT UNIQUE (cr=8 pr=0 pw=0 time=117 us cost=2 size=6 card=1)
1173 TABLE ACCESS FULL TMP_PRODUCT_LIST (cr=8 pr=0 pw=0 time=117 us cost=2 size=6 card=1)
2420700469 INDEX RANGE SCAN DB1_TRANSFER_A1_BY_PRODUCT (cr=260127049 pr=29 pw=0 time=693975872 us cost=2 size=0 card=52)(object id 1703212)
301 TABLE ACCESS BY INDEX ROWID DB1_TRANSFER_A1 (cr=2412882726 pr=756 pw=0 time=0 us cost=2 size=39 card=1)

Для примера подняла ту же БД на тестовом сервере, и вот там он делает все красиво, сначала MERGE JOIN SEMI
DB1_TRANSFER_A1 и TMP_PRODUCT_LIST, а потом уже умножение результата на TMP_PARTY_LIST.

Как оптимизатору сказать, чтоб не умничал.
Параметры оптимизатора специально сравнивала - одинаковые, версия на главном сервере Enterprise, специально сейчас проверила на двух Standart - план получается такой же.
Через JOIN пробовала переделать, то же самое, я в догадках.

SELECT X FROM DB1_TRANSFER_A1 DBTA
JOIN TMP_PRODUCT_LIST T40044916 ON DBTA.PRODUCT=T40044916.PRODUCT
JOIN TMP_PARTY_LIST T40044917 ON DBTA.PARTY=T40044917.PRODUCT
WHERE
(DEPT IN('90000000000006D0','9000000000001D13','900000000000114F','900000000000279E'))

| 0 | SELECT STATEMENT | | 1 | 51 | 8 (13)| 00:00:01 |
| 1 | HASH UNIQUE | | 1 | 51 | 8 (13)| 00:00:01 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 51 | 7 (0)| 00:00:01 |
| 4 | MERGE JOIN CARTESIAN | | 1 | 12 | 4 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | TMP_PRODUCT_LIST | 1 | 6 | 2 (0)| 00:00:01 |
| 6 | BUFFER SORT | | 1 | 6 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | TMP_PARTY_LIST | 1 | 6 | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | DB1_TRANSFER_A1_BY_PARTY | 1 | | 2 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID| DB1_TRANSFER_A1 | 1 | 39 | 3 (0)| 00:00:01 |


пардон, до этого в плане верхнюю строчку пропустила, повторю план

| 0 | SELECT STATEMENT | | 1 | 51 | 8 (13)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 51 | 8 (13)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN | | 1 | 12 | 5 (20)| 00:00:01 |
| 4 | SORT UNIQUE | | 1 | 6 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | TMP_PARTY_LIST | 1 | 6 | 2 (0)| 00:00:01 |
| 6 | BUFFER SORT | | 1 | 6 | 3 (34)| 00:00:01 |
| 7 | SORT UNIQUE | | 1 | 6 | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | TMP_PRODUCT_LIST | 1 | 6 | 2 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | DB1_TRANSFER_A1_BY_PARTY | 1 | | 2 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID| DB1_TRANSFER_A1 | 1 | 39 | 3 (0)| 00:00:01 |

На темповых нет индексов. Я их на рабочей не могу создать, на тестовой создавала, чуть ускоряло план 9000 вместо 12000, но он там и так неплохой.

Вот, на не самом большом сервере выполнила подзапрос, предварительно темповые таблички заполнив
DB1_TRANSFER_A1=455144
TMP_PARTY_LIST=105193
TMP_PRODUCT_LIST=141

план стал следующий

| 0 | SELECT STATEMENT | | 42 | 2142 | 948 (1)| 00:00:12 |
|* 1 | HASH JOIN SEMI | | 42 | 2142 | 948 (1)| 00:00:12 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 934 | 42030 | 855 (1)| 00:00:11 |
| 4 | SORT UNIQUE | | 141 | 846 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | TMP_PRODUCT_LIST | 141 | 846 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | DB1_TRANSFER_A1_BY_PRODUCT | 11 | | 2 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID| DB1_TRANSFER_A1 | 7 | 273 | 12 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | TMP_PARTY_LIST | 11762 | 70572 | 92 (0)| 00:00:02 |

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

А на главном к примеру
DB1_TRANSFER_A1=4867123
TMP_PARTY_LIST=1461347
TMP_PRODUCT_LIST=171

Shtock,

Ага, мне примерно что-то такое и нужно было, спасибо, проблема в том, что тут перечисления таблиц нет как такового, но я думаю это можно переделать через JOIN и главное попросить разработчика внести изменения.

Я еще видела отключение _optimizer_sortmerge_join_enabled=FALSE, но в 11 оракле не нашла такого параметра, может он тут по другому называется?

Получается, что когда оптимизатору кажется, что у темповых таблиц маленькая кардинальность, то он сначала перемножает темповые, когда я на пустых таблицах на малой базе делала это понятно, что такой план получился, и когда я их заполнила понятно что план поменялся, а вот на большой базе он считает, что по сравнению с главной таблицей они все равно малы и начинает их перемножать (хотя там 5 миллионов на 1000 если первое сообщение глянуть пример)
Я эти планы смотрела и в dbms_xplan, и в sql мониторе (у меня Enterprise) отчетливо видно, что каждый запрос висящий 2 и более часов с таким планом, и даже полная трассировка запроса у меня есть, порядка 7300 секунд, из них 7200 это FETCH, и это отнюдь не вывод в программу, это вот этот подзапрос и выбор из него, работа процессора 99%.

В понедельник попробую, но отключение параметра тоже интересно, тогда не придется ждать разработчиков.

pihel,
Уже теплее, спасибо.
Я о чем-то таком догадывалась, просто я список параметров в ОЕМ смотрела, там часть показывают, а этих нет, может быть скрытые.

А почему их два, второй как я понимаю и есть MERGE JOIN CARTESIAN, а первый?
Или любой отключить и сработает?

Ага, сначала отключила 2 параметр, из общего запроса выдрала вот такой текст для этого подзапроса

NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
SORT UNIQUE
TABLE ACCESS FULL TMP_PARTY_LIST
SORT UNIQUE
TABLE ACCESS FULL TMP_PRODUCT_LIST
INDEX RANGE SCAN DB1_TRANSFER_POOL_BY_PRODUCT
TABLE ACCESS BY INDEX ROWID DB1_TRANSFER_POOL

Такое подозрение, что начал перемножать просто через HASH_JOIN, судя по тому что INDEX RANGE SCAN за NESTED LOOPS,
отключила 1 параметр тоже.

Запустила тестовый запрос, результат dbms_xplan.

| 0 | SELECT STATEMENT | | 1 | 51 | 8 (25)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 1 | 51 | 8 (25)| 00:00:01 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 45 | 5 (20)| 00:00:01 |
| 4 | SORT UNIQUE | | 1 | 6 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | TMP_PARTY_LIST | 1 | 6 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | DB1_TRANSFER_A1_BY_PARTY | 1 | | 2 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID| DB1_TRANSFER_A1 | 1 | 39 | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | TMP_PRODUCT_LIST | 1 | 6 | 2 (0)| 00:00:01 |

Запрос сам выполнился за 48 секунд в первый раз за счет чтений с диска, и за 13 секунд во второй, в трассировке план такой

101 HASH JOIN SEMI (cr=584376 pr=4 pw=0 time=300 us cost=8 size=51 card=1)
1959 NESTED LOOPS (cr=584373 pr=4 pw=0 time=617626 us)
328204 NESTED LOOPS (cr=299100 pr=4 pw=0 time=2037545 us cost=5 size=45 card=1)
1462246 SORT UNIQUE (cr=4638 pr=0 pw=0 time=271483 us cost=2 size=6 card=1)
1462246 TABLE ACCESS FULL TMP_PARTY_LIST (cr=4638 pr=0 pw=0 time=184444 us cost=2 size=6 card=1)
328204 INDEX RANGE SCAN DB1_TRANSFER_A1_BY_PARTY (cr=294462 pr=4 pw=0 time=1737 us cost=2 size=0 card=1)(object id 1703213)
1959 TABLE ACCESS BY INDEX ROWID DB1_TRANSFER_A1 (cr=285273 pr=0 pw=0 time=0 us cost=2 size=39 card=1)
173 TABLE ACCESS FULL TMP_PRODUCT_LIST (cr=3 pr=0 pw=0 time=0 us cost=2 size=6 card=1)

Лучше, гораздо лучше. До этого я его побоялась бы запускать, опасаясь уйти в бесконечность.

В полном запросе этот блок такой же, осталось поймать реальный запрос от пользователя, может он тестовые и реальные по разному разбирает.

Ага, а через два JOIN план такой...

| 0 | SELECT STATEMENT | | 1 | 51 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 51 | 7 (15)| 00:00:01 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 45 | 4 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | TMP_PRODUCT_LIST | 1 | 6 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | DB1_TRANSFER_A1_BY_PRODUCT | 52 | | 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| DB1_TRANSFER_A1 | 1 | 39 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | TMP_PARTY_LIST | 1 | 6 | 2 (0)| 00:00:01 |

На продукты умножать было бы правильнее, их там всего 1000 в таблице, разработчика бы...

Ну что я могу сказать, большинство во таких запросиков где TMP_PARTY_LIST=30-80 тысяч, а TMP_PRODUCT_LIST порядка 80-1000 стали выполняться нормально за 24-39 секунд по сносному плану.

Остался один большой запрос, который выполняется как я понимаю с NESTED_LOOPS на две темповых таблицы, хотя опять же как не могу понять логики. Висит 3 часа уже. Размер TMP_PARTY_LIST 500 тысяч, размер TMP_PRODUCT_LIST пока не поняла когда он туда данные запихал и сколько, в мониторинге за 24 часа нет такой вставки.
Данные из EXPLAIN PLAN FOR

| 11 | NESTED LOOPS | | 1 | 79 | 8 (13)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 12 | 5 (20)| 00:00:01 |
| 13 | SORT UNIQUE | | 1 | 6 | 2 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | TMP_PARTY_LIST | 1 | 6 | 2 (0)| 00:00:01 |
| 15 | SORT UNIQUE | | 1 | 6 | 2 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | TMP_PRODUCT_LIST | 1 | 6 | 2 (0)| 00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID| DB1_TRANSFER | 1 | 67 | 3 (0)| 00:00:01 |

Когда его же переделываешь через JOIN получается

| 12 | NESTED LOOPS | | | | | |
| 13 | NESTED LOOPS | | 1 | 73 | 5 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | TMP_PRODUCT_LIST | 1 | 6 | 2 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | DB1_TRANSFER_BY_PRODUCT | 4 | | 3 (0)| 00:00:01 |
|* 16 | TABLE ACCESS BY INDEX ROWID| DB1_TRANSFER | 1 | 67 | 3 (0)| 00:00:01 |
| 17 | TABLE ACCESS FULL | TMP_PARTY_LIST | 1 | 6 | 2 (0)| 00:00:01 |

А вот через ORDERED выходит

|* 11 | HASH JOIN | | 1 | 79 | 388K (2)| 01:17:42 |
|* 12 | HASH JOIN | | 1 | 73 | 388K (2)| 01:17:42 |
|* 13 | TABLE ACCESS FULL | DB1_TRANSFER | 1562 | 102K| 388K (2)| 01:17:42 |
| 14 | TABLE ACCESS FULL | TMP_PRODUCT_LIST | 1 | 6 | 2 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | TMP_PARTY_LIST | 1 | 6 | 2 (0)| 00:00:01 |

Что как бы не айс.

MERGE JOIN CARTESIAN отключен.
По второму плану получается только через преобразование запроса...

помощник наты, спасибо

На тестовой заполнила временные, с пустыми было MERGE (там не отключен), с полными план красивый.
PRODUCT=141
PARTY=1 000 000
DB1_TRANSFER=11 000 000

Запрос тот же.



На главной
PRODUCT=173
PARTY=1 400 000
DB1_TRANSFER=99 000 000




То есть он просто MJC заменил... Вот гад...

А вот с заполненными таблицами тот же запрос через JOIN, я в предыдущем сообщении верхнюю строчку забыла.




Хорошо конечно, что на JOIN реагировать начал, как бы теперь обойтись без разработчика.

3. Optimizer Bug (_optimizer_transitivity_retain)

The issue with Optimizer regarding (_optimizer_transitivity_retain) is explained best by Oracle ACE Syed Jaffer:

Начинаю уже вот это подозревать, я у этой сво (лочи) merge отобрала, а он то же самое через nested loops делает, разработчик сказал вигвам, у них запросы автогенерируются, join они добавить не могут.
У льюиса тоже почитала про это, но не совсем поняла, что сия фича делает начиная с 10 версии
https://jonathanlewis.wordpress.com/2006/12/13/cartesian-merge-join/