Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 От запроса сильно растет temp в чем причина не понятно  [new]
ln_123
Guest
Есть следующий запрос
SELECT SUM(V.SUMMAINCUR * (1 - 2 * V.FLAGCREDIT)) S,
               V.ACC1,
               V.SUBACC1,
               NVL(V.CODINREF60496001, -1) COSTCENTERCODE,
               NVL(V.CODINREF60477001, -1) N0022CODE,
               NVL(V.CODINREF59529001, -1) N0054CODE,
               NVL(V.CODINREF60491001, -1) N0047CODE,
               NVL(V.CODINREF60497001, -1) N0056CODE,
               NVL(V.CODINREF60487001, -1) N0042CODE,
               NVL(V.CODINREF60469001, -1) N0007CODE,
               NVL(V.CODINREF60475001, -1) N0008CODE,
               NVL(V.CODINREF60492001, 233915001) N0048CODE,
               NVL(V.CODINREF3, -1) NGDSCODE,
               NVL(V.CODINREF5, -1) NSTOCKCODE,
               NVL(V.CODINREF13871165001, -1) NPCCODE,
               NVL(V.CODINREF5589033001, -1) NTYPECODE,
               NVL(V.CODINREF13373649001, -1) NACT
          FROM ACCTRANSREPORTVIEW V
         WHERE V.ACC1 IN (SELECT D.COSTACC 
                           FROM VSM_ACCCLOSING_DAV D 
                           GROUP BY D.COSTACC)
         GROUP BY NVL(V.CODINREF60496001, -1),
                  NVL(V.CODINREF60477001, -1),
                  NVL(V.CODINREF59529001, -1),
                  NVL(V.CODINREF60491001, -1),
                  NVL(V.CODINREF60497001, -1),
                  NVL(V.CODINREF60487001, -1),
                  NVL(V.CODINREF60469001, -1),
                  NVL(V.CODINREF60475001, -1),
                  NVL(V.CODINREF60492001, 233915001),
                  NVL(V.CODINREF3, -1),
                  NVL(V.CODINREF5, -1),
                  NVL(V.CODINREF13871165001, -1),
                  NVL(V.CODINREF5589033001, -1),
                  NVL(V.CODINREF13373649001, -1),
                  V.ACC1,
                  V.SUBACC1
        having SUM(V.SUMMAINCUR * (1 - 2 * V.FLAGCREDIT)) != 0
План запроса сейчас такой:
Plan hash value: 1265141004
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                        |     1 |    76 | 88403   (1)| 00:17:41 |
|*  1 |  FILTER                         |                        |       |       |            |          |
|   2 |   HASH GROUP BY                 |                        |     1 |    76 | 88403   (1)| 00:17:41 |
|   3 |    VIEW                         |                        |     1 |    76 | 88402   (1)| 00:17:41 |
|   4 |     HASH GROUP BY               |                        |     1 |    72 | 88402   (1)| 00:17:41 |
|   5 |      TABLE ACCESS BY INDEX ROWID| ACCTRANSREPORTVIEW     |  2668 |   179K| 87764   (1)| 00:17:34 |
|   6 |       NESTED LOOPS              |                        |     1 |    72 | 88401   (1)| 00:17:41 |
|   7 |        TABLE ACCESS FULL        | VSM_ACCCLOSING_DAV     |     1 |     3 |   636   (1)| 00:00:08 |
|*  8 |        INDEX RANGE SCAN         | IDXACCTRANSACCPLANDATE |   578K|       |  2778   (1)| 00:00:34 |
----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(SUM("$vm_col_3"*(1-2*"$vm_col_2"))<>0)
   8 - access("V"."ACC1"="D"."COSTACC")

работал он долгое время нормально, но неожиданно (я предполагаю что после очередного перебора статистики)
стал вываливаться по переполнению temp-вого tablespace
Просмотр v$sort_usage показал что потребление temp под этот запрос
растет жуткими темпами и достигает 32 Гб и это видимо не предел.
SEGTYPE = HASH я пробовал отказаться от использования HASH для агрегации,
но это делу не помогло просто SEGTYPE стал равен SORT.
Помогло комментирование подзапроса.

