Что видно в динамических таблицах

Под динамическими таблицами в Oracle подразумеваются таблицы, начинающиеся с префиксов X$, V$ и GV$. Формально таблицами являются только X$-…, а V$-… и GV$-… являются представлениями, составленными на основе X$-таблиц. (Те, кому посчастливилось изучать базы данных в институте, сразу вспомнят, что в реляционном подходе есть таблицы "хранимые" и "не хранимые", а также "базовые" и "выводимые". В соответствии с этой классификацией можно говорить о "нехранимых базовых X$-таблицах" и "выводимых V$- и GV$-таблицах"). На общепринятом Oracle-жаргоне все три типа объектов часто называются "таблицами".

X$-таблицы представляют на самом деле табличный интерфейс к внутренним переменным и структурам, бесчисленное множество которых используются при работе экземпляра СУБД Oracle. Такой подход фирмы-разработчика можно только приветствовать, но беда в том, что они практически недокументированы, а по своему определению весьма криптографичны. Для того, чтобы облегчить жизнь администраторам, фирма Oracle заводит в словаре-справочнике V$-таблицы, которые едва ли более документированы, но зато имеют часто более осмысленные имена и структуру. Создание происходит главным образом сценарием catalog.sql, который запускается при генерации базы данных Oracle.

Указанное происхождение X$- и V$-таблиц делает их неоценимым источником сведений о текущей работе Oracle, получаемых, как-бы, "из первых рук". В частности, ими пользуются все имеющиеся на рынке "графические" системы администрирования Oracle. Многие администраторы имеют в своем арсенале свои "любимые запросы" к таким таблицам. Вариант такого небольшого арсенала приводится ниже.

За основу взяты запросы, предлагаемые такими международными мэтрами администрирования, как Joseph Trezzo и Richard Niemetz из известной компании TUSC (США). Этим объясняется присутствие в запросах обращений в X$-таблицам напрямую. Вообще-то этого делать не рекомендуется (уж больно они специфичны, да и отображение из X$- в V$-таблицы далеко не всегда прозрачно, в чем можно убедиться, обратившись к текстам V$-представлений в V$FIXED_VIEW_DEFINITION), но в данном случае высочайшая квалификация авторов - залог грамотного использования X$-таблиц и поэтому такие заранее составленные запросы можно использовать без опаски.

Все тексты проверены на версии 8.1.5.

1. Параметры производительности

1. Характеристики выполняемых за последнее время (информация из shared pool) SQL-запросах

Выдается для числа чтений диска > 30.

select   b.username, a.disk_reads, a.executions,
a.disk_reads/decode(a.executions, 0, 1, a.executions) rds_exec_ratio, a.command_type, a.sql_text from v$sqlarea a, dba_users b where a.parsing_user_id = b.user_id and a.disk_reads > 30 order by a.disk_reads desc;

Статистика по числу обращений к памяти (логических чтений) может быть получена заменой disk_reads на buffer_gets. Если для выдачи запроса используется SQL*Plus, то кому-нибудь следующая переформулировка может показаться более удобной:

ACCEPT indicator CHAR PROMPT 'Enter indicator to investigate
(either ''disk_reads'' or ''buffer_gets''): ' ACCEPT nreads NUMBER PROMPT 'Enter minimal the value: ' SET VERIFY OFF select b.username, a.&indicator, a.executions,
a.&indicator/decode(a.executions, 0, 1, a.executions) rds_exec_ratio, a.command_type, a.sql_text from v$sqlarea a, dba_users b where a.parsing_user_id = b.user_id and a.&indicator > &nreads order by a.&indicator desc /

2. Список курсоров, открытых в системе

select oc.user_name, s.sql_text 
from     v$open_cursor oc, v$sqltext s 
where oc.address = s.address 
and     oc.hash_value = s.hash_value 
order  by oc.user_name, s.piece;

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

3. Состояние блоков с данными в системе

select decode(state, 0, 'Free', 1, 'Modified', 2, 'Not Modified',
3, 'Being Read', 'Other') State, count(*) cnt from sys.x$bh group by state;

Если число блоков в состоянии Free велико, можно сократить буфер. Если после старта базы оно напротив, невелико, то возможно буфер страниц стоит и увеличить. Можно выполнить более точный запрос:

 select decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE',
              'BEING USED'),3,'BEING USED',state) "BLOCK STATUS",count(*)
  from x$bh
  group by decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE',
              'BEING USED'),3,'BEING USED',state);

4. Активность ввода/вывода для файлов с данными

 select a.file#, a.name, a.status, a.bytes, b.phyrds, b.phywrts 
 from v$datafile a, v$filestat b 
 where a.file# = b.file#;

Целесообразно проверять при переконфигурировании системы или схемы данных. При дисбалансе чтений/записи файлы, возможно, стоит перераспределить по дискам по-иному.

5. Коэффициент попадания в буфер данных при обращении к блокам

select 1-(sum(decode(name, 'physical reads', value,0))/ 
                (sum(decode(name, 'db block gets', value,0)) + 
                (sum(decode(name, 'consistent gets', value,0))))) 
                "Read Hit Ratio" 
from v$sysstat; 

Для пакетных режимов рекомендуется > 85%, для интерактивных -- >95%.

6. Статистика попаданий в библиотечный буфер (в shared pool)

 select sum(pins) "Executions", sum(pinhits) "Execution Hits",
            ((sum(pinhits) / sum(pins)) * 100) "Hit Ratio",
            sum(reloads) "Misses",
