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

Откуда: Москва
Сообщений: 955
Есть несложный запрос:
SELECT 
	NVL(P1.DEFECT,P2.DEFECT) DEFECT,
	NVL(P1.QTY,0) QTY1,
	NVL(P2.QTY,0) QTY2
FROM
	(SELECT DEFECT,QTY
		FROM TABLE(IDP_R_GET_DEFECTS('6','10','22222','6','27',TO_DATE('01-NOV-2008'),TO_DATE('31-DEC-2008')))
		) P1

	FULL OUTER JOIN

	(SELECT DEFECT,QTY
		FROM TABLE(IDP_R_GET_DEFECTS('6','10','22222','6','27',TO_DATE('01-APR-2009'),TO_DATE('31-MAY-2009')))
		) P2

	ON P1.DEFECT=P2.DEFECT

По отдельности каждый из подзапросов возвращает около десятка строк и выполняется около 2 секунд. А при их соединении в приведенном примере этот запрос выполняется ну очень долго.

Вот план этого запроса:
PLAN_TABLE_OUTPUT

 
--------------------------------------------------------------------------------------------
| Id  | Operation                             |  Name              | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                    |   675K|    57M| 89886 |
|   1 |  VIEW                                 |                    |   675K|    57M| 89886 |
|   2 |   UNION-ALL                           |                    |       |       |       |
|*  3 |    HASH JOIN OUTER                    |                    |   667K|    29M|    27 |
|   4 |     COLLECTION ITERATOR PICKLER FETCH | IDP_R_GET_DEFECTS  |       |       |       |
|   5 |     VIEW                              |                    |  8168 |   358K|    11 |
|   6 |      COLLECTION ITERATOR PICKLER FETCH| IDP_R_GET_DEFECTS  |       |       |       |
|   7 |    NESTED LOOPS ANTI                  |                    |  8086 | 16172 | 89859 |
|   8 |     COLLECTION ITERATOR PICKLER FETCH | IDP_R_GET_DEFECTS  |       |       |       |
|*  9 |     COLLECTION ITERATOR PICKLER FETCH | IDP_R_GET_DEFECTS  |       |       |       |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("P2"."DEFECT"(+)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
   9 - filter(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
 
Note: cpu costing is off
Мне непонятно, откуда и зачем там взялся NESTED LOOPS ANTI под номерами 7-9, который сьедает немеренно ресурсов. Как от него избавиться?
21 май 09, 16:04    [7210758]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от непонятного лишнего NESTED LOOPS ANTI  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
publexus
Мне непонятно, откуда и зачем там взялся NESTED LOOPS ANTI под номерами 7-9, который сьедает немеренно ресурсов.
Как это откуда взялся? Оракл выполнил full join как union all 2х join-ов, один из которых проверяет "несуществование".
А где Вы видите, что на NESTED LOOPS ANTI тратится немерянно ресурсов? В трассе?

И сколько кстати (порядок) записей возвращают эти две функции? Оракл не может оценить эти цифры сам..
21 май 09, 17:11    [7211460]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от непонятного лишнего NESTED LOOPS ANTI  [new]
publexus
Member

Откуда: Москва
Сообщений: 955
Jannny
publexus
Мне непонятно, откуда и зачем там взялся NESTED LOOPS ANTI под номерами 7-9, который сьедает немеренно ресурсов.
Как это откуда взялся? Оракл выполнил full join как union all 2х join-ов, один из которых проверяет "несуществование".
А где Вы видите, что на NESTED LOOPS ANTI тратится немерянно ресурсов? В трассе?

И сколько кстати (порядок) записей возвращают эти две функции? Оракл не может оценить эти цифры сам..


Уточняю некоторые моменты:
1. Если запустить каждый из подзапросов отдельно, то они выполняются несколько 2-3 секунды.
2. Каждый подзапрос возвращает около десятка строк, весь запрос возвращает около полтора десятка строк.
3. Если делать не full outer join, а left outer или right outer join, то запрос выполняется 4-6 секунд (т.е. приблизительно как сумма времени выполнения обоих подзапросов), в плане нет nested loop anti.

Почему же при full outer join время выполнения составляет около минут и в плане появляется nested loop anti?
21 май 09, 17:20    [7211537]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от непонятного лишнего NESTED LOOPS ANTI  [new]
publexus
Member

Откуда: Москва
Сообщений: 955
Еще дополнение: если пытаюсь вручную эмулировать full outer join переписав sql:

SELECT
    P.DEFECT DEFECT,
    SUM(P.QTY1) QTY1,
    SUM(P.QTY2) QTY2
FROM
    (SELECT 
        P1.DEFECT DEFECT,
        P1.QTY QTY1,
        0 QTY2
    FROM
        TABLE(IDP_R_GET_DEFECTS('6','10','22222','6','27',TO_DATE('01-JAN-2008'),TO_DATE('31-DEC-2008'))) P1

    UNION ALL

    SELECT 
        P2.DEFECT DEFECT,
        0 QTY1,
        P2.QTY QTY2
    FROM
       TABLE(IDP_R_GET_DEFECTS('6','10','22222','6','27',TO_DATE('01-JAN-2009'),TO_DATE('31-DEC-2009'))) P2
    ) P
GROUP BY
    P.DEFECT
;

то, запрос выполняется нормально(4-6 секунд), и план соответствующий:

PLAN_TABLE_OUTPUT

 
-------------------------------------------------------------------------------------------
| Id  | Operation                            |  Name              | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                    |       |       |       |
|   1 |  SORT GROUP BY                       |                    |       |       |       |
|   2 |   VIEW                               |                    |       |       |       |
|   3 |    UNION-ALL                         |                    |       |       |       |
|   4 |     COLLECTION ITERATOR PICKLER FETCH| IDP_R_GET_DEFECTS  |       |       |       |
|   5 |     COLLECTION ITERATOR PICKLER FETCH| IDP_R_GET_DEFECTS  |       |       |       |
-------------------------------------------------------------------------------------------
 
Note: rule based optimization

Почему же в исходном запросе происходит косяк, и как может подсказать оптимизатору нормальный план?
21 май 09, 17:40    [7211695]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от непонятного лишнего NESTED LOOPS ANTI  [new]
Я и ёжик
Member

Откуда: СПб
Сообщений: 1815
publexus
Еще дополнение: если пытаюсь вручную эмулировать full outer join переписав sql:


Вы похоже плохо понимаете , что такое full outer join, вы не эмулировали full outer join, а сделали? простое объединение.
Full outer join в вашем случае эмулируется примерно так (и Oracle его делает так, до 11g):

SELECT 
	NVL(P1.DEFECT,P2.DEFECT) DEFECT,
	NVL(P1.QTY,0) QTY1,
	NVL(P2.QTY,0) QTY2
FROM
      ( SELECT DEFECT,QTY
	FROM TABLE(IDP_R_GET_DEFECTS('6','10','22222','6','27',TO_DATE('01-NOV-2008'),TO_DATE('31-DEC-2008')))
		) P1
	left OUTER JOIN
	(SELECT DEFECT,QTY
		FROM TABLE(IDP_R_GET_DEFECTS('6','10','22222','6','27',TO_DATE('01-APR-2009'),TO_DATE('31-MAY-2009')))
        	) P2
	ON P1.DEFECT=P2.DEFECT
union all
SELECT 
	P2.DEFECT    DEFECT,
	0             QTY1,
	NVL(P2.QTY,0) QTY2
FROM
	(SELECT DEFECT,QTY
		FROM TABLE(IDP_R_GET_DEFECTS('6','10','22222','6','27',TO_DATE('01-APR-2009'),TO_DATE('31-MAY-2009')))
        	) P2
         where P2.DEFECT not in
      ( SELECT DEFECT
        	FROM TABLE(IDP_R_GET_DEFECTS('6','10','22222','6','27',TO_DATE('01-NOV-2008'),TO_DATE('31-DEC-2008')))
	) P1

Подсвеченная часть и дает ваш ANTI JOIN. Он может быть как HASH так и NL, в зависимости от статистики, в вашем случае оптимизатор вообще не знает что возвращают ваши табличные функции.
Попробуйте поиграть с подсказками /*+ CARDINALITY(P1 ??) CARDINALITY(P2 ??)*/,
вместо ?? число возвращаемых строк. Но так вы вероятно сможете получить разный тип соединения HASH ANTI JOIN или NESTED LOOP ANTI JOIN, от самого антиджойна не избавитесь,
потому что так работает full outer join ( <= 10g)
21 май 09, 18:19    [7211969]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от непонятного лишнего NESTED LOOPS ANTI  [new]
Timm
Member

Откуда: Moscow, Ё-burg
Сообщений: 3696
Я и ёжик
потому что так работает full outer join ( <= 10g)

< 10.2.0.4 по-моему
SQL> alter session set "_optimizer_native_full_outer_join"=force;
 
Session altered
 
SQL> explain plan for select * from t1 t1 full join t1 t2 on t1.id1 = t2.id2;
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1391149763
--------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |    52 |     5  (20)| 00:00:01
|   1 |  VIEW                 | VW_FOJ_0 |     1 |    52 |     5  (20)| 00:00:01
|*  2 |   HASH JOIN FULL OUTER|          |     1 |    52 |     5  (20)| 00:00:01
|   3 |    TABLE ACCESS FULL  | T1       |       |       |     2   (0)| 00:00:01
|   4 |    TABLE ACCESS FULL  | T1       |     1 |    26 |     2   (0)| 00:00:01
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."ID1"="T2"."ID2")
21 май 09, 18:34    [7212030]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от непонятного лишнего NESTED LOOPS ANTI  [new]
Elic
Member

Откуда:
Сообщений: 29976
Я и ёжик
Вы похоже плохо понимаете , что такое full outer join, вы не эмулировали full outer join, а сделали? простое объединение.
Автор "сэмулировал" правильный результат исходного full outer join-а.
21 май 09, 18:40    [7212057]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от непонятного лишнего NESTED LOOPS ANTI  [new]
Я и ёжик
Member

Откуда: СПб
Сообщений: 1815
Timm
Я и ёжик
потому что так работает full outer join ( <= 10g)

< 10.2.0.4 по-моему
Поубивал бы...

Elic
Автор "сэмулировал" правильный результат исходного full outer join-а.

Ну да, не приметил...
21 май 09, 18:48    [7212093]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от непонятного лишнего NESTED LOOPS ANTI  [new]
Timm
Member

Откуда: Moscow, Ё-burg
Сообщений: 3696
Я и ёжик
Поубивал бы...

Кого?
21 май 09, 19:00    [7212143]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от непонятного лишнего NESTED LOOPS ANTI  [new]
publexus
Member

Откуда: Москва
Сообщений: 955
Elic
Я и ёжик
Вы похоже плохо понимаете , что такое full outer join, вы не эмулировали full outer join, а сделали? простое объединение.
Автор "сэмулировал" правильный результат исходного full outer join-а.

Подзапросы возвращают строки с не дублирующимся DEFECT (я этот момент ранее не уточнял), поэтому "эмуляция" - правильная, в противном случае эта "эмуляция" возвращала бы не такой же результат как и full ouiter join.
21 май 09, 19:08    [7212178]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от непонятного лишнего NESTED LOOPS ANTI  [new]
Я и ёжик
Member

Откуда: СПб
Сообщений: 1815
Timm
Я и ёжик
Поубивал бы...

Кого?

тех кто новый функционал в патчах вводит.
22 май 09, 10:36    [7213870]     Ответить | Цитировать Сообщить модератору
 Re: Как избавиться от непонятного лишнего NESTED LOOPS ANTI  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
publexus
Elic
Я и ёжик
Вы похоже плохо понимаете , что такое full outer join, вы не эмулировали full outer join, а сделали? простое объединение.
Автор "сэмулировал" правильный результат исходного full outer join-а.

Подзапросы возвращают строки с не дублирующимся DEFECT (я этот момент ранее не уточнял), поэтому "эмуляция" - правильная, в противном случае эта "эмуляция" возвращала бы не такой же результат как и full ouiter join.
Ну так а в чем тогда проблема использовать другой, более специфичный для Вашей задачи код?
22 май 09, 11:11    [7214146]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить