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

В этом выпуске мы постараемся сконцентрироваться на новшествах оптимизации в версии 10G.

Каковы же главные причины ведущие к плохой работе оптимизатора Oracle ?

На сегодняшний день разработчики Базы Oracle отмечают 3 главных фактора:

1. Плохая или недостаточная статистика на объекты базы.

2. Отсутствие нужных структур для оптимального execution plan , как то наличие нужных индексов и materialized views.
Для решения этой проблемы с версии 10G вводятся 2 новых автоматизированных советчика , как SQL Tuning Advisor and SQL Access Advisor. (Поле подробно данные возможности будут описаны в следующем выпуске)

3. Оптимизатор Oracle выбирает плохой или мене подходящий execution plan.
С этой проблемой сталкиваются многие пользователи с момента выбора работы с Cost Based Optimizer (CBO) .

По каким то необъяснимым соображениям Oracle выбирает совершенно неприемлемый execution plan и требуется немало здоровья и сил заставить Oracle выбрать подходящий план путём включения различных подсказок (Hints) в код . Конечно подключение подсказок является проблематичным , так как оно влечет за собой изменения в коде аппликации.

С версии 10G предлагается решать этот вопрос путём использования SQL Profiling. Данный метод очень напоминает подход решения Outlines известный с версии 9i. ( Более подробно о работе с SQL Profiling читайте в наших следующих выпусках).

В сегодняшней рассылке хотелось бы заострить внимание на первом факторе плохой работы оптимизатора  или " статистическая недостаточность".
Действительно работа с Cost Based Optimizer (CBO)  подразумевает наличие нужных статистик. Недостаток в статистике по теории приводит к неправильной работе оптимизатора.

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

Для этой цели каждую ночь запускается процедура DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC.
Этот job собирает отсутствующие статистики либо так называемые stale (по определению объект попадает в категорию stale если со времени последнего сбора статистики было произведено изменений на 10% или более от общего количества строк в таблице).
Этот job по правде говоря просто дублирует известный с предыдущей версии DBMS_STATS.GATHER_DATABASE_STATS при использовании опции GATHER AUTO.

Этот метод автоматичесгого сбора приводится в действие с момента создания базы без уведомления администратора.
Проверить этот факт можно путем простого запроса:

SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';

Конечно вопрос правильный ли этот подход остаётся открытым, хотя Oracle уже и  поставили нас перед фактом.
 

С одной стороны конечно оптимизатору требуются правильные статистики на объекты , но с другой стороны как при данном подходе сохранить стабильность выполнения SQL или стабильность execution plan.
Нет ничего более плохого , как нестабильная система с непредсказуемым временем SQL запросов.

Особенно этот подход может привести к плохим результатам в системах больших хранилищ данных DWH.

В любом случае Oracle оставил нам некую  степень свободы предоставив возможность отменить этот job путем запуска  процедуры :

BEGIN
  DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;

В итоге главное не забывайте , что с новой версии 10G Oracle решает за вас вопрос о сборе статистик и пока оставляет за вами право отказаться от этого решения.

Если у вас есть интересный материал , вопросы, замечания  или опыт которым вы хотели поделиться , тогда высылайте на адрес автора рассылки (eldar52@mail.ru)

В Интернете:

Интересный документ описывающий методы performance на системе Sun Solaris.
Очень полезен для администраторов при оптимизации.

Дежурный TIP :

В версии 10G опция autotrace включает в себя и пакет DBMS_XPLAN, что приводит к более подробному выводу информации при autotrace.

Пример ( По материалам Oracle Magazine)
SQL> set autotrace traceonly explain

SQL> select *
  2  from emp, dept
  3  where emp.deptno = dept.deptno
  4  and emp.job = 'CLERK';
 
Execution Plan
--------------------------------------------------------------------------------------
Plan hash value: 877088642
 
------------------------------------------------------------------------------------------------------------
| Id |   Operation              | Name  | Rows  | Bytes         | Cost (%CPU)           | Time     |
------------------------------------------------------------------------------------------------------------
|  0 |  SELECT STATEMENT        |       |     4 |   468         |     7     (15)        | 00:00:01 |
|* 1 |  HASH JOIN                         |       |     4 |   468         |     7     (15)        | 00:00:01 |
|* 2 |  TABLE ACCESS FULL       | EMP   |     4 |   348         |     3      (0)        | 00:00:01 |
|  3 |  TABLE ACCESS FULL       | DEPT  |     4 |   120         |     3      (0)        | 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
-----------------------------------------------------------------------------------------
   1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
   2 - filter("EMP"."JOB"='CLERK')
Note
--------
   - dynamic sampling used for this statement