2008-02-21

Запросы дня (SHARED POOL, SGA, LIBRARY CACHE)

V$LATCH shows aggregate latch statistics for both parent and child latches, grouped by latch name. Individual parent and child latch statistics are broken down in the views V$LATCH_PARENT and V$LATCH_CHILDREN.

Режимы защелок
Есть два варианта действия в зависимости от типа защелки : немедленная установка, установка с ожиданием. (“willing-to-wait” и “no-wait” (= immediate)). Если процесс имеет возможность продолжать работу, не получив запрашиваемую защелку, то это запрос no-wait (например, redo copy latch). Если процесс не может продолжать работу, не получив запрашиваемую блокировку, то это режим willing-to-wait. Число попыток определяется параметром инициализации spin_count. Когда число повторений достигнет spin_count , процесс переходит в состояние ожидания. Через установленное время процесс активизируется, и процедура повторяется снова.
SELECT * FROM v$latch; - отражает всю деятельность библиотечного кэша с момента последнего запуска экземпляра.

Небольшая инфа тут http://www.oracle.com/global/ru/oramag/may2001/getdoc.html

SELECT * FROM v$librarycache; - развернутая информация по SGA

сумма free memory = сумме свободной памяти в shared pool - количество unpinned recreatable chunks of the shared pool LRU lists.

Узнать unpinned можно, например, дампом shared pool
alter session set events 'immediate trace name heapdump level 2';

Информация по SGA: SELECT * FROM V$sgastat;

This view displays database objects that are cached in the library cache. Objects include tables, indexes, clusters, synonym definitions, PL/SQL procedures and packages, and triggers: SELECT * FROM v$db_object_cache;

latch: shared pool

Во время выполнения запроса из отчета столкнулся с latch: shared pool. Стало немого непонятно, откуда оно берется. Оказалось, что
library cache latch is already held while requesting for shared pool latch. Shared pool latch is acquired to request/release space from the shared pool free memory area and released immediately after that. Request for library cache latch can never be made while holding the shared pool latch (at least up to 9i) as latch level semantics will prevent that ( as shared pool latch gets are always in willing-to-wait mode).

Дальнейшее исследование навело на сайт, где описывается похожая проблема. Г-н Адамс рекомендует на своей странице следующее:
Shared pool too big

? We have recently migrated to Oracle8i, but it seems the performance is quite slow. The machine is large in terms of disks, CPU, and memory. Also, we have moved to IPC since we have batch jobs running on the database, and no users connected over the network. Could you tell me what I should diagnose on the database and the Solaris 2.6 server to find the bottlenecks? A utlestat report is attached.

This is a classic case of too large a shared pool causing extreme latch contention on the shared pool latch. Note the following statistics from your report.txt .

CPU used by this session 11639.58 (seconds)
latch free 25353.58 (seconds)

That is, you are spending twice as long waiting for latches than doing useful work, and much of your CPU time would be consumed while spinning. Note also where those latch sleeps are ...

shared pool 4292776 (sleeps)
library cache 252949 (sleeps)

That is, 94% of these sleeps are on the shared pool latch. The rest are secondary sleeps on the library cache latches.

The problem is that shared pool latch is being held too long while searching the free lists, because the free lists are too long. The free lists are too long, because the shared pool is too big. You need to make appropriate use of DBMS_SHARED_POOL.KEEP to mark valuable objects for keeping, and reduce the size of your shared pool dramatically.

? Could it be that there is not enough space in the shared pool?

No, the ideal situation is to have a small shared pool in which all the important reusable objects are marked for keeping and in which other objects are recycled quickly. People often attempt to increase the shared pool under these circumstances rather than reducing it. Normally, it appears to have worked for a while, because it takes longer for the LRU lists to begin to be flushed. But once that happens, you immediately get longer free lists and worse contention for the shared pool latch than would previously have been the case.

2008-02-19

Блокировки ORACLE

При создании транзакции Oracle выделяет блокировку типа TX, которую процесс пихает в блоки данных.
Информация для блокировки: Номер сегмента отката, слот сегмента отката и число возвратов можно посмотреть в таблице v$transaction
select XIDUSN, XIDSLOT, XIDSQN from v$transaction
При изменении Oracle ставит на таблицы блокировки TM.

Информацию о блокировках можно узнать из представлений:

V$LOCK: Информация о блокировках и ресурсах. Не включает блокировки DDL. Для TX в поле id1 хранится XIDUSN - trunc(id1/power(2,16)) и XIDSLOT - bitand (id1,power(2,16)-1) в id2 хранится XIDSQN. Для блокировок TM в id1 хранится идентификатор объекта (all_objects).

Возможные режимы блокировок:
1 (null),
2 (row share) разделяемая блокировка строки (RS) при выполнении select for update,
3 (row exclusive) монопольная блокировка строки (RX) при выполнении операторов insert, delete, update,
4 (share),
5 (share row exclusive),
6 (exclusive)

DBA_DDL_LOCKS: Все блокировки DDL, удерживаемые в базе данных, и все запросы, ожидающие блокировок DDL

DBA_DML_LOCKS: Все блокировки DML, удерживаемые в базе данных, и все запросы, ожидающие блокировок DML

DBA_LOCKS: Все блокировки или замки, удерживаемые в базе данных, и все запросы,ожидающие блокировок или замков. Этот запрос включает блокировки DML и блокировки DDL.