Опыт и рекомендации по оптимизации SQL-запросов

Валерий Михеичев,
ведущий специалист по Oracle
OCAO “Ингосстрах”

Источник: Статья предоставлена автором для публикации в FORS Magazine.

В этой статье изложен многолетний опыт оптимизации SQL-запросов в процессе работы с базами данных Oracle 9i, 10g и 11g. В качестве рабочего инструмента для получения планов запросов мною используется всем известные программные продукты Toadи PLSQL Developer.

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

• плохая статистика по таблицам и индексам запроса;
• проблемы с индексами в запросе;
• проблемы с хинтами в запросе;
• неэффективно построенный запрос;
• неправильно настроены параметры инициализации базы данных, отвечающие за производительность запросов.

Программные средства, позволяющие получить планы выполнения запросов, можно разделить на 2 группы:

• средства, позволяющие получить предполагаемый план выполнения запроса;
• средства, позволяющие получить реальный план выполнения запроса;

К средствам, позволяющим получить предполагаемый план выполнения запроса, относятся Toad, SQL Navigator, PL/SQL Developer и др. Это важный момент, поскольку надо учитывать, что реальный план выполнения может отличаться от того, что показывают эти программные средства. Они выдают ряд показателей ресурсоемкости запроса, среди которых основными являются:

• Cost – стоимость выполнения и
• Cardinality (или Rows) – кардинальность.

Чем больше значение этих показателей, тем менее эффективен запрос.
Ниже приводиться пример плана выполнения запроса:

       SELECT D.ISN FROM SUBDUTY SB, DICTI D, SUBHUMAN S 
                 WHERE SB.ISN=S.DUTYISN AND S.ISN=D.ISN  AND S.DEPTISN =C.GET('prolonggroup') 
                                  AND SB.RANK >70 AND ROWNUM <2 ORDER BY D.SHORTNAME;

полученного в Toad

Из плана видно, что наибольшие значения Cost и Cardinality содержатся во 2-й строке, в которой и надо искать основные проблемы производительности запроса.
Вместе с тем, многолетний опыт оптимизации показывает, что качественный анализ эффективности запроса требует, помимо Cost и Cardinality, рассмотрения других дополнительных показателей:

• CPU Cost — процессорная стоимость выполнения;
• IO Cost — стоимость ввода-вывода;
• Temp Space – показатель использования дискового пространства.

Если дисковое пространство используется (при нехватке оперативной памяти для выполнения запроса, как правило, для проведения сортировок, группировок и т.д.), то с большой вероятностью можно говорить о неэффективности запроса. Указанные дополнительные параметры с соответствующей настройкой можно увидеть в PL/SQL Developer и Toad при их соответствующей настройке. Для PL/SQL Developer в окне с планом выполнения надо выбрать изображение гаечного ключа, войти в окно Preferensec добавить дополнительные параметры в Select Column, после чего и нажать OK. В Toad в плане выполнения по правой кнопке мыши выбирается директиваDisplay Mode, а далее Graphic, после чего появляется дерево, в котором по каждому листу нажатием мышки можно увидеть дополнительные параметры: CPU Cost, IO Cost, Cardinality. Структура плана запроса, указанного выше, в виде дерева приведена ниже.

Предполагаемый план выполнения запроса с Cost и Cardinality можно также получить, выполнив после анализируемого запроса другой запрос, формирующий план выполнения:

       Текст выполняемого запроса;
       select * from table(dbms_xplan.display_cursor());  

Дополнительно в плане выполнения запроса выдается значение SQL_ID запроса, который можно использовать для получения реального плана выполнения запроса с набором как основных (Cost, Cardinality), так и дополнительных показателей через запрос:

       Select * from v$sql_plan where sql_id='SQL_ID'; 

Реальный план выполнения запроса и указанный выше перечень характеристик для анализа ресурсоемкого запроса дают динамические представления Oracle:V$SQL_PLAN и V$SQL_PLAN_MONITOR (последнее представление появилось в Oracle 11g).

План выполнения запроса получается из представления Oracle по запросу:

       Select * from v$sql_plan where sql_id='SQL_ID';

где SQL_ID – это уникальный идентификатор запроса, который может быть получен из разных источников, например, из представления V$SQL:

       Select sql_id from v$sql  
       where sql_fulltext like '%уникальный фрагмент текста запроса%';  

Трассировочный файл — это еще одно средство получение реального плана выполнения. Это довольно сильное средство диагностики и оптимизации запроса. Для получения трассировочного файла ( в Toad или PL/SQL Developer) запускается PL/SQL блок:

       ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER,LEVEL 12'
       TRACEFILE_IDENTIFIER='M_2013'
       TIMED_STATISTICS=TRUE  SQL_TRACE=TRUE;
       ---Исследуемый запрос, например,
       Select  * from  AGREEMENT   where ISN=138518816;
       ALTER SESSION SET SQL_TRACE=FALSE;

где первая, третья и последняя строки являются стандартными, а во второй строке пишется идентификатор (любые символы), который включается в имя трассировочного файла. Так, если в качестве идентификатора напишем M_2013, то имя трассировочного файла будет включать этот идентификатор и будет иметь вид: oraxxx_xxxxxx_ M_2013.trc. Результат пишется в соответствующую директорию сервера, которая находиться из запроса

       Select  value from v$parameter p where  p.name= 'user_dump_dest';

Трассировочный файл для удобства чтения расшифровывается утилитой Tkprof (при определенном навыке анализировать можно без расшифровки, в этом случае имеем более детальную информацию).

Ещё одним из средств получения реального плана выполнения запроса с получением рекомендаций по его оптимизации является средство Oracle SQLTUNE.

Для анализа запроса запускается PL/SQL блок (например, в Toad или PL/SQL Developer) , в котором имеются стандартные строки и анализируемый запрос. Для рассматриваемого выше запроса блок PL/SQL примет вид:

       DECLARE
       my_task_name varchar2(30);my_sqltext clob;rep_tuning   clob;
       BEGIN
       Begin DBMS_SQLTUNE.DROP_TUNING_TASK('my_sql_tuning_task');
        exception when others then NULL; end;
       MY_SQLTEXT:= '
       --текст запроса (без точки с запятой в конце запроса)
       ' ;
       MY_TASK_NAME:=DBMS_SQLTUNE.CREATE_TUNING_TASK(SQL_TEXT => my_sqltext,
       TIME_LIMIT => 60,               --задается время выполнения в секундах
       TASK_NAME =>'my_sql_tuning_task', DESCRIPTION=> my_task_name ,
       SCOPE      => DBMS_SQLTUNE.scope_comprehensive);
       begin
       DBMS_SQLTUNE.EXECUTE_TUNING_TASK('my_sql_tuning_task');
       exception when others then null;end;
       END;

Все строки (кроме текста запроса) являются стандартными.

Далее запуск запрос, который выдает на экран текст рекомендаций:

       SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task') FROM DUAL;

Для работы SQLTUNE необходимо как минимум из под SYSTEM выдать права на работу с SQLTUNE схеме, в которой запускается PL/SQL блок. Например, для выдачи прав на схему HIST выдается GRANT ADVISOR TO HIST;

В результате работы SQLTUNE выдает рекомендации (если Oracle посчитает, что есть что рекомендовать). Рекомендациями могут быть: собрать статистику, построить индекс, запустить команду создания нового эффективного плана и т.д.

Анализ плана выполнения запроса.

Анализ плана выполнения запроса имеет определенную последовательность действий. Рассмотрим на примере плана выполнения запроса из представление V$SQL_PLANдля ранее приведенного запроса

       SELECT D.ISN FROM SUBDUTY SB, DICTI D, SUBHUMAN S 
                     WHERE SB.ISN=S.DUTYISN AND S.ISN=D.ISN  AND S.DEPTISN =C.GET('prolonggroup') 
                                            AND SB.RANK >70 AND ROWNUM <2 ORDER BY D.SHORTNAME; 

1. При анализе план просматриваетcя снизу вверх. В процессе просмотра в первую очередь обращается внимание на строки с большими Cost, CPU Cost.
2. Как видно из плана, резкий скачек этих значений имеется в 4-ой строке. Причиной такого скачка является 5-я строка с INDEX FULL SCAN, указывающая на наличие полного сканирование индекса X_DICTI_NAME таблицы DICTI. С этих строк и надо начинать поиск причины ресурсоемкости запроса. После нахождения строки с большим Cost и CPU Cost продолжается просмотр плана снизу вверх до следующего большого CPU Cost и т.д. При этом, если CPU Cost в строке близок к CPU Cost первой строки (максимальное значение), то найденная строка является определяющей в ресурсоемкости запроса и с ней в первую очередь надо искать причину ресурсоемкости запроса.
3. Помимо поиска больших Cost и CPU Cost в строках плана следует просматривать первый столбец Operation плана на предмет наличия в нем HASH JOIN. Соединение по HASH JOIN приводит к соединению таблиц в памяти и, казалось бы, более эффективным, чем вложенные соединения NESTED LOOPS. Вместе с тем, HASH JOINэффективно при наличии таблиц, хотя бы одна из которых помещаются в память БД или при наличии соединения таблиц с низкоселективными индексами. Недостатком этого соединения является то, что при нехватке памяти для таблицы (таблиц) будут задействованы диски, которые существенно затормозят работу запроса.
   В связи с чем, при наличии высокоселективных индексов, целесообразно посмотреть, а не улучшит ли план выполнения хинт USE_NL, приводящий к соединению по вложенным циклам NESTED LOOPS. Если план будет лучше, то оставить этот хинт. При этом в хинте USE_NL в скобках обязательно должны перечисляться все алиасы таблиц, входящих во фразу FROM, в противном случае может возникнуть дефектный план соединения. Этот хинт может быть усилен хинтами ORDERED иINDEX. Следует обратить так же внимание на MERGE JOIN. При большом CPU Cost в строке с MERGE JOIN стоит проверить действие хинта USE_NL для улучшения эффективности запроса.
4. Особое внимание в плане следует так же уделить строкам в плане с операциями полного сканирования таблиц и индексов в столбец Operation: FULL — для таблиц и FULL SCAN, FAST FULL SCAN , SKIP SCAN — для индексов. Причинами полного сканирования могут быть проблемы с индексами: отсутствие индексов, неэффективность индексов, неправильное их применение. При небольшом количестве строк в таблице полное сканировании таблицы FULL может быть нормальным явлением и эффективнее использования индексов.
5. Наличие в столбце Operation операции MERGE JOIN CARTESIAN говорит, что между какими-то таблицами нет полной связки. Эта операция возникает при наличии во фразе From трех и более таблиц, когда отсутствуют связи между какой-то из пар таблиц.
   Решением проблемы может быть добавление недостающей связки, иногда помогает использование хинта Ordered.

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

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

• плохая статистика таблиц и индексов, участвующих в запросе (наиболее важный фактор, на который в первую очередь надо обратить внимание);
• проблемы с индексами: отсутствие нужных индексов, неэффективно построенные индексы, неэффективно используемые индексы, большое значение фактора кластеризации;
• проблемы с хинтами: отсутствие хинтов или они неэффективны;
• неэффективная структура запроса (запрос построен не корректно).

Неэффективная статистика.

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

1. Устаревшая статистика. Время последнего сбора статистики определяется значением поля Last_Analyzed для таблиц и индексов, которое находиться из Oracle таблицALL_TABLES (DBA_TABLES) и ALL_INDEXES (DBA_INDEXES) соответственно. Oracle ежедневно в определенные часы в рабочие дни и в определенные часы в выходные сам собирает статистику по таблицам. Но для этого DML операции с таблицей должны привести к изменению не менее 10% строк таблицы. Однако, мне приходилось сталкиваться с ситуацией, когда в течение дня таблица неоднократно и существенно меняет число строк или таблица столь большая, что 10% изменений наступает через длительное время. В этом случае приходилось обновлять статистику, используя процедуры сбора статистики внутри пакетов, а ряде случае использовать JOB, запускающийся в определенные часы для анализа и обновления статистики.
   Статистика по таблице и индексу (на примере таблицы AGREEMENT и индекса X_AGREEMENT в схеме HIST) обновляется соответственно процедурами:
   • для таблицы:

          execute DBMS_STATS.GATHER_TABLE_STATS 
                  ('HIST','AGREEMENT',NULL,10,NULL,'FOR ALL INDEXED COLUMNS SIZE AUTO',4);

   • для индекса:

          execute DBMS_STATS.GATHER_INDEX_STATS('HIST', 'X_AGREEMENT',null,10,null,null,4); 

   где число 10 в процедуре указывает на процент сбора статистики. С учетом времени сбора статистики и числа строк в таблице (индексе) были выработаны рекомендации для таблиц (индексов) по проценту сбора статистики: если число строк более 100 млн. процент сбора устанавливать 2 -5, при числе строк с 10 млн. до 100 млн. процент сбора устанавливать 5-10, менее 10 млн. процент сбора устанавливать 20 -100. При этом, чем выше процент сбора, тем лучше, однако, при этом растет и может быть существенным время сбора статистики.
