Для ведения этой колонки на сайте asktom.oracle.com (Ask Tom) используется один маленький сервер, на котором работают СУБД Oracle9i Database Release 2, Oracle Text в качестве машины поиска и Oracle9iAS-модуль mod_plsql для доступа к базе данных. Реальное "программное обеспечение", с которого начинался сайт Ask Tom, было доморощенной средой разработки, которая позволяла нам быстро собирать PL/SQL-приложения. Эта внутренняя работа оказалась настолько удачной, что она стала действительно одной из самых "крутых" новых функциональных возможностей СУБД Oracle Database 10g. С этой целью я попросил Sergio Leunissen – одного из ключевых специалистов, работавших над этим программном обеспечением – рассказать немного о нем.

Leunissen. Том прав. Эта доморощенная разработка стала тем, что в СУБД Oracle Database 10g называется HTML DB, одной из наиболее захватывающих новых функциональных возможностей СУБД. Среда HTML DB прямо в своем "коробочном варианте" обеспечивает усовершенствованные возможности разработки веб-приложений.

Как она работает? HTML DB полностью содержится в базе данных. Она состоит из репозитория метаданных и машины для визуализации и обработки страниц. Когда вы разрабатываете свое приложение, определения страниц, областей, шаблонов, кнопок и полей сохраняются в репозитории метаданных. Когда приложение работает, каждая страница и все ее содержимое формируются в реальном времени из репозитория метаданных.

Если вы разрабатывали веб-приложения, используя инструментарий PL/SQL Web и модуль mod_plsql, вам понравится, сколько времени сэкономит для вас HTML DB. Из-за того, что машина и репозиторий метаданных размещены в базе данных, вы будете иметь полный доступ к SQL и PL/SQL. Вывод данных из базы данных в Веб – это просто вопрос написания SQL-запросов. Все остальное сделает за вас машина визуализации. Фактически, если у вас нет опыта работы с SQL, вы даже можете положитmся на "мастеров" для выполнения всех шагов создания отчета по одной или более таблицам базы данных.

Аналогично, вставка, обновление или удаление данных из базы данных – простое дело. Когда бы ни была представлена страница, машина обработки может выполнять операции языка манипулирования данными (DML), такие, как операторы INSERT, которые определяете вы. В операторы INSERT обычно включаются из форм пользовательские входные данные, на которые можно ссылать с помощью синтаксиса переменных связывания. Например, скажем, вы хотите извлечь имена и фамилии людей и их номера телефонов и сохранить эту информацию в базе данных. Предполагая, что у вас уже есть таблица для хранения этой информации, вы начинаете с добавления к странице полей, называемых элементами (items). Элементы имеют уникальные имена, поэтому их значения можно позднее извлечь из клиентских данных сеанса (session state – состояние сеанса). После добавления к странице кнопки для представления информации, все что вам нужно – это процесс для вставки данных в вашу целевую таблицу. Предполагая, что вы назвали ваши элементы как P1_FIRST_NAME, P1_LAST_NAME и P1_PHONE, ваш процесс может быть реализован в виде следующего оператора вставки:

insert into T (first_name, last_name, phone_number)
Values (:P1_FIRST_NAME, :P1_LAST_NAME, :P1_PHONE); 

Обратите внимание, набор пользовательских входных данных в полях формы извлекается из состояния сеанса с помощью синтаксиса переменных связывания.

Машина HTML DB и репозиторий метаданных обеспечивают мощную и полную среду для создания пользовательских HTML-интерфейсов над базой данных Oracle. Фактически, каждый экран в HTML DB создан с помощью HTML DB!

Теперь вы знаете, как работает HTML DB. Немного более подробно о ее ключевых компонентах: SQL Workshop (SQL-мастерская), Data Workshop (мастерская данных) и Application Builder (конструктор приложений).

SQL-мастерская. SQL-мастерская позволяет вам взаимодействовать с вашей базой данных так же, как вы делали это с помощью инструмента SQL*Plus, но с добавленными функциональными возможностями, такими, как быстрый просмотр объектов базы данных, запросы по образцу (query by example) и быстрый просмотр словаря данных.

