Тюнинг базы на Oracle 10g

Давно не писала в блоге, сегодня решила написать хоть что-нить. Раньше всегда пыталась писать в блоге чисто с технической точки зрения, не включая мое личное отношение к разным фичерам и особенностям Оракла. Мне кажется в последующих моих постингах будет больше "меня".

Недавно оптимизировала базу на Oracle 10.2.0.3, которая используется для CRM-системы. Провели нагрузочное тестирование, по результатам которого выяснили, что на 300-400 одновременных пользователях активно выполняющих те или иные операции, база начинает виснуть. Удалось поднять производительность более чем в 4 раза.

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

В Oracle 10g в V$SESSION можно посмотреть ожидания каждой сессии, не связывая его с V$SESSION_WAIT. То есть V$SESSION содержит все столбцы V$SESSION_WAIT:

SQL> set pagesize 100
SQL> column event# format 999
SQL> column wait_class format A15
SQL> column event format A30
SQL> select event#, event, wait_class, state
  2  from v$session where username is not null;

EVENT# EVENT                          WAIT_CLASS      STATE
------ ------------------------------ --------------- -------------------
   256 SQL*Net message from client    Idle            WAITING
    30 Backup: sbtwrite2              Administrative  WAITING
   256 SQL*Net message from client    Idle            WAITING
   256 SQL*Net message from client    Idle            WAITING
   252 SQL*Net message to client      Network         WAITED SHORT TIME

SQL>

Ожидания при чтении и записи LOBов

Вчера я пыталась создать копию таблицы, у которой был столбец типа BLOB. Заливка данных повисла, и я оставила ее висеть до утра :D К тому же это было не к спеху. Таблица была средненькая: около 700 тыс. записей, весит 2 гига. Знала, что это из-за LOBа так висит, и сегодня решила докапаться до истины. И так все по-порядку.

Сперва пыталась создать копию таблицы обычным "create table ... as select", думала средненькая таблица скопируется быстро, но не тут-то было. Висела больше часа, пока не кильнула сессию. С опцией nologging тоже самое, через несколько часов и ее убила:

create table new_blob_table as select * from orig_blob_table;

create table new_blob_table nologging as select * from orig_blob_table;

А direct path insert командой insert /*+ append */ тоже висела пару часов, и я, добрая, оставила ее висеть до утра:

create table new_blob_table as select * from orig_blob_table where 1=2;

insert /*+ append */ into new_blob_table select * from orig_blob_table;

Во время всех этих попыток, сессия ожидала события direct path write (lob):

SQL> select event
     from v$session_wait
     where sid=969;

EVENT
-------------------------
direct path write (lob)

При прямой записи - direct path write - запись происходит В ОБХОД буферного кеша прямо в файлы данных. Запись выполняется серверным процессом (а не процессом DBWR, как в обычной записи), который в pga сессии подготавливает блоки и записывать их за отметкой HWM.

Бурлесон перечисляет список операций, которые могут выполнять операцию прямой записи (direct path write) и вызывать соответствущие ожидания:

Operations that could perform direct path writes include when
1) a sort goes to disk,
2) during parallel DML operations,
3) direct-path inserts,
4) parallel create table as select, and
5) some LOB operations.

Наш случай - последний, связанный с LOBами. Вот что пишут в документации Oracle о прямой записи (direct path write) и параметре CACHE/NOCACHE LOB-сегментов:

With the CACHE option, LOB data reads show up as wait event 'db file sequential read', writes are performed by the DBWR process.

With the NOCACHE option, LOB data reads/writes show up as wait events:
direct path read (lob)/direct path write (lob).

Corresponding statistics are:
physical reads direct (lob) and physical writes direct (lob).

О параметре CACHE BLOB-сегментов, я писала недавно здесь. Получается, что когда я создавала таблицу командой create table ... as select ..., LOB-сегмент таблицы создались с параметром NOCACHE, который и ведет к ожиданиям direct path write (lob).

SQL> select table_name, column_name, cache
     from dba_lobs
     where table_name='NEW_BLOB_TABLE';

TABLE_NAME           COLUMN_NAME          CACHE
-------------------- -------------------- --------
NEW_BLOB_TABLE       BLOB_COLUMN          NO

То есть новую таблицу нужно либо изначально создать с параметром CACHE LOB-сегмента:

create table new_blob_table
lob (blob_column) store as (cache)
as select * from orig_blob_table where 1=2;

