Здравствуйте, обращаюсь за советом по следующему вопросу:
есть некоторая джоба, которая запускает хранимую процедуру, в которой выполняются некоторые действия в цикле, затем запускается рефреш материализованного представления с помощью DBMS_MVIEW. Проблема заключается в том, что система испытывает просадки производительности, и при определенных обстоятельствах рефреш матвью может вылиться в длительное ожидание его завершения и тормоза.
В связи с этим, хотелось бы иметь возможность остановить рефреш по истечении определенного промежутка времени, главным условием должно быть то, что процедура, в которой выполняется рефреш, должна прекратить свою работу по истечению отведенного времени, вплоть до прихлопывания оракловой сессии. Пока видется два решения: либо каким-то образом периодически проверять сколько по времени уже выполняется процедура, в которой запускается рефреш, либо как-то ограничить сам рефреш по времени. Но конкретной реализации, к сожалению, нет. Хотелось бы услышать ваши мнения по этому вопросу. И еще небольшой вопрос: какой на ваш взгляд необходимый минимум навыков оптимизации для программиста Оракл? Если что, версия Oracle DB 11.2. Спасибо!

Vladimir FilinTesamid,
Вам оно виднее, понятное дело, однако странно получится: если не успели обновить, то придётся откатить ВСЮ транзакцию, что может превысить уже потраченное время. Если промежуточный коммит, то данные в раскоряку встанут, опять же транзакционность нарушите. Решите с админим, может лучше ресурсы лимитировать для обновления. Ещё посмотрите:
- на "Fast" (точнее инкрементальное, а не быстрое!) обновление, специфический режим и не доделанный имхо, НО работать с ним точно можно
- что в атрибуте обновления атомик/не атомик, это влияет на доступность МВ во время обноврения и, соответственно, на время
- можно ли отключить какие-то индексы ПЕРЕД обновлением, потом их перестроить быстрее обычно
Vladimir Filin, спасибо за ответ, согласен с вами, но бизнес требования таковы, что в первую очередь необходимо гарантировать установленное время выполнения процедуры. Да, есть промежуточные коммиты, но это опять же бизнес-логике не противоречит, откат не требуется. Матвьюху нет возможности привести к состоянию, когда будут выполнены все требования fast обновления. Сейчас рефреш запускается с atomic_refresh = true и перед обновлением удаляются индексы, затем заново создаются. Если выставить atomic_refresh = true как это может помочь прервать рефреш в определенный момент?
В целом первостепенная задача, это остановить выполение процедуры, в которой в том числе выполняется рефреш. Увеличение производительности рефреша, на мой взгляд, не сможет точно гарантировать, что время выполнение процедуры не превысит заданного предела, т.к. он тоже может меняться.
Может сделать костыль, создав джобу, которая периодически проверяет время существования определенной сессии и делает kill в случае, если время вышло?

andreymx, UDF-не используются вообще, сама матвью строится на вьюхе, в которой есть два тяжелых CTE, которые затем джойнятся в нескольких подзапросах, объединенных union all. Пока думал над вашим постом, пришла мысль по ее оптимизации, но опять же, это не окончательное решение. Задача в том, чтобы останавливать процедуру по заданному времени, в первой ее части, где есть итерация это уже сделано, а вот для рефреша решения нет.

Всем спасибо за ответы, приняли решение все-таки сделать костыль с прекращением сессии джобы рефрешащей матвью.
Менеджер ресурсов нет возможности использовать, т.к., если не ошибаюсь, он настраивается на уровне схемы, а ограничение ресурсов должно быть индивидуальным для каждой сессии данного пользователя.