Logo Море(!) аналитической информации!
IT-консалтинг Software Engineering Программирование СУБД Безопасность Internet Сети Операционные системы Hardware
Конференция «Технологии управления данными 2018»
СУБД, платформы, инструменты, реальные проекты.
29 ноября 2018 г.
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:

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

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

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
Внимание! Любой из материалов, опубликованных на этом сервере, не может быть воспроизведен в какой бы то ни было форме и какими бы то ни было средствами без письменного разрешения владельцев авторских прав. Подробнее...