Том Кайт: об измерении расстояния и "старении

(

On Measuring Distance and Aging

By Tom Kyte

)

Источник

Наш эксперт вычисляет расстояние и обозначает ключевые функциональные возможности СУБД Oracle9i.

Я создал PL/SQL-функцию для вычисления расстояния между двумя точками, но когда я вызываю ее из SQL, на ее выполнение затрачивается более получаса. Вот эта функция:

create or replace function f_dist

(in_lat1 in number, in_long1 in number,

in_lat2 in number, in_long2 in number)

return number

begin

return 3959 * ( acos (

(sin(in_lat1/57.3)*

sin(in_lat2/57.3))

(cos(in_lat1/57.3)*

cos(in_lat2/57.3)*

cos(abs(in_long2)/57.3

- abs(in_long1)/57.3))

) );

exception

when others then return 9999;

end f_dist;

Для вызова этой функции я использую SQL-запросы, похожие на следующий:

select substr(Z2.ZIPP, 1, 7) ZIP

from A PT, B TA, C Z2

where (PT.REGION || PT.AREA ||

PT.DISTRICT || PT.LOCATION) = :str

and PT.LOCATION_ID = TA.LOCATION_ID

and Z2.AVGLAT <

(TA.LATITUDE + ( :dist / 69 ))

and Z2.AVGLAT >

(TA.LATITUDE - ( :dist / 69 ))

and Z2.AVGLONG <

(TA.LONGITUDE + ( :dist / 47 ))

and Z2.AVGLONG >

(TA.LONGITUDE - ( :dist / 47 ))

and F_DIST(TA.LATITUDE, TA.LONGITUDE, i

Z2.AVGLAT, Z2.AVGLONG) <= :dist;

Если я не использую PL/SQL-функцию, а непосредственно выполняю функции acos, sin и т.д., на это затрачивается приблизительно 15 секунд. Мои вопросы: 1) почему вызовы PL/SQL-функции выполняются намного медленней, 2) существуют ли способы ускорения этого процесса?

Это интересный вопрос. После небольшого исследования (для этого я получил реальные данные), я обнаружил, что для данного набора данных выполнялось 612,296 вызовов PL/SQL-функции F_DIST. Для выполнения этого запроса в моей системе (она похожа на исходную систему) потребовалось около 2,083 секунд, это означает, что я вызывал PL/SQL-функцию 294 раза в секунду. То есть, за каждую секунду происходило 294 контекстных переключений из машины SQL на машину PL/SQL. На каждый внешний вызов требовалось не более 0.003 секунд, что представляет собой незначительное время, но когда вы выполняете его 612,296 раз, оно суммируется! Вы всегда можете выполнять код непосредственно в SQL-среде, и это почти всегда будет быстрее неоднократных вызовов PL/SQL-функций.

Что касается второго вопроса – ускорение выполнения – я думаю, мы можем сделать это. И не только ускорить выполнение, но и сделать это более просто. В СУБД скрыто много функциональных возможностей для работы с расширенными типами данных: XML, тексты, изображения, аудио- и видеоданные и даже пространственные данные.

Итак, я попросил нашего местного эксперта по обработке пространственных данных Кларка Коломба (Clarke Colombo) рассмотреть эту проблему, и он предложил расширить таблицы, добавив к ним столбцы с пространственными данными, а потом использовать их в запросах. Вот код, предложенный Кларком:

alter table b add

(geometry mdsys.sdo_geometry);

update b a

set a.geometry =

mdsys.sdo_geometry(2001,null,

mdsys.sdo_point_type(a.longitude,

a.latitude,

null),

null, null)

where latitude is not null;

insert into user_sdo_geom_metadata

values ('B','GEOMETRY'

mdsys.sdo_dim_array(

mdsys.sdo_dim_element

('X',-180,180,.00005),

mdsys.sdo_dim_element

('Y',-90,90,.00005)), null);

create index b_sidx on b(geometry)

indextype is mdsys.spatial_index;

Этот код предназначен для таблицы B из вашего вопроса. Для таблицы C Кларк использовал этот же код – он добавил геометрический столбец, зарегистрировал его в таблице USER_SDO_GEOM_METADATA, а затем создал по нему пространственный индекс. Теперь запрос Кларка для вычисления пространства становится простым:

select substr(Z2.ZIPP, 1, 7) ZIP

from A PT, B TA, C Z2

where (PT.REGION || PT.AREA ||

PT.DISTRICT || PT.LOCATION) = :str

and PT.LOCATION_ID = TA.LOCATION_ID

