Запрос пользователя к реляционной системе управления базой данных (СУБД) представляет собой выражение на декларативном языке запросов SQL [1-3]. Способ выполнения выбирается системой [4-6]. Процесс выбора называется оптимизацией выполнения запроса. Результатом процесса оптимизации является план выполнения запроса. Целью оптимизации является минимизация времени выполнения запроса. При этом в силу того факта, что о времени в вычислительной системе говорить бессмысленно, оно (время) оценивается затратами ресурсов компонент системы, называемыми стоимостью выполнения запроса.
В статье обсуждается вопрос сравнимости стоимостей выполнения разных запросов. Практическая интерпретация результатов производится для СУБД Oracle 9i.
Проблема сравнения стоимостей запросов возникает в случае попыток сравнить запросы, выполняющиеся на системах, имеющих различную физическую конфигурацию, либо в случаях сравнения разных запросов. Практические вопросы, подразумевающие выполнение таких действий, можно часто услышать от начинающих инженеров:
“Почему первый запрос выполняется дольше второго, хотя стоимость у него меньше?”;
Существующие описания проблемы
Общие советы, в категоричной форме не рекомендующие сравнивать стоимости запросов, можно найти в документации к СУБД Oracle 9i. Причины не поясняются.
Кроме того, довольно большое обсуждение проблемы есть на странице Тома Кайта в ветке
Consider Cost or Time - trying to compare the COST of two queries .
Достаточно полное описание особенностей работы стоимостного оптимизатора, позволяющее сделать выводы самостоятельно, можно найти в [7].
Стоимость выполнения запросов в СУБД Oracle 9i
Различные реализации СУБД могут учитывать затраты ресурсов различных компонент системы. В Oracle 9i рассматривают количество операций чтения блоков данных, количество тактов процессора и объемы дополнительной дисковой памяти. Далее эти показатели нормируются и приводятся к единицам измерения количества одноблочных чтений [7]. Формула вычисления стоимости Cost выглядит следующим образом:
Cost = (#SRds * sreadtim + #MRds * mreadtim + #CPUCycles / CPUSpeed )/ sreadtim, (1)
где #SRds – оценочное количество одноблочных дисковых чтений;
#MRds – оценочное количество многоблочных дисковых чтений;
#CPUCycles – асимптотически точные оценки количества операций, выполняемых процессором;
#sreadtim – среднее время одного одноблочного дискового чтения;
#mreadtim – среднее время одного многоблочного дискового чтения;
#CPUSpeed – количество операций, выполняемых процессором в единицу времени.
Обсуждение вопросов сбора и интерпретации системной статистики (#sreadtim, #mreadtim, #CPUSpeed) можно найти в [7].
Пример 1. Определение значения системной статистики
/* Тестовая система */
test@ORA9i> select pname, pval1
3 where sname= 'SYSSTATS_MAIN';
PNAME PVAL1
------------------------------ ----------
CPUSPEED 860
MAXTHR 174080
MBRC 8
MREADTIM ,461
SLAVETHR -1
SREADTIM 1,044
6 rows selected.
Сравнение стоимостей выполнения запросов
Проблему сравнения стоимостей выполнения запросов следует рассматривать для двух случаев:
- Сравнение стоимостей запросов, выполняемых на разных системах.
Как видим из формулы (1), итоговая стоимость получается путем умножения оценок на среднюю скорость выполнения операции в данной системе. Средняя скорость выполнения операций в разных системах будет разной. Поэтому запрос 1, имеющий стоимость Cost в системе 1 будет иметь другую стоимость в системе 2. Или переходя к векторному обозначению, система 1 и система 2 будут иметь разные базисы. Для сравнения векторов стоимости в этих системах их нужно привести к одному базису. При этом одного значения длины вектора Cost будет недостаточно – необходимо знать все компоненты вектора и все оценки времени выполнения операций в системе. Только в случае одинаковых значений (#sreadtim, #mreadtim, #CPUSpeed) можно сравнивать стоимости выполнения запросов.
С точки зрения корректности сравнения стоимостей разными системами можно считать не только разные физически системы, но и одну и ту же физическую систему в разные моменты времени. Это происходит вследствие того, что базисный вектор в разные моменты времени может иметь разные значения (#sreadtim, #mreadtim, #CPUSpeed) - например, в случае изменения настроек или обновления системной статистической информации.
Пример 2. Стоимость запроса в разных системах
/* Тестовая система */
test@ORA9i> execute dbms_random.seed(0);
PL/SQL procedure successfully completed.
test@ORA9i> create table t1
2 as select
3 rownum id,
4 trunc(100*dbms_random.normal) val
5 from all_objects, all_objects
6 where rownum < 1000*1000
7 ;
Table created.
… <- Сбор статистики
test@ORA9i> set autotrace traceonly explain
test@ORA9i> select count(*) from t1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=784 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=784 Card=999999)
/* Рабочая система */
work@ORA9i> select count(*) from t1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1062 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=1062 Card=999999)
/* Параметры рабочей системы */
work@ORA9i> set autotrace off;
work@ORA9i> select pname, pval1
2 from sys.aux_stats$
3 where sname= 'SYSSTATS_MAIN';
PNAME PVAL1
------------------------------ ----------
CPUSPEED 151
MAXTHR 85332992
MBRC 7
MREADTIM 3,188
SLAVETHR -1
SREADTIM 1,84
6 rows selected.
- Сравнение стоимостей разных запросов.
Рассмотрим вопрос сравнения стоимостей разных запросов, выполняемых в одной системе. Пусть имеется два запроса выборки данных из таблицы с разными предикатами. Обратим внимание на оценки селективности предикатов отношений (оценки количества строк, выбираемых условием where <поле>=<значение>). В запросах имеются два предиката p1 и p2 с оценками селективности n1 и n2. Оценки селективности могут отличаться от реального количества выбранных строк. Если в результате выполнения запроса оказалось, что было выбрано n1реал и n2реал строк, то затраты ресурсов и, как следствие, время выполнения будет отличаться от оценочного на величину f(n1- n1реал) и f(n2- n2реал). Нет причин считать, что значения f(n1- n1реал) и f(n2- n2реал) будут равны. Так что разные запросы, имея одинаковую оценочную стоимость в силу разной величины ошибки оценки селективности, могут иметь разное время выполнения.
Пример 3. Стоимость и время выполнения разных запросов в одной системе
/* Тестовая система */
test@ORA9i> execute dbms_stats.gather_table_stats('sys','t1',null,100, false,'FOR ALL COLUMNS SIZE 1');
PL/SQL procedure successfully completed.
test@ORA9i> set autotrace on
test@ORA9i> set timing on
test@ORA9i> select count(*) from
2 (select id, count(*) from t1 where val between 100 and 300 group by id);
COUNT(*)
----------
157393
Elapsed: 00:00:03.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3705 Card=1)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=3705 Card=219060)
3 2 SORT (GROUP BY) (Cost=3705 Card=219060 Bytes=1752480)
4 3 TABLE ACCESS (FULL) OF 'T1' (Cost=884 Card=219060 Bytes=1752480)
test@ORA9i> select count(*) from
2 (select id, count(*) from t1 where val between -100 and 100 group by id);
COUNT(*)
----------
687375
Elapsed: 00:00:10.05
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3705 Card=1)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=3705 Card=219060)
3 2 SORT (GROUP BY) (Cost=3705 Card=219060 Bytes=1752480)
4 3 TABLE ACCESS (FULL) OF 'T1' (Cost=884 Card=219060 Bytes=1752480)
Обратите внимание, что в примере 3 запросы, имея одинаковую стоимость, включают разные предикаты between 100 and 300 и between -100 and 100, поэтому эти запросы разные. Что и показывает отличие во времени выполнения в 3 раза.
Резюмируя можно сказать, что при сравнении стоимостей выполнения запросов необходимо учитывать особенности физической системы, на которой будет выполняться запрос, а также вероятностный характер оценки.
Литература:
- Кузнецов С.Д. Введение в стандарты языка баз данных SQL // http://www.citforum.ru/database/sqlbook/index.shtml
- В.В. Кириллов, Г.Ю.Громов Структуризированный язык запросов (SQL) // http://www.citforum.ru/database/sql_kg/index.shtml
- Кузнецов С.Д. Наиболее интересные новшества в стандарте SQL:2003 // http://www.citforum.ru/database/sql/sql2003/
- Кузнецов С.Д. Методы оптимизации выполнения запросов в реляционных СУБД // http://www.citforum.ru/database/articles/art_26.shtml
- Matthias Jarke, Jurden Koch (перевод Кузнецов С.Д.) Оптимизация запросов в системах баз данных // http://www.citforum.ru/database/articles/query_optimization/
- P. Griffiths Selinger, M.M.Astrahan, D.D.Chamberlin, R.A.Lorie, T.G.Price (перевод Кузнецов С.Д.) Выбор пути доступа в реляционной системе управления базами данных // http://www.citforum.ru/database/articles/path/
- Jonathan Lewis. Cost-Based Oracle Fundamentals. – Apress, 2006. – 506 S.
- Yannis Ioannidis (перевод Кузнецов С.Д.) История гистограмм // http://www.citforum.ru/database/articles/histograms/
