Профессионалу администратору

Том Кайт

Том Кайт: О явных и неявных курсорах,
размерах экстентов и сложных интервалах
(On the Explicit, Size, and Complex, By Tom Kyte)

Источник: журнал Oracle Magazine, January-February 2003,
(http://otn.oracle.com/oramag/oracle/03-jan/o13asktom.html).

Наш эксперт отвечает на вопросы о курсорах, экстентах и интервалах.

Вопрос: Действительно ли, что начиная с СУБД Oracle7 Release 7.3, неявные курсоры оптимизируются и не выполняют двойной выборки? Кроме того, почему следующий неявный курсор работает быстрее показанного ниже явного курсора, если у таблицы T имеется индекс по столбцу X, тогда как в противном случае явный курсор работает быстрее?

Ответ: Неявный курсор:

Select x 
  into y 
  from T 
 where x = j; 
Явный курсор:
cursor c(p number) is 
select x from blah where x = p; 
open c(j); 
fetch c into y; 
close c;

Я начну с короткого определения, чтобы все понимали, что из себя представляют неявные и явные курсоры.

В общем, неявный курсор – это курсор, который программист "явно" не объявляет, не открывает, не выбирает из него и не закрывает его; эти операции являются неявными. Итак, в примере, приведенном выше, запрос SELECT X INTO Y – неявный курсор. Для него не существует определения "cursor имя_курсора is ...". Во втором примере, с другой стороны, показан классический явный курсор. Программист явно объявляет его, открывает, выбирает из него и закрывает его.

Итак, действительно, неявные курсоры в PL/SQL быстрее явных курсоров, и стали они быстрее перед выпуском СУБД Oracle7 Release 7.3. В действительности, у меня есть набор тестов, который показывает, что это действительно так и в СУБД Oracle7 Release 7.1 (эти тесты см. в asktom.oracle.com/~tkyte/ivse.html). Причина, по которой неявные курсоры быстрее (как неявные курсоры курсорных циклов FOR, так и неявные курсоры в операторах SELECT INTO), заключается в том, что в этом случае машине PL/SQL требуется интерпретировать и выполнять намного меньше вашего программного кода. В общем, чем больше кода PL/SQL можно "скрыть", тем быстрее он будет работать. Для неявного курсора, показанного выше, требуется одна строка кода PL/SQL; для явного курсора требуется по крайней мере три строки кода, а если делать "правильно", фактически потребуется шесть строк кода. Ваш явный код не выполняет всей работы неявного курсора, который проверяет, что вы гарантированно получите одну и только одну строку. В вашем явном коде отсутствует многое из того, что вы должны сделать. Для аккуратного сравнения двух ваших примеров курсоров ваш явный код должен иметь больше строк:

open c(j); 
fetch c into y; 
if ( c%notfound ) then raise NO_DATA_FOUND; 
end if; 
fetch c into y; 
if ( c%found ) then raise TOO_MANY_ROWS; 
end if; 
close c;

Если бы это был ваш явный курсор, вы обнаружили бы, что явный курсор медленнее во всех случаях, даже в вашем примере с индексом или без индекса.

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

SQL> create table t ( x int )
  2  pctfree 99 pctused 1;
Table created.

SQL> insert into t
  2  select rownum
  3    from all_objects;
29264 rows created.

SQL> analyze table t compute statistics;
Table analyzed.

SQL> select blocks, empty_blocks, num_rows
  2    from user_tables
  3   where table_name = 'T';

    BLOCKS     EMPTY_BLOCKS     NUM_ROWS
-------------  ------------   -----------
        4212           140         29264

Я создал таблицу с большим количеством блоков; это делается с помощью установки параметра pctfree 99, которая резервирует 99 процентов пространства блоков для последующих обновлений блоков ("свободное пространство"). Итак, даже при том, что количество данных в таблице небольшое, сама таблица является довольно большой. Кроме того, я вставил в таблицу значения 1, 2, 3... до 29 264 в значительной степени разместившиеся по порядку. Так, значениеX=1 находится в "первом" блоке таблицы, а значение X=29,000 – достаточно близко к последнему блоку таблицы.

Затем я буду выполнять небольшой блок PL/SQL, показывающий количество операций согласованного чтения данных (consistent gets), выполняемых различными неявными и явными курсорами. В запросах будет выполняться полный просмотр всей таблицы, поскольку индексы отсутствуют. Различия в производительности определить легко, выполнив этот блок и проверив результаты:

SQL> declare
  2     l_last_cgets number default 0;
  3     l_x      number;
  4     cursor c( p_x in number ) is
  5     select x
  6     from t
  7          where x = p_x;
  8
  9  procedure cgets( p_msg in varchar2 )
 10  is
 11    l_value number;
 12  begin
 13    select b.value into l_value
 14      from v$statname a, v$mystat b
 15     where a.statistic# = b.statistic#
 16       and a.name = 'consistent gets';
 17
 18    dbms_output.put_line( p_msg );
 19    dbms_output.put_line
 20    (  'Инкрементальные согласованные чтения: ' ||
 21      to_char(l_value-l_last_cgets,
 22                       '999,999') );
 23    l_last_cgets := l_value;
 24  end;
 25
 26  begin
 27    cgets('Начало');
 28
 29    open c(1);
 30    fetch c into l_x;
 31    close c;
 32    cgets('Явный курсор для поиска X=1 ' ||
 33              'с остановкой после первого совпадения' );
 34
 35    open c(1);
 36    fetch c into l_x;
 37    fetch c into l_x;
 38    close c;
 39    cgets('Явный курсор для поиска X=1 ' ||
 40              'с проверкой на наличие дубликатов' );
 41
 42    select x into l_x
 43      from t
 44     where x = 1 AND rownum = 1;
 45    cgets('Неявный курсор для поиска X=1 ' ||
 46              'с остановкой после первого совпадения' );
 47
 48    select x into l_x
 49      from t
 50     where x = 1;
 51    cgets('Неявный курсор для поиска X=1 ' ||
 52              'с проверкой на наличие дубликатов' );
 53
 54    open c(29000);
 55    fetch c into l_x;
 56    close c;
 57    cgets('Явный курсор для поиска X=29000');
 58
 59    select x into l_x
 60            from t
 61           where x = 29000;
 62    cgets('Неявный курсор для поиска X=29000');
 63  end;
 64  /
Начало
Инкрементальные согласованные чтения:  514,690
Явный курсор для поиска X=1 с остановкой после первого совпадения
Инкрементальные согласованные чтения:        4
Явный курсор для поиска X=1 с проверкой на наличие дубликатов
Инкрементальные согласованные чтения:    4,220
Неявный курсор для поиска X=1 с остановкой после первого совпадения
Инкрементальные согласованные чтения:        4
Неявный курсор для поиска X=1 с проверкой на наличие дубликатов
Инкрементальные согласованные чтения:    4,219
Явный курсор для поиска X=29000
Инкрементальные согласованные чтения:    4,101
Неявный курсор для поиска X=29000
Инкрементальные согласованные чтения:    4,219

PL/SQL procedure successfully completed.

Здесь вы можете видеть, почему в вашем примере явный курсор казался быстрее неявного курсора. Когда я тестирую явный курсор и он делает только одну выборку для X=1, то запрос для получения ответа должен просмотреть очень мало блоков (очень мало согласованных чтений). Однако, как только я заставляю явный курсор делать всю работу неявного курсора, проверяя, что никакая другая строка не удовлетворяет тем же самым критериям, вы видите, что явный курсор просматривает каждый блок в таблице. Теперь, я перехожу к тестированию неявного курсора и вижу, что, если он также останавливается при самом первом совпадении (в операторе используется предикат ROWNUM=1), то он выполняет тот же самый объем работы, что и явный курсор. Когда неявный курсор проверяет наличие в таблице второй строки, соответствующей критериям, вы видите, что он снова выполняет то же самое число согласованных чтений, что и явный курсор; он также должен выполнить полный просмотр таблицы для проверки наличия только одной строки с X=1.

Интересно, когда я выполняю запрос с X=29,000, то независимо от того какой подход я изберу, два запроса выполняют тот же самый объем работы, так как искомая строка размещается близко от "конца" таблицы. Оба запроса должны просмотреть почти всю таблицу, чтобы найти первую строку.

Теперь, если бы был индекс по столбцу X, оба запроса имели бы тенденцию использовать выборку диапазона ROWID из индекса, и оба запроса быстро обнаружат, что есть всего лишь одна строка, соответствующая критериям, без необходимости выполнения полного просмотра таблицы.

Это объясняет поведение ваших курсоров: оператор SELECT INTO проверяет наличие второй строки, тогда как ваш явный курсор этого не делал. Если Вы сравниваете яблоки с яблоками – или делаете вторую явную выборку, или добавляете предикат "rownum = 1" к оператору SELECT INTO – вы обнаружите, что оба курсора выполняют тот же самый объем работы.

Короче говоря, неявные курсоры представляют большую ценность. Они выполняются быстрее чем тот же самый программный код с явным курсором, они проще для кодирования (меньше кода для ввода), и я лично нахожу код неявных курсоров более удобочитаемым и понятным. <1p class=bodycopy>Экстенты маленького, среднего и большого размера

Вопрос: Для нашего нового приложения мы спроектировали базу данных и создали модель данных. Мы даже установили размеры таблиц и определили параметры хранения для каждой таблицы. Но теперь наши специалисты по администрированию базы данных говорит нам, что они предоставят нам три табличных пространства: TS_small(табличное пространством небольшого размера) с одинаковым размером экстентов, равным 160 КБ, TS_med (табличное пространство среднего размера)с одинаковым размером экстентов, равным 5 МБ, и TS_large (табличное пространство большого размера)с одинаковым размером экстентов, равным 160 МБ. Они говорят нам, что нужно создавать таблицы, размер которых будет менее 5 МБ, в табличном пространстве TS_small, таблицы, размер которых будет менее 160 МБ, в TS_med и таблицы, размер которых будет больше 160 МБ, в TS_large. Кроме того, они не хотят, чтобы мы использовали какие-либо параметры хранения на уровне таблиц. Они говорят, что то же самое должно быть и для индексов. Это не выглядит для меня разумным, потому что таблицу с оцененным размером 120 МБ мы должны размещать в табличном пространстве TS_med, а для этого потребуется 24 экстента! Администраторы базы данных утверждают, что многочисленные тесты доказали, что такое проектирование обеспечивает наибольшую производительность и предотвращает фрагментацию. Мой вопрос: действительно ли они правы? Я беспокоюсь об объектах с таким большим количеством экстентов.

Ответ: Хорошо, выглядит, как будто они читали веб-сайт "Спросите Тома" (asktom.oracle.com) и материалы дискуссионных групп по интересам в Интернете и нашли хорошую рекомендацию. Глядя на их числа, я вижу, что таблица размером до 5ГБ будет иметь 32 или меньше экстентов. Учитывая, что сотни (или даже больше) экстентов не будут влиять на производительность выполнения операторов языка манипулирования данными (DML), я сказал бы, что они сделали превосходную работу.

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

То, что фрагментация невозможна, проверить просто. Табличное пространство, управляемое с помощью словаря данных (dictionary-managed tablespace), фрагментируется из-за экстентов различного размера. Табличное пространство, управляемое с помощью словаря данных, могло бы иметь тысячи экстентов, свободных и используемых, каждый из которых мог бы иметь разный размер. Теперь, вы начинаете уничтожать и создавать объекты в этом табличном пространстве и со временем у вас образуется в нем большое количество "дырок" различного размера (свободное пространство). Так, вы могли проверить табличное пространство, управляемое с помощью словаря данных, и определить размер свободного пространства в нем, обнаружив 500 свободных МБ. Но когда вы пытаетесь создать таблицу с размером начального экстента, равным 40 МБ степень, то получаете ошибку – невозможно выделить первый экстент. Как это могло случиться? Вы имеете 500 свободных МБ, правильно? Да, хорошо, но к сожалению эти 500 МБ находятся во множестве экстентов, каждый из которых имеет размер меньший чем 40 МБ! Итак, вы имеете большое непригодное для использования свободное пространство – ваше табличное пространство фрагментировано. Теперь, рассмотрите локально управляемое табличное пространство (locally managed tablespace) с экстентами одинакового размера. Здесь каждый экстент имеет один и тот же размер, что и все другие экстенты, без исключения. Если вы находите, что имеете 500 свободных МБ, я могу уверить вас, что вы будете способны выделить новый экстент в этом табличном пространстве, так как каждый свободный экстент по определению может использоваться для вашего объекта.

Что касается оптимальной производительности, вы должны понимать, что наличие десятков, сотен или даже больше экстентов не будет влиять на производительность выполнения операторов. На ваши операции DML (включая запросы) не будет неблагоприятно воздействовать наличие большого количества экстентов. Вместо того чтобы показывать это здесь, я отсылаю вас к телеконференциям сети Usenet, доступным по двум адресам: asktom.oracle.com/~tkyte/extents.html и asktom.oracle.com/~tkyte/extents2.html, в каждом из которых содержится достаточно продолжительное обсуждение по этой теме. Тридцать два экстента для ваших объектов – это превосходно, вообще не будет никакого влияния на производительность. Фактически, так как локально управляемые табличные пространства намного более эффективны при распределении пространства, чем табличные пространства, управляемые с помощью словаря данных, их использование повысит производительность, а не наоборот.

Вместо беспокойства о 32 экстентах и т.д., вас должно радовать, что вам уже не нужно заботиться об установке "лучших" значений в параметрах INITIAL, NEXT, PCTINCREASE, MINEXTENTS и MAXEXTENTS <1p class=bodycopy>Установка сложного интервала

Вопрос: Я использую пакет DBMS_JOB и хочу спланировать запуск заданий каждые 15 минут с понедельника по пятницу, начиная с 6:00 утра и до 6:00 вечера. Как это можно сделать? Я не могу вычислить интервал, передаваемый в пакет.

Ответ: Хорошо, для того чтобы вычислять сложные интервалы для пакета DBMS_JOB, я люблю использовать новый (начиная с Oracle8i Release 2) оператор CASE. Например, следующий оператор CASE возвращает правильный интервал для вашей спецификации:

SQL> alter session set nls_date_format =
  2  'dy mon dd, yyyy hh24:mi';
Session altered.

SQL> select
  2  sysdate,
  3  case
  4    when (to_char( sysdate, 'hh24' )
  5                   between 6 and 17
  6     and to_char(sysdate,'dy') NOT IN
  7                   ('sat','sun') )
  8    then trunc(sysdate)+
  9      (trunc(to_char(sysdate,'sssss')/
 10                     900)+1)*15/24/60
 11    when (to_char( sysdate, 'dy' )
 12          not in ('fri','sat','sun') )
 13    then trunc(sysdate)+1+6/24
 14    else next_day( trunc(sysdate),
 15                   'Mon' )+6/24
 16  end interval_date
 17   from dual
 18  /

SYSDATE
------------------------------
INTERVAL_DATE
------------------------------
sun sep 15, 2002 16:35
mon sep 16, 2002 06:00

Оператор CASE обеспечивает большую гибкость при генерации сложного значения, которое требуется вам. К сожалению, пакет DBMS_JOB позволяет вам использовать только такие интервалы, длина которых не превышает 200 символов, и даже если вы "сожмете" показанный выше оператор, вы обнаружите, что его длина приблизительно равна как минимум 300 символам. Итак, вы не можете использовать его непосредственно в вызове пакета DBMS_JOB. Мое решение заключается в следующем: или я создал бы представление NEXT_DATE, чтобы оператор select * from next_date возвращал время следующего запуска задания, или я реализовал бы вышеупомянутый оператор в функции PL/SQL, которая возвращает дату. Если я использовал представление, мой вызов пакета DBMS_JOB мог бы выглядеть примерно так:

begin
 dbms_job.submit
  ( :n, 'proc;', sysdate,
    '(select * from next_date)'
  );
end;

Или, если я использовал подход с функцией PL/SQL и создал бы функцию NEXT_DATE, это могло бы так:

begin
  dbms_job.submit
  ( :n, 'proc;', sysdate,
    'next_date()'
  );
end;
/

По существу, мы хотим добавлять данные текущего бюджетного года и блокировать данные предшествующих бюджетных лет, чтобы они не могли быть изменены. Мой текущий подход: размещать исторические данные в отдельном табличном пространстве (отдельно от текущих данных). Имеет ли смысл этот подход? Я использую Oracle9i Release 9.0.1 под управлением ОС Microsoft Windows 2000.

Ответ: Действительно, этого достаточно просто добиться. Табличное пространство может находиться в режиме "только для чтения" или в режиме "чтение-запись". Если вы используете отдельное табличное пространство для каждой секции (или, по крайней мере, храните исторические секции в отдельном табличном пространстве, отличном от текущего), вы, чтобы сделать его доступным только в режиме "только для чтения", можете просто выполнить оператор ALTER TABLESPACE <имя табличного пространства> READ ONLY. Конечные пользователи не смогут модифицировать это табличное пространство и, фактически, вы сможете экономить значительное время, затрачиваемое на его резервирование, поскольку вы должны будете создавать его резервную копию только один раз (если только вы не переводите его в режим "чтение-запись" и модифицируете его, в таком случае вы, естественно, должны снова создавать его резервную копию).

Фактически, вы можете даже переместить это табличное пространство на носители только для чтения, такие, как компакт-диски, предотвращая этим возможность его модификации.

Если бы я использовал Oracle9i Database Release 2, я пошел бы на еще один шаг дальше. Перед переводом этой исторической секции в режим "только для чтения", я сжал бы ее, используя новое средства сжатия таблиц – COMPRESS. Я смог бы сэкономить значительный объем дискового пространства, занимаемого этими данными. Для этого нужно "перемещать" существующую секцию с опцией COMPRESS. (Прим. пер. О сжатии таблиц см., например, статью Микела Посса "Сжатие таблиц в СУБД Oracle9i Release 2: анализ эффективности", опубликованную в OM/RE.) Коэффициенты сжатия, равные 3:1, 5:1 и даже 12:1, во многих случаях не кажутся чрезмерными; это зависит от природы данных. <1p class=bodycopy>Что означают выходные данные автоматической трассировки (Autotrace)?

Вопрос: Объясните, пожалуйста, что такое recursive calls, db block gets и т.д.

Statistics 
---------------------------------------------
     0 recursive calls 
202743 db block gets 
 84707 consistent gets  
     0 physical reads 
     0 redo size 
  2010 bytes sent via SQL*Net to client 
   430 bytes received via SQL*Net from ...
     2 SQL*Net roundtrips to/from client 
     0 sorts (memory) 
     0 sorts (disk) 
     8 rows processed

Ответ: Хорошо, это – несомненно часто задаваемый вопрос, так что рассмотрим подробно каждый статистический показатель. Я буду использовать определения, предлагаемые в руководстве Oracle9i Database Performance Tuning Guide and Reference (Прим. пер. Аналогичная информация содержится в книге Oracle9i. Справочное руководство по серверу, переведенной компанией РДТЕХ, www.rdtex.ru.), в котором описан каждый статистический показатель, и буду их комментировать, когда мне покажется, что какое-то определение не совсем понятно:

• Recursive Calls (рекурсивные вызовы). Число рекурсивных вызовов, сгенерированных как на пользовательском, так и на системном уровнях.

• DB Block Gets (чтения блоков базы данных). Количество блоков, полученных в режиме CURRENT.

Следующие шаги ЧИТАЙТЕ документацию Oracle9i: Oracle9i Database Performance Tuning Guide and Reference otn.oracle.com/docs/products/oracle9i/ doc_library/release2/server.920/a96533/autotrac.htm#14931 СПРАШИВАЙТЕ ТОМА asktom.oracle.com Том Кайт – вице-президент Oracle Government, Education, and Health group – отвечает на наиболее трудные вопросы, связанные с технологией баз данных Oracle. Наиболее яркие материалы этого форума публикуются в данной колонке. ИЗУЧАЙТЕ курсоры: asktom.oracle.com/~tkyte/ivse.html экстенты: asktom.oracle.com/~tkyte/extents.html asktom.oracle.com/~tkyte/extents2.html

• Consistent Gets (согласованные чтения) Сколько раз для блока запрашивалось выполнение операций согласованного чтения.

• Physical Reads (физические чтения). Общее количество блоков данных, прочитанных с диска. Это значение равно значению статистического показателя "physical reads direct" (физические чтения, минуя кеш буферов), к которому добавляется число всех чтений в кеш буферов.
• Redo Size (размер журнальных данных). Общий размер сгенерированных журнальных данных в байтах.
• Bytes Sent via SQL*Net to Client (байты, отосланные клиенту по SQL*Net). Общее количество байтов, посланных клиенту фоновыми процессами.

• Bytes Received via SQL*Net from Client (байты, полученные от клиента по SQL*Net). Общее число байтов, полученных от клиента по сети.

• SQL*Net Roundtrips to/from Client (взаимные обмены по SQL*Net с клиентом). Общее количество сетевых сообщений, посланных или полученных от клиента.

• Sorts (memory) (сортировки в памяти). Число операций сортировки, которые были полностью выполнены в оперативной памяти и не потребовали записи на диск.

• Sorts (disk) (сортировки на диске). Число операций сортировки, которые потребовали по крайней мере одной записи на диск. Сортировки, которые требуют дискового ввода-вывода, интенсивно потребляют ресурсы. Попробуйте увеличить значение параметра инициализации SORT_AREA_SIZE.
• Rows Processed (строк обработано). Общее количество строк, возвращенных вашим оператором SELECT или модифицированных оператором INSERT, UPDATE или DELETE.

Ведущий данной колонки Том Кайт (Tom Kyte, thomas.kyte@oracle.com) с 1993 года работает в Oracle. Кайт – вице-президент Oracle Government, Education, and Health group, он автор книги “Expert One on One: Oracle”( Прим. пер. Имеется русский перевод 1-й части книги: Oracle для профессионалов. Книга 1. Архитектура и основные особенности. – ДиаСофт, 2003 г.), и соавтор книги “Beginning Oracle Programming” – “Введение в программирование баз данных Oracle” (обе книги были опубликованы издательством Wrox Press, www.wrox.com).