Oracle. Проверка валидности - update vs for loop | |
---|---|
Pekpytep Сообщений: 727 Откуда: Луганск Дата регистрации: 19.10.2010 |
Всем привет.
Стоит задача заливки некоторых данных из csv в таблицы БД. Сначала данные заливаются во временную таблицу, по которой необходимо проверить каждую запись на валидность(проверить наличие обязательных для заполнения полей, "причесать" адреса, телефоны и т.д.). Во временной таблице порядка двух десятков столбцов, ожидаемый объем - 5-15 тыс. записей за одну загрузку около 5-7 раз в неделю. На "чистом" sql, видимо, задача должна реализовываться набором update-ов, но мне более удачным решением кажется select..bulk collect в коллекцию, цикл по коллекции с необходимыми проверками каждой записи и в конце forall update назад в эту же таблицу для фиксации результатов проверок. Собственно вопрос: насколько я понимаю, при таком подходе все данные должны быть выбраны в оперативную память. При этом, судя по форумам, оракла неохотно отдает обратно занимаемую ранее коллекциями память. Намного ли разрастется занимаемый в памяти объем csv-файла в полтора Мб и не получу ли я ****ы от ДБА за сожранную память? Оправдан ли вообще такой подход или коллекции не предназначены для работы с такими объемами данных? ДБА труднодоступен, на вопросы отвечает с большой задержкой и неохотно, доступа к серверу нет чтобы посмотреть самостоятельно. |
Re: Oracle. Проверка валидности - update vs for loop | |
---|---|
Igor Korolyov Автор Сообщений: 34580 Дата регистрации: 28.05.2002 |
В bulk collect есть волшебная опция limit - чтобы не грузить в память ВЕСЬ массив, а работать с разумного размера частями - я бы начал со "стандартного" 100 записей за раз. Это СЕРЬЁЗНО снизит нагрузку на память, при том не потеряется возможность bulk update-а.
Вот тут Том отвечает на сходный вопрос. Из staging таблицы потом в рабочую данные как переливаются? Разовым merge, или свои pl/sql-ные циклы? ------------------ WBR, Igor |
Re: Oracle. Проверка валидности - update vs for loop | |
---|---|
Pekpytep Сообщений: 727 Откуда: Луганск Дата регистрации: 19.10.2010 |
Я потому и спрашиваю, что хочу понять насколько критично выбрать все записи. Исходя из того, что у коллекции есть свойства и методы, этот объект, вероятно, будет занимать в памяти в несколько раз больший объем чем исходный csv-файл. Предположим, под коллекцию будет занят в 10 раз больший объем: 1.5 х 10 = 15 Мб При этом, даже на домашних машинках уже далеко не редкость 16 гиг оперативы. Предположу что на сервере ее не меньше 32, но скорее всего еще больше. Мне кажется, что эти 15 Мб вообще не должны никак отразиться на производительности и быстродействии сервера, но возможно я чего-то не знаю и ошибаюсь. Не очень хотелось бы переделывать все под курсоры, после фоксовских они приводят меня в уныние. В любом случае, спасибо за ссылку, завтра буду изучать. Поскольку по ТЗ я должен исключить дубликаты, то мне достаточно раскидать данные по таблицам insert..select-ом |
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 |
Re: Oracle. Проверка валидности - update vs for loop | |
---|---|
Pekpytep Сообщений: 727 Откуда: Луганск Дата регистрации: 19.10.2010 |
Как понять что это произошло? Функция вывалится в исключение? Оптимизатор разве не достаточно умный чтобы попытаться автоматом сымитировать выборку порциями если он видит превышение лимита? На процесс - это значит, что если я захочу загнать в коллекции пару-тройку небольших справочников для проверок, то их суммарный объем не должен превышать лимита? Как понять какой лимит оптимальный если у меня нет возможности мониторинга ресурсов сервера и я могу отталкиваться только от времени выполнения функции? |
Re: Oracle. Проверка валидности - update vs for loop | |
---|---|
Igor Korolyov Автор Сообщений: 34580 Дата регистрации: 28.05.2002 |
Да. При том вполне может и весь серверный процесс рухнуть - т.е. получишь "ORA-03113: end-of-file on communication channel" и будешь чесать репу, чего это тебя сервер выкинул... Конечно же это вряд ли произойдёт в твоём случае - не те объёмы, но всё же... При чём тут оптимизатор? Ты явно сказал - вынуть ВСЕ записи в коллекцию. Движок то их, возможно, и частями будет вынимать и грузить в коллекцию (тут я не скажу как оно внутри реализовано) - да толку от этого, если память тупо закончится Естественно. При том там всё достаточно сложно - одно дело режим dedicated server - где каждой коннекции сопоставлен "свой" процесс, и PGA вместе с UGA получаются приватные (т.е. "жадные" клиенты нагружают всю ОС, но не сильно мешают "соседям" - ну разве что сбивают механизм автоматического управления памятью - есть такой параметр pga_aggregate_target - если один "хам" сожрал практически весь объём, сервер будет пытаться сильно ущемить всех прочих "честных граждан"). В режиме shared server UGA (насколько я помню, pl/sql коллекции именно там живут) хранится в общей памяти - т.е. "жадина" уже заметно будет влиять на всех соседей... Проблема скорости в pl/sql это обычно переключения контекстов - т.е. когда pl/sql вызывает что-то sql-ное, ну и наоборот - когда sql вызывает pl/sql-ные функции. Попытаться минимизировать эти перескоки и есть главная цель оптимизации. bulk да forall к тому и сделан. Солидные бородатые дядьки советуют играть в пределах 100-1000 записей (и, соответственно, строк в коллекции) за итерацию лопатить. asktom.oracle.com Полагаю что в таком варианте тебе даже не потребуется общаться с DBA, и скорость должна быть вполне приемлемой. ------------------ WBR, Igor |
Re: Oracle. Проверка валидности - update vs for loop | |
---|---|
Pekpytep Сообщений: 727 Откуда: Луганск Дата регистрации: 19.10.2010 |
Переделал выборку по советам Кайта из вышеприведенных ссылок. Показалось, что время выполнения даже чуть-чуть (на пару-тройку секунд) увеличилось. Хотя на самом деле сложно замерить время выполнения т.к. каждый раз оно разное с разбросом плюс-минус процентов 15-20 (на одних и тех же данных).
Я правильно понимаю, что при каждой итерации коллекция пересоздаётся заново, а не расширяется на очередную порцию? Мне же не надо контролировать что я потом апдейтом обратно возвращаю в таблицу, кусок выбрал - кусок записал? Возник еще один вопрос. Спрошу здесь чтобы темы не плодить. Как правильно проверить, имеется ли вхождение значения элемента коллекции, к примеру, v_customer(idx).city в одно из значений справочника cities.name чтобы исключить или минимизировать переключение контекста sql - plsql ? З.Ы. большое спасибо за советы, здорово помогают просветлению |
Re: Oracle. Проверка валидности - update vs for loop | |
---|---|
Igor Korolyov Автор Сообщений: 34580 Дата регистрации: 28.05.2002 |
Теоретически это возможно - если ОС будет свопить память "жирного" процесса, то это замедлит работу - а сделав процесс не таким жадным до памяти можно этого избежать. Практически - ну только отслеживая серверную (при том не СУБД а базовой ОС) статистику можно сказать что да как. Да. Для одного элемента - простейшим параметризованным запросом. Но, конечно, это весьма накладно для обработки коллекции из 10К элементов (не важно всей кучей или порциями - всё одно 10К запросов, даже очень лёгких это некомильфо). Ну а так, если размер справочника небольшой, то вынуть все эти cities.name в ассоциативный массив индексированный "по строке" (... table of number index by varchar2(...)) и для поиска использовать её метод .exists ('искомая_строка') К сожалению, вынуть данные из таблицы в такой ассоциативный массив при помощи bulk запроса нельзя. Т.е. придётся сначала вынуть строки в table of varchar2(...) index by pls_integer, а потом пройтись по нему циклом и заполнить второй массив. Конечно же, так разумно делать если размеры справочника не гигантские, иначе снова приходим к вопросу о поедаемой этим процессом памяти... И да, сам массив имеет смысл хранить в переменной пакета, чтобы инициализировать его всего раз за сессию. Надеюсь "изменчивость" справочников позволяет без особых опасений использовать такого рода кэш - записи в справочнике не изменятся за время обработки Тут примеры есть (без загрузки из таблицы, но не суть важно - просто сравнивается скорость разных вариантов поиска в массиве): www.oracle.com ------------------ WBR, Igor |
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 |
Re: Oracle. Проверка валидности - update vs for loop | |
---|---|
Igor Korolyov Автор Сообщений: 34580 Дата регистрации: 28.05.2002 |
Естественно Нет, перебор циклом тут не нужен. Хотя я полагаю что для небольших гобъёмов - до пары тысяч элементов - ты разницы и не заметишь - что циклом, что "по индексу"... Вот "запрос", за счёт переключения контекстов, будет таки тормозить. Раз ещё "атрибуты справочника" появились, то первый ассоциативный массив усложнится - это будет table of r_city index by pls_integer, где r_city это "запись" из полей названия и часового пояса. Второй массив будет служить "индексом" - там, проверив "наличие" элемента можно будет сразу же получить и его "номер в первом массиве" (это ж массив ЧИСЕЛ с индексным доступом "по строке") - ну и соответственно по полученному номеру/индексу без всякого цикла выйти в первом массиве на нужный элемент и взять TZ. В общем ещё раз внимательно, вдумчиво читай приведенную статью - там почти всё для решения есть ------------------ WBR, Igor |
© 2000-2024 Fox Club  |