CftClub.ru
Клуб специалистов ЦФТ-Банк

Зависает вьюха 'Проверка на причастность к терроризму'
На страницу 1, 2  След.
 
Ответить на тему    Клуб специалистов ЦФТ-Банк (IBSO) -> Разработка в PL/PLUS. Оптимизация запросов Oracle
Предыдущая тема :: Следующая тема  
Автор Сообщение
timochev
Эксперт


Вступление в Клуб: 02.07.2007
СообщениеВт Дек 11, 2012 09:28   Зависает вьюха 'Проверка на причастность к терроризму' Ответить с цитатой
Полезность: Нет оценки
Коллеги!

Подскажите, плиз, как у вас обстоит ситуация со скоростью работы представления "Противодействие легализации. Проверка на причастность к терроризму" (VW_CRIT_SUSPECT_CL) в ТБП "Физические лица". Интересует работа представления при большом количестве клиентов ФЛ (например, свыше 1.5 млн. записей).

У нас, если в Навигаторе стоит "Количество строк в запросе" = 200, то представление зависает на 20 мин. и выводит 200 записей.
Если выставить "Количество строк в запросе" = 2000 (чтобы все непроверенные клиенты влезли), то зависание длится около 1 часа.
Обращение к данному представлению дает 6% полной дневной нагрузки на сервер и дисковую подсистему.

Занимаемся оптимизацией представления с ЦФТ уже 2 месяца, но безрезультатно. Правда ЦФТ говорит, что у других банков все нормально.
Мое мнение: необходимо возвращать хранимый реквизит-ссылку на P207_TERROR_FLAG из ТБП Клиенты. Сейчас реквизит IS_SUSPECT реализован как функциональный.


Последний раз редактировалось: timochev (Вт Дек 18, 2012 10:36), всего редактировалось 3 раз(а)
devor
Профи


Вступление в Клуб: 13.02.2012
СообщениеВт Дек 11, 2012 10:52   Re: Нужна ли оптимизация VW_CRIT_SUSPECT_CL? Ответить с цитатой
Полезность: Нет оценки
timochev пишет:
Коллеги!

Подскажите, плиз, как у вас обстоит ситуация со скоростью работы представления "Противодействие легализации. Проверка на причастность к терроризму" (VW_CRIT_SUSPECT_CL) в ТБП "Физические лица". Интересует работа представления при большом количестве клиентов ФЛ (например, свыше 1.5 млн. записей).

У нас, если в Навигаторе стоит "Количество строк в запросе" = 200, то представление зависает на 20 мин. и выводит 200 записей.
Если выставить "Количество строк в запросе" = 2000 (чтобы все непроверенные клиенты влезли), то зависание длится около 1 часа.
Обращение к данному представлению дает 6% полной дневной нагрузки на сервер и дисковую подсистему.

Занимаемся оптимизацией представления с ЦФТ уже 2 месяца, но безрезультатно. Правда ЦФТ говорит, что у других банков все нормально.
Мое мнение: необходимо возвращать хранимый реквизит-ссылку на P207_TERROR_FLAG из ТБП Клиенты. Сейчас реквизит IS_SUSPECT реализован как функциональный.


Версия Оракла, ТЯ? План запроса?
timochev
Эксперт