Собственно хотелось бы понять почему простой подзапрос
оказывал такое губительное действие и почему оно так резко и неожиданно проявилось?
29 июн 10, 16:46    [9018980]     Ответить | Цитировать Сообщить модератору
 Re: От запроса сильно растет temp в чем причина не понятно  [new]
makitka
Member

Откуда: FROM dual
Сообщений: 886
ln_123,

WHERE V.ACC1 IN (SELECT D.COSTACC 
                           FROM VSM_ACCCLOSING_DAV D 
                           GROUP BY D.COSTACC)
пипец.

лучше тогда уж:
WHERE EXISTS (SELECT NULL 
                           FROM VSM_ACCCLOSING_DAV D 
                           WHERE D.COSTACC = V.ACC1)
29 июн 10, 16:50    [9019022]     Ответить | Цитировать Сообщить модератору
 Re: От запроса сильно растет temp в чем причина не понятно  [new]
makitka
Member

Откуда: FROM dual
Сообщений: 886
makitka,

(SELECT D.COSTACC 
                           FROM VSM_ACCCLOSING_DAV D 
                           GROUP BY D.COSTACC)
если там много данных, то на него тратилось до фига времени и ресурсов из-за группировки. (за каким-то фигом чтобы проверить наличие хотя бы одной строки группировалась вся таблица, возможно без индекса ваще)
29 июн 10, 16:51    [9019036]     Ответить | Цитировать Сообщить модератору
 Re: От запроса сильно растет temp в чем причина не понятно  [new]
AlexFF__|
Member

Откуда:
Сообщений: 2854
ln_123,

Можно план без подзапроса7
29 июн 10, 16:53    [9019054]     Ответить | Цитировать Сообщить модератору
 Re: От запроса сильно растет temp в чем причина не понятно  [new]
mcureenab
Member

Откуда: Murmansk
Сообщений: 5928
WHERE V.ACC1 IN (SELECT D.COSTACC 
                           FROM VSM_ACCCLOSING_DAV D 
                           GROUP BY D.COSTACC)

эквивалентно

WHERE V.ACC1 IN (SELECT D.COSTACC 
                           FROM VSM_ACCCLOSING_DAV D)

Т.е. оракл в зависимости от плана запроса при необходимости сам добавляет SELECT DISTINCT D.COSTACC и т.п..

В данном случае мы поимели

4 | HASH GROUP BY

И ни чем не лучше

WHERE EXISTS (SELECT * 
                           FROM VSM_ACCCLOSING_DAV D 
                           WHERE D.COSTACC = V.ACC1)
29 июн 10, 16:59    [9019128]     Ответить | Цитировать Сообщить модератору
 Re: От запроса сильно растет temp в чем причина не понятно  [new]
mcureenab
Member

Откуда: Murmansk
Сообщений: 5928
ln_123

Собственно хотелось бы понять почему простой подзапрос
оказывал такое губительное действие и почему оно так резко и неожиданно проявилось?


Может количество записей в VSM_ACCCLOSING_DAV подросло?
29 июн 10, 17:03    [9019166]     Ответить | Цитировать Сообщить модератору
 Re: От запроса сильно растет temp в чем причина не понятно  [new]
ln_123
Guest
AlexFF__|,

План без подзапроса очень простой
Plan hash value: 902221284
 
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                    |  1099K|    59M|       |   333K  (2)| 01:06:37 |
|*  1 |  FILTER             |                    |       |       |       |            |          |
|   2 |   HASH GROUP BY     |                    |  1099K|    59M|  2227M|   333K  (2)| 01:06:37 |
|   3 |    TABLE ACCESS FULL| ACCTRANSREPORTVIEW |    21M|  1195M|       |   215K  (3)| 00:43:04 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(SUM("V"."SUMMAINCUR"*(1-2*"V"."FLAGCREDIT"))<>0)
29 июн 10, 17:10    [9019239]     Ответить | Цитировать Сообщить модератору
 Re: От запроса сильно растет temp в чем причина не понятно  [new]
makitka
Member

Откуда: FROM dual
Сообщений: 886
mcureenab
В данном случае мы поимели

4 | HASH GROUP BY