AND MDSYS.LOCATOR_WITHIN_DISTANCE

(Z2.AVG_GEOM, TA.GEOMETRY,

'distance = '||:dist||',units=mile')

= 'TRUE';

Кларк обнаружил, что этот запрос выполняется за 1 секунду вместо 15 секунд, когда используется непосредственный вызов функций в SQL-среде.

Слишком старая моментальная копия?

Я хочу удалить все индексы из моей схемы, поэтому я пробую выполнить следующий код:

begin

for i in (select index_name

from user_indexes)

loop

execute immediate

'drop index '||

i.index_name||' ';

end loop;

end;

Я должен удалить примерно 500 индексов, но в середине процесс удаления выдается ошибка ORA-01555. Когда я перезапускаю код, удаляются оставшиеся индексы. Я не понимаю этой ошибки. Для чего нужны сегменты отката при выполнении операторов удаления индексов? Пожалуйста, объясните этот механизм.

Хорошо, сначала я должен объяснить, почему возникает ошибка ORA-01555 "snapshot too old" (слишком старая моментальная копия), а потом я смогу описать, что происходит у вас. Если кратко, когда СУБД Oracle выполняет запрос, такой, как ваш запрос select index_name from user_indexes, она использует механизм согласованного чтения (consistent-read mechanism). Он использует информацию для отката транзакций в вашем сеансе или в других сеансах, которая создается во время выполнения операторов вставки, обновления и удаления данных. Так, когда сервер Oracle читает блок данных для вашего запроса, ему также может потребоваться чтение некоторой информации отката. Для подробного изучения этого процесса я рекомендую прочитать книгу

Oracle9i Database Concepts

у меня на странице asktom.oracle.com/~tkyte/mvrc.html есть ссылка на точное место в этой книге. MVRC означает "multiversioning and read consistency" (многоверсионность и согласованность чтения) – две наиболее важные концепции СУБД Oracle.

Во время обычного выполнения вашего запроса Oracle читает некоторую информацию отката. Ошибка ORA-01555 возникает тогда, когда информация отката, требуемая для выполнения вашего запроса, уже не существует, потому что она была перезаписана. Информация отката (хранится в табличном пространстве отката (undo tablespace) или в сегментах отката (rollback segments), управляемых вручную) сохраняется только на время выполнения транзакции, которая ее сгенерировала. После фиксации этой транзакции Oracle может повторно использовать выделенное пространство, перезаписывая информацию отката. Если вы правильно установили размер табличного пространства отката (сегментов отката), Oracle не будет немедленно перезаписывать эту информацию отката, а будет некоторое время сохранять ее. Это позволяет запросам, которым требуется информация отката, иметь доступ к ней.

Ошибка ORA-01555 возникает из-за недостаточного размера пространства отката, требуемого для работы вашей системы. Oracle циклически использует пространство отката, перезаписывая информацию, которая нужна какому-то активному запросу. Одна из наиболее частых причин возникновения ошибки ORA-01555 – плохая привычка фиксации транзакций в курсорном цикле FOR LOOP, таком, как следующий:

For x in ( select *

from emp )

loop

... обработка записей ...

update emp set ...

where empno = x.empno;

commit;

End loop;

Выполняя обновление вы генерируете информацию отката для таблицы EMP. В то же самое время, выполняя фиксацию транзакции сразу после обновления, вы говорите Oracle: "все OK, можно повторно использовать пространство отката". Проблема заключается в том, что вашему собственному запросу к таблице EMP требуется информация отката, чтобы получить согласованное по чтению представление таблицы EMP. Фиксируя транзакции в цикле, вы освобождаете ресурсы, которые требуются вам самим. Такой стиль кодирования поистине "испрашивает" ошибку ORA-01555.

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

Фиксации при выполнении DDL-операторов. Ваш оператор удаления индекса фактически является и оператором COMMIT.

DDL-операторы модифицируют таблицы словаря данных, при удалении индекса удаляются данные из словаря данных. Вы выполняете запросы к тому же словарю данных.

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

declare

l_index_name varchar2(30);

begin

loop

select index_name into l_index_name

from user_indexes

where rownum=1;

execute immediate 'drop index ' ||

l_index_name;

end loop;

exception

when no_data_found then null;

end;

Вы уже не выполняете фиксации в курсорном цикле FOR LOOP; вы просто выполняете запрос для поиска индекса для удаления, удаляете его, а затем выполняете другой запрос, и повторяете все это многократно, пока не будут обработаны все данные. Это не будет генерировать ошибку ORA-01555, так как продолжительность запроса мала и вы во время фиксации не имеете открытых курсоров.