2. Отсутствие статистики хотя бы в одной из таблиц, входящих во фразу From может являться определяющим фактором ресурсоемкости запроса. Это может произойти, например, в случае создания новой таблицы и использование ее до момента, когда Oracle в определенные часы сам соберет статистику по таким таблицам.
3. Плохая статистика таблиц и индексов. Как показала практика, плохой можно считать статистику, когда процент сбора статистики по таблице или индексу мене 0.1 %.
Для таблиц процент сбора статистики (на примере таблицы AGREEMENT в схеме HIST) вычисляется запросом:

   SELECT owner, table_name, 
     round(d.sample_size/decode(d.num_rows,0,100000000000,d.num_rows)*100,2) proch,d.last_analyzed 
                FROM ALL_TABLES  d  
                WHERE owner='HIST'  and table_name = 'AGREEMENT';

Процент сбора статистики по индексу находиться по запросу

   SELECT owner,table_name, index_name,
     round(sample_size*100/nvl(decode(num_rows,0,100000,num_rows),1000000),2) proch,last_analyzed
                FROM  ALL_IND_STATISTICS  D 
                Where owner='HIST' and  table_name = 'AGREEMENT';

Необходимо пересобрать статистику по таблице или индексу с плохой статистикой.
4. Одним из приемов , оправдавшим себя на практике, является блокировка сбора статистики. Используется при интенсивном изменение числа строк в таблице в течение дня (многочисленные удаления и вставки строк). Результаты ручного или ночного сбора статистики, осуществляемый Oracle, закрепляется путем блокировки дальнейшего сбора статистики.
   
   

   Блокировка статистики
   execute    dbms_stats.lock_table_stats('имя схемы','имя таблицы'); 
   Разблокировка
   execute    dbms_stats.unlock_table_stats('имя схемы','имя таблицы');
   Просмотр наличия блокировки статистики 
   SELECT OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED
   FROM ALL_tab_STATISTICS D   WHERE  OWNER='ИМЯ СХЕМЫ' AND TABLE_NAME='ИМЯ ТАБЛИЦЫ';

Замечание. При хорошем значении статистики по таблице может быть неблагополучная статистика по какому-то индексу таблицы, в силу чего целесообразно отслеживать статистику не только таблицы, но и индексов таблицы.

Проблемы с индексами

Проблемы с индексами в плане выполнения проявляются при наличии в столбце Options значений FULL, FULL SCAN, FAST FULL SCAN и SKIP SCAN в силу следующих причин:

1. Отсутствие нужного индекса. Требуемое действие — создать новый индекс;
2. Индекс имеется, но он неэффективно построен. Причинами неэффективности индекса могут быть:
   — Малая селективность столбца, на котором построен индекс, т.е. в столбце много одинаковых значений, мало уникальных значений. Решение в данной ситуации — убрать индекс из таблицы или столбец, на основе которого построен индекс, ввести в составной индекс.
   — Столбец селективный, но он входит в составной индекс, в котором этом столбец не является первым (ведущим) в индексе. Решение – сделать этот столбец ведущим или создать новый индекс, где столбец будет ведущим;
3. Построен эффективный индекс, но он работает не эффективно в силу следующих причин:
   — Индекс заблокирован от использования. Блокируют использование индекса следующие операции над столбцом, по которому используется индекс: SUBSTR, NVL, DECODE, TO_CHAR,TRUNC,TRIM, ||конкатенация, + цифра к цифровому полю и т.д.
   Решение – модифицировать запрос, освободиться от блокирующих операций или создать индекс по функции, блокирующей индекс.
   — Не собрана или неактуальная статистика по индексу. Решение – собрать статистику по индексу запуском процедуры, указанной выше.
   — Имеется хинт, блокирующий работу индекса, например NO_INDEX.
   — Неэффективно настроены параметры инициализации базы данных БД (особенно отвечающие за эффективную работу индексов, например,optimizer_index_caching и optimizer_index_cost_adj). По моему опыту использования Oracle 10g и 11g эффективность работы индексов повышалась, еслиoptimizer_index_caching=95 и optimizer_index_cost_adj=1.