или после создания пустой таблицы (where 1=2), изменить параметр CACHE LOB-сегмента:

alter table new_blob_table modify lob (request_content) (cache);

Теперь, заливка длится чуть больше 1 минуты (!!!):

SQL> create table new_blob_table
     lob (blob_column) store as (cache)
     as select * from orig_blob_table where 1=2;

Table created.

SQL> set timing on
SQL> insert /*+ append */ into new_blob_table select * from orig_blob_table;

642966 rows created.

Elapsed: 00:01:12.83

Даже обычный инсерт отработал всего 4 минуты!

SQL> insert into new_blob_table select * from orig_blob_table;

642966 rows created.

Elapsed: 00:04:18.85

Странно, что если создавать НЕпустую таблицу командой create table ... as select ... с параметром CACHE LOB-сегмента, он все равно зависает с ожиданиями direct path write (lob):

create table new_blob_table
lob (blob_column) store as (cache)
as select * from orig_blob_table;

Теперь, при заливке командой insert /*+ append */, ожидания записи direct path write (lob) исчезли, есть коротенькие ожидания, но это уже ожидания чтения db file sequential read и db file scattered read. А статистика physical writes direct (lob) заменилась на physical writes direct.

Вот. Получается, что с insert /*+ append */ происходит прямая запись, но видимо эта запись уже отличается прямой записи БЛОБов. Если у кого-то есть более подробное описание всего этого, буду рада послушать.

Партиционирование существующей таблицы

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

1) Создать новую партиционированную таблицу и залить данные из старой
CREATE TABLE ... PARTITION BY (RANGE)...
INSERT INTO ... SELECT * FROM not_partitioned_table;
или
INSERT /*+ append */INTO ... SELECT * FROM not_partitioned_table;

2) Создать новую партиционированную таблицу из старой таблицы
CREATE TABLE partitioned_table
PARTITION BY (RANGE) ...
AS SELECT * FROM not_partitioned_table;

3) С помощью EXCHANGE PARTITION
ALTER TABLE partitioned_table
EXCHANGE PARTITION calls_01012008
WITH TABLE not_partitioned_table;

4) С помощью пакета DBMS_REDEFITION

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

Теперь, попробую более подробно описать каждый из вариантов.
Скажем, у нас есть партиционированная таблица NOT_PARTITIONED_TABLE, в которой хранятся данные о звонках.

SQL> create table not_partitioned_table (
  id number,
  dialed_number varchar2(100),
  call_date date
);

Table created.

SQL> --- заполним таблицу звонковыми данными: 1 миллион записей, маловато, но пойдет
SQL> declare
     start_date date;
begin
     start_date:=to_date('01.01.2008','DD.MM.YYYY');
     for i in 1..1000000 loop
         insert into not_partitioned_table values
                      (i, '1234567890', start_date + (i-1)/24/60/10);
     end loop;
     commit;
end;
/

PL/SQL procedure successfully completed.

В итоге, у нас есть непартиционированная таблица NOT_PARTITIONED_TABLE с 1 миллионом записей о звонках с 1 по 7 января 2008 года:

SQL> select trunc(call_date), count(*)
     from not_partitioned_table
     group by trunc(call_date);

