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

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


Вступление в Клуб: 26.04.2012
СообщениеВт Сен 17, 2013 10:06   Медленное выполнение запроса Ответить с цитатой
Полезность: Нет оценки
Добрый день

Дано: TBL_ONE - таблица, где есть поле FIELD_ARR - массив со структурой
TBL_TWO c полями
FIELD_NAME - varchar(100)
FIELD_VALUE - varchar(200);

есть функция
Код:
function getRecord(doc_id varchar2(200)) return ref [TBL_ONE] is
p_rec ref [TBL_ONE];
begin
   select a(a%id   :   id)
   in ::[TBL_ONE], ([TBL_TWO] all : b ) all
   where   b.[FIELD_NAME] = 'DOC_ID' and
         b.[FIELD_VALUE] = doc_id and
         b%collection = a.[FIELD_ARR]
   into p_rec;
   return p_rec;
exception
   when others then
      return null;
end;

Данная функция вызывается из цикла. Проблема в том что у данной конструкции очень низкая производительность, где то 4 итерации цикла в секунду. Опытным путем установил что проблема в select по двум таблицам. Если сделать select по одной таблице, то все выполняется очень быстро.

Возможно увеличить производительность?
Volod
Эксперт


Вступление в Клуб: 19.09.2007
СообщениеВт Сен 17, 2013 10:41    Ответить с цитатой
Полезность: Нет оценки
Ну, например, добавить в селект, по которому цикл, в виде подселекта.
Alkov
Профи


Вступление в Клуб: 23.09.2010
СообщениеВт Сен 17, 2013 10:47    Ответить с цитатой
Полезность: 1
План запроса бы приложил.
Ну например индекс по b.[FIELD_VALUE] есть ?
или составной по b.[FIELD_VALUE] и b.[FIELD_NAME] ?
Damir
Участник - экстремал


Вступление в Клуб: 29.03.2013
СообщениеВт Сен 17, 2013 13:27    Ответить с цитатой
Полезность: 1
Alkov пишет:
План запроса бы приложил.
Ну например индекс по b.[FIELD_VALUE] есть ?
или составной по b.[FIELD_VALUE] и b.[FIELD_NAME] ?

Угу, еще проверить обязательно наличие индексов по полям
[TBL_TWO]. collection,
[TBL_ONE]. [FIELD_ARR]
Ну и план нужен и количество записей в таблицах.
Еще - какое количество раз дергаете эту функцию (сколько итераций в цикле)?
Damir
Участник - экстремал


Вступление в Клуб: 29.03.2013
СообщениеВт Сен 17, 2013 13:45    Ответить с цитатой
Полезность: Нет оценки
Volod пишет:
Ну, например, добавить в селект, по которому цикл, в виде подселекта.

прям по живому Very Happy
Есть 2 подхода написания софта под цфт
1) Всю логику - в подзапросы. Оптимизатору легче в этом случае.
2) Всю логику - в функции. Оптимизатору срывает крышу периодически. Но поддерживать (сопровождать) 1 функцию легче, чем 80 подзапросов.
Random
Эксперт


Вступление в Клуб: 27.06.2011
СообщениеСр Сен 18, 2013 06:35   Re: Медленное выполнение запроса Ответить с цитатой
Полезность: Нет оценки
e.kha пишет:
Добрый день

Дано: TBL_ONE - таблица, где есть поле FIELD_ARR - массив со структурой
TBL_TWO c полями
FIELD_NAME - varchar(100)
FIELD_VALUE - varchar(200);

есть функция
Код:
function getRecord(doc_id varchar2(200)) return ref [TBL_ONE] is
p_rec ref [TBL_ONE];
begin
   select a(a%id   :   id)
   in ::[TBL_ONE], ([TBL_TWO] all : b ) all
   where   b.[FIELD_NAME] = 'DOC_ID' and
         b.[FIELD_VALUE] = doc_id and
         b%collection = a.[FIELD_ARR]
   into p_rec;
   return p_rec;
exception
   when others then
      return null;
end;