Вступление в Клуб: 02.07.2007
СообщениеВт Дек 11, 2012 12:53    Ответить с цитатой
Полезность: Нет оценки
представление версии 12.21
Код:
type main is
      select u( u.[REGISTR_NUM]                : C_REGISTR_NUM
                  , u.[NAME]                         : C_CLIENT_NAME
                  , u.[INN]                          : C_INN
                  , f.val.[ CODE ]                     : C_IS_SUSPECT
                  )
            in    ::[CLIENT],
            (     select f(distinct f.[CHECK_OBJ] : cl
                           , analytic(first_value(f.[CHECK_VAL]), 'over (partition by [1], [2] order by [3] desc)', f.[CHECK_OBJ], f.[CHECK_TYPE], f.[CHECK_DATE]) : val
                             )
                  in    ::[CL_CHECK_RESULT]
                  where f.[CHECK_TYPE] = ::[CLIENT_CHECKS]([ CODE ] = 'TERROR_LIST')
            )
      where u = f.cl(true)
            and         '1' = decode( f.val, null                                  , '1'
                                   , ::[P207_TERROR_FLAG]([ CODE ] = 'TERRORIST_AUTO')    , '1'
                                   , ::[P207_TERROR_FLAG]([ CODE ] = 'EXTREMIST_AUTO')    , '1'
                                   , ::[P207_TERROR_FLAG]([ CODE ] = 'NEED_CHECK')        , '1'
                                   , '0')
            and u.[NAME] is not null;


трейс снимался с тестовой базы, поэтому еще медленнее, чем заявлено в 1-ом посте, Oracle 10.2.0.4
Код:
SELECT /*+ FIRST_ROWS */ ID, CLASS_ID, TO_CHAR(C_1) C_1, C_2, C_3, C_4, REF4
FROM
 IBS.VW_CRIT_SUSPECT_CL WHERE (CLASS_ID = :1) AND (ROWNUM <= :2) ORDER BY
  IBS.VW_CRIT_SUSPECT_CL.C_1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2      0.04       0.03          0         65          0           0
Fetch        3   1017.98    2262.54    1690239     931499         98         200
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6   1018.02    2262.58    1690239     931564         98         200

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 187  (IBS)

Rows     Row Source Operation
-------  ---------------------------------------------------
    200  SORT ORDER BY (cr=931499 pr=1690239 pw=888436 time=2262542286 us)
    200   COUNT STOPKEY (cr=931499 pr=1690239 pw=888436 time=2259727078 us)
    200    NESTED LOOPS OUTER (cr=931499 pr=1690239 pw=888436 time=2259726671 us)
    200     FILTER  (cr=931297 pr=1690238 pw=888436 time=2259698616 us)
 103126      HASH JOIN OUTER (cr=931297 pr=1690238 pw=888436 time=2260244612 us)
1257586       TABLE ACCESS BY INDEX ROWID Z#CLIENT (cr=186911 pr=65162 pw=0 time=189962372 us)
1257669        INDEX RANGE SCAN IDX_Z#CLIENT_CLASS_ID (cr=9046 pr=9046 pw=0 time=56662019 us)(object id 7836)
 245116       VIEW  (cr=744386 pr=1620951 pw=888136 time=2054987944 us)
 245116        HASH UNIQUE (cr=744386 pr=1620951 pw=888136 time=2054742818 us)
35388732         WINDOW SORT (cr=744386 pr=1607991 pw=871996 time=2015107907 us)
35388732          TABLE ACCESS BY INDEX ROWID Z#CL_CHECK_RESULT (cr=744386 pr=744177 pw=0 time=849383820 us)
35388732           INDEX RANGE SCAN Z#IX_Z#CL_CHECK_RESULT_REF5 (cr=252387 pr=252387 pw=0 time=566432804 us)(object id 282411)
    200     TABLE ACCESS BY INDEX ROWID Z#P207_TERROR_FLAG (cr=202 pr=1 pw=0 time=41128 us)
    200      INDEX UNIQUE SCAN PK_Z#P207_TERROR_FLAG_ID (cr=2 pr=1 pw=0 time=21464 us)(object id 120807)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: HINT: FIRST_ROWS
    200   SORT (ORDER BY)
    200    COUNT (STOPKEY)
    200     NESTED LOOPS (OUTER)
    200      FILTER
 103126       HASH JOIN (OUTER)
1257586        TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF
                   'Z#CLIENT' (TABLE)
1257669         INDEX   MODE: ANALYZED (RANGE SCAN) OF
                    'IDX_Z#CLIENT_CLASS_ID' (INDEX)
 245116        VIEW
 245116         HASH (UNIQUE)
