Oracle Query Оптимизация сложного запроса на удаление. Оптимизация запросов oracle

sql - Оптимизация запросов в oracle

Без плана объяснения или схемы /DDL существует ограниченная оптимизация, которая может быть выполнена.

Вот альтернатива, но вам нужно протестировать ее, чтобы понять, не имеет ли значения. (Замените соединение с помощью коррелированного запроса sub-.)

SELECT coupon_upc, sum(loyalty_a) a, sum(loyalty_b) b, sum(loyalty_c) c, sum(loyalty_x) x FROM ( SELECT ( SELECT coupon_upc FROM table2 WHERE schedule_key = 'XXX' AND coupon_id = a.coupon_id AND division = a.division GROUP BY coupon_upc ) as coupon_upc, (case when a.loyalty_cell = 'A' then 1 else 0 end) as loyalty_a, (case when a.loyalty_cell = 'B' then 1 else 0 end) as loyalty_b, (case when a.loyalty_cell = 'C1' then 1 else 0 end) as loyalty_c, (case when a.loyalty_cell = 'X' then 1 else 0 end) as loyalty_x FROM view1 a WHERE a.campaign_code = 'XXX' ) a GROUP BY coupon_upc

Кроме этого, вид оптимизации: - сохранение взгляда - индексы - рефакторинг структур данных

ИЗМЕНИТЬ

Еще один возможный рефакторинг запроса... Я не знаю, насколько Oracle мог бы оптимизировать 4 экземпляра коррелированных запросов sub-.

SELECT coupon_upc, SUM((SELECT COUNT(*) FROM view1 WHERE campaign_code = 'XXX' AND loyalty_cell = 'A' AND coupon_id = map.coupon_id AND division = map.division)) AS loyalty_a, SUM((SELECT COUNT(*) FROM view1 WHERE campaign_code = 'XXX' AND loyalty_cell = 'B' AND coupon_id = map.coupon_id AND division = map.division)) AS loyalty_b, SUM((SELECT COUNT(*) FROM view1 WHERE campaign_code = 'XXX' AND loyalty_cell = 'C1' AND coupon_id = map.coupon_id AND division = map.division)) AS loyalty_c, SUM((SELECT COUNT(*) FROM view1 WHERE campaign_code = 'XXX' AND loyalty_cell = 'X' AND coupon_id = map.coupon_id AND division = map.division)) AS loyalty_x FROM ( SELECT coupon_upc, coupon_id, division FROM table2 WHERE schedule_key = 'xxx' GROUP BY coupon_upc, coupon_id, division ) AS map GROUP BY coupon_upc

Или, может быть...

SELECT map.coupon_upc, SUM(data.loyalty_a) AS a, SUM(data.loyalty_b) AS b, SUM(data.loyalty_c) AS c, SUM(data.loyalty_x) AS X FROM ( SELECT coupon_upc, coupon_id, division FROM table2 WHERE schedule_key = 'xxx' GROUP BY coupon_upc, coupon_id, division ) AS map INNER JOIN ( SELECT coupon_id, division, SUM(CASE WHEN loyalty_cell = 'A' THEN 1 ELSE 0 END) AS loyalty_a, SUM(CASE WHEN loyalty_cell = 'B' THEN 1 ELSE 0 END) AS loyalty_b, SUM(CASE WHEN loyalty_cell = 'C1' THEN 1 ELSE 0 END) AS loyalty_c, SUM(CASE WHEN loyalty_cell = 'X' THEN 1 ELSE 0 END) AS loyalty_x FROM view1 WHERE campaign_code = 'XXX' ) AS data ON data.coupon_id = map.coupon_id AND data.division = map.division GROUP BY map.coupon_upc

qaru.site

Оптимизация SQL-запросов в Oracle. Часть 2. Практические рекомендации::Журнал СА 01-02.2016

Рубрика: Базы данных / Инструменты

Facebook

Twitter

Мой мир

Вконтакте

Одноклассники

Google+

ВАЛЕРИЙ МИХЕИЧЕВ, эксперт Oracle, СПАО «Ингосстрах», [email protected]