4. Имеются сильные индексы, но они соперничают между собой.
   Это происходит тогда, когда в условии where имеется строка, в которой столбец одной таблицы равен столбцу другой таблицы. При этом на обоих столбцах построены сильные или уникальные индексы. Например, в условии Where имеется строка AND A.ISN=B.ISN. При этом оба столбца ISN разных таблиц имеют уникальные индексы. Однако, эффективно может работать индекс только одного столбца (левого или правого в равенстве). Индекс другого столбца, в лучшем случае, даст FAST FULL SCAN. В этой ситуации, чтобы эффективно заработали оба индекса, потребуется вести дополнительное условие для одного из столбцов.
5. Индекс имеет большой фактор кластеризации CLUSTERING_FACTOR.
   По каждому индексу Oracle вычисляет фактор кластеризации (ФК), определяющий число перемещений от одного блока к другому в таблице при выборе индексом строк из таблицы. Минимальное значение ФК равно числу блоков таблицы, максимальное — числу строк в таблице. CLUSTERING_FACTOR определяется по запросу:

       Select I.OWNER,T.TABLE_NAME, I.INDEX_NAME, T.BLOCKS, I.CLUSTERING_FACTOR, I.NUM_ROWS  
                from ALL_INDEXES I, ALL_TABLES T
                where I.table_name=T.table_name and I.owner=T.owner and I.owner='имя схемы' 
                                                and I.index_name='имя индекса'; 

Фактор кластеризации для индекса считает во время сбора статистики. Он используется оптимизатором при расчете стоимости индексного доступа к данным таблицы. Большой ФК (особенно близкий к числу строк в таблице) говорит о неэффективном индексе. Таким образом, ФК является характеристикой индекса, а не таблицы. Первое решение при большом ФК является убрать существующий индекс как не эффективный. Второе решение, если данный индекс наиболее часто применяется в запросах и он нужен, то перестроить структуру таблицы таким образом, чтобы строки в блоках таблицы были упорядочены в том же порядке, в котором расположена информация по данным строкам в индексе, т.е. сделать кластерными блоки таблицы, уменьшив таким образом число перемещений от одного блока к другому при работе индекса.
6. Индекс давно не перестраивался (индекс сильно фрагментирован за счёт многократных удалений в таблице). В этом случае может быть проведена либо перестройка Rebuild индексов (осуществляется по команде ALTER INDEX owner. имя индекса REBUILD ONLINE с освобождением пространства, в котором находиться индекс) , либо COALESCE — процедура уменьшения числа листовых блоков в индексе путем их объединения без освобождения пространства (эта процедура не блокирует таблицу в процессе выполнения и выполняется по команде ALTER INDEX owner. имя индекса COALESCE).

Проблемы с хинтами в запросе

Проблемы с хинтами могут быть следующие:

1. Неэффективный хинт. Он может привести к существенному снижению производительности. Причины возникновения не эффективности хинтов:
   — хинт был написан, когда БД работала на 9-ом Oracle, при переходе на Oracle 10g и выше хинт стал тормозом (это могут быть хинты Rule, Leading и др.). Leading –мощный хинт, но при переходе на другую версию Oracle в некоторых случаях приводит в резкому снижению производительности и перед применение этих хинтов необходимо учитывать вероятность изменения со временем статистики системы и ее объектов (таблиц и индексов), используемых в запросе;
   — в хинте USE_NL содержится не полный перечень алиасов;
   — в составном хинте используется неправильный порядок следования хинтов, в результате чего хинты блокирую эффективную работу друг. Например, хинтLeading полностью игнорируются при использовании двух или более конфликтующих подсказок Leading или при указании в нем более одной таблицы.
   — хинт написан давно, после чего была модификация запроса (например, отсутствует или изменился индекс, указанный в хинте).