Данная функция вызывается из цикла. Проблема в том что у данной конструкции очень низкая производительность, где то 4 итерации цикла в секунду. Опытным путем установил что проблема в select по двум таблицам. Если сделать select по одной таблице, то все выполняется очень быстро.

Возможно увеличить производительность?


Лично я не делал бы ограничений на field_value.
Исключение тоже выбросил бы.
Код:
type map_num is table of number index by varchar2(32767);
lm_GetRecordCache map_num;

function getRecord(doc_id varchar2(200)) return ref [TBL_ONE] is
begin
   if doc_id is null then
      return null;
   end if;
   if lm_GetRecordCache.count = 0 then
      lm_GetRecordCache(0) := 0;
      for (
         select a(a%id   :   id
      ,   b.[FIELD_VALUE] :doc_id
         ) in ::[TBL_ONE], ([TBL_TWO] all : b ) all
         where   b.[FIELD_NAME] = 'DOC_ID' and
--         b.[FIELD_VALUE] = doc_id and
            b%collection = a.[FIELD_ARR]
         and   b.[FIELD_VALUE] is not null
      ) loop
         lm_GetRecordCache(a.doc_id) := a.id;
      end loop;
   end if;

   if lm_GetRecordCache.exists(doc_id) then
      return lm_GetRecordCache(doc_id);
   end if;
   return null;
end;
e.kha
Участник со стажем


Вступление в Клуб: 26.04.2012
СообщениеСр Сен 18, 2013 07:05    Ответить с цитатой
Полезность: Нет оценки
to Alkov, Damir.

Были индексы
[TBL_TWO]. collection,
[TBL_ONE]. [FIELD_ARR]

и составной
[TBL_TWO]. collection
[TBL_TWO]. [FIELD_NAME]

в план делал, coast был чуть больше 1560.

С индексом b.[FIELD_VALUE] и b.[FIELD_NAME] проверю.

to Random.
Я попробую ваше предложение, но в чем профит?
когда я экспериментировал, то получалось быстро если я из селекта убирал одну таблицу, только я добавлял вторую, без всякого условия по ней, сразу начинало работать медленно.
Random
Эксперт


Вступление в Клуб: 27.06.2011
СообщениеСр Сен 18, 2013 11:45    Ответить с цитатой
Полезность: Нет оценки
e.kha пишет:

to Random.
Я попробую ваше предложение, но в чем профит?
когда я экспериментировал, то получалось быстро если я из селекта убирал одну таблицу, только я добавлял вторую, без всякого условия по ней, сразу начинало работать медленно.


Профит в том, что выполнение запроса осуществляется только 1 раз.
Попробуй.

Хотя... количество записей
Код:
select a(count(1))
   in ::[TBL_ONE], ([TBL_TWO] all : b ) all
   where   b.[FIELD_NAME] = 'DOC_ID' and
--         b.[FIELD_VALUE] = doc_id and
         b%collection = a.[FIELD_ARR]
можешь сказать?
maestro
Профи


Вступление в Клуб: 12.10.2010
СообщениеСр Сен 18, 2013 14:23    Ответить с цитатой
Полезность: Нет оценки
Alkov пишет:
План запроса бы приложил.
Ну например индекс по b.[FIELD_VALUE] есть ?
или составной по b.[FIELD_VALUE] и b.[FIELD_NAME] ?

+1!

Сделай составной индекс по 3м полям: b.[FIELD_VALUE], b.[FIELD_NAME], b%collection.

Колекшн в индексе нужен для того, чтобы оракл все тянул из индекса и не лез в данные.
e.kha
Участник со стажем


Вступление в Клуб: 26.04.2012
СообщениеЧт Сен 19, 2013 02:05    Ответить с цитатой
Полезность: Нет оценки
Господа, огромное вам спасибо!


Помогло след.
Индекс
[TBL_ONE]. [FIELD_ARR]
Индекс
[TBL_TWO]. collection
Индекс
[TBL_TWO].[FIELD_VALUE] и [TBL_TWO].[FIELD_NAME].

Работает, как взрыв!