TRUNC(CAL   COUNT(*)
--------- ----------
01-JAN-08     144000
02-JAN-08     144000
03-JAN-08     144000
04-JAN-08     144000
05-JAN-08     144000
06-JAN-08     144000
07-JAN-08     136000

7 rows selected. 

Наша цель: партиционировать эту таблицу с наименьшим даунтаймом таблицы.

Вариант 1: Создать новую партиционированную таблицу и залить данные из старой

План действий такой:
- Создаем новую партиционированную таблицу
- Можно заблокировать непартиционированной таблицу, чтоб никто не смог изменить данные во время заливки
- Заливаем данные из непартиционированной таблицу
- Удаляем непартиционированную таблицу
- Переименовываем новую партиционированную таблицу

SQL> create table partitioned_table (
       id number,
       dialed_number varchar2(100),
       call_date date)
     partition by range (call_date)
     (
     partition calls_01012008 values less than (to_date('02.01.2008','DD.MM.YYYY')),
     partition calls_02012008 values less than (to_date('03.01.2008','DD.MM.YYYY')),
     partition calls_03012008 values less than (to_date('04.01.2008','DD.MM.YYYY')),
     partition calls_04012008 values less than (to_date('05.01.2008','DD.MM.YYYY')),
     partition calls_05012008 values less than (to_date('06.01.2008','DD.MM.YYYY')),
     partition calls_06012008 values less than (to_date('07.01.2008','DD.MM.YYYY')),
     partition calls_maxvalue values less than (maxvalue)
     );
Table created. 

Перед заливкой заблокируем таблицу в режиме EXCLUSIVE командой LOCK TABLE, чтоб пока мы заливаем данные, никто не смог изменить данные в них или добавить новые.

SQL> set timing on
SQL> set autotrace on statistics
SQL> lock table not_partitioned_table in exclusive mode;

Table(s) Locked.

Elapsed: 00:00:00.07

Теперь зальем данные командой INSERT. Сперва посмотрим заливку обычным INSERTом:

SQL> insert into partitioned_table select * from not_partitioned_table;

1000000 rows created.

Elapsed: 00:07:53.31

Statistics
----------------------------------------------------------
       5666  recursive calls
      42061  db block gets
      11930  consistent gets
       4035  physical reads
   35909076  redo size
        357  bytes sent via SQL*Net to client
        345  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

Заливка 1 миллиона записей длилась почти 8 минут. Инсерт сгенерил 35 909 076 байт redo - это почти равно размеру непартиционированной таблицы, таблица весит 33 554 432 байт.

SQL> select bytes from dba_segments
     where segment_name like 'NOT_PARTITIONED_TABLE';

     BYTES
----------
  33554432

Теперь попробуем залить те же данные прямым инсертом (direct path insert), командой INSERT /*+ APPEND */.

Direct Path Insert отличается от обычного инсерта тем, что вставка происходит:
1) в обход буферного кеша
2) новые блоки добавляются за отметкой HWM

То есть новые блоки данных подготавливаются в pga сессии и в обход буферного кеша добавляются ЗА отметкой HWM, если даже до отметки HWM есть свободное место для данных. Как объясняет Том Кайт, неправильно считать, что при таком инсерте (direct path insert) вообще не генерится redo. Redo по-любому генерится, но совсем малюсенький по сравнению с обычным инсертом, что существенно увеличивает скорость заливки.
После таких заливок следует сделать полный бэкап базы на всякий случай.

SQL> insert /*+ append */ into partitioned_table
     select * from not_partitioned_table;

1000000 rows created.

Elapsed: 00:00:03.15

Statistics
----------------------------------------------------------
       5710  recursive calls
       3735  db block gets
       5928  consistent gets
       3670  physical reads
     343352  redo size
        351  bytes sent via SQL*Net to client
        359  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
    1000000  rows processed 

Заливка "инсерт аппендом" закончилась за 3 минуты (почти в 3 раза быстрей обычного инсерта), и сгенерил 343352 байтов redo (почти в 100 раз меньше чем при обычном инсерте).

В три раза быстрей чем обычный инсерт, но как можно еще как-то ускорить заливку? Есть один способ: можно заранее выделить необходимый объем экстентов партициям, чтоб во время заливки на это не тратилось время.

SQL> alter table partitioned_table
     modify partition calls_01012008 allocate extent (size 5M);

Table altered.
....
SQL> alter table partitioned_table
     modify partition calls_maxvalue allocate extent (size 5M);

Table altered.

После предварительного выделения эктентов партициям, инсерт аппенд отработал всего за 5 секунд!!!!

SQL> insert /*+ append */ into partitioned_table
    select * from not_partitioned_table;

1000000 rows created.

Elapsed: 00:00:05.06

А обычный инсерт отработал за 11 секунд:

SQL> insert into partitioned_table select * from not_partitioned_table;

1000000 rows created.

Elapsed: 00:00:11.13

Для ускорения заливки и уменьшения даунтайма таблицы, можно заранее выделить необходимые эктенты партициям, чтоб во времы заливки на эту рекурсивную операцию не тратилось время.

Теперь осталось удалить старую таблицу и переименовать партиционированную таблицу.

SQL> drop table not_partitioned_table;

Table dropped.

SQL> alter table partitioned_table rename to new_table;

Table altered.

Можно и партиции переименовать (но их можно было бы создать с нужным именем изначально):

SQL> alter table new_table rename partition calls_01012008 to new_calls_01012008;

Table altered.
...

В следующие варианты - в следующем выпуске новостей.