Мастерская данных. С помощью мастерской данных вы можете загружать данные в базу данных и извлекать данные из нее. Мое личное любимое средство позволяет вам за несколько секунд загрузить данные из электронной таблицы в базу данных. Все что вам нужно сделать – это вырезать и вставить ваши данные из электронной таблицы и прогнать их через мастер. Как только вы загрузите вашу электронную таблицу в таблицу базы данных, вам потребуется только несколько минут для создания веб-отчета, к которому можно обращаться из браузера.

Конструктор приложений. Конструктор приложений – центральная часть HTML DB, он загружается вместе с мастерами для помощи в создании страниц, содержащих отчеты, формы и диаграммы. Страницы легко соединяются с помощью условных переходов.

Во время сборки вашего приложения вы можете использовать около 75 предопределенных элементов управления окном (widgets), от основных элементов HTML-форм до более эффективных всплывающих списков значений, всплывающих календарей, которые выбирают списки, которые автоматически выдают страницы, и т.д. Хотите проверять корректность данных, вводимых в форму? Нет проблем. Вы можете добавлять проверки простым щелчком мышью. Вы даже можете использовать синтаксис регулярных выражений, новый в Oracle Database 10g (более подробно о регулярных выражениях в Oracle Database 10g см. "First Expressions"), для выполнения проверок элементов данных, таких, как телефонные номера или номера социального обеспечения.

Возможности подготовки отчетов в конструкторе приложений также достаточно широки. Шаблоны пользовательского интерфейса для составления отчетов, такие, как перелистывание страниц (способность листать – нумеровать – страницы результирующего набора), сортировка по нескольким столбцам, управляемая пользователем, и вставка гиперссылок на другие отчеты или формы, могут добавляться декларативно. То есть, не требуется никакого программирования, только несколько щелчков мышью.

Существует еще больше функциональных возможностей, которые позволяют сокращать время разработки веб-приложений:

Управление сеансами и их состояниями. Для каждого пользователя, запустившего приложение в среде HTML DB, всегда создается сеанс, и для этого не требуется никакого программирования. Каждый элемент формы, размещенный вами на странице с помощью HTML DB, автоматически имеет соответствующее состояние сеанса, ведущееся в базе данных. В вашем приложении вы можете ссылаться на любую переменную состояния сеанса на любой странице, используя знакомый синтаксис переменных связывания.

Управление пользовательским интерфейсом. Внешнее оформление (look-and-feel) отделено от логики приложения и доступа к данным за счет повсеместного использования шаблонов в HTML DB. Шаблоны используются для внешнего оформления страниц, отчетов, кнопок, вкладок, меню, меток полей и т.д. Это означает, что вы можете быстро приступить к конструированию приложения, не беспокоясь сразу же о внешнем оформлении. Как только вы будете удовлетворены работой вашего приложения, вы можете проектировать HTML-шаблоны. Либо вы можете выбрать их из тем галереи шаблонов, имеющейся в HTML DB.

Гибкие схемы аутентификации и авторизации. Если вы решаете, что ваше приложение будет требовать входа в систему по имени пользователя и паролю, вы можете либо выбрать одну из встроенных схем аутентификации из галереи HTML DB, либо создать собственную схему, удовлетворяющую вашим требованиям. Заранее изготовленные схемы аутентификации позволяют использовать в вашем приложении, среди прочих, пользовательский репозиторий HTML DB, LDAP-репозиторий или однократную регистрацию (single sign-on) в Oracle9iAS.

И наконец, HTML DB непосредственно поддерживает разработку в среде хостинга. Это означает, что сервер базы данных Oracle может поддерживать множественные проекты разработок, каждый из которых разрабатывается различными группами специалистов, использующих только веб-браузер.

Sergio Leunissen

Введение массового связывания в ваши процессы обработки данных

В настоящее время мы используем для нашего ETL-процесса следующую структуру:

  Cursor for Delta table 
    Loop 
      insert into dimension tables values ( cursor variables ) 
      insert into xref tables 
      insert into fact tables 
    end loop 


   commit;

Причина использования этой структуры заключается в необходимости регистрации всех ошибок в процессе. Сейчас для обработки 140,000 записей требуется более 16 часов. Для обработки 300 записей в таблице delta требуется приблизительно одна минута. Эти операторы INSERT являются переменными. Должно ли их выполнение занимать так много времени? Есть ли что-нибудь существенно неправильное в этой процедуре? Будет ли фиксация транзакций в цикле (чему вы решительно сопротивляетесь) ускорять этот процесс?

