Вопросы о чекпоинтах (Questions about checkpoints)

На прошлой неделе на очередном тренинге для коллег-дба, я подготовила вопросы о чекпоинтах.
Вот несколько из них:

10. Какое из следующих утверждений НЕверно о различиях между полным и инкрементальным чекпоинтами?
a) инкрементальный чекпоинт выполняется намного чаще полного
b) полный чекпоинт сбрасывает все грязные блоки, а инкрементальный – только часть
c) полный чекпоинт обновляет заголовки всех online файлов данных, инкрементальный - заголовки файлов данных, в которых произошли изменения
d) полный чекпоинт обычно вызывается переключением лог файлов, а инкрементальный – увеличением кол-ва грязных блоков и превышением их порогового значения

Ответ: с

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

Количество ожиданий в Oracle 10g (Number of Wait Events in Oracle 10g)

Сегодня читала про ожидания в Oracle 10g и наткнулась на эту презентацию, где увидела этот график:
Я знала, что в Oracle 10g появилось очень много новых событий ожидания (wait events) но даже не думала, что настоолько много! Первым делом решила проверить это и посчитала кол-во событий ожидания в версиях 9i и 10g:

В Oracle 9.2.0.8:

SQL> select count(*) from v$event_name;

  COUNT(*)
----------
       406

В Oracle 10.2.0.4:

SQL> select count(*) from v$event_name;

  COUNT(*)
----------
       889

В 10g кол-во ожиданий увеличилось больше чем вдвое: было 406, стало 889! Здесь пишут, что события ожидания в Oracle 10g стали более "descriptive", то есть более описательными, более детальными.

Wait event names in Oracle 10g are more descriptive in the areas of latches, enqueues, and buffer busy waits.

Здесь я писала об одном из таких примеров, когда из ожидания buffer busy waits "родилось и отколось" ожидание read by other session.

Размер redo блоков (Redo log block size)

Сегодня хотела бы написать об размере блоков redo log buffer'а. Все мы знаем, о размере блока базы данных, который устанавливается параметром db_block_size, и который задает размер блоков в файлах данных и размер буфферов в буфферном кеше.

А какая структура у redo log buffer'а? Понятно, что она - цикличная, запись в него выполняется последовательно, не то что в буфферном кеше или в файлах данных, когда чтение и запись выполняется вразброс.

Мне всегда казалось, что если она цикличная и запись в него последовательная, то и думать тут не о чем: значит у него и структура памяти какая-то неразрывная, что ли.

Сегодня, когда пыталась понять смысл латча redo allocation, не могла понять, зачем вообще нужен этот латч... Что тут выделять-то? Память под redo log buffer уже выделена же в SGA. У Стива Адамса прочитала следующее:

The redo allocation latch must be taken to allocate space in the log buffer. This latch protects the SGA variables that are used to track which log buffer blocks are used and free.

Вот так я узнала, что redo log buffer состоит из блоков одинакового размера, а его цикличность - это структура данных, а в памяти они могут находится вразброс.

А теперь, собственно, про размер блоков redo log buffer'а. Стив Адамс пишет здесь:

Although the size of redo entries is measured in bytes, LGWR writes the redo to the log files on disk in blocks. The size of redo log blocks is fixed in the Oracle source code and is operating system specific.

Перевод: Хотя размер redo измеряется в байтах, LGWR пишет red в лог файлы на дисках в блоках. Размер redo блоков зашит в код ядра Oracle и зависит от операционной системы.

То есть размер redo блоков невозможно изменить параметром инициализации как размер блоков данных db_block_size.

Ниже, он приводит размеры redo блоков в разных ОС:

Log Block Size    Operating Systems
512 bytes         Solaris, Windows, UnixWare
1024 bytes        HP-UX, Tru64 Unix
2048 bytes        SCO Unix, Reliant Unix
4096 bytes        MVS, MPE/ix

А так же, фактический размер redo блоков можно узнать следующим способом:

SQL> select max(lebsz) from sys.x$kccle;

MAX(LEBSZ)
----------
       512

Ожидания log file sync

Сессия, ожидает события log file sync, в то время как LGWR сбрасывает redo-информации из redo log buffer в redo log файлы.

Подробней: скажем какая-то сессия апдейтит какую-то таблицу. То есть выполняя апдейт, она генерит redo-информацию и записывает их в redo log buffer. В тот самый момент, когда сессия пишет redo-информацию в redo log buffer, LGWR сидит и ждет. Как только сессия коммитит транзакцию, LGWRу нужно сбросить redo-информацию из redo log buffer в redo log файлы и выдать сессии подверждение, что транзакция закоммичена. И пока LGWR сбрасывает redo-информацию из redo log buffer в redo log файлы, сессия ожидает события log file sync.

Мой коллега говорит, что это ожидание возникает только при commit'е сессии. Том Кайт тоже здесь пишет, что это ожидание возникает только при коммите:

log file sync - это клиентское ожидание события. Именно этого события ваши клиенты ждут, когда говорят "commit". Это ожидание, пока процесс LGWR фактически запишет их данные повторного выполнения на диск и фиксация транзакции будет завершена. Можно "настроить" этот процесс, ускорив работу процесса lgwr (отказавшись от использования raid 5, например) и фиксируя транзакции реже, генерируя меньше данных повторного выполнения (множественные изменения генерируют меньше данных повторного выполнения, чем построчные)

А как же сброс redo-информации из redo log buffer в redo-файлы по истечению 3 секунд, при заполнении redo log buffer'а на 1/3 и тд?

Если кто-то знает точный ответ на этот вопрос, напишите здесь.

"buffer busy waits" и "read by other session"