И ни чем не лучше

WHERE EXISTS (SELECT * 
                           FROM VSM_ACCCLOSING_DAV D 
                           WHERE D.COSTACC = V.ACC1)

да конечно
29 июн 10, 17:17    [9019328]     Ответить | Цитировать Сообщить модератору
 Re: От запроса сильно растет temp в чем причина не понятно  [new]
makitka
Member

Откуда: FROM dual
Сообщений: 886
хотя вы правы, а я ошибся
(что-то за сегодня моя любовь к WHERE EXISTS поугасла...)
29 июн 10, 17:27    [9019473]     Ответить | Цитировать Сообщить модератору
 Re: От запроса сильно растет temp в чем причина не понятно  [new]
Shredder2003
Member

Откуда: деревня Ильбяково Азнакаевского района РТ
Сообщений: 460
makitka
ln_123,

WHERE V.ACC1 IN (SELECT D.COSTACC 
                           FROM VSM_ACCCLOSING_DAV D 
                           GROUP BY D.COSTACC)
пипец.

лучше тогда уж:
WHERE EXISTS (SELECT NULL 
                           FROM VSM_ACCCLOSING_DAV D 
                           WHERE D.COSTACC = V.ACC1)


на Оракле 8i вроде такая фича работала, сам так ускорял отчёты с 10 до 2 минут.
Может, щас оптимизаторы поумнее стали...
29 июн 10, 18:57    [9020172]     Ответить | Цитировать Сообщить модератору
 Re: От запроса сильно растет temp в чем причина не понятно  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18343
На asktom было обсуждение exists vs in, not exists vs not in.
Не поленитесь - поищите, можно неожиданно открыть для себя пару полезностей
29 июн 10, 19:00    [9020183]     Ответить | Цитировать Сообщить модератору
 Re: От запроса сильно растет temp в чем причина не понятно  [new]
ln_123
Guest
makitka,

Насколько я помню, раньше (на RBO) in и exists отличались
и если меня память не подводит то in выполнялся до прохода по основной таблице
а exists после, сейчас видимо CBO стирает границы хотя и не все, во всяком случае у меня план с exists отличался от плана c in
29 июн 10, 19:51    [9020383]     Ответить | Цитировать Сообщить модератору
 Re: От запроса сильно растет temp в чем причина не понятно  [new]
ln_123
Guest
mcureenab, Если и подросло то не сильно да же не в разы,
единственное в чем может быть загвостка, что на момент когда собрилась статистика данные в таблице могли
существенно отличатся от данных которые были когда запускался запрос.
Это вообще особенность данной таблицы там данные расчетные и могут существенно изменятся...
Может ее вообще из сбора статистики исключить стоит?
И все равно не понятно почему при использование того плана
который получился так существенно использовался temp, все таки 32 гига отхапать под hash это не шутки...
29 июн 10, 19:57    [9020402]     Ответить | Цитировать Сообщить модератору
 Re: От запроса сильно растет temp в чем причина не понятно  [new]
mcureenab
Member

Откуда: Murmansk
Сообщений: 5928
ln_123,

похоже, реальный план выполнения мы таки не видели.

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

Закрепляя план не нужно искать наилучший план для конкретных данных, достаточно найти лучший план из тех, что хорошо работают на любых возможных данных.
29 июн 10, 21:08    [9020736]     Ответить | Цитировать Сообщить модератору
 Re: От запроса сильно растет temp в чем причина не понятно  [new]
mcureenab
Member

Откуда: Murmansk
Сообщений: 5928
ln_123
makitka,

Насколько я помню, раньше (на RBO) in и exists отличались
и если меня память не подводит то in выполнялся до прохода по основной таблице
а exists после, сейчас видимо CBO стирает границы хотя и не все, во всяком случае у меня план с exists отличался от плана c in


в 10g оптимизатор умеет крутить и вертеть подзапросы в EXISTS и IN практически как угодно. Даже в твоём плане видно, что оптимизатор сделал подзапросу UNNESN.

Получилось примерно следующее

select --+ leading(l) use_nl(v) index(v)
  sum ....
