по обычным запросам основное время уходит на
INSERT INTO AE_STATE_LOG
могу посоветовать добавить составные индексы по таблицам
AE_PARAMETER
AE_DOMAIN
AE_STATE_POLICY
AE_THRESHOLD
первым полем в индексе также оставить колонку PK, а набор остальных колонок в индексе должен быть таким, чтобы в плане запроса не было обращения к таблице вообще, а все бралось чисто из индекса.
Это может на порядок ускорить выборку.
Или, как вариант, сделать их идексно-организованными.
посмотрел distinct.
для начала хорошо бы избавиться от динамического сэмплинга по AE_STATE_TMP — он отжирает ресурсы.
проще всего добавить в выборки из этой таблицы хинт
/*+ dynamic_sampling(t 0) */ — вместо t алиас таблицы. возможно, также залочить по ней статистику dbms_stats.lock_table_stats
По коду точный баг не смог найти — их там много разных, но не про временные таблицы.
Полагаю, что должен быть пофикшен в 10.2.0.5 или даже в 10.2.0.4.
Вообще, рекомендую 11.2.0.3 + последний PSU. На 10.2.0.3 даже таймзону в БД проблематично правильную сделать… особенно если есть есть официальная поддержка.
Если на эту табличку никто ссылаться не будет, то можно отойти от канонов и убрать PK и его индекс — практической пользы от него все равно никакой.
И еще не нравится троекратное повторение select device_id, profile_id, param_id, num
, max(id) keep (dense_rank last order by datetime) id
, max(value) keep (dense_rank last order by datetime) value
, max(datetime) datetime
from ae_state_tmp
group by device_id, profile_id, param_id, num
Может еще одну промежуточную врменную таблицу попробовать сделать?
Хорого бы также снять AWR, ASH или просто trace+tkprof.
direct path подходит только для однопоточной заливки.
Если есть возможность параллелить, то, возможно, если реализовать все вышеописанное (убрать все констраинты и индексы с таблички истории, убрать FK constraints с таблицы state, переделать с temp table на pl/sql table), то можно получить резальтаты даже лучше.
Или вообще обычным view c использованием аналитических функий — при наличии на test_history индексов будет вполне шустро работать.
test_history секционируйте по event_date. От первичного ключа лучше вообще избавиться (убрать поле id). Или сделать его локальным, добавив event_date или приделать к id префикс на основе даты.
Можно и с одного пробовать, если все в количество IOPS на БД упирается.
Еще, как вариант, можно рассмотреть выкладывание текстовых файликов на сервер с БД и обработку их через external table.
Да, еще табличку test_state можно попробовать релизовать как материализованное представление с refresh on commit вместо использования merge.
А пробовали одновременно несколько процессов запустить? Какая получится максимальная суммарная производительность?
Надеюсь, что redo у вас достаточно размера, чтобы switch происходил не чаще раз в 10-15 минут.
Кажется я понял, в чем Вы заблуждаетесь. Commit — это не только выкидывание redo на диск, а непосредственно запись некой последовательности байтов, обозначающих команду commit в redo-лог. И она не может быть записана на диск раньше, чем commit произойдет — ведь может быть и rollback :)
Из своего опыта могу сказать, что системы ввода-вывода кроме ограничения на количество мегабайт в секунду имеют также ограничение на IOPS — количество операций ввода-вывода в секунду. Большое количество commit приводят к серьезной деградации как раз по потолку IOPS (сессии ждут выполнения lgwr-ом log file sync). Можете попробовать на какой-нибудь тестовой системе. Так что один commit — как мимнимум одна операция ввода-вывода.
INSERT INTO AE_STATE_LOG
могу посоветовать добавить составные индексы по таблицам
AE_PARAMETER
AE_DOMAIN
AE_STATE_POLICY
AE_THRESHOLD
первым полем в индексе также оставить колонку PK, а набор остальных колонок в индексе должен быть таким, чтобы в плане запроса не было обращения к таблице вообще, а все бралось чисто из индекса.
Это может на порядок ускорить выборку.
Или, как вариант, сделать их идексно-организованными.
для начала хорошо бы избавиться от динамического сэмплинга по AE_STATE_TMP — он отжирает ресурсы.
проще всего добавить в выборки из этой таблицы хинт
/*+ dynamic_sampling(t 0) */ — вместо t алиас таблицы. возможно, также залочить по ней статистику dbms_stats.lock_table_stats
Полагаю, что должен быть пофикшен в 10.2.0.5 или даже в 10.2.0.4.
Вообще, рекомендую 11.2.0.3 + последний PSU. На 10.2.0.3 даже таймзону в БД проблематично правильную сделать… особенно если есть есть официальная поддержка.
И еще не нравится троекратное повторение
select device_id, profile_id, param_id, num , max(id) keep (dense_rank last order by datetime) id , max(value) keep (dense_rank last order by datetime) value , max(datetime) datetime from ae_state_tmp group by device_id, profile_id, param_id, numМожет еще одну промежуточную врменную таблицу попробовать сделать?
Хорого бы также снять AWR, ASH или просто trace+tkprof.
версия БД, opatch lsinventory?
Если есть возможность параллелить, то, возможно, если реализовать все вышеописанное (убрать все констраинты и индексы с таблички истории, убрать FK constraints с таблицы state, переделать с temp table на pl/sql table), то можно получить резальтаты даже лучше.
test_history секционируйте по event_date. От первичного ключа лучше вообще избавиться (убрать поле id). Или сделать его локальным, добавив event_date или приделать к id префикс на основе даты.
Еще, как вариант, можно рассмотреть выкладывание текстовых файликов на сервер с БД и обработку их через external table.
Да, еще табличку test_state можно попробовать релизовать как материализованное представление с refresh on commit вместо использования merge.
Надеюсь, что redo у вас достаточно размера, чтобы switch происходил не чаще раз в 10-15 минут.