Введение
Ожидание «library cache pin». Рано или поздно, каждый администратор, в процессе мониторинга базы данных сталкивается с данным событием. На высоконагруженных системах массовое возникновение длительного ожидания «library cache pin» является довольно неприятным сигналом, говорящим о том, что нормальное функционирование экземпляра нарушено. В большинстве случаев это происходит тогда, когда над каким-то объектом базы данных, в моменты его интенсивного использования сеансами осуществляются административные действия. Возникшая ситуация обычно исправляется либо уничтожением сеанса, который явился первопричиной критической обстановки, либо ожиданием того момента времени, когда используемый объект будет освобождён другими сеансами. Болезненных последствий в этом случае для системы обычно не возникает. Правда, иногда ожидание «library cache pin» может переходить в неуправляемый лавинообразный процесс. Это особенно ярко проявляется в моменты переопределения объектов с множественными зависимостями. В этом случае для восстановления работоспособности системы приходиться массово уничтожать сеансы, в том числе и те которые не генерируют это ожидание.
Так что же такое library cache pin? Попробуем на примерах разобраться в том, что представляет собой это ожидание и чем его массовое возникновение так опасно для нормальной работы СУБД.
Защёлка
Library cache pin – это событие ожидания одноимённой защёлки. Оно возникает при первоначальной загрузке объекта в библиотечный кэш, а также тогда, когда сеанс хочет изменить или проверить объект в памяти библиотечного кэша. Обычно это происходит в случаях выполнения, разбора объекта, его переопределения или изменения привилегий доступа к нему. В эти моменты происходит либо загрузка в память библиотечного кэша динамических областей связанных с объектом или же использование этих структур, если объект уже загружен. Для того чтобы запретить одновременное изменение этих структур другими сеансами и держать библиотечный кэш в согласованном виде, сеанс должен предварительно приобрести защёлку library cache pin, причём сразу после другой защёлки library cache lock, так же связанной с доступом сеанса в библиотечный кэш. Вообще-то эти две защёлки всегда тесно связаны друг с другом. Если library cache lock управляет доступом между сеансами к объектам в библиотечном кэше, то library cache pin обеспечивает конкурентный доступ в пределах самого библиотечного кэша.
Защёлка library cache pin может захватываться в разделяемом и монопольном режимах. Разделяемый режим используется для чтения структур объекта библиотечного кэша, без права их изменения. Обычно это выполнение или исследование SQL или PL/SQL объекта. Защёлка в монопольном режиме, наоборот, приобретается для изменения информации в динамических областях объекта библиотечного кэша. Потребность в этом возникает в случаях разбора объекта, его переопределения, компиляции, а также при изменении привилегий к нему. Некоторые из этих действий совершаются сеансами самих пользователей, другие выполняются только администраторами баз данных.
Пожалуй, здесь мы ненадолго остановимся и для лучшего понимания механизма работы защёлки library cache pin немного коснёмся устройства самого библиотечного кэша.
Библиотечный кэш
Библиотечный кеш расположен в памяти разделяемого пула и в общем случае состоит из связанного набора довольно сложных структур. В самом верху этого набора расположена так называемая хэш-таблица, которая представляет собой массив бакетов (ячеек таблицы). Каждый бакет имеет свой индекс и содержит адрес начала двунаправленного списка дескрипторов объектов библиотечного кэша (Library Cache Object Handle). Дескрипторы в свою очередь указывают на структуры самих объектов библиотечного кэша (Library Cache Object или LCO). Обычно в бакете расположен список с всего одним дескриптором LCO, но бывает и так, что в результате так называемой хэш-коллизии в список указателей может попадать и несколько объектов.
Объект библиотечного кэша представляет собой довольно сложную структуру. В эту структуру входит имя объекта, представляющее собой либо действительное имя объекта, либо текст SQL курсора или PL/SQL блока. В структуру входит также хэш значение, пространство имён (namespace), время изменения объекта (timestamp), таблица зависимостей, таблица дочерних курсоров, таблица разрешений, списки ожиданий, тип объекта, флаг состояний, статус и блоки данных. Из всего этого набора составных частей LCO нас больше будут интересовать блоки данных. Почему? Об этом немного позже. А пока…
Объект библиотечного кэша может содержать до восьми блоков данных, на которые ссылается с помощью дескрипторов. Каждый блок имеет заголовок, содержащий флаг статуса и счётчик pin, указывающий на количество закреплений данного блока. Информация, хранимая в блоках данных, разнообразна и зависит от типа объекта библиотечного кэша. С этим же связано так же то, что в структуре LBO могут присутствовать не все блоки данных. Ниже представлена краткая информация о содержимом блоков данных LBO:
- Блок 0 – Object. Информация о самом объекте;
- Блок 1 – Source. Исходный код объекта;
- Блок 2 – DIANA. Это промежуточный код PL/SQL объекта;
- Блок 3 - PCODE;
- Блок 4 – MCODE. Здесь расположен машинно-зависимый код PL/SQL объекта;
- Блок 5 - Errors;
- Блок 6 – SQL Context. Здесь обычно храниться план выполнения;
- Блок 7 - Free;
И так, теперь мы в общих чертах представляем, как устроен библиотечный кэш Oracle и знаем, для чего в системе нужна защёлка library cache pin. Можно приступать к практической части статьи. Но прежде чем это делать, пройдёмся немного по описанию динамических таблиц, непосредственно связанных с библиотечным кэшем.
Динамические таблицы
Из всего списка динамических таблиц Oracle касающихся библиотечного кэша, пожалуй, можно выделить всего три таблицы. Это таблица x$kgllk, которая содержит все структуры блокировок библиотечного кэша, таблица x$kglpn, содержащая структуры состояний закреплений в библиотечном кэше и таблица x$kglob, хранящая в себе все ресурсы, находящиеся в библиотечном кэше. Так же в процессе изучения нам понадобятся ещё две вспомогательных динамические таблицы x$ksuse и x$ksled. Первая из них хранит всю информацию о текущих сеансах Oracle, вторая, наименования всех ожиданий Oracle.
Столбцов у этих динамических таблиц много, поэтому ограничимся только теми полями, которые нам понадобятся для изучения.
x$kgllk - блокировки библиотечного кэша
- kgllkuse - адрес сеанса;
- kgllkhdl – дескриптор KGL объекта (объекта в Kernel Generic Library cache);
- kgllkcnt - количество блокировок объекта;
- kgllkmod – режим захвата блокировки;
- kgllkreq– режим запроса блокировки;
x$kglpn – закрепления в библиотечном кэше
- kglpnhdl - дескриптор KGL объекта;
- kglpnuse - адрес сеанса;
- kglpncnt - количество закреплений объекта в сеансе (включая зависимые объекты);
- kglpnmod – режим захвата закрепления объекта;
- kglpnreq – режим запроса закрепления объекта;
x$kglob –ресурсы библиотечного кэша
- kglhdadr – дескриптор KGL объекта;
- kglnaown – владелец ресурса;
- kglnaobj – имя ресурса;
x$ksuse - текущие сеансы
- addr – адрес сеанса;
- ksusenum – идентификатор сеанса (SID);
- ksuudlna – имя пользователя;
- ksuseopc – номер события;
x$ksled – наименования событий
- indx – индекс события;
- kslednam – наименование события;
Попробуем соединить имеющиеся в нашем распоряжении динамические таблицы, и начнем, пожалуй, с таблицы x$kglpn. Именно в ней храниться информация обо всех текущих закреплениях объектов в библиотечном кэше. Единственное чего там нет, это наименований самих этих объектов, в отличии, к примеру, от динамической таблицы x$kgllk. В то же время в таблице x$kglpn есть поле kglpnhdl. Оно содержит адрес дескриптора KGL объекта в библиотечном кэше и указывает на поле kglhdadr из таблицы x$kglob. Связав две таблицы по перечисленным выше полям, мы вычислим имя ресурса закреплённого в библиотечном кэше. В дальнейшем нам остаётся только связать таблицу x$kglpn с таблицей x$ksuse, таким образом, мы определим сеанс, осуществивший закрепление объекта в библиотечном кэше. Оставшаяся таблица x$ksled нужна нам для вывода наименования текущего события, происходящего в сеансе. Впрочем, если вместо таблицы x$ksuse использовать динамическое представление v$session, то данная таблица просто не понадобиться.
В результате полученных связок динамических таблиц мы получим запрос, который будет показывать нам все закрепления сеансов пользователей применительно к какому-то определённому объекту:
SELECT c.ksusenum sid, c.ksuudlna username, a.kglpncnt pin_cnt,
a.kglpnmod pin_mode, a.kglpnreq pin_req, d.kslednam event
FROM x$kglpn a, x$kglob b, x$ksuse c, x$ksled d
WHERE b.kglhdadr = a.kglpnhdl AND a.kglpnuse = c.addr AND
c.ksuseopc = d.indx AND b.kglnaown = 'TEST' AND
b.kglnaobj = 'SLEEP_PROC'
Данным объектом в нашем случае будет тестовая процедура, которую мы создадим чуть позже, а пока приведем пример запроса для другой динамической таблицы x$kgllk:
SELECT c.ksusenum sid, c.ksuudlna username, a.kgllkcnt lck_cnt,
a.kgllkmod lck_mode, a.kgllkreq lck_req, d.kslednam event
FROM x$kgllk a, x$kglob b, x$ksuse c, x$ksled d
WHERE b.kglhdadr = a.kgllkhdl AND a.kgllkuse = c.addr AND
c.ksuseopc = d.indx AND b.kglnaown = 'TEST' AND
b.kglnaobj = 'SLEEP_PROC'
Запрос предназначен для вывода информации о блокировках библиотечного кэша, выбранного нами объекта. Проще говоря, он будет выводить состояние блокировок library cache lock и в дальнейшем позволит прояснить некоторые моменты закрепления объектов. По соединению таблиц запрос аналогичен предыдущему SQL выражению, хотя здесь можно было бы обойтись и без таблиц x$kglob и x$ksuse. В таблице x$kgllk большинство полей уже имеют необходимую нам информацию.
Создание теста
После того как мы определились с запросами, которые помогут нам наблюдать состояние исследуемых защёлок, можно приступать к созданию теста. В качестве тестовой базы данных у нас будет выступать редакция Oracle Database 10G Express Edition, а в качестве клиентского приложения инструмент администратора ZhiSQL for Oracle.
Для начала создадим трёх пользователей user1, user2, user3. Они будут выступать в качестве простых клиентов, выполняющих какую-то процедуру. Пользователь user3 при этом не будет иметь права на её запуск. Далее создадим пользователя user_dba. Это будет администратор, который имеет право компилировать указанную процедуру и выполнять другие привилегированные действия. В качестве последнего действия создадим саму процедура sleep_proc расположенную в схеме test, единственным действием которой будет засыпание на 60 секунд.
И так, приступим к созданию необходимых нам тестовых объектов:
Подключение к:
Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production
SYS@XE> CREATE USER user_dba IDENTIFIED BY test;
Пользователь создан
SYS@XE> GRANT DBA TO user_dba;
Grant succeeded
SYS@XE> CREATE USER user1 IDENTIFIED BY test;
Пользователь создан
SYS@XE> CREATE USER user2 IDENTIFIED BY test;
Пользователь создан
SYS@XE> CREATE USER user3 IDENTIFIED BY test;
Пользователь создан
SYS@XE> GRANT CONNECT TO user1, user2, user3;
Grant succeeded
SYS@XE> CREATE USER test IDENTIFIED BY test;
Пользователь создан
SYS@XE> GRANT CONNECT, RESOURCE TO test;
Grant succeeded
SYS@XE> GRANT EXECUTE ON sys.dbms_lock TO test;
Grant succeeded
SYS@XE> CREATE OR REPLACE PROCEDURE test.sleep_proc
2> AS
3> BEGIN
4> sys.dbms_lock.sleep(60);
5> END;
Процедура изменена
SYS@XE> GRANT EXECUTE ON test.sleep_proc TO user1, user2;
Grant succeeded
Разделяемый режим защёлки
Тестовые объекты созданы. Теперь можно приступать и к изучению защёлки. Для начала произведём имитацию обычной работы пользователей. Образуем два сеанса от имени пользователей user1, user2 и запустим на выполнение в этих сеансах процедуру sleep_proc:
USER1@XE(38)> EXECUTE test.sleep_proc;
PL/SQL procedure successfully completed
USER2@XE(29)> EXECUTE test.sleep_proc;
PL/SQL procedure successfully completed
Процедура приостанавливает работу сеанса на 60 секунд. Этого времени нам с избытком хватит для просмотра состояния защёлки library cache pin с помощью запроса, составленного нами ранее:
SYS@XE(31)> SELECT c.ksusenum sid, c.ksuudlna username, a.kglpncnt pin_cnt,
2> a.kglpnmod pin_mode, a.kglpnreq pin_req, d.kslednam event
3> FROM x$kglpn a, x$kglob b,x$ksuse c, x$ksled d
4> WHERE b.kglhdadr = a.kglpnhdl AND a.kglpnuse = c.addr AND
5> c.ksuseopc = d.indx AND b.kglnaown = 'TEST' AND
6> b.kglnaobj = 'SLEEP_PROC'
SID USERNAME PIN_CNT PIN_MODE PIN_REQ EVENT
--- -------- ------- -------- ------- -----------------
38 USER1 3 2 0 PL/SQL lock timer
29 USER2 3 2 0 PL/SQL lock timer
Выбрано: 2 строки
Запрос вывел для процедуры test.sleep_proc две строки. Уже одно это означает, что объект закреплён или наоборот ожидает закрепления. Информация в таблице x$kglpn появляется только на момент закрепления и исчезает сразу после его снятия. В столбце EVENT мы видим, что сеансы находятся в состоянии ожидания «PL/SQL lock timer». Через 60 секунд процедура закончит своё выполнение, а пока что столбец PIN_REQ показывает нам, ожидает ли сеанс закрепления объекта. В нашем случае он равен нолю, это означает, что ожидания закрепления нет. Значит, объект уже закреплён, и нам необходимо обратиться к столбцу PIN_MODE. Значение в нём указывает на режим, в котором произвелось закрепление. Сейчас здесь присутствует число 2 и это свидетельствует о том, что процедура sleep_proc была закреплена в библиотечном кэше в разделяемом режиме. Такой режим устанавливается для объекта библиотечного кэша в момент его выполнения или исследования сеансом и позволяет только читать информацию из структур LBO. При этом запрещается дальнейший захват защёлки в монопольном режиме на этом объекте.
Получение защёлки в разделяемом режиме позволяет расширить параллелизм в использовании объекта несколькими сеансами. Но разве нельзя было бы обойтись для этого приобретением только одной защёлки library cache lock? Для того, что бы прояснить этот вопрос, нам придётся сделать дамп библиотечного кэша и исследовать его структуру.