Если вы поищете "ORA-01555" на сайте asktom.oracle.com, вы найдете массу информации об ошибке "слишком старая моментальная копия", а также разборы других конкретных случаев.

Время сохранения информации отката

Я слышал, что параметр UNDO_RETENTION (время сохранения информации отката) является только "директивным" и пространство отката используется повторно, если табличное пространство отката переполняется (независимо от значения параметра UNDO_RETENTION). Правда ли это?

Параметр UNDO_RETENTION применим только в СУБД Oracle9

i

Release 1 и следующих версиях, поскольку он воздействует на табличные пространства отката, доступные в Oracle9

i

. Параметр UNDO_RETENTION задает, как долго СУБД Oracle должна пытаться сохранять на диске информацию отката до ее повторного использования. Это помогает снижать вероятность возникновения ошибки ORA-01555, рассмотренной в предыдущем разделе. Если информация отката сохраняется так долго, что пользователь, например, может удалить 500 индексов, то ошибка ORA-01555 не возникнет. Кроме того, параметр UNDO_RETENTION позволяет АБД сохранять информацию отката для поддержки механизма ретроспективных запросов (Flashback query). Если требуется, чтобы пользователи могли восстанавливать данные, измененные за последние 3 часа, это и будет подходящим значением этого параметра.

Теперь вернемся к вопросу: "является ли период сохранения информации отката обязательным для соблюдения или рекомендательным?". Он фактически является рекомендацией. Если сервер Oracle имеет достаточное пространство или может расширять табличное пространство отката до достаточного размера, он не будет повторно использовать информацию отката до истечения периода ее сохранения. С другой стороны, Oracle не будет причиной сбоев транзакций только из-за того, что для обеспечения периода сохранения было недостаточно пространства. Oracle, если потребуется, будет преждевременно перезаписывать информацию отката, сгенерированную фиксированными транзакциями. Это можно проверить с помощью простого теста. Я создам два табличных пространства отката: UNDO_BIG и UNDO_SMALL. UNDO_BIG будет иметь начальный размер, равный 1МБ и будет расширяться до 2ГБ. UNDO_SMALL будет иметь начальный размер, равный 1МБ и не будет расширяться. Я буду выполнять транзакции, поочередно используя эти табличные пространства, и проверю их окончательный размер. Я буду использовать в параметре UNDO_RETENTION значение, равное 10,800 (три часа). Посмотрим, что получилось:

SQL> create undo tablespace

2 UNDO_BIG datafile

3 size 1m autoextend on next 1m

4 maxsize 2048m;

Tablespace created.

SQL> create undo tablespace

2 UNDO_SMALL datafile

3 size 1m autoextend off;

Tablespace created.

SQL> show parameter undo_retention

NAME VALUE

---------- ------------

undo_retention 10800

SQL> alter system

2 set undo_tablespace = UNDO_BIG

3 scope = memory;

System altered.

SQL> drop table t;

Table dropped.

SQL> create table t

2 ( x char(2000),

3 y char(2000),

4 z char(2000)

5 );

Table created.

SQL> insert into t values('x','x','x');

1 row created.

SQL> begin

2 for i in 1 .. 500

3 loop

4 update t set x=i,y=i,z=i;

5 commit;

6 end loop;

7 end;

8 /

PL/SQL procedure successfully completed.

SQL> select bytes,maxbytes

2 from dba_data_files

3 where tablespace_name = 'UNDO_BIG';

BYTES MAXBYTES

------- -----------

5242880 2147483648

Здесь показано, что я сгенерировал около 5МБ информации отката. Мы знаем, что табличное пространство UNDO_BIG было только что создано, а его начальный размер был равен 1МБ. Тот факт, что сейчас размер файла данных увеличился до 5МБ показывает, что сервер Oracle расширил его для сохранения информации SQL в течение предложенных трех часов. Теперь я буду использовать небольшое табличное пространство UNDO_SMALL и попробую снова:

SQL> alter system

2 set undo_tablespace = UNDO_SMALL

3 scope = memory;

System altered.

SQL> show parameter undo_t

NAME VALUE

----------- ---------

undo_tablespace UNDO_SMALL

Затем я выполнил те же самые операторы DROP TABLE, CREATE TABLE и INSERT INTO TABLE, а потом так же выполнил 500 обновления. Результат, однако, оказался другим:

SQL> select bytes,maxbytes

2 from dba_data_files

3 where tablespace_name = 'UNDO_SMALL';

BYTES MAXBYTES

------- ---------

1048576 0

Сервер Oracle не расширял мой файл данных (ему это было не разрешено), но он также не допустил аварийного завершения моих транзакций. Вместо этого, Oracle преждевременно перезаписывал информацию отката, которая уже не требовалась для возможных откатов. Заметим, преждевременная перезапись информации отката может стать причиной возникновения ужасной ошибки ORA-01555, которая является индикатором, что размер вашего табличного пространства отката слишком мал для вашей работы. Вы можете воспользоваться столбцом UNXPBLKREUCNT динамического представления производительности V$UNDOSTAT, чтобы проверить, сколько неустаревших блоков было преждевременно перезаписано в табличном пространстве отката. Когда я сделал это для данного теста, я обнаружил, что в табличном пространстве UNDO_SMALL было преждевременно перезаписано 413 блоков – 3.3МБ в моей системе.

Как долго будет продолжаться откат транзакции?

Когда в моем сеансе выполняется откат транзакции из-за какой-то ошибки (обычно, когда сегмент отката или табличное пространстве отката не могут расширяться), каков лучший способ определения, выполняется ли откат, и когда он закончится? Я использую запрос SELECT USED_UBLK FROM V$TRANSACTION для определения, сколько блоков отката выделено транзакции для ее изменений. Если данное число уменьшается, то это указывает, что выполняется откат транзакции. Является ли этот подход предпочтительным или существуют лучшие? Кроме того, как мне узнать, сколько блоков отката транзакция имела в начале процесса отката, чтобы я мог оценить, сколько еще будет продолжаться откат?

USED_UBLK – это тот столбец, который вам нужен. Вы можете также проверять столбец FLAG в представлении V$TRANSACTION. Если вы попробуете выполнить оператор select to_char(flag,'0000000X') from v$transaction и проверите предпоследний символ в результате, значение 8 весьма правдоподобно будет указывать на откат, а 0 – на "нормальную" транзакцию. Так, 00001E8E, например, указывает на откат, а 00001E03 –нормальную транзакцию.

Что касается вашего последнего вопроса, вам не нужно знать, сколько блоков использовалось в начале отката. Вам просто нужно знать следующее:

сколько блоков используется сейчас (used_now);

сколько блоков используется, скажем, через 60 секунд (used_later).

Теперь вычислим: (used_later / (used_now - used_later)), и это будет приблизительно равно количеству минут, оставшихся до завершения процесса отката. (Конечно, здесь не будет 100-процентной точности, но это достаточно правдоподобная оценка.)

Так, если значение столбца USED_UBLK равно 500 блокам, а через 60 секунд – 400 блокам, вы можете приблизительно оценить, что 400/(500-400) = 4 минутам, и это будет – оставшимся временем отката.

Преобразование шестнадцатиричных чисел

Есть ли в СУБД Oracle функция или простой способ преобразования шестнадцатиричных чисел в десятичные или десятичных в шестнадцатиричные?

В действительности, начиная с Oracle8

i

Release 8.1.5, мы можем это делать. Это – простые форматные маски для чисел в функциях to_number и to_char. Для преобразования шестнадцатиричных чисел в десятичные вы можете использовать запрос select to_number( 'AA', 'xx' ) from dual; для преобразования десятичных в шестнадцатиричные – запрос select to_char( 111, 'xxxx' ) from dual.

Предугадывая другой вопрос ("Как насчет других оснований систем счисления, таких, как двоичное, восьмеричное и т.д."?), отвечаю, Oracle "прямо" не выполняет такие преобразования, но у меня есть набор PL/SQL-функций, которые выполняют такие преобразования. Информацию об этом см. на странице asktom.oracle.com/~tkyte/hexdec/index.html.

Что нового?

Не могли бы вы помочь мне прокомментировать нижеследующие замечания (представленные моим коллегой относительно наращивания функциональных возможностей в Oracle9i для приложений хранилищ данных):

В СУБД Oracle9i имеется масса "новых функциональных возможностей", которые корпорация Oracle "спакетировала" из других продуктов, например, OLAP-сервер, который представляет собой просто старый Express. Я также думаю, Oracle Warehouse Builder – старый продукт, глубинный анализ данных (data mining) – просто алгоритмы, а не визуализация. Существует не так много возможностей, которые действительно являются новыми, исключая некоторые дополнительные возможности оптимизации производительности (битовые индексы соединений), совершенствования функций LEAD и LAG (одновременный доступ к нескольким строкам таблицы без выполнения самосоединения) и секционирования.

Хорошо, сервер оперативной аналитической обработки (OLAP- сервер) в Oracle9

i

– это не старый продукт Express. Это – разные "световые годы". Express означал: "загрузите ваши данные из базы данных Oracle в эту другую базу данных и используйте другой язык для ее чтения, сопровождения, защиты, резервирования и т.д.". OLAP означает: "соединитесь с базой данных Oracle и все данные будут готовы для их реляционной обработки или для обработки как OLAP-гиперкуба". Если ваш коллега думает, что это – просто Express с новым именем, то сейчас самое время изменить свой взгляд, и otn.oracle.com будет подходящим местом, чтобы начать изучение с него.

А называть средства глубинного анализа данных "просто алгоритмами" – это правильно, конечно, но не относится ли это ко всему программному обеспечению? А визуализация выполняется с помощью Oracle9

i

JDeveloper. Переходя к ядру самой СУБД, коротко перечислим новые функциональные возможности Oracle9

i

сжатые таблицы. Производят сильное впечатление. Потенциально большая экономия пространства;

секционирование по списку значений ключей;

внешние таблицы. Параллельная загрузка данных в прямом режиме на уровне SQL. Мне нравится использование этой возможности в хранилищах данных;

оператор MERGE (обновление со вставкой), многотабличные вставки;

загрузка данных в прямом режиме с помощью SQL-функций;

суммарная память (aggregate target) программной глобальной области (PGA, Program Global Area). Больше нет размера области сортировки, размера области кеширования и других подобных размеров;

конвейерные (pipelined) функции;

предложение WITH (внешнее предложение подзапроса) в операторах select. Позволяет при выполнении запросов уменьшать количество материализаций подзапросов или вложенных представлений;

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

компиляция процедур PL/SQL в машинный код, выполняемый на уровне операционных систем;

ассоциативные массивы для выполнения поиска или очистки данных (lookups/scrubbing). Табличные PL/SQL-типы, в которых индекс – строка. Так, например, конструкция state_name := state_lookup( 'AK' ) допустима и быстро работает;

Oracle9

i

Real Application Clusters (RAC). Используйте Oracle9

i

RAC, если для вас важны отказоустойчивость и масштабируемость, достигаемые добавлением к вашему кластеру дополнительных компьютеров, а также заменой небольших компьютеров на более мощные;

выполнение всех операций в режиме реального времени. Выполняйте оперативную реконструкцию и реорганизацию;

восстановление на уровне блоков. Используйте, если в вашем файле данных размером 32ГБ "повреждается" один блок размером 16КБ, а база данных требуется для работы;

существенные совершенствования Resource Manager (диспетчер ресурсов). Определяйте группы активных пользователей, назначайте им обоснованные доли потребления ресурсов;

битовые индексы соединений;

битовые индексы индекс-таблиц (IOT, index-organized table);

базы данных с блоками разного размера. Переносите наборы уже сформатированных таблиц и индексов из OLTP-систем с размером блоков, равным 4КБ, в ваше хранилище данных и выполняйте извлечение, преобразование и загрузку данных (ETL-процесс, extraction, transformation and loading) с помощью операторов типа create table as select or insert /*+ append */, используя сложные запросы к OLTP-данным. (В вашем хранилище данных размер блоков будет равен 16КБ или 32КБ, а в вашей OLTP-системе – 2, 4 или 8KБ, так что, это будет уместно.);

детальный аудит (Fine-grained auditing). Позволяет определять нарушения политики безопасности и реагировать на них в реальном масштабе времени. Полезен также для анализа производительности;

возобновляемое выделение пространства. Если вы используете Oracle9

i

, например, для повторного создания битового индекса размером 15ГБ и получаете ошибку, связанную с выделением пространства, вы можете приостановить выполнение оператора, исправить проблему (например, добавить файл к временному табличному пространству), а затем продолжить создание оставшейся части индекса;

мониторинг индексов. Действительно ли кто-то использует индекс?

обновление глобальных индексов. Вместо того чтобы делать глобальные индексы недействительными, требующими повторного создания, они обновляются во время реорганизации секций;

оперативное изменение размера системной глобальной области (SGA). Добавляйте буфера блоков и удаляйте их;

консультативные справки по кешу буферов, разделяемому пулу и т.д. Новые V$-таблицы показывают вам, например, что если вы на столько-то увеличите размер кеша буферов, то получите на n процентов больше логических операций ввода-вывода.

< Предыдущая		Следующая >

DeepEdit!