В Oracle 10g появились ожидания read by other session, которые являются частным и "отколовшимся" случаем ожиданий buffer busy waits. Вот что пишут в документации Oracle о них:

This event ['read by other session'] occurs when a session requests a buffer that is currently being read into the buffer cache by another session. Prior to release 10.1, waits for this event ['read by other session'] were grouped with the other reasons for waiting for buffers under the 'buffer busy wait' event.

Наталья Гусева, одна из суперских ДБА, которых я знаю, подсказала, что read by other session - это тот же buffer busy waits с reason code = 130.

Reason code = 130: Block is being read by another session, and no other suitable block image was found, so we wait until the read is completed. This may also occur after a buffer cache assumed deadlock. The kernel can't get a buffer in a certain amount of time and assumes a deadlock. Therefore it will read the CR version of the block.

Полный список и описание reason code можно почитать здесь, правда там говорится применительно версии 9i.

Ожидания read by other session возникают, когда какая-то сессия пытается прочитать блок из диска в буферный кеш, в тот момент когда другая сессия уже читает ее из диска в буферный кеш.

В моей практике, причины таких внезапных ожиданий в продуктивных базах были - неэффективные планы выполнений, когда планы "сбиваются" по каким-то причинам.

В тестовых базах такие ожидания появлялись, когда кто-то забывал создать индексы после переноса схемы.

Новый шаблон в новом году

Сегодня обновила шаблон блога на новый, захотелось сделать очередное "что-то новое" в новом году.

Новый шаблон очень необычный, по крайней мере для блога по Ораклу, но мне нравится.

Я его скачала на этом сайте - они раздают бесплатные шаблоны для блогов Blogspot.com, видимо, зарабатывают на рекламе.

FGA в 9i и 10g

Оказывается в Oracle 9i FGA позволяет аудитить только SELECTы, а в 10g появилась возможность аудитить и INSERT, UPDATE, DELETE.
Здесь пишут как настраивать этот параметр:

Under Oracle 9i Database, this policy could only audit SELECT statements. In Oracle Database 10g, however, you can extend it to include INSERT, UPDATE, and DELETE as well. You would do so by specifying a new parameter:

statement_types => 'INSERT, UPDATE, DELETE, SELECT'

begin
 dbms_fga.add_policy(object_schema   => 'SCOTT',
                     object_name     => 'DEPT',
                     policy_name     => 'DEPT_AUDIT',
                     audit_column    => 'DNAME',
                     statement_types => 'INSERT, UPDATE',
                     audit_trail     => DBMS_FGA.DB_EXTENDED);
end;

Сайт об оптимизации производительности БД Oracle

Сегодня наткнулась на сайт http://www.novoelba.ru об оптимизации производительности БД Oracle.

Авторы сайта предлагают соптимизировать производительность любых "безнадежных" баз данных Oracle:

"Мы поможем Вам ускорить «безнадёжные» отчеты, даже если Вам, возможно, уже сообщили, что ускорить их невозможно без покупки нового дорогого оборудования."

Прикольно, кажется впервые в Рунете вижу службу, которая предлагает такую услугу. Чем-то напоминает консалтинг от Бурлесона http://www.dba-oracle.com.

Как узнать старый пароль дблинка (если он был изменен недавно)?

Как вы думаете, как можно узнать старый пароль дблинка (database link), который был изменен совсем недавно?
Один мой коллега (НЕ ДБА!) предложил суперский вариант, как это можно сделать: посмотреть флешбеком (flashback) состояние системной таблицы, в которой хранится информация о дблинках.
Смотрим, к каким системным таблицам обращается вьюшка dba_db_links:

create or replace view dba_db_links
(owner, db_link, username, host, created)
as
select u.name, l.name, l.userid, l.host, l.ctime
from sys.link$ l, sys.user$ u
where l.owner# = u.user#

Дальше, флешбеком смотрим состояние системной таблицы sys.link$ на час назад:

SQL> select name, userid, password
     from sys.link$ as of timestamp(sysdate-1/24)
     where name ='MYDBLINK';

NAME                 USERID     PASSWORD
-------------------- ---------- ----------
MYDBLINK             USERNAME   OLDPASSWD

OLDPASSWD - это наш старый пароль. Чтоб убедиться, что мы нашли то, что нужно посмотрим текущий пароль:

SQL> select name, userid, password
     from sys.link$ as of timestamp(sysdate)
     where name ='KI_ICR002';

NAME                 USERID     PASSWORD
-------------------- ---------- ----------
MYDBLINK             USERNAME   NEWPASSWD

Вот так вот нашли только что измененный пароль. А я уже хотела логмайнером (logminer) порыться в реду-логах (redo). Правда, описанный выше метод сработает только если изменения были сделаны совсем недавно, то есть в пределах undo_retention (не всегда гарантированно).

Flush buffer cache

Эта команда используется и в Oracle 9i и в 10g для сброса разделяемого пула.

alter system flush shared_pool

В Oracle 10g есть аналогичная команда, сбрасывающая буферный кеш:

alter system flush buffer_cache;

Только было бы замечательно уточнить, что именно происходит при сбросе буферного кеша: во-первых, записываются измененные блоки в файлы данных. Может вся хеш-таблица буферного кеша тоже чистится?

В Oracle 9i такой команды нет, но, оказывается можно сбросить буферный кеш установкой события:

alter session set events = 'immediate trace name flush_cache';

Тюнинг базы на 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.
...

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

Параметр 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

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

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

Есть 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, которая парсит этот стэк и возвращяет кем была вызвана процедура или функция.

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

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

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

Теперь, своей первой группе прочитаю вторую часть тренинга и сделаю выводы о том, что можно улучшить.

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

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

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.

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;