Я представил этот вопрос как LIFO-очередь (last in, first out – последним пришел, первым обслужен) и буду отвечать в обратном порядке. Перенос фиксации транзакций в курсорный цикл FOR приведет к следующему:

1. Ваш код будет выполняться более медленно.
2. Вы будете генерировать больше журнальной информации.
3. Вы будете генерировать больше информации для отката транзакций.

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

Что касается вопроса: "Есть ли что-нибудь существенно неправильное в этой процедуре?", ответ будет положительным. Она запрограммирована так, что в ней вообще не используются какие-либо операции с массовым связыванием. По крайней мере, следует использовать массовую выборку из курсора и массовую вставку с помощью оператора FORALL. Операции с массовым связыванием позволяют повысить производительность процедуры во много раз. Сжатие кода до одного SQL-оператора (экстремальный случай) позволяет повысить производительность процедуры на много порядков. Здесь я буду сравнивать выполнение построчных операций, выполнение массовых операций и выполнение операций с помощью одного SQL-оператора. Вы увидите повышение производительности, которое вы можете ожидать.

Начнем с демонстрационных таблиц T и T2, сначала я хочу загрузить данные из T в T2. Я установил так, чтобы 24 строки были отвергнуты. Кроме того, я отсортировал данные по столбцу object_name, чтобы ошибки во время загрузки возникали в случайном порядке:

create table t
as
select * from all_objects
 order by object_name;

create table t2
as
select * from all_objects
 where 1=0;

alter table t2 
add constraint check_object_id
check ( object_id <= 31900 );

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

declare
    l_cnt number := 0;
begin
    for x in ( select * from t )
    loop
    begin
        insert into t2 values X;
    exception
        when others then 
          dbms_output.put_line(sqlcode);
          l_cnt := l_cnt+1;
    end;
    commit;
    end loop;

    dbms_output.put_line
    ( l_cnt || ' total errors' );
end;

Когда я выполняю это в SQL*Plus, в выходных данных будет следующее:

...
24 total errors
PL/SQL procedure successfully completed.
Elapsed: 00:00:58.69

Это предлагаемый вами способ: оператор COMMIT помещен в курсорный цикл FOR. Просто уберем этот COMMIT из цикла и повторно выполним код, результат будет следующим:

...
24 total errors
PL/SQL procedure successfully completed.
Elapsed: 00:00:15.68

Это показывает влияние фиксации транзакций в цикле, которое имели бы вы. Теперь продолжим пример, используя массовые операции. Я рассмотрю пример, специфический для Oracle9i Database Release 2. (О том, как это сделать в Oracle8i и более ранних версиях см. asktom.oracle.com/~tkyte/8iBulk.html.) В нем используется новое предложение BULK_EXCEPTIONS и возможность выполнения операторов INSERT/UPDATE над записями. Следующий код выполняет массовую выборку 100 строк за один раз, а затем массовую вставку этих 100 строк за один раз, безупречно перехватывая все ошибки. Здесь идея состоит в том, что после выборки, но до вставки, вы могли бы выполнять некоторую обработку. Потом вы увидите, как будет выглядеть код, если этот шаг обработки не нужен.

DECLARE
    type array is table of t%rowtype 
         index by binary_integer;
    data       array;
    errors     NUMBER;

    dml_errors EXCEPTION;
    l_cnt      number := 0;
    PRAGMA exception_init
          (dml_errors, -24381);

    cursor c is select * from t;
BEGIN
  open c;
  loop
    fetch c BULK COLLECT 
            INTO data LIMIT 100;
    /* выполнение некоторой обработки */
    begin
      FORALL i IN 1 .. data.count 
      SAVE EXCEPTIONS
        insert into t2 
        values data(i);
    EXCEPTION
    WHEN dml_errors 
    THEN
      errors := 

        SQL%BULK_EXCEPTIONS.COUNT;
      l_cnt := l_cnt + errors;
      FOR i IN 1..errors LOOP
        dbms_output.put_line
        ('Error occurred 
          during iteration ' ||
     SQL%BULK_EXCEPTIONS(i).ERROR_INDEX
         || ' Oracle error is ' ||
     SQL%BULK_EXCEPTIONS(i).ERROR_CODE 
        );
      end loop;
    end;
    exit when c%notfound;
  END LOOP;
  close c;
  dbms_output.put_line
  ( l_cnt || ' total errors' );
end;

