:: Не фоксом единым
Oracle. Проверка валидности - update vs for loop
Pekpytep

Сообщений: 727
Откуда: Луганск
Дата регистрации: 19.10.2010
Всем привет.

Стоит задача заливки некоторых данных из csv в таблицы БД. Сначала данные заливаются во временную таблицу, по которой необходимо проверить каждую запись на валидность(проверить наличие обязательных для заполнения полей, "причесать" адреса, телефоны и т.д.). Во временной таблице порядка двух десятков столбцов, ожидаемый объем - 5-15 тыс. записей за одну загрузку около 5-7 раз в неделю.
На "чистом" sql, видимо, задача должна реализовываться набором update-ов, но мне более удачным решением кажется select..bulk collect в коллекцию, цикл по коллекции с необходимыми проверками каждой записи и в конце forall update назад в эту же таблицу для фиксации результатов проверок.

Собственно вопрос: насколько я понимаю, при таком подходе все данные должны быть выбраны в оперативную память. При этом, судя по форумам, оракла неохотно отдает обратно занимаемую ранее коллекциями память. Намного ли разрастется занимаемый в памяти объем csv-файла в полтора Мб и не получу ли я ****ы от ДБА за сожранную память? Оправдан ли вообще такой подход или коллекции не предназначены для работы с такими объемами данных?
ДБА труднодоступен, на вопросы отвечает с большой задержкой и неохотно, доступа к серверу нет чтобы посмотреть самостоятельно.
Ratings: 0 negative/0 positive
Re: Oracle. Проверка валидности - update vs for loop
Igor Korolyov
Автор

Сообщений: 34580
Дата регистрации: 28.05.2002
В bulk collect есть волшебная опция limit - чтобы не грузить в память ВЕСЬ массив, а работать с разумного размера частями - я бы начал со "стандартного" 100 записей за раз. Это СЕРЬЁЗНО снизит нагрузку на память, при том не потеряется возможность bulk update-а.
Вот тут Том отвечает на сходный вопрос.

Из staging таблицы потом в рабочую данные как переливаются? Разовым merge, или свои pl/sql-ные циклы?


------------------
WBR, Igor
Ratings: 0 negative/0 positive
Re: Oracle. Проверка валидности - update vs for loop
Pekpytep

Сообщений: 727
Откуда: Луганск
Дата регистрации: 19.10.2010
Igor Korolyov
чтобы не грузить в память ВЕСЬ массив, а работать с разумного размера частями
Я потому и спрашиваю, что хочу понять насколько критично выбрать все записи. Исходя из того, что у коллекции есть свойства и методы, этот объект, вероятно, будет занимать в памяти в несколько раз больший объем чем исходный csv-файл. Предположим, под коллекцию будет занят в 10 раз больший объем:
1.5 х 10 = 15 Мб
При этом, даже на домашних машинках уже далеко не редкость 16 гиг оперативы. Предположу что на сервере ее не меньше 32, но скорее всего еще больше. Мне кажется, что эти 15 Мб вообще не должны никак отразиться на производительности и быстродействии сервера, но возможно я чего-то не знаю и ошибаюсь.

Igor Korolyov
Вот тут Том отвечает на сходный вопрос.
Не очень хотелось бы переделывать все под курсоры, после фоксовских они приводят меня в уныние. В любом случае, спасибо за ссылку, завтра буду изучать.

Igor Korolyov
Из staging таблицы потом в рабочую данные как переливаются? Разовым merge, или свои pl/sql-ные циклы?
Поскольку по ТЗ я должен исключить дубликаты, то мне достаточно раскидать данные по таблицам insert..select-ом
Ratings: 0 negative/0 positive
Re: Oracle. Проверка валидности - update vs for loop
Igor Korolyov
Автор

Сообщений: 34580
Дата регистрации: 28.05.2002
Если это ОДИН процесс в один момент времени, то админ, возможно, и не будет против. Но если таких "погрузчиков" будет запущено несколько, то вполне может возмутится. Это ж ОБЩИЕ серверные ресурсы. Там может одновременно и 500 и 1000 сессий работать.
Кроме того он запросто может отказать лишь на том основании что настраивает сессии на "типичную" нагрузку (например не даёт им жрать больше 10Мб памяти, а твоя коллекция запросто перешагнёт этот предел), и делать "особые настройки" не захочет (и это понятно - одному для такой задачи позволь, другому для другой что-то подкрути - и в итоге вместо понятной и строгой схемы управления ресурсами получаем бардак).

В любом случае, зачем его вообще парить всем этим, если усложнения в варианте с bulk collect limit будет на 5 копеек, а пользы на целый рупь А уж где прописан сам запрос - в отдельном cursor или прямо в тексте - ну я особой разницы не вижу. Опять таки - в общем у dba может быть своё особое мнение на этот счёт - это одно из "соглашений" - некоторые, например, не приветствуют неявные курсоры в коде... Но чтобы какой-то DBA был против bulk collect limit - это очень сомнительно Вот без limit - вполне может отказать.


