Июнь 2005
Профессионалу разработчику
Том Кайт
Том Кайт: о секционировании и конвейеризации
(On Partitioning and Pipelining, by Tom Kyte)
Источник: журнал Oracle Magazine, November/December 2004
(
http://www.oracle.com/technology/oramag/oracle/04-nov/o64asktom.html ).
Наш эксперт рассказывает об операциях UNION ALL, конвейерах и последних субботах.
Вопрос: В моей OLTP-системе – крупномасштабная база данных, в которой таблицы для облегчения очистки секционированы по дате. Все индексы, не связанные с датой, – глобальные и несекционированные. Для обновления глобальных индексов требуется очень много времени (размер моих таблиц – от 4 до 25 гигабайтов), поэтому я интересуюсь блокированием и другими вопросами конкурентного доступа к таблицам во время выполнения операторов с предложением DROP секция ... UPDATE GLOBAL INDEXES, потому что в это время люди продолжают обновлять и вставлять данные в таблицы с довольно высокой скоростью (для каждой таблицы от 60 до 80 DML-операций в секунду).
Ответ: Проблема здесь не в совокупном размере таблиц, а в размере отдельных секций. Если вы "порежете" таблицу на достаточно маленькие секции, то каждая операция удаления секции не будет такой большой и будет выполняться быстрее.
Ответ на вопрос о блокировании: во время удаления секции не должно быть активных транзакций, работающих с ней (во время выполнения операции DROP вы получите ошибку ORA-54, если у секции есть активные транзакции); операция DROP будет выполняться даже при наличии конкурентных/невыполненных DML-операций, работающих с другими секциями. Предложение UPDATE GLOBAL INDEXES выполняется подобно DML-операции (так же как оператор DELETE) и может выполнять конкурентно с другими транзакциями.
Операции UNION или UNION ALL
Вопрос: Я оптимизирую запрос с предложением WHERE EXISTS, похожим на это: WHERE EXISTS (SELECT 'x' FROM t1 WHERE ... UNION SELECT 'x' FROM t2 WHERE ... UNION SELECT 'x' FROM t3 WHERE ... ). Для выполнения этого предложения WHERE EXISTS необходим чрезмерный объем времени. Существует ли лучший способ сделать это?
Ответ: Несомненно. Я часто наблюдаю, что используется операция UNION там, где гораздо более эффективно использовать операцию UNION ALL. Проблема заключается в том, что сервер Oracle при выполнении операции UNION находит все строки, удовлетворяющие условиям поиска, а затем удаляет все дубликаты. Чтобы понять, что я имею в виду, вы можете просто сравнить следующие запросы:
SQL> select * from dual
2 union
3 select * from dual;
D
-
X
SQL> select * from dual
2 union ALL
3 select * from dual;
D
-
X
X
Обратите внимание, первый запрос возвращает только одну запись, а второй – две. Операция UNION принудительно выполняет большую сортировку и удаляет одинаковые значения. Чаще всего этого совсем не нужно делать. Чтобы показать, как это может повлиять на вас, я буду использовать таблицы словаря данных для выполнения запросов с предложением WHERE EXISTS, в котором используются операции UNION и UNION ALL, а результаты буду сравнивать с помощью утилиты TKPROF. Результаты поразительны.
Сначала я выполняю запрос с операциями UNION:
<
SQL> select *
2 from dual
3 where exists
4 (select null from all_objects
5 union
6 select null from dba_objects
7 union
8 select null from all_users);
call cnt cpu ela query
---- --- ---- --- ------
Parse 1 0.01 0.00 0
Execute 1 2.78 2.75 192234
Fetch 2 0.00 0.00 3
----- ---- ---- ---- ------
total 4 2.79 2.76 192237
Обратите внимание, была проделана большая работа – выполнено свыше 192 000 операций ввода-вывода, и только для того, чтобы выбрать одну строку из таблицы DUAL. Теперь я добавлю к запросу операции UNION ALL:
SQL> select *
2 from dual
3 where exists
4 (select null from all_objects
5 union all
6 select null from dba_objects
7 union all
8 select null from all_users);
call cnt cpu ela query
------ ---- ---- ---- -----
Parse 1 0.00 0.00 0
Execute 1 0.01 0.00 9
Fetch 2 0.00 0.00 3
------ ---- ---- ---- -----
total 4 0.01 0.00 12
Все совершенно изменилось! Здесь подзапрос в предложении WHERE EXISTS перестал выполняться после возврата первой найденной строки, и сервер базы данных не должен был заниматься удалением дубликатов, поэтому первая строка возвращается очень быстро.
Сухой остаток: если вы можете использовать операцию UNION ALL, во что бы то ни стало используйте ее, а не операцию UNION, это поможет избежать дорогостоящего шага удаления дубликатов – шага, который чаще всего даже и не нужен.
Конвейерные функции
Вопрос: Можете ли вы продемонстрировать использование конвейерных функций на простом примере (с таблицам EMP и DEPT)?В каких случаях использование конвейерных функций может быть эффективным?
Ответ: Конвейерные функции – это просто код, с которым вы можете обращаться как с таблицей базы данных. Конвейерные функции позволяют вам использовать (это поразительно для меня) конструкции типа SELECT * FROM <ФУНКЦИЯ_PLSQL>;.
Рассмотрим пример ETL-процесса (Extraction, Transformation and Loading, извлечение преобразование и загрузка данных), он вводит данные из плоского файла в PL/SQL-функцию, она преобразует эти данные, преобразованные данные затем используются для обновления существующих табличных данных. В примере демонстрируется довольно много функциональных возможностей сервера базы данных, включая внешние таблицы, конвейерные функции и оператор MERGE (обновление со вставкой).
Для создания и использования внешней таблицы мне нужен объект directory (каталог). Этот объект будет указывать на каталог tmp:
SQL> create or replace
2 directory data_dir as '/tmp/'
3 /
Directory created.
Теперь я создам внешнюю таблицу. Часть ее определения выглядит как управляющий файл загрузчика, поскольку часть процесса создания внешней таблицы представляет собой, на самом деле, создание управляющего файла:
SQL> create table external_table
2 (EMPNO NUMBER(4) ,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 )
11 ORGANIZATION EXTERNAL
12 (type oracle_loader
13 default directory data_dir
14 access parameters
15 (fields terminated by ',')
16 location ('emp.dat')
17 )
18 /
Table created.
Теперь для создания плоского файла с данными таблицы EMP я буду использовать утилиту flat. Вы можете найти ее на сайте asktom.oracle.com/~tkyte/flat.
SQL> host flat scott/tiger -
> emp > /tmp/emp.dat
Теперь я готов проверить внешнюю таблицу; созданный мной плоский файл теперь работает как таблица базы данных:
SQL> select empno, ename, hiredate
2 from external_table
3 where ename like '%A%'
4 /
EMPNO ENAME HIREDATE
---------- ------ ---------
7499 ALLEN 20-FEB-81
7521 WARD 22-FEB-81
7654 MARTIN 28-SEP-81
7698 BLAKE 01-MAY-81
7782 CLARK 09-JUN-81
7876 ADAMS 12-JAN-83
7900 JAMES 03-DEC-81
7 rows selected.
Я создам ETL-подпрограмму на языке PL/SQL, чтобы она "поглощала" плоский файл и выдавала оперативные данные для обновления или вставки. Конвейерная функция должна возвращать тип-коллекцию, и я хочу возвращать коллекцию, которая выглядит как сама таблица EMP, поэтому я создаю скалярный объектный тип и таблицу этого типа:
SQL> create or replace type
2 emp_scalar_type as object
3 (EMPNO NUMBER(4) ,
4 ENAME VARCHAR2(10),
5 JOB VARCHAR2(9),
6 MGR NUMBER(4),
7 HIREDATE DATE,
8 SAL NUMBER(7, 2),
9 COMM NUMBER(7, 2),
10 DEPTNO NUMBER(2)
11 )
12 /
Type created.
SQL> create or replace type
2 emp_table_type as table
3 of emp_scalar_type
4 /
Type created.
Теперь я готов создать саму конвейерную функцию. Заметим, показанная ниже ETL-функция очень упрощена; она модифицирует столбец ename, но вы можете включать в нее любую сложную логику, какую вы хотите:
create or replace function emp_etl
(p_cursor in sys_refcursor)
return emp_table_type
PIPELINED
as
l_rec external_table%rowtype;
begin
loop
fetch p_cursor into l_rec;
exit when (p_cursor%notfound);
<1P class=bodycopy> -- подпрограмма проверки
<1P class=bodycopy> -- протоколирование некорректных строк куда-то в другое место
<1P class=bodycopy> -- поиск каких-то значений
<1P class=bodycopy> -- выполнение преобразования
pipe row(
emp_scalar_type(l_rec.empno,
LOWER(l_rec.ename),
l_rec.job,
l_rec.mgr,
l_rec.hiredate,
l_rec.sal,
l_rec.comm,
l_rec.deptno) );
end loop;
return;
end;
/
Function created.
Конвейерная функция emp_etl работает так же как и таблица. В следующем запросе из функции выбираются столбцы empno и ename, а сама эта функция выбирает все столбцы из нашей внешней таблицы:
SQL> select empno, ename
2 from TABLE(emp_etl(
3 cursor(select *
4 from external_table
5 ) ) )
6 where ename like '%a%';
EMPNO ENAME
---------- ------
7499 allen
7521 ward
7654 martin
7698 blake
7782 clark
7876 adams
7900 james
7 rows selected.
Обратите внимание на использование в определении этой функции ключевого слова PIPELINED; оно обязательно при определении конвейерной функции. Также обратите внимание на использование в коде PL/SQL директивы pipe row – это то "волшебство", которое делает конвейерные функции действительно интересными. Директива pipe row немедленно возвращает данные клиенту, это означает, что моя клиентская подпрограмма получает данные из этой функции до того, как функция сгенерирует последнюю строку данных. Если курсор, переданный мною в эту конвейерную функцию, возвращает 1 000 000 строк, я для получения первой строки не должен буду ждать, когда PL/SQL-код обработает все 1 000 000 строк; данные начнут возвращаться по мере их готовности. Вот почему они называются конвейерными функциями: данные текут – как в большой трубе – от курсора в PL/SQL-функцию, а затем в вызывающую программу.
Теперь для завершения работы я создам таблицу данных, которую я хочу обновлять по данным исходной системы, которая передала мне плоский файл, созданный мною выше. Логика следующая: если запись уже существует в моей базе данных, я выполняю оператор UPDATE для обновления столбцов ename и sal; если запись не существует, я выполняю оператор INSERT для ее вставки. Я начну работать с некоторыми данными из таблицы EMP:
SQL> create table emp as
2 select * from scott.emp
3 where mod(empno,2) = 0;
Table created.
А здесь – оператор MERGE, который непосредственно в ETL-процессе "распоряжается" данными из плоского файла, не забивая диск промежуточными файлами:
SQL> merge into EMP e1
2 using (select *
3 from TABLE
4 (emp_etl(
5 cursor(select *
6 from external_table))
7 )
8 ) e2
9 on (e2.empno = e1.empno)
10 when matched then
11 update set e1.sal = e2.sal,
12 e1.ename = e2.ename
13 when not matched then
14 insert (empno, ename, job, mgr,
15 hiredate, sal, comm, deptno)
16 values (e2.empno, e2.ename,
17 e2.job, e2.mgr,
18 e2.hiredate, e2.sal,
19 e2.comm, e2.deptno)
20 /
14 rows merged.
Последняя суббота каждого месяца
Вопрос: Мне нужно разработать запрос, возвращающий дату последней субботы любого заданного месяца. Как это сделать?
Ответ: Это довольно просто сделать с помощью функций NEXT_DAY (следующий день) и LAST_DAY (последний день). Вы можете просто взять дату последнего дня месяца, вычесть из не 7 дней, а затем для определения даты субботы, следующей после вычисленной даты, использовать функцию NEXT_DAY. Я выполню два простых запроса, первый (см. листинг 1) возвращает дату последней субботы каждого месяца текущего года, а во втором я ввожу месяц и получаю дату последней субботы только в этом месяце.
|
ЛИСТИНГ 1: найти дату последней субботы каждого месяца в году. |
SQL> select next_day(
2 last_day(
3 add_months( trunc( sysdate,'y'),rownum-1 ) )-7,
4 to_char( to_date( '29-01-1927', 'dd-mm-yyyy' ), 'DAY' ) )
5 from all_objects
6 where rownum <= 12;
NEXT_DAY(
---------
31-JAN-04
28-FEB-04
27-MAR-04
24-APR-04
29-MAY-04
26-JUN-04
31-JUL-04
28-AUG-04
25-SEP-04
30-OCT-04
27-NOV-04
25-DEC-04
12 rows selected.
|
Вы можете спросить: "В чем смысл 29-01-1927"? Это просто случайная дата, выбранная мною, поскольку я знал, это была суббота – можно использовать дату любой субботы. Я использую в запросе этот способ (вместо указания литерала SAT), поскольку в языках, отличных от английского, сокращение SAT – это не суббота. Этот запрос будет работать в любых языковых средах.
Листинг 2 показывает запрос, в который вводится месяц, а затем возвращается дата последней субботы этого месяца.
|
ЛИСТИНГ 2: найти дату последней субботы заданного месяца. |
SQL> select next_day(
2 last_day( to_date( '&YOUR_MONTH', 'MM' ))-7,
3 to_char( to_date( '29-01-1927', 'dd-mm-yyyy' ), 'DAY' )
4 )
5 from dual;
Enter value for your_month: 01
old 2: last_day( to_date( '&YOUR_MONTH', 'MM' ))-7,
new 2: last_day( to_date( '01', 'MM' ))-7,
NEXT_DAY(
----------
31-JAN-04
|
Этот запрос работает с текущим годом. Если вам нужно, чтобы он возвращал дату последней субботы для любого заданного года, вы легко можете сделать это – просто замените формат ввода даты MM на любой другой, нужный вам. Один из читателей ("Ant" из Нью-Йорка) предложил великолепное усовершенствование первого запроса. Используя новое предложение MODEL оператора SELECT в сервере Oracle Database 10g, он смог получить результат вообще без какого-либо ввода-вывода из базы данных.
Это решение показано на листинге 3.
|
ЛИСТИНГ 3: использование предложения MODEL для поиска даты последней субботы. |
SQL> select next_day( last_day( add_months( trunc( sysdate,'y' ),cell ) )-7,
2 to_char( to_date( '29-jan-1927', 'dd-mon-yyyy' ), 'DAY' ) )
3 from dual
4 model return all rows
5 dimension by (0 attr)
6 measures (0 cell)
7 rules iterate (12) (
8 cell[iteration_number] = iteration_number
9 );
Execution Plan
------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1)
1 0 SQL MODEL (ORDERED FAST)
2 1 FAST DUAL (Cost=2 Card=1)
Statistics
------------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
666 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12 rows processed
|
Более подробно о предложении MODEL и о возможностях его использования см. превосходную статью Джонатана Генника (Jonathan Gennick), опубликованную в журнале Oracle Magazine.
Путаница с пакетом STATSPACK Вопрос: Я работаю в среде Oracle9i Database Release 2 (9.2.0.4) на сервере ALPHA OPEN VMS с двумя центральными процессорами. Мне нужна помощь в интерпретации отчета пакета STATSPACK, который я изучал. Интервал между снятием моментальных копий статистических данных был равен 10 минутам. В 10 минутах – 600 секунд. Тем не менее, когда я смотрю на раздел "Top 5 Wait Events", я вижу, что время ожидания события db_file_sequential_read (последовательное чтение из файлов базы данных) превышает 900 секунд, а общее время ожидания всех событий значительно превышает 1 000 секунд. Как такое может быть? Я работал всего 600 секунд. Кроме того, я вижу, 27 сеансов потребили только 54 секунды времени центральных процессоров. Я пытаюсь по отчету STATSPACK вычислить время обслуживания и просто сбит с толку увиденным.
Ответ: Если у вас одновременно 1 000 человек будут ждать по 1 секунде, у вас получится 1 000 секунд времени ожидания – и все это за одну эту секунду. Этот конкретный отчет STATSPACK говорит только о том, что суммарное время ожидания события db_file_sequential_read было равно 906 секундам (я полагаю, из-за неправильного использования индексов). Предположим, вы имеете только 27 сеансов. Это означает, что каждый сеанс в вашем 10-минутном (600-секундном) интервале измерений в среднем ждет 33 секунды (27 * 33 = 891).
|
Следующие шаги
СПРАШИВАЙТЕ Тома
Том Кайт – вице-президент группы Oracle Government, Education, and Healthcare – отвечает на наиболее трудные вопросы, связанные с технологией баз данных Oracle. Наиболее яркие материалы этого форума публикуются в данной колонке. ЧИТАЙТЕ
об утилите flat о предложении MODEL
Еще Тома
Effective Oracle by Design |
Что касается времени центральных процессоров, это может означать, что каждый сеанс во время измерений в среднем мог бы использовать по две секунды времени центральных процессоров (я подчеркиваю, мог бы). С другой стороны, этого могло бы и не быть. Проблема заключается в том, что если 26 из этих сеансов непосредственно перед вашим первым моментальном снимком вызвали хранимые процедуры и в момент вашего заключительного моментального снимка эти процедуры по-прежнему работали и усиленно потребляли ресурсы центральных процессоров, не выполняя никаких операций ввода-вывода, то в ваш отчет эти процедуры внесут вклад, равный ровно нулю секунд времени центральных процессоров. Отчет о времени процессоров выдается только при окончании вызовов процедур, которые так и не закончили свою работу.
Так, для процедуры, выполнявшейся 10 часов и завершившейся во время ваших измерений, все это время попадет в ваш отчет.
И наоборот, для процедуры, выполнявшейся 10 часов и не завершившейся во время ваших измерений, в ваш отчет не попадет никакого времени.
Так что, будьте осторожны со временем центральных процессоров, оно иногда вводит в заблуждение в пакетных системах, но в значительной степени уместно в интерактивных системах. Но все эти размышления не позволяют вычислить по отчету STATSPACK среднее время обслуживания; данные, доступные вам, слишком агрегированы для этого. Вы по отчету STATSPACK не сможете никогда никаким способом вычислить время обслуживания, игнорируйте тех, то говорит обратное.
Единственный способ точно определять времена обслуживания состоит в том, чтобы клиентские приложения регистрировали где-нибудь эту информация.
Быстрый совет
Хотите, сделать ваш JDBC-код более эффективным? О новых функциональных возможностях, которые позволяют JDBC-коду работать как PL/SQL-коду oracle.com/technology/products/oracle9i/daily/jun24.html.
Ведущий данной колонки Том Кайт (Tom Kyte, thomas.kyte@oracle.com) работает в Oracle с 1993 года. Кайт – вице-президент группы Oracle Government, Education, and Healthcare, он автор книг "Effective Oracle by Design" (издательство Oracle Press, 2003) – "Проектирование эффективных приложений Oracle" и "Expert One on One: Oracle" (издательство Apress, 2003) ( Прим. пер. Имеется русский перевод: Oracle для профессионалов. Книга 1. Архитектура и основные особенности. Книга 2. Расширение возможностей и защита. – ДиаСофт, 2003 г.).
| 