Оптимизация SQL-запросов в OracleЧасть 2. Практические рекомендации

В процессе многолетней практики оптимизации SQL-запросов был выработан ряд рекомендаций, позволяющих оперативно и качественно провести оптимизацию SQL-запросов. Рассмотрим их и изучим инструментальные средства, появившиеся в Oracle 11g

В процессе разработки и эксплуатации SQL-запросов (далее будем называть их запросами) возникают вопросы по оптимизации запросов, работающих длительное время (неэффективных запросов). В статье [1] показан порядок анализа планов выполнения неэффективных запросов и приведены причины неэффективности их работы. Сегодня рассмотрим инструментальные средства, появившиеся в Oracle 11g, позволяющие диагностировать неэффективные запросы, а также дадим систематизированные рекомендации пооптимизации работы неэффективных запросов.

Мониторинг эффективности запросов

К существующим средствам мониторинга эффективности работы запросов, таким как динамические представления Oracle v$sql, v$sql_plan, v$sql_bind_capture и т.д., в Oracle 11g добавились новые представления v$sql_monitor и v$sql_plan_monitor.

Достоинством нового представления v$sql_monitor по сравнению с v$sql является то, что в нем появились столбцы, идентифицирующие сессию: столбцы sid и session_serial# (идентификатор сессии и ее серия). Наличие данных столбцов позволяет увидеть последовательность (цепочку) запросов, выполняющихся в сессии.

Статью целиком читайте в журнале «Системный администратор», №01-02 за 2016 г. на страницах 70-75.

PDF-версию данного номера можно приобрести в нашем магазине.

Михеичев В. Причины неэффективности SQL-запросов в Oracle. Оптимизация производительности SQL-запросов. // «Системный администратор», №6, 2015 г. – С. 47-52 (http://samag.ru/archive/article/2967).
Михеичев В. Мониторинг блокировок в Oracle. Методы предупреждения и автоматического устранения. // «Системный администратор», №4, 2015 г. – С. 30-35 (http://samag.ru/archive/article/2926).

Facebook

Twitter

Мой мир

Вконтакте

Одноклассники

Google+

samag.ru

performance - Оптимизация запросов Oracle с помощью ROWNUM

Этот вопрос теперь спорный

Я пересчитал статистику по таблице, добавил новые индексы и повторно проанализировал существующие индексы. Это полностью изменило мои результаты и аннулирует большинство моих результатов ниже. В этот момент я нашел новый запрос, который достаточно эффективен и не нуждается в трюке ROWNUM.

Кроме того, я считаю, что стоит отметить, что нижеприведенный запрос, как написано, не гарантирует результаты, которые я хотел. Добавление DISTINCT к среднему запросу может потенциально уничтожить упорядочение, которое я попытался применить в самом внутреннем запросе. На практике это не происходило, но я не могу на это положиться.

Оригинальный вопрос

Я написал запрос, который, кажется, значительно улучшился, когда я ввел фальшивую проверку ROWNUM:

SELECT * FROM ( SELECT DISTINCT * FROM ( SELECT TransactionID FROM WOWDev.QueryLog WHERE UPPER(UserName)=UPPER('xyz') AND TransactionID IS NOT NULL ORDER BY TransactionID DESC ) WHERE ROWNUM<=1e100 -- fake ROWNUM check! this gets us on the fast path ) WHERE ROWNUM<=50

Здесь план оптимизатора.

SELECT STATEMENT, GOAL = ALL_ROWS 38025 50 650 COUNT STOPKEY VIEW JSTILES 38025 801 10413 SORT UNIQUE NOSORT 38025 801 3204 COUNT STOPKEY VIEW JSTILES 38024 801 3204 TABLE ACCESS BY INDEX ROWID WOWDEV QUERYLOG 38024 545694 9276798 INDEX FULL SCAN DESCENDING WOWDEV IX_QUERYLOG_TID 1263 212704

Если я прокомментирую фальшивую проверку ROWNUM, внезапно запрос развалится и станет намного медленнее (а также имеет 5-кратную стоимость).