35388732          WINDOW (SORT)
35388732           TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID)
                      OF 'Z#CL_CHECK_RESULT' (TABLE)
35388732            INDEX   MODE: ANALYZED (RANGE SCAN) OF
                       'Z#IX_Z#CL_CHECK_RESULT_REF5' (INDEX)
    200      TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF
                 'Z#P207_TERROR_FLAG' (TABLE)
    200       INDEX   MODE: ANALYZED (UNIQUE SCAN) OF
                  'PK_Z#P207_TERROR_FLAG_ID' (INDEX (UNIQUE))


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       3        0.00          0.00
  db file sequential read                    809340        0.45        693.91
  direct path write temp                       8498        0.76         74.18
  latch: cache buffers lru chain                  1        0.00          0.00
  direct path read temp                      284375        0.39        437.44
  latch free                                      1        0.00          0.00
  SQL*Net more data to client                     3        0.00          0.00
  SQL*Net message from client                     3        0.00          0.00
********************************************************************************

timochev
Эксперт


Вступление в Клуб: 02.07.2007
СообщениеВт Дек 18, 2012 10:36    Ответить с цитатой
Полезность: Нет оценки
up!
pas
Профи


Вступление в Клуб: 20.11.2007
СообщениеВт Дек 18, 2012 11:37    Ответить с цитатой
Полезность: Нет оценки
попробовал на тестовой схеме с установленой 12,22
Поставил "количество строк в запросе"=1000 отработало буквально за 15 сек.

Версия 12.22
ТЯ 7.3.0.6

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

Не думаете на 11 переходить?
timochev
Эксперт


Вступление в Клуб: 02.07.2007
СообщениеВт Дек 18, 2012 12:44    Ответить с цитатой
Полезность: Нет оценки
А у Вас в таблицах Z#CLIENT, Z#CL_CHECK_RESULT много записей? Сколько записей вывелось в результате запроса?
Переход на 11g в проекте.
pas
Профи


Вступление в Клуб: 20.11.2007
СообщениеВт Дек 18, 2012 12:50    Ответить с цитатой
Полезность: Нет оценки
timochev пишет:
А у Вас в таблицах Z#CLIENT, Z#CL_CHECK_RESULT много записей? Сколько записей вывелось в результате запроса?
Переход на 11g в проекте.


CLIENT ~500000
CL_CHECK_RESULT ~2500000
timochev
Эксперт


Вступление в Клуб: 02.07.2007
СообщениеВт Дек 18, 2012 12:56    Ответить с цитатой
Полезность: Нет оценки
Спасибо. Может действительно дело в СУБД.
timochev
Эксперт


Вступление в Клуб: 02.07.2007
СообщениеСр Дек 19, 2012 09:46    Ответить с цитатой
Полезность: Нет оценки
А может кто-нибудь объяснить на пальцах теорию того, как подобный запрос может быстро работать?
Ведь основная таблица - Z#CLIENT. Мы вынуждены делать полный перебор записей в ней! А далее для каждой записи идет обращение к Z#CL_CHECK_RESULT (предположим, что здесь используется индекс по ID клиента). Отдельный запрос по Z#CL_CHECK_RESULT по одному клиенту действительно выполняется моментально.
Но в общем получается фактический FULL SCAN по Z#CLIENT...
IBSO
Профи


Вступление в Клуб: 20.08.2009
СообщениеСр Дек 19, 2012 11:47    Ответить с цитатой
Полезность: Нет оценки
timochev пишет:
А может кто-нибудь объяснить на пальцах теорию того, как подобный запрос может быстро работать?
Ведь основная таблица - Z#CLIENT. Мы вынуждены делать полный перебор записей в ней! А далее для каждой записи идет обращение к Z#CL_CHECK_RESULT (предположим, что здесь используется индекс по ID клиента). Отдельный запрос по Z#CL_CHECK_RESULT по одному клиенту действительно выполняется моментально.
Но в общем получается фактический FULL SCAN по Z#CLIENT...