------------------
WBR, Igor
Ratings: 0 negative/0 positive
Re: Oracle. Проверка валидности - update vs for loop
Pekpytep

Сообщений: 727
Откуда: Луганск
Дата регистрации: 19.10.2010
Igor Korolyov
настраивает сессии на "типичную" нагрузку (например не даёт им жрать больше 10Мб памяти, а твоя коллекция запросто перешагнёт этот предел)
Как понять что это произошло? Функция вывалится в исключение? Оптимизатор разве не достаточно умный чтобы попытаться автоматом сымитировать выборку порциями если он видит превышение лимита?
На процесс - это значит, что если я захочу загнать в коллекции пару-тройку небольших справочников для проверок, то их суммарный объем не должен превышать лимита?
Как понять какой лимит оптимальный если у меня нет возможности мониторинга ресурсов сервера и я могу отталкиваться только от времени выполнения функции?
Ratings: 0 negative/0 positive
Re: Oracle. Проверка валидности - update vs for loop
Igor Korolyov
Автор

Сообщений: 34580
Дата регистрации: 28.05.2002
Pekpytep
Как понять что это произошло? Функция вывалится в исключение?
Да. При том вполне может и весь серверный процесс рухнуть - т.е. получишь "ORA-03113: end-of-file on communication channel" и будешь чесать репу, чего это тебя сервер выкинул... Конечно же это вряд ли произойдёт в твоём случае - не те объёмы, но всё же...
Pekpytep
Оптимизатор разве не достаточно умный чтобы попытаться автоматом сымитировать выборку порциями если он видит превышение лимита?
При чём тут оптимизатор? Ты явно сказал - вынуть ВСЕ записи в коллекцию. Движок то их, возможно, и частями будет вынимать и грузить в коллекцию (тут я не скажу как оно внутри реализовано) - да толку от этого, если память тупо закончится
Pekpytep
На процесс - это значит, что если я захочу загнать в коллекции пару-тройку небольших справочников для проверок, то их суммарный объем не должен превышать лимита?
Естественно. При том там всё достаточно сложно - одно дело режим dedicated server - где каждой коннекции сопоставлен "свой" процесс, и PGA вместе с UGA получаются приватные (т.е. "жадные" клиенты нагружают всю ОС, но не сильно мешают "соседям" - ну разве что сбивают механизм автоматического управления памятью - есть такой параметр pga_aggregate_target - если один "хам" сожрал практически весь объём, сервер будет пытаться сильно ущемить всех прочих "честных граждан"). В режиме shared server UGA (насколько я помню, pl/sql коллекции именно там живут) хранится в общей памяти - т.е. "жадина" уже заметно будет влиять на всех соседей...
Проблема скорости в pl/sql это обычно переключения контекстов - т.е. когда pl/sql вызывает что-то sql-ное, ну и наоборот - когда sql вызывает pl/sql-ные функции. Попытаться минимизировать эти перескоки и есть главная цель оптимизации. bulk да forall к тому и сделан.
Pekpytep
Как понять какой лимит оптимальный если у меня нет возможности мониторинга ресурсов сервера и я могу отталкиваться только от времени выполнения функции?
Солидные бородатые дядьки советуют играть в пределах 100-1000 записей (и, соответственно, строк в коллекции) за итерацию лопатить.
asktom.oracle.com
Полагаю что в таком варианте тебе даже не потребуется общаться с DBA, и скорость должна быть вполне приемлемой.


------------------
WBR, Igor
Ratings: 0 negative/0 positive
Re: Oracle. Проверка валидности - update vs for loop
Pekpytep

Сообщений: 727
Откуда: Луганск
Дата регистрации: 19.10.2010
Переделал выборку по советам Кайта из вышеприведенных ссылок. Показалось, что время выполнения даже чуть-чуть (на пару-тройку секунд) увеличилось. Хотя на самом деле сложно замерить время выполнения т.к. каждый раз оно разное с разбросом плюс-минус процентов 15-20 (на одних и тех же данных).

Я правильно понимаю, что при каждой итерации коллекция пересоздаётся заново, а не расширяется на очередную порцию? Мне же не надо контролировать что я потом апдейтом обратно возвращаю в таблицу, кусок выбрал - кусок записал?

Возник еще один вопрос. Спрошу здесь чтобы темы не плодить. Как правильно проверить, имеется ли вхождение значения элемента коллекции, к примеру, v_customer(idx).city в одно из значений справочника cities.name чтобы исключить или минимизировать переключение контекста sql - plsql ?