SELECT STATEMENT, GOAL = ALL_ROWS 204497 50 650 COUNT STOPKEY VIEW JSTILES 204497 34865 453245 SORT GROUP BY STOPKEY 204497 34865 592705 INDEX FAST FULL SCAN WOWDEV IX_QUERYLOG_USER_TID 204462 545694 9276798

Очевидно, что он попадает и в совершенно другой индекс (который, по-видимому, не устраивает его).

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

SELECT * FROM ( SELECT DISTINCT TransactionID FROM WOWDev.QueryLog WHERE UPPER(UserName) = UPPER('xyz') AND TransactionID IS NOT NULL ORDER BY TransactionID DESC ) WHERE ROWNUM <= 50

Объясняется так:

SELECT STATEMENT, GOAL = ALL_ROWS 207527 50 650 COUNT STOPKEY VIEW JSTILES 207527 34865 453245 SORT UNIQUE STOPKEY 207491 34865 592705 INDEX FAST FULL SCAN WOWDEV IX_QUERYLOG_USER_TID 204462 545694 9276798

Как было предложено ниже, я попытался заменить мой ROWNUM<=1e100 на ROWNUM>0, и это также попало в быстрый путь, но с немного другим планом:

SELECT STATEMENT, GOAL = ALL_ROWS 38025 50 650 COUNT STOPKEY VIEW JSTILES 38025 801 10413 SORT UNIQUE NOSORT 38025 801 3204 COUNT FILTER VIEW JSTILES 38024 801 3204 TABLE ACCESS BY INDEX ROWID WOWDEV QUERYLOG 38024 545694 9276798 INDEX FULL SCAN DESCENDING WOWDEV IX_QUERYLOG_TID 1263 212704

Может ли кто-нибудь объяснить это поведение? Есть ли более чистый, менее хакерский способ получить Oracle на быстром пути?

qaru.site

Оптимизация запросов Oracle

У нас есть запрос, который работает на представлении, а время его выполнения составляет около 6 минут.

SELECT COUNT(*) FROM someschema.item_total_violations WHERE channel_id = 122507833 AND item_date >= TIMESTAMP'2015-02-01 00:00:00';

Его не материализованного представления, так что нет индекса на item_date.

Базовый запрос о представлении ниже:

SELECT i.id ,i.title ,i.body ,i.description ,i.item_date ,i.created_date ,i.channel_id ,i.community_id ,i.person_id ,i.message_type ,ch.client_id ,upper(p.NAME) AS person_name_upper ,upper(ch.NAME) AS channel_name_upper ,p.NAME AS person_name ,ch.NAME AS channel_name ,ch.connector_type AS channel_type ,cm.NAME AS community_name ,cm.network_id ,nvl(ct2.cnt, 0) + nvl(ct4.cnt, 0) AS total_violations_count ,nvl(ct1.cnt, 0) + nvl(ct3.cnt, 0) AS quarantined_violations_count ,nvl(ct5.cnt, 0) AS quarantined_attributes_count ,nvl(lh_info.cnt_lh, 0) AS legal_holds_count ,nvl(lh_info.cnt_lh_w_no_date, 0) AS legal_holds_count_w_no_date ,nvl(lh_info.max_hold_until, to_date('1970-01-01', 'yyyy-mm-dd')) AS legal_hold_max_hold_until FROM item i ,channel ch ,person p ,community cm ,( SELECT /*+ NO_MERGE */ pv.item_id ,count(*) AS cnt FROM policy_violation pv WHERE pv.item_id IS NOT NULL AND pv.quarantine_status = 'QUARANTINED' GROUP BY pv.item_id ) ct1 ,( SELECT /*+ NO_MERGE */ pv.item_id ,count(*) AS cnt FROM policy_violation pv WHERE pv.item_id IS NOT NULL GROUP BY pv.item_id ) ct2 ,( SELECT /*+ NO_MERGE */ aa.item_id AS item_id ,COUNT(*) AS cnt FROM item_attachment aa ,policy_violation pv WHERE aa.id = pv.item_attachment_id AND pv.quarantine_status = 'QUARANTINED' GROUP BY aa.item_id ) ct3 ,( SELECT /*+ NO_MERGE */ aa.item_id AS item_id ,COUNT(*) AS cnt FROM item_attachment aa ,policy_violation pv WHERE aa.id = pv.item_attachment_id AND pv.item_id IS NULL GROUP BY aa.item_id ) ct4 ,( SELECT /*+ NO_MERGE */ pv.item_id ,count(DISTINCT (pv.item_attribute_id)) AS cnt FROM policy_violation pv WHERE pv.item_id IS NOT NULL AND pv.item_attribute_id IS NOT NULL AND pv.quarantine_status = 'QUARANTINED' GROUP BY pv.item_id ) ct5 ,( SELECT lh_rel.item_id AS item_id ,count(DISTINCT (lh.id)) AS cnt_lh ,max(lh.HOLD_UNTIL) AS max_hold_until ,sum(CASE WHEN lh.hold_until IS NULL THEN 1 ELSE 0 END) AS cnt_lh_w_no_date FROM LEGAL_HOLD_RELATION lh_rel ,LEGAL_HOLD lh WHERE lh.id = lh_rel.legal_hold_id AND lh.STATUS = 'A' GROUP BY lh_rel.item_id ) lh_info WHERE ch.STATUS = 'A' AND i.channel_id = ch.id AND i.person_id = p.id AND i.community_id = cm.id(+) AND i.id = ct1.item_id(+) AND i.id = ct2.item_id(+) AND i.id = ct3.item_id(+) AND i.id = ct4.item_id(+) AND i.id = ct5.item_id(+) AND i.id = lh_info.item_id(+)