Скорость выполнения запроса зависит от железа, от размещения данных в таблице (например, есть индексы или нет) и от оптимизации самого запроса.
timochev
Эксперт


Вступление в Клуб: 02.07.2007
СообщениеСр Дек 19, 2012 12:40    Ответить с цитатой
Полезность: Нет оценки
IBSO пишет:
Скорость выполнения запроса зависит от железа, от размещения данных в таблице (например, есть индексы или нет) и от оптимизации самого запроса.

Абсолютно с Вами согласен. Оставив за скобками апгрейд железа (поскольку на скорость работы остального функционала жалоб нет), предлагаю рассмотреть 2 конкретные таблицы с достаточно конкретным набором дистрибутивных индексов. Оптимизацией же можно заниматься, если в голове есть понимание схемы эффективной работы данного запроса. У меня понимания нет. Пытаюсь его найти, но не получается. Любые попытки переписать запрос, заканчиваются зависаниями.
IBSO
Профи


Вступление в Клуб: 20.08.2009
СообщениеСр Дек 19, 2012 13:04    Ответить с цитатой
Полезность: Нет оценки
timochev пишет:
IBSO пишет:
Скорость выполнения запроса зависит от железа, от размещения данных в таблице (например, есть индексы или нет) и от оптимизации самого запроса.

Абсолютно с Вами согласен. Оставив за скобками апгрейд железа (поскольку на скорость работы остального функционала жалоб нет), предлагаю рассмотреть 2 конкретные таблицы с достаточно конкретным набором дистрибутивных индексов. Оптимизацией же можно заниматься, если в голове есть понимание схемы эффективной работы данного запроса. У меня понимания нет. Пытаюсь его найти, но не получается. Любые попытки переписать запрос, заканчиваются зависаниями.

Думаю у вас п.2 - проблема с размещением данных в оракле.
timochev
Эксперт


Вступление в Клуб: 02.07.2007
СообщениеСр Дек 19, 2012 13:08    Ответить с цитатой
Полезность: Нет оценки
IBSO пишет:
Думаю у вас п.2 - проблема с размещением данных в оракле.
Абсолютно не понятно, что Вы имеете в виду.
IBSO
Профи


Вступление в Клуб: 20.08.2009
СообщениеСр Дек 19, 2012 20:39    Ответить с цитатой
Полезность: Нет оценки
timochev пишет:
IBSO пишет:
Думаю у вас п.2 - проблема с размещением данных в оракле.
Абсолютно не понятно, что Вы имеете в виду.

п1 железо работает нормально раз все остальное пашет. П3 прикладное по, тоже нормально раз у всех пашет. П3-субд. Значит надо там копать. Например, собрать статистику или переиндексировать таблицу. С ораклистами посовещайтесь.
Volod
Эксперт


Вступление в Клуб: 19.09.2007
СообщениеЧт Дек 20, 2012 12:23    Ответить с цитатой
Полезность: Нет оценки
Проверить не на чем, но м.б.
Код:
            and         '1' = decode( f.val, null                                  , '1'
                                   , ::[P207_TERROR_FLAG]([ CODE ] = 'TERRORIST_AUTO')    , '1'
                                   , ::[P207_TERROR_FLAG]([ CODE ] = 'EXTREMIST_AUTO')    , '1'
                                   , ::[P207_TERROR_FLAG]([ CODE ] = 'NEED_CHECK')        , '1'
                                   , '0')


сунуть в where селекта f (обернуть селектом текущую выборку)?
Показать сообщения:   
Ответить на тему    Клуб специалистов ЦФТ-Банк (IBSO) -> Разработка в PL/PLUS. Оптимизация запросов Oracle Часовой пояс: GMT + 3
На страницу 1, 2  След.
Страница 1 из 2

 
Перейти:  
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Рейтинг@Mail.ru