Май 2005
Профессионалу разработчику
Том Кайт
Том Кайт: о выборке, хранении и
индексировании
(On Fetching, Storing, and Indexing, by Tom Kyte)
Источник: журнал Oracle Magazine, September/October
2004
(
http://www.oracle.com/technology/oramag/oracle/05-sep/o54asktom.html ).
Наш эксперт рассказывает о правильном времени открытия
курсоров, хранении в области хранения строки и за ее пределами, а также о
глобальных индексах.
Вопрос. У нас проблема, связанная с ошибкой
ORA-01002 в блоке PL/SQL. Мы узнали, что эта ошибка иногда возникает в некоторых
особых случаях при обновлении выбранных данных, например, когда используется
оператор SELECT FOR UPDATE. Но здесь дело не в этом. Странная вещь – мы получаем
эту ошибку только тогда, когда читаем данные из курсора, без каких-либо
обновлений этих данных. Ошибка возникает, когда мы выполняем откат до точки
сохранения (savepoint)
create table test (a integer); insert into test values (1);
create table test2 (b integer);
insert into test2 values (11);
declare
cursor c is select * from test;
begin
update test2 set b = 22;
savepoint my_savepoint;
for x in c
loop
-- выполняем некоторую работу, но если
-- возникает ошибка, то:
rollback to
savepoint my_savepoint;
end loop;
end;
/
У нас сложная логика; мы обрабатываем результирующий набор, а
затем в цикле выполняем другую обработку. Если возникает ошибка, мы хотим
отменить всю нашу работу в этом цикле. Но мы обнаружили, что при выполнении
отката до точки сохранения всегда возникает ошибка ORA-01002 "Fetch out of
Sequence". Мы используем СУБД Oracle8i Release 3 (8.1.7) и до сих пор не можем
отыскать схожую проблему на каком-либо форуме, так что будем рады любым
подсказкам.
Ответ. Это имеет смысл, и, надеюсь, вы
согласитесь. Ваш временной график был таким:
- во время t0 вы выполняли обновление – то есть, начали транзакцию;
- во время t1 вы установили "точку сохранения" (savepoint);
- во время t1+ вы могли модифицировать базу данных;
- во время t2 вы открыли результирующий набор.
Этот результирующий набор может "видеть" состояние базы данных
на момент времени t2, включая все изменения, сделанные в вашем сеансе, – все,
что случилось в промежутке от момента времени t0 до t2. Вы могли, например,
модифицировать таблицу TEST. Вы не делали этого, но ничто нам не говорит, что вы
не могли этого сделать.
Во время t3 вы говорите: "Верните назад данные такими, какими
они были во время t1". Это так, как будто во время t1+ ничего не случалось; в
вашем случае, этого и не случалось, но это могло иметь место. Сервер Oracle не
знает об этом, он это не отслеживает.
Итак, во время отката к точке сохранения были бы уничтожены
записи, которые теоретически ваш курсор должен и был в состоянии видеть. Это
похоже на возвращение в прошлое. Вы сделали недействительным этот курсор. Если
бы он оставался открытым, то он вполне мог бы видеть записи, которые уже не
существуют.
Общее правило: любой откат к точке сохранения, установленной
до открытия курсора, вызывает эту ошибку. Если такой откат не вызовет ошибки, в
большом количестве появятся несовместимые результаты.
Решение вашей дилеммы кодирования – открывать курсор перед
точкой сохранения, как в следующем примере: SQL> declare
2 cursor c is select * from test;
3 l_rec test%rowtype;
4 begin
5 update test2 set b = 22;
6 open c;
7 savepoint my_savepoint;
8 loop
9 fetch c into l_rec;
10 exit when c%notfound;
11 rollback to
12 savepoint my_savepoint;
13 end loop;
14 end;
15 /
PL/SQL procedure successfully completed.
Это будет работать так, как вы предполагаете. Результирующий
набор, указываемый курсором C, согласован на момент времени перед точкой
сохранения, поэтому откат к точке сохранения совершенно не затрагивает этот
курсор.
Очень интересно отметить, в сервере базы данных Oracle
10g, в некоторых случаях будет казаться, что ваш исходный код работает
нормально. Контрольный пример, представленный вами, заработает сразу же (он не
возвращает ошибку ORA-01002). Дело в том, что в сервере Oracle 10g
выполняется оптимизация PL/SQL-кода, во время которой ваши однострочные выборки
"молча" преобразуются в массовые выборки (BULK COLLECT) по 100 строк каждая. То
есть, в вашем PL/SQL-коде во время первой выборки выбирается до 100 строк,
которые затем выдаются вам, когда вы их попросите. Следовательно, в вашем
контрольном примере во время обработки первого запроса на выборку из базы данных
извлекается, фактически, весь результирующий набор, а последующие выборки просто
извлекают данные из кеша памяти PL/SQL. Я могу показать, что общее поведение
сервера Oracle 10g не изменилось (то есть, он по-прежнему делает курсор
недействительным), добавив еще 100 строк к таблице TEST. Я продемонстрирую это,
выполнив сначала ваш пример в среде Oracle 10g, а затем повторю его после
добавления 100 строк: ops$tkyte@ORA10g> declare
2 cursor c is select * from test;
3 l_rec test%rowtype;
4 begin
5 update test2 set b = 22;
6 savepoint my_savepoint;
7 for x in c
8 loop
9 rollback to
10 savepoint my_savepoint;
11 end loop;
12 end;
13 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA10g> insert into test
2 select rownum
3 from all_objects
4 where rownum <= 100;
100 rows created.
ops$tkyte@ORA10g> declare
2 cursor c is select * from test;
3 l_rec test%rowtype;
4 begin
5 update test2 set b = 22;
6 savepoint my_savepoint;
7 for x in c
8 loop
9 rollback to
10 savepoint my_savepoint;
11 end loop;
12 end;
13 /
declare
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 7
Здесь вторая "реальная" выборка (после того, как были
использованы первые 100 строк из кеша памяти PL/SQL) вызовет ошибку.
Типы данных CLOB или VARCHAR2s
Вопрос. Размер значений в столбце одно из таблиц
базы данных, которую я обрабатываю, может превышать 4 000 байтов, но это
случается редко. Я думаю, что вместо использования одного столбца типа CLOB, я
могу, возможно, использовать два столбца типа VARCHAR2(4000). Я думаю, это может
повысить производительность. Что об этом думаете вы?
Ответ. Я думаю, если размер может превышать
4 000 bytes, то скором времени он может превышать 8 000 байтов.
Я думаю, использование типа CLOB – корректный подход. Поля
CLOB размером до 4 000 байтов будут храниться в области хранения строки как
VARCHAR2. Когда этот размер будет превышен, значение типа CLOB будет перемещено
из области хранения строки. Следовательно, в вашем наиболее частом случае, когда
размер поля меньше 4 000 байтов, значение CLOB будет "маскироваться" под
VARCHAR2.
В редких случаях это будет полем CLOB, хранимым за пределами
области хранения строки.
И наконец, когда вам для поиска потребуется текстовый индекс,
у вас будет одно поле, которое легко индексировать. И когда будет решено
разрешить использование текста размером, например, до 16KБ, вы к этому будете
готовы; никаких изменений кода не потребуется. В заключение, если вы не
работаете над приложением, кодировщик, который обслуживает вас, будет иметь дело
с одним простым полем.
Локальный или глобальный индекс первичного ключа
Вопрос. У меня есть таблица счетов (accounts),
содержащая 80 миллионов записей. Я хочу секционировать ее по столбцу
ACCT_BY_DATE. Я собираюсь использовать диапазонные секции и глобальные индексы.
Меня интересует первичный ключ ACCT_ID. Должен ли индекс, который будет создан
для этого ключа, быть локальным или глобальным индексом?
Ответ. Индекс, обеспечивающий применение
первичного ключа, может быть локальным, если и только если ключ секционирования
содержится в первичном ключе. В противном случае, вы получите ошибку ORA-14039
(partitioning columns must form a subset of key columns of a unique index –
столбцы секционирования должны формировать подмножество столбцов ключа
уникального индекса).
В данном случае вы секционируете по столбцу ACCT_BY_DATE, а
первичный ключ –ACCT_ID. Индекс, использованный для обеспечения этого первичного
ключа, должен быть глобальным индексом; здесь нет никаких вариантов
использования локального индекса.
Если вы задумаетесь о структуре локального индекса, значение
ACCT_ID = 55 могло бы находиться в любой секции. Следовательно, при любой
вставке нужно было бы проверять все локальные секции, нет ли там числа 55, и
блокировать каждую секцию, чтобы гарантировать, чтобы в это время никто даже и
не пытался вставить ACCT_ID = 55. Это было бы громадным замедлителем
масштабируемости, поскольку одновременно только один человек мог вставлять,
обновлять или удалять первичный ключ.
В глобально секционированном индексе, с другой стороны,
значение 55 может находиться точно в одной секции. Рассматривайте эту секцию
так, как если бы она была обычным индексом, и вы можете одновременно выполнять
множественные операции вставки, обновления и удаления.
Планы выполнения запросов с временными таблицами
Вопрос. Я имею два запроса:
- запрос 1:соединение глобальной временной таблицы и таблицы с 3 миллионами
записей. Для выполнения запроса требуется около семи секунд;
- запрос 2: соединение обычной таблицы и таблицы с 3 миллионами записей. Для
выполнения запроса требуется меньше одной секунды.
Построив план выполнения запроса с глобальной временной
таблицей (запрос 1), я обнаружил, что выполняется полный просмотр таблицы с 3
миллионами записей. В запросе с обычной таблицей (запрос 2) полный просмотр не
выполняется.
Как уменьшить время выполнения запроса 1? Почему выполняется
полный просмотр таблицы с 3 миллионами записей, когда она соединяется с
глобальной временной таблицей, и как этого избежать?
Ответ. Основная причина здесь – отсутствие у
оптимизатора статистической информации. По умолчанию он считает, что в
глобальной временной таблице N строк (N = 8 168 в базе данных с размером
блоков, равным 8KБ). Поскольку в реальной жизни в вашей глобальной таблице редко
будет содержаться 8 168 строк, вам нужно помочь оптимизатору, обеспечив его
реалистическими статистическими данными. Перед тем, как сделать это, давайте
посмотрим, используя автотрассировку (autotrace) утилиты SQL*Plus, сколько строк
предполагается во временной таблице: 9iR2> create global temporary table
2 gtt ( x int );
Table created.
9iR2> set autotrace traceonly explain
9iR2> select /*+ first_rows */ *
2 from gtt;
Execution Plan
---------------------------------------
SELECT STATEMENT
(Cost=11 Card=8168 Bytes=106184)
TABLE ACCESS (FULL) OF 'GTT'
(Cost=11 Card=8168 Bytes=106184)
Интересно заметить, в сервере Oracle 10g вы по
умолчанию увидите в плане выполнения нечто совершенно другое: 10g> select *
2 from gtt;
Execution Plan
---------------------------------------
SELECT STATEMENT
(Cost=2 Card=1 Bytes=13)
TABLE ACCESS (FULL) OF 'GTT'
(Cost=2 Card=1 Bytes=13)
Причина заключается в том, что сервер Oracle 10g
гораздо лучше оценивает истинный размер глобальной временной таблицы. Это может
быть одним из трех вариантов вашего решения.
В сервере Oracle 10g оптимизатор по стоимости (CBO,
Cost-Based Optimizer) – единственный оптимизатор, поэтому очень важно, чтобы он
имел корректные статистические данные. Поэтому сервер базы данных использует
технологию, называемую динамической статистической выборкой (dynamic sampling),
появившуюся впервые в сервере Oracle9i Release 2. Динамическая выборка
позволяет оптимизатору "посмотреть" на таблицу, когда отсутствуют статистические
данные. Выборка данных из таблицы позволит ему оценить, с чем он имеет дело.
Итак, вам доступны три варианта решения:
- использовать динамическую статистическую выборку;
- использовать процедуру DBMS_STATS.SET_TABLE_STATS;
- использовать подсказку CARDINALITY (кардинальность).
Я поочередно продемонстрирую, как использовать каждый из них.
В сервере Oracle 10g динамическая выборка будет
работать сразу же без какой-либо настройки, поскольку уровень динамической
выборки (OPTIMIZER_DYNAMIC_SAMPLING) по умолчанию увеличен с 1 до 2. На уровне 2
оптимизатор перед построением плана выполнения будет динамически выбирать из
любого непроанализированного объекта, на который ссылается запрос,
обрабатываемый оптимизатором. В сервере Oracle9i Release 2 я для
включения такого же режима, который не включается по умолчанию, могу
воспользоваться операторами ALTER SESSION|SYSTEM или подсказкой
dynamic_sampling, как это показано ниже: 9iR2> select /*+ first_rows
2 dynamic_sampling(gtt 2) */ *
3 from gtt;
Execution Plan
----------------------------------
SELECT STATEMENT
(Cost=11 Card=1 Bytes=13)
TABLE ACCESS (FULL) OF 'GTT'
(Cost=11 Card=1 Bytes=13)
В этом запросе я для таблицы GTT установил уровень
динамической выборки, равный 2. Поэтому оптимизатор быстро отсканирует таблицу,
чтобы получить более реалистичные оценки истинного размера этой таблицы. В
следующем примере в таблицу GTT вставляются строки, а в запрос вставляется
подсказка dynamic_sampling, чтобы оптимизатор выполнил динамическую выборку из
всех непроанализированных объектов, на которые в запросе есть ссылки. Обратите
внимание на увеличение значения Card= (кардинальность): 9iR2> insert into gtt
2 select rownum
3 from all_objects;
32073 rows created.
9iR2> set autotrace traceonly explain
9iR2> select /*+ first_rows
2 dynamic_sampling(gtt 2) */ *
3 from gtt;
Execution Plan
------------------------------------
SELECT STATEMENT
(Cost=11 Card=32073 Bytes=416949)
TABLE ACCESS (FULL) OF 'GTT'
(Cost=11 Card=32073 Bytes=416949)
Другое решение, полезное в версиях сервера до Oracle9i
Release 2, – использовать процедуру DBMS_STATS для репрезентативной выборки
статистических данных глобальной временной таблицы. Вы можете делать это после
создания таблицы или после заполнения ее данными (и, следовательно, вы будете
знать, сколько строк содержится в ней). Если во время выполнения запросов размер
глобальной временной таблицы один и тот же, вы могли бы собрать эти
статистические данные только один раз, а затем всегда работать с ними. Если
размер временной таблицы колеблется в широких пределах, вы могли бы счесть
необходимым сбор этих данных после каждого заполнения таблицы. В процедуре
DBMS_STATS неявно выполняется фиксация транзакции (в этом смысле она похожа на
DDL-операторы), поэтому вы должны осторожно использовать ее. В следующем примере
демонстрируется использование прагмы AUTONOMOUS_TRANSACTION (автономная
транзакция), чтобы процедура DBMS_STATS выполнялась без фиксации вашей текущей
транзакции: 9iR2> declare
2 pragma autonomous_transaction;
3 begin
4 dbms_stats.set_table_stats
5 ( пользователь, 'GTT',
6 numrows=> 12345 );
7 commit;
8 end;
9 /
PL/SQL procedure successfully completed.
9iR2> set autotrace traceonly explain
9iR2> select * from gtt;
Execution Plan
------------------------------------
SELECT STATEMENT Optimizer=CHOOSE
(Cost=11 Card=12345 Bytes=160485)
TABLE ACCESS (FULL) OF 'GTT'
(Cost=11 Card=12345 Bytes=160485)
Заметим, оператор commit в этом примере мог бы очистить
глобальную временную таблицу, аннулировав все ваши действия над ней! Вот поэтому
здесь действительно важна прагма AUTONOMOUS_TRANSACTION.
Теперь оптимизатор полагает, что в таблице GTT содержится
12 345 строк, и будет использовать этот факт во время оптимизации запросов,
которые ссылаются на эту таблицу.
Третье решение – подсказка CARDINALITY. Я включаю это решение,
поскольку оно – единственное решение, когда используются переменные
типа-коллекции (не глобальные временные таблицы, а, скорее, таблицы в
оперативной памяти, содержащиеся в коллекциях PL/SQL). Сервер базы данных
динамически не собирает их статистические данные, и они не являются реальными
таблицами, так что никаких их статистических данных не может быть сохранено.
Единственный способ сообщить серверу базы данных оцененный размер объектов этого
типа – использовать эту подсказку, как в этом примере: 9iR2> select
2 /*+ cardinality( gtt 999 ) */ *
3 from gtt;
Execution Plan
-----------------------------------
SELECT STATEMENT Optimizer=CHOOSE
(Cost=11 Card=999 Bytes=12987)
TABLE ACCESS (FULL) OF 'GTT'
(Cost=11 Card=999 Bytes=12987)
Здесь я явно указал оптимизатору, сколько строк он мог бы
предположительно обнаружить в моей глобальной временной таблице. Заметим,
подсказка CARDINALITY доступна только в сервере Oracle9i Release 1 и
более поздних версий. См. в врезке "Следующие шаги" ссылку на полный пример,
демонстрирующий этот метод для PL/SQL-таблиц.
Что в конце файла?
Вопрос. Я имею локально управляемое табличное
пространство, содержащее приблизительно 1 000 объектов (500 таблиц и 500
индексов). Табличное пространство состоит из трех файлов данных (3 * 4ГБ). После
некоторой реорганизации один файл данных оказался почти пустым, но при попытке
изменения его размера возникает ошибка, потому что экстенты некоторых объектов
находятся в конце файла данных. Существует ли простой способ обнаружения
объектов, которым принадлежат эти экстенты?
Ответ. Разобраться в этом, на самом деле,
довольно просто. Все, что вам нужно, – имя этого файла данных. Имея его, вы
можете превратить имя файла в его идентификатор FILE_ID и, используя этот
идентификатор, выполнить запрос к представлению DBA_EXTENTS, чтобы найти все
экстенты, выделенные в этом файле. Если вы отсортируете этот результат по
столбцу BLOCK_ID (в убывающем порядке), первые N возвращенных строк покажут
экстенты, находящиеся в конце файла (которые мешают вам сжать файл данных).
Такие объекты вы можете переместить (используя операторы ALTER TABLE MOVE для
таблиц и ALTER INDEX REBUILD для индексов), что позволит сжать этот файл. Запрос
выглядит так: select *
from (
select owner, segment_name,
segment_type, block_id
from dba_extents
where file_id =
( select file_id
from dba_data_files
where file_name = :FILE )
order by block_id desc
)
where rownum <= 5
Этот запрос находит пять наиболее удаленных от начала файла
экстентов. Интересный сопутствующий запрос я называю "максимальное_сжатие". Он
генерирует операторы ALTER DATABASE для сжатия файлов данных до наименьшего
возможного размера:
column value new_val blksize
select value
from v$parameter
where name = 'db_block_size'
/
select 'alter database datafile ''' ||
file_name || ''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )
|| 'm;' cmd
from dba_data_files a,
( select file_id,
max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and
ceil(blocks*&&blksize/1024/1024)-
ceil((nvl(hwm,1)*
&&blksize)/1024/1024 ) > 0
/
Заказной аудит
Вопрос. В одной из моих сред, находящихся в
промышленной эксплуатации, после вставки в таблицу записи с полем
valid_ind='Y', некоторый код или программа использует оператор
UPDATE, устанавливающий в этой же записи
valid_ind='N'. Существует ли способ определения, какой процесс ОС,
или пользователь базы данных, или SQL-оператор выполняет это обновление? Мы
десять раз просмотрели весь код от и до, но так и не поняли, откуда берется этот
оператор обновления. Может, вы чем поможете?
Ответ. Это довольно просто. Вы можете для этой
таблицы создать триггер AFTER UPDATE, FOR EACH ROW, который содержит код,
похожий на этот: if ( :old.valid_ind = 'Y'
and :new.valid_ind = 'N' )
then
insert into my_log
select sysdate, a.*
from v$session a
where sid = (select sid
from v$mystat
where rownum = 1);
end if;
Заметим, владельцу триггера нужно предоставить от имени
пользователя SYS привилегию DIRECT для доступа к представлениям V_$SESSION и
V_$MYSTAT (символы подчеркивания важны). См. в врезке "Следующие шаги" ссылку на
описание технической причины, почему эту привилегия нужно предоставлять
непосредственно.
После создания этого триггера вы будете записывать в
протокольную таблицу всю информацию уровня сеанса (включая имя пользователя, имя
программы, используемый терминал ОС и т.д.). Эту таблицу легко создать: create table my_log
as
select sysdate dt,
a.*
from v$session a
where 1=0; n
Ведущий данной колонки Том Кайт (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 г.).
| 