З.Ы. большое спасибо за советы, здорово помогают просветлению
Ratings: 0 negative/0 positive
Re: Oracle. Проверка валидности - update vs for loop
Igor Korolyov
Автор

Сообщений: 34580
Дата регистрации: 28.05.2002
Pekpytep
Показалось, что время выполнения даже чуть-чуть (на пару-тройку секунд) увеличилось.
Теоретически это возможно - если ОС будет свопить память "жирного" процесса, то это замедлит работу - а сделав процесс не таким жадным до памяти можно этого избежать. Практически - ну только отслеживая серверную (при том не СУБД а базовой ОС) статистику можно сказать что да как.
Pekpytep
Я правильно понимаю, что при каждой итерации коллекция пересоздаётся заново
Да.
Pekpytep
Как правильно проверить, имеется ли вхождение значения элемента коллекции, к примеру, v_customer(idx).city в одно из значений справочника cities.name чтобы исключить или минимизировать переключение контекста sql - plsql?
Для одного элемента - простейшим параметризованным запросом. Но, конечно, это весьма накладно для обработки коллекции из 10К элементов (не важно всей кучей или порциями - всё одно 10К запросов, даже очень лёгких это некомильфо).
Ну а так, если размер справочника небольшой, то вынуть все эти cities.name в ассоциативный массив индексированный "по строке" (... table of number index by varchar2(...)) и для поиска использовать её метод .exists ('искомая_строка')
К сожалению, вынуть данные из таблицы в такой ассоциативный массив при помощи bulk запроса нельзя. Т.е. придётся сначала вынуть строки в table of varchar2(...) index by pls_integer, а потом пройтись по нему циклом и заполнить второй массив.
Конечно же, так разумно делать если размеры справочника не гигантские, иначе снова приходим к вопросу о поедаемой этим процессом памяти...
И да, сам массив имеет смысл хранить в переменной пакета, чтобы инициализировать его всего раз за сессию. Надеюсь "изменчивость" справочников позволяет без особых опасений использовать такого рода кэш - записи в справочнике не изменятся за время обработки
Тут примеры есть (без загрузки из таблицы, но не суть важно - просто сравнивается скорость разных вариантов поиска в массиве):
www.oracle.com


------------------
WBR, Igor
Ratings: 0 negative/0 positive
Re: Oracle. Проверка валидности - update vs for loop
Pekpytep

Сообщений: 727
Откуда: Луганск
Дата регистрации: 19.10.2010
Запрос - это на поверхности и очевидно, что эта проверка будет являться причиной повышенной нагрузки.
На самом деле все несколько сложнее, мне мало того что нужно проверить, есть ли указанный город в справочнике, мне еще из справочника нужно взять часовой пояс по его значению.

По сути, если сделать выборку в коллекцию v_cities мне нужно найти номер элемента idx для соответствующего справочнику города чтобы потом из v_cities(idx).timezone взять значение.
Кроме перебора циклом пока ничего умного в голову не приходит, хотя это примерно по производительности наверное то же самое, что делать выборку запросом.



Исправлено 1 раз(а). Последнее : Pekpytep, 04.05.17 15:27
Ratings: 0 negative/0 positive
Re: Oracle. Проверка валидности - update vs for loop
Igor Korolyov
Автор

Сообщений: 34580
Дата регистрации: 28.05.2002
Pekpytep
Запрос - это на поверхности и очевидно, что эта проверка будет являться причиной повышенной нагрузки.
Естественно
Pekpytep
По сути, если сделать выборку в коллекцию v_cities мне нужно найти номер элемента idx для соответствующего справочнику города чтобы потом из v_cities(idx).timezone взять значение.
Кроме перебора циклом пока ничего умного в голову не приходит, хотя это примерно по производительности наверное то же самое, что делать выборку запросом.
Нет, перебор циклом тут не нужен. Хотя я полагаю что для небольших гобъёмов - до пары тысяч элементов - ты разницы и не заметишь - что циклом, что "по индексу"... Вот "запрос", за счёт переключения контекстов, будет таки тормозить.

Раз ещё "атрибуты справочника" появились, то первый ассоциативный массив усложнится - это будет table of r_city index by pls_integer, где r_city это "запись" из полей названия и часового пояса. Второй массив будет служить "индексом" - там, проверив "наличие" элемента можно будет сразу же получить и его "номер в первом массиве" (это ж массив ЧИСЕЛ с индексным доступом "по строке") - ну и соответственно по полученному номеру/индексу без всякого цикла выйти в первом массиве на нужный элемент и взять TZ.
В общем ещё раз внимательно, вдумчиво читай приведенную статью - там почти всё для решения есть


------------------
WBR, Igor
Ratings: 0 negative/0 positive


Извините, только зарегистрированные пользователи могут оставлять сообщения в этом форуме.

On-line: 3 (Гостей: 3)

© 2000-2024 Fox Club 
Яндекс.Метрика