2. В запросе отсутствует хинт, который бы повысил эффективность работы запроса. В ряде случаем наличие хинта повышает эффективность запроса и обеспечивает стабилизацию планов выполнения (например, при изменении статистики).
3. При создании хинта в запросе есть ряд рекомендаций:
   — В хинте INDEX могут быть перечислены несколько индексов. Оптимизатор сам выберет соответствующий индекс. Можно поставить хинт NO_INDEX, если надо заблокировать использование какого-то индекса.
   — При наличии Distinct в запросе Distinct ставиться после хинта (т.е. хинт всегда идет после Select).
   — Наиболее эффективные и часто используемыми являются хинты: Ordered, Leading, Index , No_Index, Index_FFS, Index_Join, Use_NL, Use_Hash, Use_Merge, First_Rows(n), Parallel, Use_Concat, And_Equal, Hash_Aj и другие. При этом, например, индекс Index_FFS кроме быстрого полного сканирования индекса позволяет ему выполняться параллельно, в силу чего можно получить существенный выигрыш в производительности. Пример такого использования для секционированной таблицы

   Select /*+ parallel(32) Index_FFS (T имя_индекса) */ count(*) 
                  From имя_таблицы Partition (имя_партиции) T; 

   где T-алиас таблицы.
   — Изменение параметров инициализации базы данных в пределах запроса позволяет сделать хинт /*+ opt_param('Параметр инициализацци' N) */ , например, /*+ opt_param('optimizer_index_caching' 10) */. Данный хинт используется для проверки производительности работы запроса в случае, когда запрос разрабатывается или тестируется на базе с одним значением параметров инициализации, а работает на базе с другими значениями.

Замечание. В некоторых случаях, когда хинт неэффективный, но заменить его оперативно в запросе не представляется возможным (например, чужая разработка), имеется возможность, не меняя рабочий запрос в программном модуле, заменить хинт (хинты) в запросе, а также в его подзапросах, на эффективный хинт (хинты). Это прием —подмена хинтов (который известен, как использование хранимых шаблонов Stored Outlines). Но такая подмена должна быть временным решением до момента корректировки запроса, поскольку постоянная подмена хинта может привести к некоторому снижению производительности запроса.

Неэффективно написанный запрос.

Причин неэффективности запроса несколько:

• неэффективное соединение таблиц;
• использование NOT и NOT IN в условии where;
• блокировка индекса в силу использования неправильных функций к столбцу, по которому построен индекс;
• большая вложенность запроса или большая его длина;
• большой объем выбираемых данных, требующих подключения в работу дисков, в том числе для выполнения агрегированных функций (order by, group by и т.д.);
неэффективные хранимые процедуры

1. Среди причин неэффективности особое внимание следует уделить неэффективному соединению таблиц (наличие HASH или MERGE соединений там, где предпочтительнее NESTED LOOP — о чем сказано выше). Кроме того эффективность соединения может зависеть от порядка таблиц во фразе FROM. Чтобы оптимизатор работал с таблицами в том порядке, в каком они находятся во фразе From используется хинт Ordered.
2. Эффективность соединения зависит от полноты связи во фразе WHERE между таблицами. При недостаточной связке в плане выполнения появляется MERGE JOIN CAPTESIAN (о чем было сказано выше).
3.  Особое внимание при модификации запроса следует уделить фразе NOT IN в условии where. Как вариант освобождения от NOT IN можно использовать прием, при котором пишется первый запрос без NOT IN, а за ним после MINUS пишется тот же запрос с IN (вычитание из полного числа строк строки, получаемые после использования условия IN, который работает быстрее, чем NOT IN).
4. В целях ускорения работы запроса использовать (там, где это можно) вместо UNION фразу UNION ALL (UNION операция более медленная, т.к. осуществляется путем сортировки).
5. Рекомендуется уменьшать число таблиц во фразе FROM. Это позволит сделать план выполнения прозрачным для оптимизатора и его анализа. В первую очередь убрать из FROM таблицы, столбцы которых не используются после фразы Select. В этом случае можно использовать подзапросы с этими таблицами после Select или во фразе where.
6. Задание диапазона дат, начиная с 01.01.0001, приводит к неэффективному плану выполнения. Надо сделать минимальную границу даты, т.е. как можно ближе к реальной дате.
7. В целях повышения производительности запроса не делать длинные запросы, т.к. длинный запрос увеличивает время разбора запроса оптимизатором, время передачи по каналам и занимает избыточную память.
8. В целях повышения производительности работы запроса шире использовать кэширование всех видов: последовательностей, таблиц, результатов выполнения запросов. Кэширование результатов выполнения запросов появилось в Oracle 11g и позволяет извлекать результат первого выполнения запроса из оперативной памяти. Это особенно эффективно при большом числе выполнения запроса и отсутствие в момент многократного выполнения запроса операций DML над таблицей.