Результат выполнения будет следующим:

...
24 total errors
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.45

Теперь, с точки зрения производительности, видны различия как между днем и ночью. Обработка 100 строк за один раз по сравнению с 1 строкой за один раз имеет огромные различия. Я говорил, что если вам здесь не нужен шаг обработки, вы могли бы использовать даже лучший способ. Просто перенесите вашу "проверку" в сам оператор SELECT. Здесь, поскольку я знаю, что записи, у которых object_id > 31900, будут причиной ошибок, я просто пропущу их. Результаты вставки будут следующими:

insert into t2 
select * from t 
where object_id <= 31900;

29895 rows created.
Elapsed: 00:00:00.52

Это показывает, что производительность повышается, если вы перенесете проверку ETL-процесса в сам SQL!

  Ускорение выполнения запросов

Как я могу убедить моих разработчиков, что существует лучший способ сделать следующее?

select max (weight) weight 
  from client.pet_weight 
    where pet_id = :pet_id 
  and setup_date = 
  (select max (setup_date) 
     from client.pet_weight 
    where pet_id = :pet_id) 

Ответ, как всегда, заключается в следующем: найти этот лучший способ, выполнить его эталонное тестирование и числа сами скажут за себя. Здесь я покажу запрос, который может выполняться намного быстрее. И не только быстрее, но и будет использовать существенно меньше ресурсов логического ввода-вывода (а это – одна из целей при настройке запросов). Для того чтобы написать лучший запрос, я перефразирую вашу проблему как постановку задачи на обычном языке, а не на SQL. Таким образом я могу "настраивать" вопрос, не настраивая оператор запроса. Ваша проблема формулируется следующим образом: "для данного pet_id найти вес (weight) для наиболее большого значения setup_date" или "для данного значения pet_id, отсортированного в убывающем порядке по столбцу setup_date, взять первое найденное значение weight". Как только у меня есть это, придумать альтернативный запрос становится достаточно просто. Я начну с создания простой таблицы для эталонного тестирования:

create table t 
( weight int, 
  pet_id int, 
  setup_date date );

insert into t 
( pet_id, weight, setup_date )
select mod(rownum,1000), 
       dbms_random.random, 
       sysdate-1000 + 
          dbms_random.value(0,1000)
  from all_objects;

create index t_idx on 
t( pet_id, setup_date, weight );

analyze table t compute statistics
for table for all indexes
for all indexed columns;

Теперь в моей системе в таблице T содержится около 31,000 строк. Я начал тестирование с выполнения вашего запроса к моей таблице. Я делал это в цикле от 1 до 1,000, получая значение weight для каждого pet_id, которые я только что вставил. Результат был следующим:

select max(weight) 
  from t  
 where pet_id = :b1
   and setup_date = 
      (select max(setup_date)
         from t
        where pet_id = :b1 );

call     cnt     cpu     query   rows
_____    _____   _____   _____   _____

Parse    1       0.00    0       0
Execute  1000    0.42    2000    0
Fetch    1000    0.06    2005    1000
_____    _____   _____   _____   _____

total    2001    0.48    4005    1000

Rows   Row Source   Operation
_____  __________   __________

100    SORT         AGGREGATE
1000   INDEX RANGE  SCAN T_IDX

Итак, для выполнения этого запроса с 1,000 различных значений входных данных потребовалось примерно полсекунды времени ЦП и немногим более 4,000 операций логического ввода-вывода. Запрос, который придумал я, позволяет получить такой же ответ, но в нем используется вложенное представление, в котором для получения результатов сортируются данные вместе с ROWNUM. Я хочу с помощью этого запроса избавиться от коррелированного подзапроса, который заставляет меня дважды обращаться к данным, что я и делал выше, когда первый раз я обращался к данным для получения max(setup_date), а потом – для получения weight для данных pet_id и max(setup_date). Этот запрос с вложенным представлением имеет следующие характеристики производительности:

select weight 
  from (select weight 
          from t 
         where pet_id = :b1
         order by pet_id DESC, 
                  setup_date DESC, 
                  weight DESC )
  where rownum = 1;

 call     cnt     cpu     query   rows
 _____    _____   _____   _____   _____

 Parse    1       0.00    0       0
 Execute  1000    0.21    0       0
 Fetch    1000    0.06    2000    1000
 _____    _____   _____   _____   _____

 total    2001    0.27    2000    1000

 Rows   Row Source   Operation
 _____  __________   __________


 1000   COUNT        STOPKEY
 1000   VIEW
 1000   INDEX RANGE  SCAN 
	DESCENDING   T_IDX 

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

Следующие шаги СПРАШИВАЙТЕ Тома asktom.oracle.com Том Кайт – вице-президент группы Oracle Government, Education, and Healthcare – отвечает на наиболее трудные вопросы, связанные с технологией баз данных Oracle. Наиболее яркие материалы этого форума публикуются в данной колонке. ЧИТАЙТЕ Еще Тома asktom.oracle.com/~tkyte Документацию Oracle /documentation ОБСУЖДАЙТЕ На форумах Oracle www.oracle.com/forums

Теперь, если вы не можете использовать в подзапросе предложение ORDER BY, объявите курсор, который похож на показанный ниже, откройте его, извлеките 1 строку и закройте его:

select weight 
  from t 
 where pet_id = :b1
 order by pet_id DESC, 
          setup_date DESC, 
          weight DESC;

  Учитывает ли оптимизатор по стоимости маркер максимального заполнения?

Учитывает ли оптимизатор по стоимости (CBO, cost-based optimizer) все блоки до маркера максимального заполнения (HWM, high-water mark), когда вычисляет стоимость полного просмотра таблицы?

Да, он должен делать это. Все блоки до HWM просматриваются во время полного просмотра, поэтому это должно быть учтено в стоимости.

Рассмотрим следующее:

SQL> create table t pctfree 90 pctused 5
   2 as
   3 select * from all_objects;

SQL> analyze table t compute statistics;
Table analyzed.

SQL> set autotrace traceonly explain;
SQL> select * from t;

Execution Plan
________________________________________

SELECT STATEMENT Optimizer=CHOOSE 
(Cost=384 Card=29272 Bytes=2488120)
  TABLE ACCESS (FULL) OF 'T' 
  (Cost=384 Card=29272 Bytes=2488120)

SQL> set autotrace off
SQL> delete from t;
29272 rows deleted.

SQL> commit;
Commit complete.


SQL> analyze table t compute statistics;
Table analyzed.

SQL> set autotrace traceonly explain;
SQL> select * from t;

Execution Plan
________________________________________

SELECT STATEMENT Optimizer=CHOOSE 
(Cost=384 Card=1 Bytes=128)
  TABLE ACCESS (FULL) OF 'T' 
  (Cost=384 Card=1 Bytes=128)

Обратите внимание, значение Cost (стоимость) для полного просмотра остается постоянным, равным 384, независимо от количества строк в таблице. Это происходит из-за того, что, когда сервер Oracle выполняет полный просмотр, он должен читать каждый и любой блок, расположенный ниже HWM. Во втором плане выполнения обратите внимание, значение Card (сокращение от "cardinality" – кардинальность – предполагаемое количество строк в результирующем наборе) существенно ниже. Оптимизатор знает, что он найдет только одну строку, но он должен будет просмотреть столько же блоков, сколько он просматривал для поиска 29,272 строк. Давайте очистим (truncate) эту таблицу, уменьшив этим значение HWM, и посмотрим, что получилось:

SQL> truncate table t;
Table truncated.

SQL> analyze table t compute statistics;
Table analyzed.

SQL> set autotrace traceonly explain
SQL> select * from t;

Execution Plan
________________________________________


SELECT STATEMENT Optimizer=CHOOSE 
(Cost=2 Card=1 Bytes=128)
  TABLE ACCESS (FULL) OF 'T' 
  (Cost=2 Card=1 Bytes=128)

Для той же самой таблицы с таким же количеством строк, но после ее очистки, уменьшающей значение HWM, стоимость полного просмотра существенно уменьшается (Cost=2).

Ведущий данной колонки Том Кайт (Tom Kyte, thomas.kyte@oracle.com) работает в Oracle с 1993 года. Кайт – вице-президент группы Oracle Government, Education, and Healthcare, он автор книг "Effective Oracle by Design" (издательство Oracle Press) – "Проектирование эффективных приложений Oracle" и "Expert One on One: Oracle" (издательство Apress) ( Прим. пер. Имеется русский перевод: Oracle для профессионалов. Книга 1. Архитектура и основные особенности. Книга 2. Расширение возможностей и защита. – ДиаСофт, 2003 г.).