Советы, рекомендации, опыт

Павел Слепушкин
АБД ОАО МТС
Поволжье,
ЮгоВосток

Подсматривание значений bind-переменных оптимизатором.

Статья предоставлена автором

Начиная с 9-ой версии оптимизатор Oracle при разборе предложения с bind-переменными “подсматривает” значения этих переменных и использует эти значения для построения плана. При последующих вызовах значения bind-переменных не учитываются, используется существующее (разобранное) предложение из library cache. Это поведение может привести к интересным эффектам.

Пример:
Создаем таблицу для тестирования и собираем статистику:
scott@9206> create table test_plan as select * from all_objects;
Table created.

scott@9206> alter table test_plan add (n1 number,n2 number);
Table altered.

scott@9206> update test_plan set n1=100,n2=100;
20204 rows updated.

scott@9206> update test_plan set n1=1, n2=1 where rownum<2;
1 row updated.

scott@9206> create index i_n1 on test_plan(n1);
Index created.

scott@9206> create index i_n2 on test_plan(n2);
Index created.

scott@9206> exec dbms_stats.gather_table_stats
         (ownname => 'SCOTT',
          tabname => 'TEST_PLAN',
          method_opt => 'FOR ALL INDEXED COLUMNS SIZE 10',
          cascade=>true);
PL/SQL procedure successfully completed.

scott@9206> exec dbms_stats.export_table_stats('scott','test_plan',null,'stattab');

Очевидно, что при использовании в where предложения условия n1=1 выгодно использовать индекс i_n1, а при использовании условия n2=1 выгодно пользоваться индексом i_n2. Убедиться в этом можно, посмотрев на планы запросов:

  
 select * from test_plan
       where n1=1 and n2=100;

---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |   101 |     2 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TEST_PLAN   |     1 |   101 |     2 |
|*  2 |   INDEX RANGE SCAN          | I_N1        |     1 |       |     1 |
---------------------------------------------------------------------------
  
select * from test_plan
    where n1=100 and n2=1;

---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |   101 |     2 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TEST_PLAN   |     1 |   101 |     2 |
|*  2 |   INDEX RANGE SCAN          | I_N2        |     1 |       |     1 |
---------------------------------------------------------------------------

scott@9206> set autot trace stat
scott@9206> variable bn1 number;
scott@9206> variable bn2 number;
scott@9206> exec :bn1:=1;

PL/SQL procedure successfully completed.

scott@9206> exec :bn2:=100;

PL/SQL procedure successfully completed.

scott@9206> select * from test_plan
  2     where n1=:bn1 and n2=:bn2;

no rows selected

Statistics
---------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        560  bytes sent via SQL*Net to client
        230  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

scott@9206> exec :bn1:=100;

PL/SQL procedure successfully completed.

scott@9206> exec :bn2:=1;

PL/SQL procedure successfully completed.

scott@9206> select * from test_plan
  2     where n1=:bn1 and n2=:bn2;

no rows selected

Statistics
---------------------------------------------------
          0  recursive calls
          0  db block gets
        323  consistent gets
          0  physical reads
          0  redo size
        560  bytes sent via SQL*Net to client
        230  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

Реальный план выполнения (из представления V$SQL_PLAN):

---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |   101 |     2 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TEST_PLAN   |     1 |   101 |     2 |
|*  2 |   INDEX RANGE SCAN          | I_N1        |     1 |       |     1 |
---------------------------------------------------------------------------

В результате второе выполнение запроса идет по неоптимальному плану, что можно увидеть из статистики по логическим чтениям (3 в первом случае и 323 во втором).

Если же первый разбор провести со значениями :bn1:=100 и :bn2:=1, то будет использоваться индекс I_N2, и неоптимальным уже будет выполнение при значениях :bn:=1 и :bn:=100.

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

Симптомом такой проблемы является различие планов: explain plan показывает один план выполнения, в V$SQL_PLAN – другой при одинаковых параметрах сессий.

Наиболее простое решение такой проблемы - заставить оптимизатор переразобрать запрос. Для этого нужно обновить статистику по одной из таблиц, участвующих в запросе. Лучше всего это сделать с помощью dbms_stats. Сначала нужно сохранить статистику процедурой dbms_stats.export_table_stats, а затем только что сохраненную статистику установить процедурой dbms_stats.import_table_stats. Это приведет к переразбору предложения при следующем вызове. Также, можно воспользоваться и outline-ами, создав несколько категорий и переставлять их, исходя из необходимости.