to maestro
Я сделал индекс как вы посоветовали, но почему то
один индекс по трем полям работает значительно медленнее чем
два индекса по
b%collection.
и b.[FIELD_NAME], b.[FIELD_VALUE]

Почему, не знаю
maestro
Профи


Вступление в Клуб: 12.10.2010
СообщениеЧт Сен 19, 2013 07:58    Ответить с цитатой
Полезность: Нет оценки
e.kha пишет:

Почему, не знаю


План в студию!
maestro
Профи


Вступление в Клуб: 12.10.2010
СообщениеЧт Сен 19, 2013 08:04    Ответить с цитатой
Полезность: 1
e.kha пишет:
Почему, не знаю


Давай устроим гонки! Выполни в SQLPlus следующее.

Код:

SQL> alter system flush shared_pool;
SQL> alter system flush buffer_cache;
SQL> set timing on
SQL> set autot traceonly

SQL> <%Сюда вставляешь запрос%>

SQL> /


Запускаешь такую штуку для запросов с разными индексами и статистику выкладываешь сюда!

Ну и сам посмотри, кто по цифрам лучше!
e.kha
Участник со стажем


Вступление в Клуб: 26.04.2012
СообщениеЧт Сен 19, 2013 08:22    Ответить с цитатой
Полезность: Нет оценки
to maestro
Индекс
[TBL_ONE]. [FIELD_ARR]
Индекс
b.[FIELD_VALUE], b.[FIELD_NAME], b%collection
План
Код:
ADMIN SELECT STATEMENT Cost=18
    2.1 NESTED LOOPS
      3.1 NESTED LOOPS
        4.1 INDEX(RANGE SCAN) - IDX_Z#TBL_TWO_CL_ID_FN_FV(INDEX)
        4.2 INDEX(UNIQUE SCAN) - IDX_Z#TBL_ONE_FIELD_ARR(INDEX (UNIQUE))
      3.2 TABLE ACCESS(BY INDEX ROWID) - Z#TBL_ONE(TABLE)




Индекс
[TBL_ONE]. [FIELD_ARR]
Индекс
[TBL_TWO]. collection
Индекс
[TBL_TWO].[FIELD_VALUE] и [TBL_TWO].[FIELD_NAME].

План
Код:
ADMIN SELECT STATEMENT Cost=15
    2.1 NESTED LOOPS
      3.1 NESTED LOOPS
        4.1 TABLE ACCESS(BY INDEX ROWID) - Z#TBL_TWO(TABLE)
          5.1 INDEX(RANGE SCAN) - IDX_Z#TBL_TWO_FN_FV(INDEX)
        4.2 INDEX(UNIQUE SCAN) - IDX_Z#TBL_ONE_FIELD_ARR(INDEX (UNIQUE))
      3.2 TABLE ACCESS(BY INDEX ROWID) - Z#TBL_ONE(TABLE)



Если я правильно эти планы сделал, вообще не копенгаген в этом


Я устраивал гонки, по простому время начал выполнения главного цилка и окончания, так и пришел к выводу...
e.kha
Участник со стажем


Вступление в Клуб: 26.04.2012
СообщениеЧт Сен 19, 2013 08:30    Ответить с цитатой
Полезность: Нет оценки
Random пишет:
Хотя... количество записей
Код:
select a(count(1))
   in ::[TBL_ONE], ([TBL_TWO] all : b ) all
   where   b.[FIELD_NAME] = 'DOC_ID' and
--         b.[FIELD_VALUE] = doc_id and
         b%collection = a.[FIELD_ARR]
можешь сказать?


В в каждой коллекции где то 30 записей,
этот селект выбирает только один результат,
наверно корректней тут locate использовать..
maestro
Профи


Вступление в Клуб: 12.10.2010
СообщениеЧт Сен 19, 2013 09:14    Ответить с цитатой
Полезность: Нет оценки
ИМХО, первый план лучше.
Отсутствует строка
Код:

4.1 TABLE ACCESS(BY INDEX ROWID) - Z#TBL_TWO(TABLE)

А значит Оракл не лезет в таблицу z#TBL_TWO.

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

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