Logo Море(!) аналитической информации!
IT-консалтинг Software Engineering Программирование СУБД Безопасность Internet Сети Операционные системы Hardware
2006 г.

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

Борчук Леонид Евгеньевич, Череповец

Запрос пользователя к реляционной системе управления базой данных (СУБД) представляет собой выражение на декларативном языке запросов SQL [1-3]. Способ выполнения выбирается системой [4-6]. Процесс выбора называется оптимизацией выполнения запроса. Результатом процесса оптимизации является план выполнения запроса. Целью оптимизации является минимизация времени выполнения запроса. При этом в силу того факта, что о времени в вычислительной системе говорить бессмысленно, оно (время) оценивается затратами ресурсов компонент системы, называемыми стоимостью выполнения запроса.

В статье обсуждается вопрос сравнимости стоимостей выполнения разных запросов. Практическая интерпретация результатов производится для СУБД Oracle 9i.

Когда возникает проблема сравнения стоимостей

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

Стоимость выполнения запросов в СУБД 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
  2  from sys.aux_stats$
  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), итоговая стоимость получается путем умножения оценок на среднюю скорость выполнения операции в данной системе. Средняя скорость выполнения операций в разных системах будет разной. Поэтому запрос 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.

2. Сравнение стоимостей разных запросов.
Рассмотрим вопрос сравнения стоимостей разных запросов, выполняемых в одной системе. Пусть имеется два запроса выборки данных из таблицы с разными предикатами. Обратим внимание на оценки селективности предикатов отношений (оценки количества строк, выбираемых условием 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 раза.

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

Литература

  1. Кузнецов С.Д. Введение в стандарты языка баз данных SQL
  2. В.В. Кириллов, Г.Ю.Громов Структуризированный язык запросов (SQL)
  3. Кузнецов С.Д. Наиболее интересные новшества в стандарте SQL:2003
  4. Кузнецов С.Д. Методы оптимизации выполнения запросов в реляционных СУБД
  5. Matthias Jarke, Jurden Koch (перевод Кузнецов С.Д.) Оптимизация запросов в системах баз данных
  6. P. Griffiths Selinger, M.M.Astrahan, D.D.Chamberlin, R.A.Lorie, T.G.Price (перевод Кузнецов С.Д.) Выбор пути доступа в реляционной системе управления базами данных
  7. Jonathan Lewis. Cost-Based Oracle Fundamentals. - Apress, 2006. - 506 S.

Новости мира IT:

Архив новостей

Последние комментарии:

Loading

IT-консалтинг Software Engineering Программирование СУБД Безопасность Internet Сети Операционные системы Hardware

Информация для рекламодателей PR-акции, размещение рекламы — adv@citforum.ru,
тел. +7 985 1945361
Пресс-релизы — pr@citforum.ru
Обратная связь
Информация для авторов
Rambler's Top100 TopList liveinternet.ru: показано число просмотров за 24 часа, посетителей за 24 часа и за сегодня This Web server launched on February 24, 1997
Copyright © 1997-2000 CIT, © 2001-2015 CIT Forum
Внимание! Любой из материалов, опубликованных на этом сервере, не может быть воспроизведен в какой бы то ни было форме и какими бы то ни было средствами без письменного разрешения владельцев авторских прав. Подробнее...