Под динамическими таблицами в 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. Для многих это даже, скорее, обозначение пути, могущего стать для администратора весьма плодотворным. Надеюсь, что читатель всякой квалификации найдет в вышеприведенном тексте что-то для себя полезное.