Oracle изменить время запуска job
Рассматривается система управления планами (SPM), введенная в версии Oracle 11 применительно к повторяющимся запросам приложения. Она позволяет формировать и хранить для запросов допустимые наборы планов (baselines), заставить СУБД работать только по ним и тем самым избежать в отдельных случаях непредусмотренного падения производительности при обработке.
Введение
Как известно, СУБД, получив от приложения запрос, сначала строит программу вычисления ответа («план»), и тут же эту программу отрабатывает. Теоретически план можно построить наилучшим образом: получить множество всех возможных для данного запроса планов и выбрать из них наиболее поизводительный в нужном отношении. На практике же любая промышленная СУБД, включая Oracle, вырабатывает план на скорую руку, достигая при этом приемлемого времени ответа, но зато жертвуя точностью решения задачи. Как следствие, применяемые к поступающим запросам планы нередко оказываются не самыми лучшими, и общая производительность СУБД страдает.
Для решения этой проблемы фирма Oracle, равно как и прочие разработчики, дает пользователю средства вмешиваться в выработку плана СУБД. Косвенное вмешательство достигается воздействием на факторы, влияющие на выработку оптимизатором планов: параметры СУБД и сеанса, показатели статистики объектов запроса, употребление подсказок. Прямое вмешательство состоит в диктовке СУБД, какие именно планы следует использовать для конкретных запросов.
Исторически первым инструментом последней категории стали «очертания» (outline) запросов, появившиеся в версии 8.1. «Очертание» запроса – это план, «схваченный» в подходящий момент времени и сохраненный для последующего употребления. Пользователь получает возможность в пределах конкретных сеансов «включать» «очертание», заставляя СУБД пользоваться этим конкретным планом независимо от текущих обстоятельств. После версии 11 эта техника фиксации плана прекращает свое официальное существование.
Вторым по времени инструментом стал «профиль» (profile) запроса, введенный в версии 10. Профили могут возникнуть в результате специальной процедуры анализа СУБД запроса, представляющего интерес, в рамках работы советника SQL Tuning Advisor. Пользователь имеет право в любой момент включить имеющийся профиль, и тогда текущий план запроса подкорректируется в сторону улучшения. Влиянием на применяемый СУБД план будет включение и отключение профиля по мере необходимости.
В версии 11 в Oracle появилось третье по счету средство указания СУБД, каким определенным планом ей воспользоваться при обработке того или иного запроса. Оно получило название «управления планами»: SQL plan management (SPM). Его основное назначение – не дать возможность пустить обработку повторяющихся запросов приложения по «плохому» плану. «Плохим» же план может оказаться ненароком при смене обстоятельств очередного поступления запроса – значений переменных привязки, статистики объектов, переменных СУБД или сеанса и даже версии СУБД или ОС.
Система управления планами запросов
SPM позволяет формировать «базу управления запросами» (SQL management base, SMB). Она располагается в AWR (automatic workload repository) и может пополняться и вычищаться как вручную, так и автоматикой AWR. В SMB для каждого представляющего интерес запроса можно накапливать историю его планов (plan history), а из нее формировать основную линию (baseline) планов. Суть «основной линии» в том, администратор формирует ее из планов, которые полагает («назначает») удовлетворительно производительными. Управление планами обработки на деле начнется при переводе оптимизатора CBO в специальный режим «учета SPM» (SPM aware optimizer). Тогда ни при какой смене обстоятельств запуска запроса (или всего приложения) оптимизатор не применит к нему план, хуже имеющихся в «основной линии».
Режим учета SPM (использования основных линий планов) устанавливается значением TRUE параметра СУБД OPTIMIZER_USE_SQL_PLAN_BASELINES. Это значение умолчательное. Изменить его можно как на уровне СУБД, так и отдельных сеансов.
При поступлении в СУБД запроса, для него вырабатывается план. Далее, если OPTIMIZER_USE_SQL_PLAN_BASELINES = FALSE, запрос выполняется по этому плану. Если = TRUE, и план присутствует и в истории планов, и в основной линии, он также принимается к исполнению. Если же план отсутствует либо в истории, либо в основной линии, для исполнения запроса будет выбран наиболее «легкий» из имеющихся в основной линии. Но если план отсутствует в истории, он дополнительно будет туда занесен.
Содержимое SMB представлено в таблице DBA_SQL_PLAN_BASELINES. На деле это, конечно, виртуальная таблица, то есть view, показывающая данные из реальных таблиц SQLOBJ$, SQLOBJ$AUXDATA и SQL$TEXT в схеме SYS). Эти данные общесистемные, а потому аналогичных таблиц с префиксами USER и ALL не существует. Вот некоторые поля этой таблицы:
Поле | Значение |
---|---|
SIGNATURE | «Подпись» запроса, вычисляемая по нормализованному тексту запроса (см. функцию DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE). |
SQL_TEXT | Текст запроса. |
SQL_HANDLE | Символьное выражение подписи, ключ для удобства поиска планов основной линии. |
PLAN_NAME | Символьное выражение для обозначения плана. |
ENABLED | Признак нахождения плана в рабочем состоянии. Если установить = 'NO', оптимизатор будет этот план игнорировать. |
ACCEPTED | Признак того, что план включен в основную линию как приемлемый. |
FIXED | Если в основной линии есть планы, помеченные FIXED = 'YES', считается, что основная линия для запроса не подлежит автоматической перестройке, то есть является фиксированой. |
AUTOPURGE | Признак, разрешающий автоматическое удаление плана из SBM автоматикой AWR по прошествии установленного времени. |
OPTIMIZER_COST, EXECUTIONS, CPU_TIME и др. | Общие количественные показатели плана. |
Помимо этого сведения о базовой линии планов для запроса в текстовом виде предоставляет функция DISPLAY_SQL_PLAN_BASELINE из пакета DBMS_XPLAN.
Совершению действий с SPM служит пакет DBMS_SPM. Этот же пакет используется в OEM для графического доступа к его собственной функциональности.
Далее SPM рассматривается на примере употребления непосредственно программным образом.
Подготовка к примеру
Далее приводится пример, где в обстоятельства выдачи приложением запроса вносятся изменения в виде добавленного индекса. Положим, мы не очень уверены, как это отразится на обработке запроса. Дабы не просчитаться с непредвиденной потерей эффективности, построим для него основную линию, включающую прежний проверенный план. Таким образом после добавления в БД индекса запрос заведомо не ухудшит производительности, но возможно улучшит.
Для примера подобран нереально простой запрос. Это сделано осознано воимя доходчивости изложения техники.
В примере будут переключения в схемы SCOTT и SYS, но предполагается, что работа выполняется в SQL*Plus без выхода из этой программы, что позволит сохранить значения переменных.
Очистим для предотвращения путаницы общую область курсоров в shared pool (технически это необязательно, но упростит здесь обращение к нужным данным в shared pool), заведем рабочие переменные и сбросим ради простоты показа в файл текст для выдачи плана последнего запроса:
Предполагается, что основная линия запросов изначально пуста. Исходно план запроса не зависит от того, применяет оптимизатор управление планами, или нет:
Загрузка плана в базу управления запросами
Вот случай, достойный памяти и занесения в литературу!
Плиний Младший, Панегирик императору Траяну
Сейчас для интересующего нас запроса СУБД завела рабочую память в общей области курсоров в shared pool. Загрузим оттуда план (первый по счету) в основную линию в базе управления запросами SMB, сославшись на идентификатор курсора SQL ID:
Для простоты последующих обращений к таблице DBA_SQL_PLAN_BASELINES за сведениями о SMB запомним в файле еще один рабочий запрос. Он параметризован ключом прикладного запроса, который узнаем по тексту запроса и поместим в переменную SQLHANDLE:
Несмотря на то, что в нашем случае запрос попал в SMB по ссылке на SQL ID, в самой базе он идентифицируется ключом SQL_HANDLE, который автоматически порождается по подписи запроса, в свою очередь вычисляемой по нормализованому тексту. Это позволяет хранить план в AWR долговременно, независимо от того, представлен ли запрос вообще в курсорной области в данный момент, и под каким именно SQL ID представлен.
Обратите внимание, что использованный способ загрузки плана в основную лонию автоматически выставил признаки ENABLED и ACCEPTED в состояние 'YES', то есть единственный пока план в SMB и в рабочем состоянии, и включен в основную линию.
Использование основной линии планов запроса оптимизатором CBO
Изменим обстоятельства запуска запроса, индексировав таблицу. План должен поменяться. Однако при включенном (по умолчанию) управлении планами мы этого не увидим. Примечательно, что убедиться в этом удастся только со второй попытки:
Первый раз оптимизатор построил новый план, с учетом индекса, но в SMB его не обнаружилось. Тогда оптимизатор занес план в историю и выполнил запрос по единственному в основной линии плану – старому. Со второго раза рабочая область в shared pool оказалась заведена, но запрос по-прежнему был отработан по единственному в основной линии старому плану. Если же управление планами отключить, СУБД отработает по более выгодному в этой версии оптимизатора новому плану:
Пополнение основной линии планов путем оценки планов
По результату выполненых действий в основной линии планов для нашего запроса оказалось два плана: с учетом индекса (признак ACCEPTED = 'NO') и без учета (признак ACCEPTED = 'YES'):
Можно выдать оптимизатору задание проверить с планы признаками ACCEPTED = 'NO' (то есть учтеные в SMB, но не причисленые к приемлемым) на эффективность и включить их в основную линию (пометить как «приемлемые»), если они окажутся не хуже ранее там имевшихся:
Первую проверку изменений в SMB выполним по признаку ACCEPTED в справочной таблице:
Вторую проверку выполним по содержимому переменной REPORT, составленному функцией EVOLVE_SQL_PLAN_BASELINE:
Вердикт о влючении плана в основную линию производится на основании сравнения взвешенных суммарных оценок поперечисленным в отчете показателям («Compound improvement ratio»; точное правило не оглашается). В нашем случае он оказался благоприятным для плана-кандидата (коэффициент улучшения эффективности >= 1.5).
Теперь можно проверить прежним манером, как появление второго плана в основной линии отразилось на отработке запроса, выдав последовательность:
На этот раз планы окажутся одинаковыми, «новыми» (INDEX RANGE SCAN → TABLE ACCESS BY INDEX ROWID).
Прочие способы управления основной линией планов
Пополнение и ручная чистка основной линии планов
Основную линию планов какого-нибудь запроса можно пополнять («развивать», evolve): вручную либо автоматически.
Ручное пополнение основной линии в результате запуска задания на проверку приемлемости плана выполняется функцией EVOLVE_SQL_PLAN_BASELINE и демонстрировалось выше.
Процедура LOAD_PLANS_FROM_SQLSET позволяет загружать основную линию планы из настроечного набора (SQL tuning set). Настроечный набор может быть получен любым доступным путем, например перенесен из другой БД, возможно даже из версии 10.
Процедуры PACK_STGTAB_BASELINE и UNPACK_STGTAB_BASELINE разрешают сохранить планы основных линий в специально созданной таблице и загружать их из такой таблицы.
Включение в сеансе параметра СУБД OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES в состояние TRUE вызовет автоматическое пополнение SMB планами запросов, поступающих из приложения. Для запросов приложения, основные линии планов которых желательно исключить из процедуры автоматического пополнения (то есть «зафиксировать»), можно использовать значение атрибута FIXED = 'YES' планов, составляющих соответствующую линию. Наличие планов с атрибутом FIXED = 'YES' препятствует только автоматическому пополнению и не сказывается на возможности добавлять планы вручную, по SQL ID и по настроечному набору.
Автоматическое пополнение основных линий также может осуществляться в результате «одобрения» (принятия) администратором профиля, рекомендованного для запроса советником SQL Tuning Advisor. По умолчанию этот советник запускается автоматическим заданием в «окошко поддержки» СУБД ежесуточно.
Путь попадания плана в основную линию обозначен в таблице DBA_SQL_PLAN_BASELINES в поле ORIGIN:
Ручное удаление плана из основной линии выполняется функцией DROP_SQL_PLAN_BASELINE.
Изменение свойств планов в SMB
Процедура ALTER_SQL_PLAN_BASELINE позволяет устанавливать атрибутам ENABLED, FIXED и AUTOPURGE плана требуемые значения явочным порядком. Пример:
Теперь система управления планами снова откажется применять «новый» план к запросу, несмотря на его присутствие в основной линии планов – благодаря нерабочему состоянию.
Регулирование накопления и хранения планов в основных линиях
- максимальным процентом данных SMB в пространстве SYSAUX (от 1 до 50%, исходно 10%);
- максимальным периодом отсутствия интереса к плану, после чего он будет автоматически удален (от 5 до 523 недель, исходно 53).
Узнать текущие характеристики регламента накопления и хранения SMB можно запросом:
Изменить эти характеристики можно процедурой CONFIGURE, например:
Уроки системы управления планами в Oracle
Система управления планами в Oracle способна не только сохранить производительность при смене обстоятельств запуска запросов, но и преподать пользователю уроки.
Рассмотрим план для запроса SELECT DISTINCT … (далее все аналогично для запросов с UNION и GROUP BY). Как известно, в версиях до 9 включительно этот запрос обрабатывался с применением внутренней сортировки SORT UNIQUE. С версии 10 оптимизатор предлагает для такого запроса план с HASH UNIQUE, внутренней процедурой расстановки строк, с «хешированием». Большинство пользователей, обративших на это внимание, посчитали его целесообразным новшеством, улучшающим производительность отработки. Однако попытка применить для таких запросов управление планами (хотя бы ради сохранения производительности) заставляет в этом усомниться.
Действительно, попробуем в стиле вышеизложеного построить основную линию планов для другого отправного случая и другого запроса. Выдадим:
Включим план в основную линию, как выше. Это будет план с SORT UNIQUE. Поменяем обстоятельства выдачи запроса, например:
Получится план с HASH UNIQUE. Однако попытка дополнить им основную линию планов запроса функцией EVOLVE_SQL_PLAN_BASELINE обречена. SPM не считает новый план, который дают версии 10+ для этого запроса, лучше прежнего!
Более пристальное изучение обнаруживает, что план с HASH UNIQUE имеет большую стоимость обработки (cost), нежели «старый» с SORT UNIQUE (10 единиц против 5), хотя с ростом размера таблицы этот проигрыш и сокращается.
Я не нашел объяснения этому явлению, однако если здесь нет подводных камней, система управления планами способна и в этом случае предотвратить неожиданый нежелательный рост трат на обработку.
Можно получить выигрыш в производительности, если выполнять обработку данных параллельно несколькими потоками - в нескольких сеансах Oracle. При помощи пакета DBMS_JOB этого можно было добиться и в более ранних версиях Oracle. Однако Oracle Scheduler, появившийся в версии 10 и усовершенствованный в версии 11g и 12c, предоставляет для этого более развитые возможности. В этой статье мы рассмотрим следующие темы:
- кратко рассмотрю цепочки (chains) Oracle Scheduler,
- продемонстрирую создание цепочки для параллельной обработки данных,
- продемонстрирую организацию параллельной обработки данных без создания цепочки.
Предопределенные окна обслуживания
В Oracle Database 11g и 12c есть семь предопределенных окон обслуживания, которые перечислены ниже:
- MONDAY_WINDOW — начинается в 10 вечера в понедельник и заканчивается в 2 ночи;
- TUESDAY_WINDOW — начинается в 10 вечера во вторник и заканчивается в 2 ночи;
- WEDNESDAY_WINDOW — начинается в 10 вечера в среду и заканчивается в 2 ночи;
- THURSDAY_WINDOW — начинается в 10 вечера в четверг и заканчивается в 2 ночи;
- FRIDAY_WINDOW — начинается в 10 вечера в пятницу и заканчивается в 2 ночи;
- SATURDAY_WINDOW — начинается в 6 утра в субботу и заканчивается в 2 ночи;
- SUNDAY_WINDOW — начинается в 6 утра в воскресенье и заканчивается в 2 ночи.
Окна рабочих дней недели открыты в течение 4 часов, а окна выходных — в течение 20 часов. Эти семь окон обслуживания входят в группу, именуемую MAINTENANCE_WINDOW_GROUP. Окнами обслуживания можно управлять, изменяя их время начала и конца. Можно также создавать свои окна обслуживания и удалять или отключать окна по умолчанию. В следующих разделах будет показано, как это делать.
Ошибки
Шаг цепочки
- определяет, какую программу (или цепочку) необходимо запустить,
- имеет имя,
- создается с помощью dbms_scheduler.define_chain_step ,
- запускается на выполнение согласно правилам цепочки,
- удаляется с помощью dbms_scheduler.drop_chain_step .
Вынужденное выполнение задания
Если необходимо запустить задание в текущий момент времени, то сделать это можно с помощью следующей процедуры пакета:
Если параметр FORCE имеет значение ложно, то задание может быть выполнено в приоритетном режиме только в указанном экземпляре, иначе .
Задание выполняется в текущем сеансе, при этом повторно инициализируются пакеты текущего сеанса, и происходит неявная фиксация транзакции.
Выключение задания
Бывают случаи, когда задание временно не должно выполняться. Для этого совсем необязательно его удалять. Достаточно его просто выключить. Выключение (включение) задания производится установкой специального флага состояния - BROKEN. Делается это с помощью следующей процедуры:
Если флаг BROKEN имеет значение истинно, то такое задание считается разрушенным и выполняться не будет. Параметр NEXT_DATE определяет здесь дату следующего выполнения задания и действует только при его включении.
В момент выключения задания параметр NEXT_DATE устанавливается в максимальное значение даты. Если не указать параметр NEXT_DATE в момент включения, то задание начнёт выполняться немедленно.
Для примера выключим задание:
Настройка сервера
Для того чтобы задания начались выполняться необходимо, установить параметр инициализации JOB_QUEUE_PROCESSES. Изначально он имеет значение 0 и задаёт максимальное количество фоновых процессов для выполнения заданий. В версии Oracle 9.2 максимальное значение этого параметра может составлять 1000. На практике же обычно можно ограничиться не более 5 процессами. В любом случае вы всегда можете изменить это значение с помощью команды ALTER SYSTEM SET без перезагрузки сервера. Итак, для начала внесем новую строчку в файл инициализации и перезагрузим сервер:
В файле alert.log мы увидим, что в момент, когда стартуют фоновые процессы, у нас появилось новая запись:
Это стартовал новый фоновый процесс CJQ0, так называемый координатор заданий. Что он делает и для чего он нужен, мы рассмотрим чуть ниже, а на этом предварительную настройку сервера можно считать законченной.
Отключение задачи обслуживания
С помощью процедуры DISABLE можно отключить любое из трех заданий обслуживания во время определенного окна обслуживания:
В приведенном примере задача SQL Tuning Advisory отключается только во время MODAY_WINDOW и остается включенной во всех остальных окнах.
Управление автоматизированными задачами обслуживания
Поскольку база данных не назначает постоянных заданий Scheduler трем автоматизированным задачам обслуживания, ими нельзя управлять с помощью пакета DBMS_SCHEDULER. Если необходимо выполнять тонко настроенные задачи обслуживания, которые модифицируют автоматизированные задачи обслуживания, следует использовать пакет DBMS_AUTO_TASK_ADMIN.
Прерывание задания
В статье рассматриваются некоторые свойства и примеры употребления планировщика заданий, появившегося в версии Oracle 10 на смену старому.
Введение
СУБД Oracle — большой и сложный механизм, требующий выполнения определенных плановых работ, таких как сбор статистики о хранимых объектах или сбор/чистка внутренней информации. Необходимость осуществлять плановый запуск работ могут испытывать и пользователи БД.
Первый механизм планового запуска появился в версии 7 для поддержки автоматических обновлений снимков (snapshots), как поначалу именовались нынешние материализованные виртуальные таблицы (materialized views). В версии 8 этот механизм был открыт для обычных пользователей через посредство некоторых параметров СУБД, таблиц словаря-справочника, а также пакета DBMS_JOB. Пакет DBMS_JOB позволял (и позволяет) запускать хранимую процедуру, или же неименованный блок PL/SQL в моменты времени, вычисляемые по указанной пользователем формуле.
- Schedule (расписание)
- Program (программа)
- Job (плановое задание = расписание + программа)
В отличие от старого планировщика, в новом «программой» может быть не только блок PL/SQL, но и хранимая процедура на PL/SQL или на Java, внешняя процедура на С или даже команда ОС. Последнее означает, что Oracle отменяет необходимость использовать специфичные для разных платформ планировщики заданий ОС (cron, at) при построении БД-центричного приложения. Вдобавок, сам запуск заданий получил возможность учета текущей вычислительной обстановки в СУБД, а также желаемой приоритетности среди прочих заданий.
- таблицы словаря-справочника LIKE '%SCHEDULER_%' (DBA_SCHEDULER_JOBS, DBA_SCHEDULER_JOB_LOG и прочие);
- несколько типов объектов хранения, как то:
- JOB
- SCHEDULE
- PROGRAM
- JOB CLASS,
- CREATE SESSION
- CREATE JOB
- CREATE ANY JOB
- EXECUTE ANY PROGRAM
- EXECUTE ANY CLASS
- MANAGE SCHEDULER
- CREATE EXTERNAL JOB,
- EXECUTE
- ALTER
- ALL,
- запуска «легковесных» заданий, делающей реальным их создание и удаление сотнями за секунду;
- запуска заданий на удаленных машинах посредством использования специального агента;
- запуска заданий только на основной БД физического горячего резерва или на страхующей БД логического резерва.
Некоторые ключевые моменты использования планировщика в Oracle 10 рассматриваются ниже на примерах.
Простой запуск задания
Простой запуск задания очень напоминает запуск с помощью процедуры SUBMIT из пакета DBMS_JOB. Однако, в отличие от SUBMIT, он возможен только при наличии привилегии CREATE JOB. В последующих примерах созданием заданий и управлением ими для простоты будет заниматься пользователь SCOTT, хотя в жизни разумно подумать об отдельном администраторе для этой цели. Выдадим пользователю SCOTT нужную привилегию:
Кроме системных привилегий, использование планировщика регулируется объектными привилегиями EXECUTE, ALTER и ALL, выдача которых применительно (GRANT . ON) к заданию, программе, расписанию или классу заданий позволяет работать с объектами БД типов JOB, PROGRAM, SCHEDULE и JOB CLASS соответственно, введенных в Oracle 10 вместе с новым планировщиком.
Ввиду того, что в дальнейшем предполагаются эксперименты с изменениями зарплаты сотрудников, будет удобно исходную зарплату сохранить:
Внутреннее задание для СУБД
Пример внутреннего задания в виде неименованного блока PL/SQL:
- Обрамлять блок словами BEGIN и END не обязательно, так как код пакета DBMS_SCHEDULER это сделает самостоятельно (ради особой программной логики, добавляемой им к тексту пользователя).
- Задание запускается в этом же сеансе и сопровождается неявной выдачей COMMIT. В этом легко удостовериться: Зарплата SAL увеличится на 2. Проверить это в качестве упражнения.
Для хранимой процедуры задание формируется аналогично:
Обратите внимание, что нам не потребовалось удалять старое задание SIMPLE_JOB, так как при выбранных нами параметрах процедуры CREATE_JOB задания (и первое, и второе) прогонялись однократно, моментально и сразу же удалялись автоматически. Последнее как раз можно и отменить посредством не использованного в примере выше параметра AUTO_DROP.
В случае невозможности запустить задание СУБД, подобно тому, как это делалось для старого планировщика (пакет DBMS_JOB), будет делать повторные попытки, но только по несколько иной схеме: через секунду, затем через 10 секунд, затем через 100 и далее – всего 6 раз, если только до этого не наступит очередной плановый момент.
Внешнее задание (для ОС)
Совсем новым в планировщике Oracle 10 является возможность запускать плановые задания в ОС. Однако чтобы это было возможно, в ОС должна быть запущена программа extjob из ПО СУБД. На Windows она запускается службой OracleJobScheduler . Для того чтобы следующий пример проработал, службу необходимо запустить. Вдобавок потребуется выдать пользователю SCOTT еще одну привилегию.
Обратите внимание, что в Windows выдача команды ОС или же запуск командного файла напрямую (без вызова cmd.exe), не проходит.
В Unix аналогичное действие можно записать как 'ls > /tmp/out. txt'.
Возможности запуска, наблюдения, вмешательства
Так же, как для пакета DBMS_JOB, в новом планировщике предусмотрено именно плановое, а не одноразовое исполнение задания. Добавим к последнему вызову параметр:
В результете корневой файл out.txt получим через 10 секунд после создания задания. Добавим еще параметр: В результате задание будет исполняться ежемесячно по воскресениям и последним субботам месяца. В отличие от DBMS_JOB, DBMS_SCHEDULER, в дополнение к возможности употребить выражение на PL/SQL, имеет для формулирования графика запуска еще и специальный язык. Он позволяет указывать частоту, интервал и уточнитель запуска задания. Примеры:
Для проверки правильности составления выражения можно воспользоваться специальной процедурой:
Полное описание языка приводится в документации по Oracle.
Если указать план запуска, задание появится в системе уже надолго. Удалить его при необходимости можно будет так:
Информацию об имеющихся заданиях пользователь SCOTT может посмотреть запросом:
Более подробную информацию SCOTT обнаружит в таблицах USER_SCHEDULER_%, а более общую – в обычной таблице USER_OBJECTS.
Скомпонованное задание
Более развитая возможность DBMS_SCHEDULER позволяет скомпоновать задание из независимых элементов: программы и расписания. Характерная особенность в том, что оба эти элемента самостоятельны; их можно комбинировать в разных заданиях и изменять, не внося изменений в определения заданий.
Создание программы
Простой пример создания программы:
Список сведений об имеющихся программах для планировщика имеется в таблицах DBA/ALL/USER_SCHEDULER_PROGRAMS.
Другими значениями параметра PROGRAM_TYPE могут быть 'PLSQL_BLOCK' и 'EXECUTABLE' (см. выше).
При наличии у процедуры параметров их количество потребуется указать особо:
Обратите внимание, что программа создана «отключенной». Дело в том, что указать фактические значения параметрам программе во «включенном» состоянии нельзя, так что последовательность действий будет следующая:
Создание расписания
Пример создания расписания:
В общем случае язык указания графика для расписания (параметр REPEAT_INTERVAL) допускает ссылаться на ранее созданные таким же образом расписания.
Список сведений об имеющихся расписаниях для планировщика имеется в таблицах DBA/ALL/USER_SCHEDULER_SCHEDULES.
Простой пример скомпонованного задания
Из самостоятельно существующих программы и расписания можно составить задание:
При наличии параметра пример может выглядеть так:
Обратите внимание, что в этом случае задание сначала создается выключенным, и только после указания значения параметра программе оно может включаться.
Создание и использование ресурсного окошка СУБД для задания
Ресурсный план построить несложно, но, чтобы не отвлекаться, воспользуемся встроенным в любую БД планом SYSTEM_PLAN (см. таблицу DBA_RSRC_PLANS). Тогда создание окошка может выглядеть так:
Теперь каждые три минуты на минуту будет включаться ресурсный план SYSTEM_PLAN. Это легко наблюдать, выдав несколько раз от имени SYS:
Если подгадать момент, когда значение OPERATION для окошка MY_JOB_WINDOW станет OPEN, от имени SYS можно будет удостовериться, что план включен:
Чтобы связать с этим периодически открывающимся ресурсным окошком задание, пользователю SCOTT достаточно указать его вместо расписания:
С этого момента можно наблюдать уменьшение зарплат сотрудников, осуществляемое каждые три минуты в условиях благоприятного ресурсного плана. В качестве варианта, в процедуре CREATE_WINDOW можно было сослаться и на какое-то существующее расписание.
Так как СУБД в каждый момент времени умеет работать только по одному ресурсному плану, окошки «не умеют» перекрываться, а могут только переопределять своим планом другой, ранее установленный и пришедшийся на то же время.
В качестве развития этой темы Oracle позволяет создавать именованный список существующих окошек и под маркой «группы» (window group) указывать его заданию значением параметра SCHEDULE_NAME, то есть там, где у нас было указано имя окошка.
Изменение свойств объектов планировщика
Хотя упомянутые JOB, SCHEDULE, PROGRAM, WINDOW и проч., причисляются к объектам хранения БД (и видны в таблицах DBA/ALL/USEROBJECTS), не только их создание и удаление, но и изменение свойств выполняются так, как было удобно разработчику: через API. Для всех перечисленных видов объектов существует довольно много поведенческих свойств, указанию которых нет места в процедурах LIKE 'CREATE_%'. Устанавливать их следует явно единой для всех процедурой SET_ATTRIBUTE. Вот пример, как для задания MY_WINDOW_JOB (а) задать приоритет выполнения (по отношению к другим заданиям своего класса), если на одно время пришлось выполнение нескольких заданий одновременно, и (б) потребовать прекращения (процедурой STOP_JOB), если оно еще не выполнилось, а ресурсное окошко уже закрылось:
Полный список атрибутов и объектов, к которым они применимы, имеется в документации по Oracle.
Заключение
Помимо использованного выше общения с планировщиком Oracle 10 средствами PL/SQL и SQL, общаться с ним можно через графический интерфейс Oracle Enterprise Manager. По сути, OEM ничего нового не дает, так как в конечном итоге отсылает к СУБД те же команды на PL/SQL и SQL, но выполнение разовых действий через OEM часто администратору быстрее и понятнее. Для автоматизации работ, однако, лучше может подойти работа со сценариями запросов.
После проведенных опытов с планировщиком не забудьте освободить БД от ненужных объектов. Например:
Удаление прочих созданных ранее объектов и изъятие выданных пользователю SCOTT привилегий предлагается сделать самостоятельно в виде упражнения, воспользовавшись таблицами словаря-справочника и, при надобности, документацией. Можно также использовать OEM.
Изменение задания
И так задание создано. Теперь попробуем изменить некоторые его параметры. Для изменения доступны следующие параметры задания: WHAT, NEXT_DATE, INTERVAL и INSTANCE. Их можно менять все одновременно или по отдельности. К примеру, следующая процедура пакета меняет все три параметра, при этом следует учитывать, что если какой либо из них равен NULL, то значение параметра не изменится.
А вот уже эта процедура меняет значение только параметра WHAT:
Также с параметрами NEXT_DATE,INTERVAL,INSTANCE:
В качестве примера, увеличим паузу, которое делает задание, созданное ранее, до 30 секунд и изменим, время повторного запуска задания на 3 часа 15 минут:
Изменить параметры или совершать другие действия над заданием можно только его владельцу. В противном случае возникнет ошибка ORA-23421: job number 24 is not a job in the job queue.
Удаление задания
Если задание становиться ненужным, то его можно удалить. Сделать это можно следующей процедурой:
Создам цепочку DEMO_CHAIN:
Каждый шаг цепочки будет связан с одной из программ, определенных выше. В связи с этим, можно автоматизировать создание шагов цепочки:
В результате определены следующие шаги:
Для завершения определения цепочки нужно создать правила. Первое правило запускает на выполнение шаг DEMO_S_START :
Следующее правило проверяет, завершен ли шаг DEMO_S_START , и запускает шаги DEMO_S_0 , DEMO_S_1 и DEMO_S_2 параллельно:
Третье правило проверяет, завершены ли шаги DEMO_S_0 , DEMO_S_1 и DEMO_S_2 , и запускает шаг DEMO_S_FINALLY :
Четвертое, и последнее, правило завершает выполнение цепочки после завершения шага DEMO_S_FINALLY :
Посмотреть созданные правила можно следующим запросом:
Условия правил используют либо специальный синтаксис (scheduler chain condition syntax), либо синтаксис выражений SQL WHERE. В условиях можно проверять не только завершение шагов как таковое ( ИМЯШАГА COMPLETED ), но и успешное ( ИМЯШАГА SUCCEEDED ) или ошибочное завершение шага ( ИМЯШАГА FAILED ), а также анализировать код ошибки, переданный из программы через RAISE_APPLICATION_ERROR ( ИМЯШАГА ERROR_CODE ). Об условиях и действиях правил см. описание процедуры dbms_scheduler.define_chain_rule в документации по Oracle 11g.
Прежде, чем цепочку пожно будет запустить на выполнение, необходимо ее активировать:
Теперь запущу цепочку и проверю результат ее работы:
Посмотреть подробности выполнения шагов цепочки можно следующим запросом:
Все шаги демонстрационной цепочки отработали быстро, поскольку они выполняют очень мало работы. Если бы цепочка выпонялась долго, то следующий запрос помог бы проследить за текущим статусом выполнения ее шагов:
На этом закончу работу с цепочкой и удалю ее:
Вместе с цепочкой были удалены ее шаги и правила:
Но программы, непосредственно не связанные с цепочкой, остались, и их нужно удалить отдельно:
Теперь продемонстрирую выполнение такого же сценария - подготовка, параллельная обработка и завершение - без создания цепочки.
Для этого создам пакет DEMO_PKG , в который помещу процедуры
- initialize - для подготовки данных (аналог программы DEMO_P_START ),
- process - для обработки данных (аналог программ DEMO_P_0 , DEMO_P_1 , DEMO_P_2 ),
- finalize - для завершения обработки (аналог программы DEMO_S_FINALLY ),
- run - для скоординированного выполнения процедур (аналог цепочки DEMO_CHAIN ).
Для параллельного запуска процедуры process с разными аргументами в нескольких сеансах воспользуюсь dbms_scheduler.create_job . Контроль завершения процедур в параллельных сеансах выполняется с помощью пакета DBMS_LOCK .
Запущу формирование данных и проверю результат:
Что ж, результат соответствует ожиданиям.
Демонстрационный пакет DEMO_PKG не содержит ни одной команды COMMIT или ROLLBACK , в реальном пакете они могут быть уместны.
Автоматизированные задачи обслуживания — это автоматически запускаемые в базе данных задания, выполняющие операции по обслуживанию. Ниже перечислены автоматизированные задачи обслуживания в Oracle Database 11g и 12c:
- Automatic Optimizer Statistics Collection (Автоматический сбор статистики оптимизатора);
- Automatic Segment Advisor (Автоматический советник по сегментам);
- Automatic SQL Tuning Advisor (Автоматический советник по настройке SQL).
Все три задачи автоматического обслуживания запускаются во время системного окна обслуживания по умолчанию, приходящегося на ночное время. Ниже мы поговорим о предопределенных окнах обслуживания.
Блокировки
Если подробнее разобрать представление DBA_JOBS_RUNNING, то можно увидеть что в его основе лежит соединение таблицы SYS.JOB$ и представления V$LOCK. Кажется, какая тут есть связь? Оказывается, есть и самая прямая. Для того чтобы гарантировать, что данное задание выполняется одновременно только в одном сеансе, Oracle выставляет блокировку JQ. Это можно хорошо видеть, сделав запрос к представлению V$LOCK во время выполнения задания:
При этом столбец ID2 будет указывать на идентификатор выполняемого задания.
Мониторинг автоматизированных задач обслуживания
Для получения подробной информации относительно выполнения автоматизированных заданий обслуживания в базе данных потребуется запросить представления DBA_AUTOTASK_CLIENT и DBA_AUTOTASK_OPERATION. Эти два представления совместно используют множество столбцов. Ниже показан запрос к представлению DBA_AUTOTASK_CLIENT:
Столбец ATTRIBUTES показывает, что все три автоматизированные задачи обслуживания по умолчанию включены, о чем свидетельствует атрибут ON BY DEFAULT. Когда открывается окно обслуживания, база данных автоматически создает три автоматизированных задачи обслуживания и выполняет эти задания. Однако только задача SQL Tuning Advisor показывает атрибут OPEN PER WINDOW. Это потому, что база данных выполняет обе задачи — Automatic Optimizer Statistics Collection и Auto Space Advisor — более одного раза, если позволяют размеры окна обслуживания, в то время, как SQL Tuning Advisor выполняется на протяжении окна обслуживания только один раз.
База данных присваивает клиентское имя каждой из автоматизированных задач обслуживания, чтобы они выглядели как клиенты. Задание Scheduler, ассоциированное с тремя клиентами, получает имя операции, поскольку задания считаются операциями. Вот имена операций, ассоциированные с каждой из трех автоматизированных задач обслуживания:
Выделение ресурсов автоматическим задачам
План ресурсов по умолчанию, назначаемый всем окнам обслуживания — это DEFAULT_MAINTENANCE_PLAN. Когда окно обслуживания открывается, база данных активизирует DEFAULT_MAINTENANCE_PLAN для контроля ресурсов центрального процессора, используемых автоматическими задачами обслуживания. Три автоматических задачи обслуживания запускаются под ORA$AUTOTASK_SUB_PLAN — подпланом DEFAULT_MAINTENANCE_PLAN. Выделение ресурсов для автоматизированных задач можно изменить, изменяя выделение ресурсов этому подплану для конкретного окна обслуживания.
В Oracle существует возможность запланировать выполнение определенного набора действий в виде заданий. Задание может, представляет собой хранимую процедуру, анонимный блок PL /SQL, внешнюю процедуру на языке C или Java. Время выполнения может иметь значение любого времени суток и подчинятся заданному интервалу. Это хорошо подходит для переноса тяжёлых в обработке расчётов на менее загруженное ночное время. По умолчанию выполнение заданий выключено. Поэтому надо провести небольшую дополнительную настройку сервера.
Реализация автоматизированных задач обслуживания
Процесс ABR (Autotask Background Process — фоновый процесс автоматизированных задач) отвечает за реализацию трех автоматизированных задач обслуживания, преобразуя их в задания Scheduler. Для каждой из этих задач ABR создает список заданий и назначает им приоритеты. Есть три уровня приоритета: высокий, средний и срочный. Scheduler также создает классы заданий и отображает группу потребителей на соответствующий класс заданий. ABR назначает задания в каждый класс заданий, а классы заданий отображают задания на группы потребителей на основе уровня приоритета задания. Фоновый процесс MMON осуществляет перезапуск и отслеживает процесс ABR. Представление DBA_AUTOTASK показывает задания, хранящиеся в репозитории ABR, которым служит табличное пространство Sysaux.
Просматривать репозиторий ABR можно путем опроса представления DBA_AUTOTASK_TASK.
Цепочка (chain) Oracle Scheduler
Цепочка содержит шаги (chain steps) и правила (chain rules).
Контроль задания
Как было указано выше, координатор заданий обращается в своей работе к системной таблице SYS.JOB$, хранящей описания всех заданий. На эту таблицу существуют три представления: DBA_JOBS, ALL_JOBS и USER_JOBS. Они наиболее часто используются для контроля над заданиями. Рассмотрим их некоторые столбцы:
- JOB, NEXT_DATE, INTERVAL, WHAT, INSTANCE - эти столбцы знакомы нам по процедуре SUBMIT.
- LOG_USER - пользователь под которым была создано задание. Фактически это и есть владелец задания.
- PRIV_USER - пользователь привилегии которого используются для выполнения задания.
- SCHEMA_USER - схема по умолчанию для разбора задания.
- LAST_DATE (LAST_SEC) - дата (время) последнего успешного выполнения задания.
- THIS_DATE (THIS_SEC) - дата (время) начала выполнения задания.
- TOTAL_TIME - общее время выполнения задания в секундах. Содержит суммарное время длительности всех выполнений задания.
- BROKEN - этот столбец показывает состояние флага разрушенного задания. Если значение равно Y задание выполняться не будет.
- FAILURES - количество неудачных попыток выполнить задание. Максимальное значение может достигать 16.
- NLS_ENV - NLS параметры сеанса. Соответствуют параметрам сеанса, в котором задание создавалось.
Кроме вышеперечисленных представлений существует и ещё одно - DBA_JOBS_RUNNING. Оно показывает задания, которые выполняются в текущий момент времени. С его помощью можно легко определить SID сеанса выполняемого задания.
Создание заданий
Для управления заданиями в Oracle существует специальный пакет DBMS_JOB. С его помощью над ними можно осуществлять различные действия. Для начала попробуем создать новое задание. В нашем случае для этого, нужно применить следующую процедуру пакета:
Опишем параметры этой процедуры:
JOB - это идентификатор задания. Имеет уникальное значение для каждого задания, генерируемое системной последовательностью. Является выходным параметром. Его лучше запомнить, если потребуются дальнейшие действия над заданиями.
WHAT - тело задания. Представляет собой анонимный PL/SQL блок. Всё что здесь указано, будет выполнено в процессе работы задания. Если вы запускаете только одну процедуру, то можно не заключать её в блок достаточно поставить в конце названия процедуры точку с запятой. Значение WHAT в этом случае автоматически будет помещено в PL/SQL блок. Если процедура имеет строковые параметры, то они обязательно должны заключаться в две одинарные кавычки с каждой стороны. В PL/SQL блоке можно также писать DML и DDL команды, но нельзя производить создание и запуск заданий. Это только приведёт к ошибке ORA-32317. Если же используются ссылки на удалённую базу данных, то они должны явно включать имя и пароль. Анонимные ссылки здесь не поддерживаются. И, наконец, владельцу задания требуется явно предоставить привилегии, на объекты, используемые в теле задания.
NEXT_DATE - дата следующего выполнения задания. Время непосредственно задаётся владельцем или автоматически вычисляется Oracle.
Если вы введёте дату меньше чем текущую, то выполнение задания может начаться немедленно или отложится на неопределённое время. В этом случае попробуйте принудительно запустить задание или повторно изменить этот параметр, это инициирует немедленный запуск задания.
INTERVAL - формула интервала времени. Представляет собой DATE функцию. Именно от её правильного значения будет зависеть дата следующего выполнения задания казанного в столбце NEXT_DATE. Приведём некоторые примеры формулы интервала задания:
Задание будет выполняться ровно в n часов m минут каждого дня.
Задание будет выполняться ровно в n часов m минут последнего дня каждого месяца.
Задание будет выполняться ровно в n часов m минут первого дня каждого месяца.
Учитывайте, что вычисление NEXT_DATE с помощью формулы интервала происходит после выполнения задания. Поэтому всегда учитывайте это время и старайтесь не ставить их на время близкое к окончанию суток из-за возможного неправильного расчёта следующей даты выполнения.
NO_PARSE - флаг разбора PL/SQL. Если его значение равно FALSE разбор происходит в момент установки задания. Иначе, в момент выполнения задания.
INSTANCE - какой экземпляр производит выполнение задания.
FORCE - если значение этого параметра истинно то в качестве INSTANCE может выступать любое положительное целое число. В противном случае экземпляр, указанный в INSTANCE должен быть обязательно запущен, иначе поднимается исключение.
При создании задания или изменения его параметров ORACLE записывает текущие параметры NLS владельца. Эти параметры каждый раз восстанавливаются при выполнении задания. Это может приводить к некоторым ошибкам в случае ожидания других значений. Поэтому если необходимо лучше производить установку нужных NLS значений с помощью команды ALTER SESSION в параметре WHAT задания.
В качестве примера создадим простое задание, которое при запуске будет делать паузу в 20 секунд, первый раз выполниться 1 января 2006 года в 1 час 5 минут и будет повторяться каждый день в то же самое время.
Правило цепочки
- содержит условие и действие,
- однократно при выполнении условия выполняет действие,
- имеет имя,
- создается с помощью dbms_scheduler.define_chain_rule ,
- создается с помощью dbms_scheduler.drop_chain_rule .
Обычно, условия правил проверяют завершение выполнения шагов цепочки, а действия правил - запускают на выполнение шаги цепочки. В каждой правильно построенной цепочке должны быть
- правило с условием, которое выполняется безусловно(!), например, 1=1 ; это правило запускает на выполнение первый логический шаг цепочки;
- правило с действием END , которое завершает выполнение цепочки.
Прежде чем построить и выполнить демонстрационную цепочку, нужно познакомиться еще с одним объектом Oracle Scheduler - программой (program). Программа
- определяет PL/SQL блок, имя PL/SQL процедуры или внешнюю команду ОС, которую необходимо выполнить,
- имеет имя,
- создается с помощью dbms_scheduler.create_program ,
- может иметь параметры, создаваемые с помощью dbms_scheduler.define_program_argument ,
- запускается на выполнение из шага цепочки или из задания (scheduler job),
- удаляется с помощью dbms_scheduler.drop_program.
В отличие от шагов и правил, программа непосредственно не связана с конкретной цепочкой, но связывается с шагом цепочки при его определении. Одна и та же программа, при необходимости, может использоваться многими шагами разных цепочек.
Спроектирую цепочку для параллельной обработки данных. Первый шаг цепочки будет выполнять подготовительную работу. Далее будут параллельно запускаться 3 шага, каждый из которых делает свою часть работы. Последний шаг будет выполнять некоторую консолидирующую работу, пользуясь результатами завершившихся параллельных шагов. Например, он может выгрузить подготовленные данные во внешний файл, или послать по электронной почте уведомление о завершении обработки данных.
Для демонстрационных целей создам таблицу demo_data :
Прежде всего, определю программы, которые будут использоваться цепочкой:
Для программы типа PLSQL_BLOCK в качестве выполняемого действия можно указать PL/SQL блок, как для DEMO_P_START выше, или фрагмент кода на PL/SQL, не заключенный в ключевые слова BEGIN и END . Перед запуском на выполнение Oracle Scheduler поместит код программы внутрь PL/SQL блока.
Посмотреть созданные программы можно следующим запросом:
Процессы
Итак, процесс координатора заданий запущен, и как можно догадаться из его названия именно этот процесс осуществляет общее управление всеми заданиями. Для начала он выбирает таблицу SYS.JOB$, в которой хранятся параметры заданий. Если среди заданий имеются те, которые будут выполняться в ближайший интервал времени указанный в скрытом параметре _JOB_QUEUE_INTERVAL (по умолчанию его значение составляет 5 секунд), то для них порождаются фоновые процессы очереди заданий Jnnn, которые в свою очередь создают сеансы для непосредственного выполнения запланированных действий. Именно максимальное количество процессов Jnnn, которые могут быть одновременно запущены и отражает настраиваемый параметр JOB_QUEUE_PROCESSES.
После того как выбранным заданиям были выделены процессы Jnnn, координатор выжидает интервал времени, указанный в параметре _JOB_QUEUE_INTERVAL. Процессы заданий в тоже время продолжают работать. После выполнения задания процесс Jnnn считается свободным, и координатор распределяет его на выполнение другого задания. Если такого задания не находится в течение определенного времени, то процесс Jnnn уничтожается. В случае, когда процессов Jnnn не хватает, задание будет выполнено в тот момент времени, когда один из них освободится.
Выставляйте значение параметра JOB_QUEUE_PROCESSES чуть больше максимального количества одновременно запускаемых заданий. Маленькое значение может привести к сдвигу времени выполнения из-за конкуренции за процессы Jnnn. Большое значение к неоправданному запуску этих же процессов в исключительных ситуациях.
Включение задачи обслуживания
Выполните процедуру ENABLE, чтобы включить ранее отключенного клиента или операцию, как показано ниже:
Извлечь атрибуты CLIENT_NAME и OPERATION_NAME можно, опросив представления DBA_AUTOTASK_CLIENT и DBA_AUTOTASK_OPERATION.
Экспорт задания
Если требуется перенести задание в другую базу данных или пересоздать задание, то это можно легко сделать с помощью следующей процедуры пакета:
Переменная mycall будет при этом содержать текст команды, с помощью которой можно заново создать задание.
Читайте также: