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

Спасибо за вашу оценку!

Спасибо за обратную связь! Если англ.язык - не преграда, то сайт и youtube-канал Берта действительно будут полезны каждому, кто интересуется подобной тематикой

спасибо!

Без чтения планов в оракле оптимизировать что-то невозможно. Поэтому любые советы по оптимизации по хорошему нужно сопровождать демонстрацией плана запросов, например как это делал @xtender в своих статьях на orasql.org.

Что касается CTE в оракле, то использовать их нужно с умом - они могут материализовываться в отдельную временную таблицу, а могут и инлайниться, встраиваюсь в запрос таким образом, как будто CTE и нет вообще, форсированно этим можно управлять соответствующими хинтами MATERIALIZE и INLINE соответственно. CTE c материализацией часто используется для следующей цели - допустим у нас есть тяжелый подзапрос с большим количеством соединений, возвращающий очень малое количество строк. И этот подзапрос нужно связать с другим сложным подзапросом или представлением - чтобы оракл не пытался объединить эти две части, ломая хорошо оптимизированные подзапросы, которые вернут условно по три строчки за пол секунды каждый, в один мегазапрос с кучей рейндж или фулл сканов, который будет работать пол часа. Поэтому надо чётко понимать, что заинлайненный СТЕ - это просто декорация, а материализованный - временная таблица, в которую выгружены все данные из подзапроса СТЕ. В вашем случае без CTE вероятно как раз и родился такой мегазапрос, который отъедал temp на группировках/сортировках, а переписав его на CTE вы материализовали какую-то его часть в отдельный подзапрос, который выполнился отдельно и выгрузился во временную таблицу и дальше уже использовались только результирующие строки из него.

Я тоже не сторонник использования hint's, но не упомянуть этот пункт не мог, потому что в отдельных случаях имеет право на жизнь - у нас на 19 оракле до сих пор они есть во многих скриптах. В качестве замены MV можно использовать создание обычных таблиц или представлений с постановкой их на регламентное обновление - в команде именно его и применяем, поскольку нет прав на создание MV с обновлением в режиме ON COMMIT. Но лично мне MV нравятся больше из-за возможности поставить зависимость от изменений таблиц источника без использования дополнительных ETL-инструментов, хоть и отъедают больше ресурсов у БД. Result cache - тема для продвинутых, нужен опыт работы с функциями/пакетами, а целевая аудитория данной статьи вряд ли сможет это корректно применить.

О многом ни слова. Да тема такая огромная, что одной статьей не обойтись.
Но все же, имхо, автор, зря вы с хинтов начинаете.
Начинают разрабы пихать их не к месту после таких статей.
Все таки с основ cost based оптимизации лучше. Дальше, что есть estimated планы ( о которых речь в общем то тут и идет), и реальные.
А как про реальные планы, так тут и статистика , child cursors, hard/soft parse и plan stability( вот тут о хинтах уже можно, наряду с outlines).

Что касается хинтов, то, как мне кажется, средний Оракл-разработчик проходит следующие ступени:

0. Не знает о наличии хинтов или о том как ими пользоваться.
1. Узнает о наиболее популярных хинтах и начинает использовать их как универсальный инструмент для каждого длительного запроса.
2. Ловит на п.1. столько граблей, что начинает в некоторых случаях даже ратовать за запрет использования хинтов в коде и говорить что в каждом конкретном случае проблема решается каким-то другим способом ( простейший - через работу со статистикой, но есть и другие), активно осваивает эти способы.
3. В конце концов приходит к тому, что некоторые проблемы никак нельзя решить без хинтов как бы ты правильно ни делал все остальное и уже наступив по разу на все грабли с хинтами использует их правильно и очень дозированно.

Про хинты у меня противоположный опыт. Единственные проекты, где запросы всегда работали нормально – это где все запросы приложения были захинтованы. А те проекты, которые считали хинты злом, регулярно ловили большие проблемы с производительностью. При этом налицо была корреляция, что у тех команд, которые не использовали хинты, было множество других проблем с пониманием, как всё работает под капотом. Там и разбиение на сущности неправильное было, и типы данных, и все остальные ошибки из этого поста.

На самом деле есть случай, когда извлекаемые и используемые в соединении с другими таблицами целиком находятся в одном составном индексе. В этом случае оракл может вообще не обращаться к сегменту таблицы, если не требуется дополнительных полей. И тут разница уже будет - в случае со * это будет "index scan + table access", в случае выбора только проиндексированного поля - только "index scan" без обращения к таблице. Плюс ещё бывают поля с *LOB'ами которые ещё в одном сегменте находятся и могут не требоваться в данном конкретном запросе.

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |          |     1 |    71 |     4   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE                     |          |     1 |    71 |     4   (0)| 00:00:01 |   KEY |   KEY |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TBL1     |     1 |    71 |     4   (0)| 00:00:01 |   KEY |   KEY |
|*  3 |    INDEX RANGE SCAN                        | IDX_TBL1 |     1 |       |     3   (0)| 00:00:01 |   KEY |   KEY |
-----------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |    12 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE|          |     1 |    12 |     3   (0)| 00:00:01 |   KEY |   KEY |
|*  2 |   INDEX RANGE SCAN    | IDX_TBL1 |     1 |    12 |     3   (0)| 00:00:01 |   KEY |   KEY |
--------------------------------------------------------------------------------------------------

А ну ещё забыл тоже достаточно частый вариант, с left join'ами (часто такое с представлениями (view) бывает). Тут у оракла есть возможность не только выкидывать сегмент таблицы, но даже выкидывать целые подзапросы из результирующего плана, если не нужны все поля.

create table CLIENTS as
select level as id, 'client '||level as name
  from dual
 connect by level <= 1000
/

ALTER TABLE CLIENTS
 ADD CONSTRAINT PK_CLIENTS
  PRIMARY KEY (ID)
/

create table PAYMENTS as
select c.ID * 10000 + p.PAY_ID as PAY_ID, c.ID as CLIENT_ID, 0 as PAY_SUM 
from CLIENTS c, (select level as pay_id from dual connect by level <= 100) p
/

ALTER TABLE PAYMENTS
 ADD CONSTRAINT PK_PAYMENTS
  PRIMARY KEY (PAY_ID)
/

CREATE INDEX IDX_PAYMENTS_CLIENT_ID ON PAYMENTS
(CLIENT_ID)
/

ALTER TABLE PAYMENTS
 ADD CONSTRAINT FK_PAYMENTS_CLIENT 
  FOREIGN KEY (CLIENT_ID) 
  REFERENCES CLIENTS (ID)
/

create or replace view V_PAYMENTS as
select P.*, C.NAME
  from PAYMENTS P
   left join CLIENTS C on P.CLIENT_ID = C.ID
/

BEGIN
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
     OwnName           => USER
    ,TabName           => 'PAYMENTS'
    ,Cascade           => TRUE);
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
     OwnName           => USER
    ,TabName           => 'CLIENTS'
    ,Cascade           => TRUE);
END;
/

Вот чисто синтетический пример с таблицами PAYMENTS и CLIENTS, завёрнутые во вьюшку так, чтобы CLIENTS соединялась через left join. Теперь посмотрим планы запросов:

select * 
  from V_PAYMENTS
 where client_id = 1

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |   100 |  2700 |    50   (2)| 00:00:01 |
|*  1 |  HASH JOIN OUTER             |            |   100 |  2700 |    50   (2)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL          | PAYMENTS   |   100 |  1200 |    49   (3)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| CLIENTS    |     1 |    15 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_CLIENTS |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("P"."CLIENT_ID"="C"."ID"(+))
   2 - filter("P"."CLIENT_ID"=1)
   4 - access("C"."ID"(+)=1)

select PAY_ID, PAY_SUM
  from V_PAYMENTS
 where client_id = 1

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |   100 |  1200 |    49   (3)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| PAYMENTS |   100 |  1200 |    49   (3)| 00:00:01 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("P"."CLIENT_ID"=1)

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

Про postgresql / greenplum - тема для отдельной статьи, т.к. там множество собственных фич - дистрибуция, сжатие, распределение по сегментам, сбор статистики и пр.