from 
 (SELECT --+ merge full(d)
  distinct D.COSTACC 
  FROM VSM_ACCCLOSING_DAV D
 ) l,
 ACCTRANSREPORTVIEW V
where V.ACC1 = L.COSTACC
GROUP BY ....
29 июн 10, 21:14    [9020765]     Ответить | Цитировать Сообщить модератору
 Re: От запроса сильно растет temp в чем причина не понятно  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7429
ln_123
SELECT SUM(V.SUMMAINCUR * (1 - 2 * V.FLAGCREDIT)) S,
               NVL(V.CODINREF60496001, -1) COSTCENTERCODE,
               NVL(V.CODINREF60477001, -1) N0022CODE,
               NVL(V.CODINREF59529001, -1) N0054CODE,
               NVL(V.CODINREF60491001, -1) N0047CODE,
               NVL(V.CODINREF60497001, -1) N0056CODE,
               NVL(V.CODINREF60487001, -1) N0042CODE,
               NVL(V.CODINREF60469001, -1) N0007CODE,
               NVL(V.CODINREF60475001, -1) N0008CODE,
               NVL(V.CODINREF60492001, 233915001) N0048CODE,
               .......
          FROM ACCTRANSREPORTVIEW V
         WHERE V.ACC1 IN (SELECT D.COSTACC 
                           FROM VSM_ACCCLOSING_DAV D 
                           GROUP BY D.COSTACC)
         GROUP BY NVL(V.CODINREF60496001, -1),
                  NVL(V.CODINREF60477001, -1),
                  NVL(V.CODINREF59529001, -1),
                  NVL(V.CODINREF60491001, -1),
                  NVL(V.CODINREF60497001, -1),
                  NVL(V.CODINREF60487001, -1),
                  NVL(V.CODINREF60469001, -1),
                  NVL(V.CODINREF60475001, -1),
                  NVL(V.CODINREF60492001, 233915001),
                  NVL(V.CODINREF3, -1),
                  NVL(V.CODINREF5, -1),
                  NVL(V.CODINREF13871165001, -1),
                  NVL(V.CODINREF5589033001, -1),
                  NVL(V.CODINREF13373649001, -1),
                  V.ACC1,
                  V.SUBACC1
        having SUM(V.SUMMAINCUR * (1 - 2 * V.FLAGCREDIT)) != 0
План запроса сейчас такой:
[src oracle]
Да-уж. Прикольные названия колонок (как вы их запоминаете?:).
Почему-бы не заменить IN или EXISTS джоином?
29 июн 10, 21:35    [9020872]     Ответить | Цитировать Сообщить модератору
 Re: От запроса сильно растет temp в чем причина не понятно  [new]
ln_123
Guest
Relic Hunter,
Relic Hunter
Почему-бы не заменить IN или EXISTS джоином?

Я пробовал, получалось то же самое что с in. Как выше написал mcureenab оптимизатор и так in приводил к джойну.
30 июн 10, 08:30    [9021752]     Ответить | Цитировать Сообщить модератору
 Re: От запроса сильно растет temp в чем причина не понятно  [new]
ln_123
Guest
mcureenab,

Реальный план выполнения (в смысле тот с которым было все плохо) был в первом посте.
Сейчас ночью был очередной пересчет статистики и план стал другим, судя по всему гораздо лучше.

Я попробовал тот запрос который вы с хинтами привели по нему четко строится старый плохой план :) только вот стоимость там совершенно другая просто пугающая, что видимо соответствует действительности.
Единственное что я не понял почему группировка/дистинкт выполняется после nl?
на мой взгляд именно в этом вся загвоздка

Вообщем буду прибивать план гвоздями (в смысле хинтами :)).
Подзапрос у меня всегда возвращает не больше 6 значений и сам по себе работает быстро, видимо от этого и нужно плясать.
30 июн 10, 08:57    [9021828]     Ответить | Цитировать Сообщить модератору
 Re: От запроса сильно растет temp в чем причина не понятно  [new]
reader-636
Guest
ln_123
|   7 |        TABLE ACCESS FULL        | VSM_ACCCLOSING_DAV     |     1 |     3 |   636   (1)| 00:00:08 |