((sum(pins) / (sum(pins) + sum(reloads))) * 100) "Hit Ratio" from v$librarycache

Выдается статистика попаданий в библиотечный буфер (расположен в shared pool). Если один из коэффициентов попаданий меньше 99%, рекомендуется увеличить shared pool.

7. Коэффициент попадания в словарный буфер

 select sum(gets), sum(getmisses), 
              (1 - (sum(getmisses) / (sum(gets) + sum(getmisses)))) *
100 HitRat from v$rowcache;

Если коэффициент попадания в словарный буфер (dictionary cache, еще одна составляющая shared pool) меньше 90%, рекомендуется увеличить shared pool. Убрав из запроса суммирование, можно осуществить "data mining" и получить статистику по отдельным компонентам словарного буфера. Если, например, коэффициент попадания для таблиц или процедур низок, их можно индивидуально закрепить в памяти встроенной процедурой DBMS_SHARED_POOL.KEEP.

2. Борьба за общие ресурсы

8. Активность Rollback Segments

select a.name, b.xacts, c.sid, c.serial#, c.username, d.sql_text 
from v$rollname a, v$rollstat b, v$session c, v$sqltext d,v$transaction    e 
where a.usn = b.usn 
and b.usn = e.xidusn
and c.taddr = e.addr
and c.sql_address = d.address and c.sql_hash_value = d.hash_value order by a.name, c.sid, d.piece;

Позволяет показать долго работающие операторы DML. Соответствующий сеанс можно оборвать

alter system kill session 'sid,serial#'; 

3. Характеристики выполняющихся сеансов

9. Список сеансов и параметров использования ими диска и памяти

select a.username, b.block_gets, b.consistent_gets, b.physical_reads,
b.block_changes, b.consistent_changes from v$session a, v$sess_io b where a.sid = b.sid order by a.username;

Позволяет определить сеансы с большой активностью использования пространства. Следующий шаг - определить, что выполняют подобные сеансы.

10. SQL-предложения, выполняемые сеансами

select a.sid, a.username, s.sql_text 
from v$session a, v$sqltext s 
where a.sql_address = s.address 
and a.sql_hash_value = s.hash_value 
order by a.username, a.sid, s.piece; 

(Поле PIECE упорядочивает куски SQL-текста).

11. Выдача числа сеансов от имени каждого пользователя

select username, count(*) 
from v$session 
group by username;

Если от имени одного пользователя запущено много сеансов, это может быть связано с неправильной работой приложения.

12. К каким объектам происходит текущий доступ

select a.sid, a.username, b.owner, b.object, b.type 
from v$session a, v$access b 
where a.sid = b.sid;

13. Выдача текущей статистики по пользователям

select a.sid, a.username, c.name, b.value 
from    v$session a, v$sesstat b, v$statname c 
where a.sid = b.sid and b.statistic# = c.statistic# 
and     b.value != 0; 
4. Некоторые параметры системы и текущей    конфигурации

14. Выдача основной информации о текущем экземпляре Oracle

select * 
from v$database;

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

15. Установленные в системе параметры создания новых сеансов

select * 
from v$license; 

Выдаваемый по запросу 0 означает, что параметр не установлен в INIT.ORA. Например, для поля SESSIONS_MAX выдача 0 будет означать, что число подсоединений к системе не ограничено.

16. Какие дополнительные возможности системы установлены, а какие нет

select * 
from v$option; 

17. Суммарные сведения об SGA

select * 
from v$sga; 

18. Подробные сведения об SGA

select * 
from v$sgastat;

Выдает размеры более 30 внутренних структур SGA. Например, если по результату запроса выясняется, что показатель free memory все время невелик, нужно подумать об увеличении shared pool. Иначе в какой-то момент может не хватить место для выполнения какого-нибудь пакета или хранимой процедуры.

19. Выдача параметров системы

select * 
from v$parameter 
order by name; 

Иногда бывает быстрее посмотреть нужный параметр в SQL*Plus, выдав SHOW PARAMETER …, но если параметр - это длинное имя файла, то чтобы увидеть его полностью, придется обратиться к v$parameter. В этой же таблице есть более подробная информация о том, изменяем ли параметр без остановки системы, был ли он изменен и т.д.

20. Выдача NLS-параметров

select * 
from v$nls_parameter; 

Помимо NLS_CHARACTERSET (кодировка хранения текстовых данных в базе) и NLS_NCHAR_CHARACTERSET (кодировка CLOB-данных в базе), наиболее важный интерес могут представлять поля NLS_SORT (тип сортировки текстовых данных, например, при выборке с ORDER BY или DISTINCT), а также NLS_DATE_FORMAT, который рекомендуется выставить в DD-MON-RR для снятия части проблем 2000 года.

21. Выдача информации об оперативных журнальных файлах

select a.member, b.* 
from v$logfile a, v$log b 
where a.group# = b.group#; 

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

22. Выдача информации о произведенном архивировании оперативных журнальных файлов

select * 
from v$log_history; 

Запрос незаменим для организации процедур резервного копирования/восстановления. Кроме этого, результат запроса позволяет определить реальную частоту переключения журнальных файлов. Если она невелика, "окно времени" допустимой потери данных (скажем, 1 час) может оказаться слишком велико и неприемлемо, и тогда частоту переключения журналов нужно увеличить - сократить размер журналов и, возможно, двумя параметрами переключения из INIT.ORA.

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


Страница сайта http://test.interface.ru
Оригинал находится по адресу http://test.interface.ru/home.asp?artId=3782