Том Кайт
Том Кайт: о соединениях и планах выполнения запросов
(On Joins and Query Plans,
by Tom Kyte )
Источник: журнал Oracle Magazine, May/June 2006
(http://www.oracle.com/technology/oramag/oracle/06-may/o36asktom.html).
Наш эксперт оптимизирует соединения и объясняет, почему изменяются планаы выполнения запросов.
Вопрос. Я знаю некоторые правила применения соединений и то, что второе из следующих соединений лучше первого. Есть ли другие правила применения соединений, которые я должен знать, а также которые из них являются наиболее эффективными?
1) SELECT distinct D.deptno, D.dname
FROM dept D, emp E
WHERE E.deptno = D.deptno
ORDER BY D.deptno;
2) SELECT D.deptno, D.dname
FROM dept D
WHERE EXISTS
(
SELECT 1
FROM emp E
WHERE E.deptno = D.deptno
)
ORDER BY D.deptno;
Ответ. В общем, вы должны формулировать запросы так, чтобы они были бы наилучшими. Если бы один набор соединений был эффективным во всех случаях, сервер Oracle не рассматривал бы другие варианты!
В общем, вам нужно использовать соединение, когда в окончательном списке выборки нужны данные более чем из одной таблицы. Здесь же вам нужны данные только из одной таблицы DEPT, следовательно, соединение не нужно. Вместо этого нужно использовать предложения WHERE EXISTS или WHERE IN. (Оптимизатор по стоимости (CBO, cost-based optimizer) считает их более или менее эквивалентными – вы можете считать так же.)
Я написал два запроса:
select deptno, dname
from dept
where exists
( select NULL
from emp
where emp.deptno
= dept.deptno )
order by deptno;
и
select deptno, dname
from dept
where deptno in
( select deptno
from emp )
order by deptno;
В обоих случаях, оптимизатор, проверяя существование строк, будет применять либо полусоединение (semi join), которое вы не можете задать, а оптимизатор может (соединение останавливается после первого совпадения), либо индексное зондирование (index probe) таблицы EMP методом вложенных циклов (NESTED LOOPS).
Этот пример показывает, что может произойти. Когда оптимизатор обнаруживает, что таблица DEPT небольшая, а таблица EMP большая, он будет применять индексное зондирование большой таблицы EMP для каждой строки таблицы DEPT. С другой стороны, если он посчитает таблицу DEPT большой, то будет применять чистое полусоединение с массовой выборкой, без использования индексов. Для тестирования я использую копии таблиц EMP и DEPT:
SQL> create table emp as
2 select * from scott.emp;
Table created.
SQL> create table dept as
2 select * from scott.dept;
Table created.
SQL> create index emp_deptno_idx
2 on emp(deptno);
Index created.
Теперь я установлю первую "репрезентативную" статистику. Чтобы таблица EMP казалась большой, с 1 000 000 строк, а таблица DEPT маленькой, только с 100 строк, я использую процедуру SET_TABLE_STATS. Коме того, я сообщаю оптимизатору о статистике индекса по столбцу EMP(DEPTNO). Сервер Oracle Database 10g Release 2 по умолчанию собирает статистику по индексу во время создания индекса, поэтому я удалю существующую статистику по этому индексу и заменю ее на более "репрезентативную":
SQL> begin
2 dbms_stats.set_table_stats
3 ( user,
4 'EMP',
5 numrows => 1000000,
6 numblks => 100000 );
7 dbms_stats.delete_index_stats
8 ( user,
9 'EMP_DEPTNO_IDX' );
10 dbms_stats.set_index_stats
11 ( user,
12 'EMP_DEPTNO_IDX',
13 numrows => 1000000,
14 numdist => 10000,
15 numlblks =>10000 );
16 dbms_stats.set_column_stats
17 ( user,
18 'EMP',
19 'DEPTNO',
20 DISTCNT => 10000 );
21 dbms_stats.set_table_stats
22 ( user,
23 'DEPT',
24 numrows=> 100,
25 numblks => 100 );
26 end;
27 /
Теперь я готов посмотреть, что оптимизатор решит сделать. Чтобы проверить сгенерированные планы выполнения этих двух запросов, любой из которых – правильный способ формулирования запроса, я использую автотрассировку (AUTOTRACE). Я показываю два запроса для демонстрации того, что когда разработчики используют оптимизатор по стоимости, они не должны выбирать лучший способ формулирования запроса – оптимизатор распознает определенные конструкции и выбирает лучший возможный путь доступа. В этом его отличие от старого оптимизатора по синтаксису (RBO, rule-based optimizer), который обрабатывает предложения WHERE IN и WHERE EXISTS совершенно иначе.
Используя оптимизатор по стоимости с большой таблицей EMP и маленькой таблицей DEPT, сервер Oracle Database мог бы использовать такие планы, которые показаны на листинге 1. Оптимизатор решил прочитать каждую строку таблицы DEPT, а затем выполнить просмотр диапазона по индексу (INDEX RANGE SCAN) – индексное зондирование таблицы EMP, чтобы проверить, существует ли строка в другой таблице. Поскольку у меня относительно небольшой объем индексного зондирования (оптимизатор думает, что нужно приблизительно 100 проверок), а таблица EMP большая, то оптимизатор использует этот план как для запроса с предложением WHERE IN, так и для запроса с предложением WHERE EXISTS.
|
ЛИСТИНГ 1: план оптимизатора по стоимости с большой таблицей EMP и маленькой таблицей DEPT. |
SQL> set autotrace traceonly explain
SQL> select deptno, dname
2 from dept where deptno in
3 ( select deptno
4 from emp )
5 order by deptno;
Execution Plan
-----------------------------------------------------------
Plan hash value: 3383088615
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
-----------------------------------------------------------
| 0 |SELECT STATEMENT | | 100 | 3500 |
| 1 |SORT ORDER BY | | 100 | 3500 |
| 2 |NESTED LOOPS SEMI| | 100 | 3500 |
| 3 |TABLE ACCESS FULL| DEPT | 100 | 2200 |
|* 4 |INDEX RANGE SCAN | EMP_DEPTNO_IDX| 1000K | 12M |
-----------------------------------------------------------
Predicate Information (identified by operation id):
-----------------------------------------------------------
4 - access("DEPTNO"="DEPTNO")
SQL> select deptno, dname
2 from dept where exists
3 ( select null
4 from emp
5 where emp.deptno =
6 dept.deptno )
7 order by deptno;
Execution Plan
------------------------------------------------------------
Plan hash value: 3383088615
------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 3500 |
| 1 | SORT ORDER BY | | 100 | 3500 |
| 2 | NESTED LOOPS SEMI| | 100 | 3500 |
| 3 | TABLE ACCESS FULL| DEPT | 100 | 2200 |
|* 4 | INDEX RANGE SCAN | EMP_DEPTNO_IDX| 1000K | 12M |
------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------------------------
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO") |
Сравните это с планом оптимизатора по синтаксису, который показан на листинге 2. Используя этот оптимизатор, разработчик должен задаваться вопросом: "Каков размер таблиц EMP и DEPT?" В зависимости от ответа для получения "оптимального" плана он должен выбирать либо предложение WHERE IN, либо предложение WHERE EXISTS. Этот оптимизатор генерирует два разных плана исключительно на основании синтаксиса запроса, а не на основании фактического объема данных. Это – одно из главных преимуществ оптимизатора по стоимости по сравнению с оптимизатором по синтаксису: разработчики не должны знать, как написать лучший во всех случаях запрос – из этого не следует, что они не должны изучать язык SQL! Они только не должны выяснять такие мелочи, например, где нужно использовать предложение WHERE IN, а где – предложение WHERE EXISTS.
|
ЛИСТИНГ 2: план оптимизатора по синтаксису. |
SQL> select /*+ RULE */
2 deptno, dname
3 from dept where deptno in
4 ( select deptno
5 from emp )
6 order by deptno;
Execution Plan
-------------------------------------
Plan hash value: 1932208647
-------------------------------------
| Id | Operation | Name |
-------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MERGE JOIN | |
| 2 | SORT JOIN | |
| 3 | TABLE ACCESS FULL| DEPT |
|* 4 | SORT JOIN | |
| 5 | VIEW | VW_NSO_1 |
| 6 | SORT UNIQUE | |
| 7 | TABLE ACCESS FULL| EMP |
-------------------------------------
Predicate Information (identified by operation id):
-------------------------------------
4 - access("DEPTNO"="$nso_col_1")
filter("DEPTNO"="$nso_col_1")
Note
----------
- rule based optimizer used (consider using cbo)
SQL> select /*+ RULE */
2 deptno, dname
3 from dept where exists
4 ( select null
5 from emp
6 where emp.deptno =
7 dept.deptno )
8 order by deptno;
Execution Plan
----------------------------------------
Plan hash value: 4109416194
----------------------------------------
| Id | Operation | Name |
----------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT ORDER BY | |
|* 2 | FILTER | |
| 3 | TABLE ACCESS FULL| DEPT |
|* 4 | INDEX RANGE SCAN | EMP_DEPTNO_ID|
----------------------------------------
Predicate Information (identified by operation id):
----------------------------------------
2 - filter( EXISTS (SELECT 0 FROM "EMP" "EMP" WHERE
"EMP"."DEPTNO"=:B1))
4 - access("EMP"."DEPTNO"=:B1) |
Теперь, вернемся к оптимизатору по стоимости. Когда объем данных в таблице DEPT возрастет и оптимизатор решит, что понадобится гораздо большее количество операций индексного зондирования таблицы EMP, то план выполнения запроса изменится. Вместо вложенных циклов и просмотров диапазона по индексу будут использоваться более эффективные массовые операции. Чтобы увидеть это, я сообщаю оптимизатору, что объем данных в таблице DEPT существенно увеличился:
SQL> begin
2 dbms_stats.set_table_stats
3 ( user,
4 'DEPT',
5 numrows=> 100000,
6 numblks => 10000 );
7 end;
8 /
Затем я повторяю запросы и проверяю полученные планы, показанные на листинге 3. Как видите, теперь оптимизатор решил, в обоих случаях, выполнять полные просмотры (TABLE ACCESS FULL) и хеш-полусоединения (HASH JOIN SEMI), которые останавливаются после первого совпадения; он отказался от индексного зондирования, поскольку потребовалось бы использовать индекс 100 000 раз.
|
ЛИСТИНГ 3: планы выполнения запросов с более большой таблицей DEPT. |
SQL> set autotrace traceonly explain
SQL> select deptno, dname
2 from dept where deptno in
3 ( select deptno
4 from emp )
5 order by deptno;
Execution Plan
-----------------------------------------------------------------
Plan hash value: 3127359958
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K | 3417K |
| 1 | SORT ORDER BY | | 100K | 3417K |
|* 2 | HASH JOIN SEMI | | 100K | 3417K |
| 3 | TABLE ACCESS FULL | DEPT | 100K | 2148K |
| 4 | INDEX FAST FULL SCAN| EMP_DEPTNO_IDX | 1000K| 12M |
-----------------------------------------------------------------
Predicate Information (identified by operation id):
-----------------------------------------------------------------
2 - access("DEPTNO"="DEPTNO")
SQL> select deptno, dname
2 from dept where exists
3 ( select null
4 from emp
5 where emp.deptno =
6 dept.deptno )
7 order by deptno;
Execution Plan
---------------------------------------------------------------
Plan hash value: 3127359958
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K | 3417K |
| 1 | SORT ORDER BY | | 100K | 3417K |
|* 2 | HASH JOIN SEMI | | 100K | 3417K |
| 3 | TABLE ACCESS FULL | DEPT | 100K | 2148K |
| 4 | INDEX FAST FULL SCAN| EMP_DEPTNO_IDX| 1000K| 12M |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------------------
2 - access("EMP"."DEPTNO"="DEPT"."DEPTNO") |
Следует отметить еще одну важную вещь, оптимизатор по синтаксису не может принимать решение о выполнении полусоединения. Ему доступен только очень ограниченный набор путей доступа и операций соединения, поэтому он выбирает такие неэффективные операции, как сортировки, возвращающие уникальные значения (SORT UNIQUE), как это показано на листинге 2. Это еще одна причина необходимости перехода к использованию оптимизатора по стоимости!
Почему мой план изменяется?
Вопрос. Я заметил, что сбор статистики по таблицам, в которых не изменялись никакие данные, может приводить к изменению планов выполнения запросов к этим таблицам. Например, однажды я собрал статистику, выполнил некоторые запросы и сохранил их планы в отчете. На следующий день я вновь собрал статистику (никакие данные во всех этих таблицах не модифицировались) и обнаружил, что планы различаются. Чем это вызвано? Новые планы запросов стали лучшими, но я хотел бы понять причину этого.
Ответ. Причина этого изменения заключается, скорее всего, в значении по умолчанию параметра METHOD_OPT, используемого в пакете DBMS_STATS. В сервере Oracle Database 10g в параметре METHOD_OPT по умолчанию установлено значение "SIZE AUTO". Это приводит к тому, что после того как вы выполнили какой-то запрос сервер базы данных запоминает использованные предикаты и обновляет таблицу словаря данных SYS.COL_USAGE$. Затем, когда вы в следующий раз для сбора статистики вновь запустили пакет DBMS_STATS, он для определения, для каких столбцов нужно автоматически строить гистограммы (на основании прошлой рабочей нагрузки выполнения запроса), выполнил запрос к этой таблице. Пакет посмотрел на ваши предикаты и сказал: "Хм, эти столбцы –кандидаты на гистограммы".
Это легко проверить на относительно маленьком примере. Я начинаю с создания небольшой таблицы, в которой в столбце ID содержатся данные со скошенным распределением, и собираю по ней статистику, используя режим сбора, заданный по умолчанию. Данные в столбце ID такие, что для значений от 0 до 4 будут выбираться около 20 процентов строк таблицы, а для значения 99 – только одна строка. Я использую этот гиперболизированный пример только для того, чтобы показать изменение планов выполнения запросов:
SQL> create table t
2 as
3 select mod(rownum,5) id, a.*
4 from all_objects a;
Table created.
SQL> update t
2 set id = 99
3 where rownum = 1;
1 row updated.
SQL> create index t_idx on t(id);
Index created.
SQL> begin
2 dbms_stats.gather_table_stats
3 ( user, 'T' );
4 end;
5 /
SQL> select column_name, count(*)
2 from user_tab_histograms
3 where table_name = 'T'
4 and column_name = 'ID'
5 group by column_name;
COLUMN_NAME COUNT(*)
----------- ------------
ID 2
Итак, в данный момент у столба ID нет детальной гистограммы – по умолчанию создаются только два бакета, как это показано запросом к представлению USER_TAB_HISTOGRAMS. Эти две записи в представлении сообщают оптимизатору только самые большое и маленькое значения, а для распознавания скошенного характера данных нужно большее количество бакетов.
Оптимизатор знает самое большое значение (99), самое маленькое (0), число несовпадающих значений (6 в данном случае) и общее число строк в моей таблице T (когда я тестировал, их было 50 119). Учитывая эти факты, оптимизатор будет полагать, что по предикату WHERE ID = <значение> будет возвращено 50 119/6 = 8 353 строк. Действительно, когда я выполняю запросы с предикатами ID=1 или ID=99, я наблюдаю результаты, показанные на листинге 4.
|
ЛИСТИНГ 4: планы выполнения запросов с предикатами ID=1 и ID=99. |
SQL> set autotrace traceonly explain
SQL> select *
2 from t
3 where id = 1;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes| Cost (%CPU) | Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8353 | 783K| 163 (2) | 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 8353 | 783K| 163 (2) | 00:00:02 |
----------------------------------------------------------------------------
SQL> select *
2 from t
3 where id = 99;
Execution Plan
----------------------------------------------------------------------------
Plan hash value: 1601196873
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes| Cost (%CPU) | Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8353 | 783K| 163 (2) | 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 8353 | 783K| 163 (2) | 00:00:02 |
---------------------------------------------------------------------------- |
Теперь, я сразу же собираю статистику, используя показанные выше операторы:
SQL> begin
2 dbms_stats.gather_table_stats
3 ( user, 'T' );
4 end;
5 /
SQL> select column_name, count(*)
2 from user_tab_histograms
3 where table_name = 'T'
4 and column_name = 'ID'
5 group by column_name;
COLUMN_NAME COUNT(*)
------------ -----------
ID 5
Обратите внимание, сейчас в моей гистограмме больше двух бакетов. Здесь пакет DBMS_STATS, использующий параметр METHOD_OPT со значением "SIZE AUTO", собрал больше информации. Если бы сейчас я выполнил запрос к таблице SYS.COL_USAGE$, я обнаружил бы, что была добавлена новая строка, указывающая, что в моей системе были выполнены запросы к этому конкретному столбцу, в которых использовались предикаты равенства. Это "волшебство", которое заставило пакет DBMS_STATS изменить способ сбора статистик и привело к кардинальному изменению планов выполнения моих запросов, показанных на листинге 5.
|
ЛИСТИНГ 5: новые планы выполнения запросов с предикатами ID=1 и ID=99. |
SQL> select *
2 from t
3 where id = 1;
Execution Plan
----------------------------------------------------------------------------
Plan hash value: 1601196873
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes| Cost (%CPU) | Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10260| 961K| 164 (2) | 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 10260| 961K| 164 (2) | 00:00:02 |
----------------------------------------------------------------------------
SQL> select *
2 from t
3 where id = 99;
Execution Plan
----------------------------------------------------------------------------------------
Plan hash value: 470836197
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes| Cost (%CPU) | Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 96 | 2 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 96 | 2 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX| 1 | | 1 (0) | 00:00:01 |
---------------------------------------------------------------------------------------- |
Обратите внимание, как сильно изменилась кардинальность таблицы – число 8 353 теперь изменилось на 10 260 и на 1. Это изменение привело к изменению полной стоимости выполнения первого запроса и к появлению совершенно другого плана выполнения второго запроса.
Это – существенный факт, который должны знать администраторы баз данных. Запросы выполняемые конечными пользователями могут со временем изменяться, поэтому также может изменяться и автоматически собираемая статистика. Если вы не знаете про эту функциональную возможность, включенную в сервере Oracle Database 10g по умолчанию, она может выглядеть как необъяснимое волшебство, которое случается в вашем сервере базы данных.
Ведущий данной колонки Том Кайт (Tom Kyte, thomas.kyte@oracle.com) работает в корпорации Oracle с 1993 года. Кайт – вице-президент Oracle, возглавляющий группу Oracle Public Sector; он автор таких книг, как "Expert Oracle: 9i and 10g Programming Techniques and Solutions" (Apress, 2005), "Effective Oracle by Design" (Oracle Press, 2003), а также ряда других. |