Таблица item является самым большим, и мы попытались секционирования таблицы в chaannel_id, но это не помогло нашему запросу. Я понимаю, что в исходном запросе нет индекса на item_date, и это влияет на производительность. Помимо проверки материализованного представления, есть ли что-то, что я могу улучшить в базовом запросе представления?

dba.stackovernet.com

sql - Oracle Query Оптимизация сложного запроса на удаление

Этот запрос генерирует большое количество временных сегментов (26G), как показано в плане SQL, и запрос рассчитан на запуск более 30 часов. Поэтому вы получаете ORA-04031 во время выполнения.

Это мои добрые рекомендации.

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

Вторая рекомендация - добавить предикат исключения разделов и явно указать значение даты, используя переменную literal или bind. Предварительно вычислите его в блоке PL/SQL перед выполнением запроса. Не используйте несинтетическую функцию SYSDATE для условия. Также было бы неплохо зарезервировать разумное условие для нижней границы даты (это касается дизайна).

Третий заключается в том, чтобы позволить Oracle самостоятельно найти rowid строк, которые должны быть удалены с помощью другого условия вместо явного предоставления rowid (s). Он может уменьшить количество логических операций ввода-вывода в нашем случае (используйте autotrace для проверки).

И, наконец, запрос может быть что-то вроде этого (я не проверял его, но хочу выразить эту идею):

delete from TABLE1 t1_1 where C3_Date < :upper_date_bound and C3_Date >= :lower_date_threshold and (C1_Varchar2, C2_Varchar2, C3_Date) not in (select C1_Varchar2, C2_Varchar2, max(C3_Date) from table1 t1_2 where C3_Date < :upper_date_bound and C3_Date >= :lower_date_bound group by C1_Varchar2, C2_Varchar2)

Из-за того, что количество удаляемых строк меньше половины таблицы, вы можете рассматривать предложение "IN" или "EXISTS" с другим подзапросом вместо "NOT IN". Например, создайте локальный индекс в столбце C3_Date, выполните статистический сбор и попробуйте эту часть в основном запросе

... exists (select null from table1 t1_2 where t1_2.C1_Varchar2 = t1_1.C1_Varchar2 and t1_2.C2_Varchar2 = t1_1.C2_Varchar2 and t1_2.C3_Date = t1_1.C3_Date /* don't forget about partition selectivity hint */ and t1_2.C3_Date < :upper_date_bound and t1_2.C3_Date >= :lower_date_bound group by t1_2.C1_Varchar2, t1_2.C2_Varchar2 having t1_1.C3_Date < max(t1_2.C3_Date))

Привет

qaru.site