Oracle думает, что в таблице VSM_ACCCLOSING_DAV только 1 строка, Но стоимость получения этой строки достаточно высока (636). Это означает (возможно) что в таблице действительно одна строка и много пустых блоков после delete . Или - что статистика кривая.

Покажи результаты запросов :
select num_rows,blocks,last_analyzed,sample_size  from dba_tables where table_name = 'VSM_ACCCLOSING_DAV';

select num_distinct,density,num_nulls, num_buckets,last_analyzed,sample_size from dba_tab_col_statistics where table_name = 'VSM_ACCCLOSING_DAV'
and column_name = 'COSTACC';
30 июн 10, 09:04    [9021861]     Ответить | Цитировать Сообщить модератору
 Re: От запроса сильно растет temp в чем причина не понятно  [new]
makitka
Member

Откуда: FROM dual
Сообщений: 886
ln_123
makitka,

Насколько я помню, раньше (на RBO) in и exists отличались
и если меня память не подводит то in выполнялся до прохода по основной таблице
а exists после, сейчас видимо CBO стирает границы хотя и не все, во всяком случае у меня план с exists отличался от плана c in

забавно то, что у меня
SELECT * FROM tst t WHERE t.call_id IN (SELECT t2.call_id FROM tst t2 GROUP BY t2.call_id)
выполняется быстрее, чем
SELECT * FROM tst t WHERE EXISTS (SELECT NULL FROM tst t2 WHERE t2.call_id = t.call_id)
30 июн 10, 10:15    [9022354]     Ответить | Цитировать Сообщить модератору
 Re: От запроса сильно растет temp в чем причина не понятно  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
makitka
ln_123
makitka,

Насколько я помню, раньше (на RBO) in и exists отличались
и если меня память не подводит то in выполнялся до прохода по основной таблице
а exists после, сейчас видимо CBO стирает границы хотя и не все, во всяком случае у меня план с exists отличался от плана c in

забавно то, что у меня
SELECT * FROM tst t WHERE t.call_id IN (SELECT t2.call_id FROM tst t2 GROUP BY t2.call_id)
выполняется быстрее, чем
SELECT * FROM tst t WHERE EXISTS (SELECT NULL FROM tst t2 WHERE t2.call_id = t.call_id)
Осталось понять зачем там GROUP BY t2.call_id, а потом выкинуть.
30 июн 10, 10:20    [9022385]     Ответить | Цитировать Сообщить модератору
 Re: От запроса сильно растет temp в чем причина не понятно  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6723
makitka,

0. Планы в студию
1. Методика сравнения
2. Трассировка, дабы было видно время выполнения
30 июн 10, 10:20    [9022390]     Ответить | Цитировать Сообщить модератору
 Re: От запроса сильно растет temp в чем причина не понятно  [new]
makitka
Member

Откуда: FROM dual
Сообщений: 886
env
makitka,

0. Планы в студию
1. Методика сравнения
2. Трассировка, дабы было видно время выполнения


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

убрал GROUP BY - оба стали выполняться одинаково долго
30 июн 10, 10:26    [9022442]     Ответить | Цитировать Сообщить модератору
 Re: От запроса сильно растет temp в чем причина не понятно  [new]
mcureenab
Member

Откуда: Murmansk
Сообщений: 5928
ln_123

Подзапрос у меня всегда возвращает не больше 6 значений и сам по себе работает быстро, видимо от этого и нужно плясать.


Это он возвращает 6 уникальных значений или там в таблице всего шеть строк? Если distinct или group by (которые там нафиг не нужны, оракл сам их подставит, если понадобится) сильно сокращает количество записей, то хинт no_merge может помочь.
30 июн 10, 10:42    [9022576]     Ответить | Цитировать Сообщить модератору
 Re: От запроса сильно растет temp в чем причина не понятно  [new]
ln_123
Guest
mcureenab,

6 уникальных, а строк в таблице по больше :) сейчас 51000.
Про хинт спасибо буду пробовать.
Кстати во интересное по поводу distinct, group by в подзапросах нашел
http://iusoltsev.wordpress.com/2009/07/07/distinct-vs-exists-vs-group-by-in-subqueries/
30 июн 10, 13:11    [9024057]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить