31 июля 2008 г.

Параметр CACHE LOB-сегмента

На прошлой неделе при поддержке запуска одной системы, столкнулась с резким снижением производительности в системе. Администраторы приложения доложили, что процессы еле-еле шевелятся. Я начала копать, правда не сразу докопалась до истины. Большинство сессий этих процессов ожидали объекта SYS_LOB0000086738C00025$$ - LOB сегмента по одному из столбцов (скажем PAYMENT_INFO) одной таблицы (скажем PAYMENT). Содержимое столбца извлекались этим запросом:

select PAYMENT_INFO
from PAYMENT
where PAYMENT_ID = :1 AND PAYMENT_TYPE = :2
for update;
Выяснилось, что сегмент SYS_LOB0000086738C00025$$ имеет параметр NOCACHE в отличие от аналогичной базы данных, в котором этот же LOB-сегмент имеет параметр CACHE. Вот, что пишут в документации Oracle об этом параметре LOB-сегментов:
CACHE, NOCACHE

Definition

The CACHE storage parameter causes LOB data blocks to be read/written via buffer cache.

With the NOCACHE storage parameter, LOB data is read/written using direct reads/writes. This means that the LOB data blocks are never in the buffer cache and the Oracle server process performs the reads/writes.
Если честно, до этого не знала, что по умолчанию, LOB сегменты читаются в обход буфферного кеша. А установкой параметра LOB-сегмента в CACHE, можно попросить Oracle, чтоб он извлекал блоки этого сегмента через буфферный кеш, что увеличивает производительность системы. Только нужно быть осторожным с LOB-сегментами больших размеров, чтоб они не забили весь буферный кеш. Там же пишут, что использование параметра CACHE вместо NOCACHE улучшает производительность операций ввода-вывода:
The CACHE option gives better read/write performance than the NOCACHE option.
В нашем случае, изменения параметра LOB-сегмента в CACHE помогло:
alter table PAYMENT modify lob ("PAYMENT_INFO") (cache);
Прежние ожидания исчезли и приложение вернулось на прежний высокий уровень производительности. Обожаю хеппи енды.

Working days between two dates

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

MONTHS_BETWEEN
Calculates the number of months between two dates.

В инете поискала такие самописные функции, которые бы считали количество месяцев между двумя датами. Больше всех понравилась этот метод подсчета, описанная в Tips of the Week на сайте Oracle, мне кажется он самый оптимальный из тех, что я посмотрела:

create table date_test (start_dt date, end_dt date);

select start_dt,
end_dt,
trunc(end_dt - start_dt) age,
(trunc(end_dt - start_dt) - ((case
WHEN (8 - to_number(to_char(start_dt, 'D'))) >
trunc(end_dt - start_dt) + 1 THEN 0
ELSE
trunc((trunc(end_dt - start_dt) - (8 - to_number(to_char(start_dt, 'D')))) / 7) + 1
END) + (case
WHEN mod(8 - to_char(start_dt, 'D'), 7) >
trunc(end_dt - start_dt) - 1 THEN
0
ELSE
trunc((trunc(end_dt - start_dt) -
(mod(8 - to_char(start_dt, 'D'), 7) + 1)) / 7) + 1
END))) workingdays
from date_test

Здесь он считает суммарное количество суббот и воскресений и отнимает от общего количества дней между двумя датами. Правда, глубоко вникать не стала, на тествых датах работает правильно. Можно переписать в функцию:
create or replace function working_days_between (start_dt in date, end_dt in date) return number is
wdays number;
begin
select (trunc(end_dt - start_dt) - ((case
WHEN (8 - to_number(to_char(start_dt, 'D'))) >
trunc(end_dt - start_dt) + 1 THEN 0
ELSE
trunc((trunc(end_dt - start_dt) - (8 - to_number(to_char(start_dt, 'D')))) / 7) + 1
END) + (case
WHEN mod(8 - to_char(start_dt, 'D'), 7) >
trunc(end_dt - start_dt) - 1 THEN 0
ELSE
trunc((trunc(end_dt - start_dt) - (mod(8 - to_char(start_dt, 'D'), 7) + 1)) / 7) + 1
END))) into wdays from dual;
return wdays;
end working_days_between;

Сравнение двух схем

Недавно столкнулась с необходимостью сравнить объекты двух схем, что-то влом было самой писать скрипты по сравнению, поэтому поискала готовые методы сравнения:
1) Сравнение схем с помощью Oracle Change Manager

Сегодня еще рассказали, что в Тоаде тоже есть такая возможность:
2) С помощью Toad for Oracle
Правда начиная с поздних версий, например в 9.1 точно есть, а в 6.4 - нет.
Database -> Compare -> Schemas

23 июля 2008 г.

"Кем была вызвана процедура или функция?"

Мой коллега нашел метод позволяющий в процедуре узнать кем она была вызвана, например, в таком примере:

Есть 3 процедуры: процедура 1, процедура 2 и процедура 3
При этом процедура 3 вызывается как из процедуры 1, так и из процедуры 2
Может ли процедура 3 не используя входные параметры понять из какой процедуры она вызвана, из 1-й или 2-й?
Он нашел функцию пакета dbms_utility.format_call_stack, который оказывается возвращает полную цепочку процедур, функций или анонимного PL/SQL блока, которая вызвала эту процедуру:
----- PL/SQL Call Stack -----
object line object
handle number name
3902e1880 4 procedure MYSCHEMA.PROC3
3902e8470 3 procedure MYSCHEMA.PROC1
3902d3400 2 anonymous block
В этом примере, анонимный блок вызвал процедуру PROC1, а PROC1 вызвала процедуру PROC3.
Процедура 3, которая вызывается процедурой 1 или 2:
create or replace procedure proc3 is
call_stack varchar2(4096);
begin
call_stack := dbms_utility.format_call_stack;
dbms_output.put_line(call_stack);
end;
Процедуры 1 и 2, которые вызывают процедуру 3:
create procedure proc1 is
begin
proc3;
end;

create procedure proc2 is
begin
proc3;
end;
Вызов процедур 1 и 2:
SQL> exec proc1;

----- PL/SQL Call Stack -----
object line object
handle number name
3902e1880 4 procedure MYSCHEMA.PROC3
3902e8470 3 procedure MYSCHEMA.PROC1
390175a80 1 anonymous block

PL/SQL procedure successfully completed

SQL> exec proc2;

----- PL/SQL Call Stack -----
object line object
handle number name
3902e1880 4 procedure MYSCHEMA.PROC3
3902db938 3 procedure MYSCHEMA.PROC2
390180160 1 anonymous block

PL/SQL procedure successfully completed
Только, из стэка вызывающих процедур и функций нужно вытащить то, что нужно. В данном случае вторую строку стэка. У Тома Кайта есть процедура who_called_me, которая парсит этот стэк и возвращяет кем была вызвана процедура или функция.

22 июля 2008 г.

Мои второй тренинг

Вчера во второй раз прочитала свой тренинг SQL Tuning Workshop, правда только одну треть своего тренинга. Мне показалось, что мне уже легче читается, ведь после первого тренинга много чего добавила и улучшила. Темы были такие:

  • Краткое описание архитектуры Oracle
  • Шаги выполнения SQL запросов
  • Совместное использование курсоров
  • Построение и чтение плана выполнения
  • Трассировка, чтение трассировочного файла
  • Форматирование файла трассировки утилитой TKPROF, чтение отформатированного файла.
Теперь, своей первой группе прочитаю вторую часть тренинга и сделаю выводы о том, что можно улучшить.

11 июля 2008 г.

Дамп структуры индекса

Иногда очень полезно посмотреть на внутреннюю структуру индекса. Для этого можно выгрузить индекс в дамп следующей командой:

alter session set events 'immediate trace name treedump level nnnn;

где nnnn равен object_id индекса, который можно посмотреть в dba_objects:

SQL> column object_name format A15
SQL> select object_id, object_name
from dba_objects a
where owner = 'SCHEMA' and
object_type = 'INDEX' and
object_name = 'CUSTOMERS_PK';

OBJECT_ID OBJECT_NAME
---------- ---------------
33243 CUSTOMERS_PK

SQL> alter session set events 'immediate trace name treedump level 33243';

Session altered.
Последняя команда выгрузит структуру указанного индекса в файл в папку user_dump_dest, который обычно указывает на $ORACLE_BASE/admin/SID/udump.
Содержимое файла выглядит приблизительно так (заголовок не включен):
----- begin tree dump
branch: 0x2402004 37756932 (0: nrow: 104, level: 1)
leaf: 0x2402005 37756933 (-1: nrow: 517 rrow: 517)
leaf: 0x2402006 37756934 (0: nrow: 514 rrow: 514)
leaf: 0x2402007 37756935 (1: nrow: 481 rrow: 481)
leaf: 0x2402008 37756936 (2: nrow: 481 rrow: 481)
leaf: 0x2403309 37761801 (3: nrow: 481 rrow: 481)
...
leaf: 0x240336d 37761901 (97: nrow: 481 rrow: 481)
leaf: 0x240336e 37761902 (98: nrow: 482 rrow: 482)
leaf: 0x240336f 37761903 (99: nrow: 481 rrow: 481)
leaf: 0x2403370 37761904 (100: nrow: 481 rrow: 481)
leaf: 0x2403372 37761906 (101: nrow: 481 rrow: 481)
leaf: 0x2403373 37761907 (102: nrow: 377 rrow: 377)
----- end tree dump
По дампу видно, что индекс состоит из одного корневого блока и 104 листовых блоков (с -1 до 102 включительно). Строки начинающиеся на branch - это либо корневой блок, либо блоки ветвления. Листовые блоки начинаются на leaf. Вторая цифра после него 37756932 - это адрес блока. Используя этот адрес блока, можно получить id файла и блока функциями пакета dbms_utility и выгрузить содержимое конкретных блоков индекса. Об этом чуть позже. Дальше, nrow - это суммарное количество ключей или строк включая строки, которые отмечены как удаленные, а rrow - количество ключей или строк.

В этом дампе мы увидели общую структуру индекса и сколько ключей хранятся в каждом узле индекса, но в ней нет самих значений ключей. Я пока не видела одношагового решения для выгрузки всех узлов вместе со значениями ключей, но можно выгрузить содержимое отдельного узла, то есть блока индекса следующим образом:
SQL> select dbms_utility.data_block_address_file(37756932) file_id,
dbms_utility.data_block_address_block(37756932) block_id
from dual;

FILE_ID BLOCK_ID
---------- ----------
9 8196

SQL> alter system dump datafile 9 block 8196;

System altered.

10 июля 2008 г.

Index Skip Scan

Думаю, об индексах в Oracle можно писать и писать. Индексы - это одна из моих любимых тем по базам данных Oracle. До 9 версии Оракла, при поиске по составному индексу не использовался индекс если в запросе не было поиска по лидирующему (leading) столбцу индекса. Поэтому и рекомендовалось в качестве лидирующего столбца выбирать столбец наиболее часто использующийся для поиска.

В 9 версии появился новые индексный доступ INDEX SKIP SCAN, который позволяет использовать составной индекс для поиска данных, даже если в условиях поиска отсутствует лидирующий столбец.

SQL> create table t (a number, b number);

Table created.

SQL> create index t_ix on t(a, b);

Index created.

SQL>
SQL> begin
2 for i in 1 .. 10000
3 loop
4 insert into t values(mod(i, 2), i);
5 end loop;
6 commit;
7 end;
8 /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL> begin
2 dbms_stats.gather_table_stats(user, 'T', cascade=>true);
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> explain plan set statement_id='SKIP'
2 for select * from t where b=5;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display('PLAN_TABLE','SKIP', 'ALL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------


--------------------------------------------------------------------
Id Operation Name Rows Bytes Cost
--------------------------------------------------------------------
0 SELECT STATEMENT 1 6 3
* 1 INDEX SKIP SCAN T_IX 1 6 3
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------

1 - access("T"."B"=5)
filter("T"."B"=5)

Note: cpu costing is off

15 rows selected.

SQL>
В этом примере использовался индексный доступ INDEX SKIP SCAN по составному индексу, при отсутствии поиска по лидирующему столбцу.

Так, как же работает INDEX SKIP SCAN? На одной из публикаций на сайте Оракла, пишут:
During a skip scan, the composite index is accessed once for each distinct value of the leading column(s). For each distinct value, the index is searched to find the query's target values.
То есть для каждого различного значения лидирующего столбца выполняется поиск значений "второстепенных" столбцов (не нашла более подходящего слова для описания остальных, "лидируемых", ведомых, "менее значимых" столбцов составного индекса). Практически, выполняется INDEX RANGE SCAN для каждого различного значения лидирующего столбца.

В документации Оракла пишут, что INDEX SKIP SCAN эффективен в тех случаях, когда лидирующий столбец составного индекса имеет минимальное количество различных значений.
Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.
В нашем примере лидирующий столбец имеет всего 2 различных значения (1 и 0), но если бы он был более селективным, то есть содержал больше различных значений, то скорее всего оптимизатор предпочел бы TABLE FULL SCAN. Как, например, здесь:
SQL> create table t2 (a number, b number);

Table created.

SQL> create index t2_ix on t2(a, b);

Index created.

SQL>
SQL> begin
2 for i in 1 .. 10000
3 loop
4 insert into t2 values(mod(i, 10), i);
5 end loop;
6 commit;
7 end;
8 /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL> begin
2 dbms_stats.gather_table_stats(user, 'T2', cascade=>true);
3 end;
4 /

PL/SQL procedure successfully completed.

SQL>
SQL> explain plan set statement_id='NOSKIP'
2 for select * from t2 where b=5;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display('PLAN_TABLE','NOSKIP', 'ALL'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------


--------------------------------------------------------------------
Id Operation Name Rows Bytes Cost
--------------------------------------------------------------------
0 SELECT STATEMENT 1 6 5
* 1 TABLE ACCESS FULL T2 1 6 5
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------


PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------

1 - filter("T2"."B"=5)

Note: cpu costing is off

14 rows selected.

SQL>
Мы всего лишь увеличили количество различных значений лидирующего столбца с 2 до 10, и оптимизатор посчитал, что в этом случае ему выгодней использовать TABLE FULL SCAN чем INDEX SKIP SCAN. Есть хинт index_ss(алиас_таблицы название_индекса), который можно использовать для принудетельного использования индексного доступа INDEX SKIP SCAN:
SQL> explain plan set statement_id='INDEX_SS'
2 for select /*+ index_ss (t2 t2_ix) */* from t2 where b=5;

Explained.

SQL> select * from table(dbms_xplan.display('PLAN_TABLE','INDEX_SS', 'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------


--------------------------------------------------------------------
Id Operation Name Rows Bytes Cost
--------------------------------------------------------------------
0 SELECT STATEMENT 1 6 11
* 1 INDEX SKIP SCAN T2_IX 1 6 11
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------

1 - access("T2"."B"=5)
filter("T2"."B"=5)

Note: cpu costing is off

15 rows selected.

SQL>
Как уже было сказано выше, INDEX SKIP SCAN эффективен в тех случаях, когда лидирующий столбец имеет минимальное количество различных значений. INDEX SKIP SCAN - это не "silver bullet", то есть не идеальное и эффективнейшее решение для всех поисков по составному ключу без лидирующего столбца. Джонатан Льюс (Jonathan Lewis) в своей книге о стоимостном оптимизаторе (кстати, это одна из моих любимых книг по Ораклу) пишет, что INDEX SKIP SCAN может наоборот снижать производительность:
This is a beneficial feature in some cases, particularly if you have compressed indexes with a very low number of distinct values in the first column(s). But occasionally you may find that a skip-scan is causing performance problems.
Есть еще скрытый параметр _optimizer_skip_scan_enabled, который можно установить в false, чтоб отключить индексный путь доступа INDEX SKIP SCAN.
alter session set "_optimizer_skip_scan_enabled"=false;