Оценка производительности системы Oracle: процессор, память, диски

Vovan_ST

Vovan_ST

ИТ специалист со стажем. Автор статьи. Профиль

Настройка сервера Oracle: память системы, процессор и дисковая подсистемаУсилия по настройке экземпляра, предпринимаемые внутри Oracle, будут давать ограниченный (или даже негативный) эффект, если не обращать никакого внимания на производительность системы (сервера) в целом. Под производительностью системы подразумевается производительность ЦП (CPU), использование памяти (ОЗУ) и дисковый ввод-вывод (массивы HDD). В этой заметке блога мы рассмотрим каждый из этих важных ресурсов весьма подробно.



Производительность ЦП

Для получения информации о производительности ЦП (CPU) сервера можно использовать такие поставляемые в составе операционной системы утилиты, как sar (System Activity Reporter — генератор отчетов по активности системы) или vmstat. Не стоит паниковать, если в периоды максимальной загрузки процессоры оказываются занятыми — именно для этого они и предназначены. Если же процессоры выглядят сильно загруженными в периоды низкой активности, тогда действительно понадобится дальнейший анализ. В листинге 1 приведен пример вывода команды sar, показывающий, насколько сильно в системе в текущий момент эксплуатируются ресурсы ЦП сервера.


$ sar -u 10 5

HP-UX finance1 B.11.00 A 9000/800 07/03/05
13:39:17   %usr   %sys   %wio   %idle
13:39:27     34     23      7      36
13:39:37     37     17      8      38
13:39:47     34     18      6      41
13:39:57     31     16      9      44
13:40:07     38     19     11      32
Average      35     19      8      38

Четыре столбца в этом листинге сообщают о следующих показателях использования ЦП:

  • столбец %usr показывает, сколько в общем времени ресурсы ЦП использовали пользователи;
  • столбец %sys — сколько времени ресурсы ЦП использовала сама система;
  • столбец %wio — сколько времени (в процентах) системах ожидала ввода-вывода;
  • столбец %idle — сколько времени ЦП находился в бездействующем состоянии.

Если показатели в столбцах %wio и %idle составляют около нуля в часы не пиковой нагрузки, значит, система ограничена возможностями ЦП.

Следует запомнить, что интенсивный уровень использования ЦП (CPU сервера) может означать, что либо какой-то процесс операционной системы поглощает ресурсы ЦП, либо какой-то процесс Oracle, возможно, причиняет ущерб. В последнем случае “виновником” может быть либо фоновый процесс наподобие PMON, либо процесс пользователя Oracle, выполняющий в отношении производственной системы какой-нибудь чрезвычайно плохой нерегламентированный запрос. В некоторых случаях удается отслеживать такого пользователя и уведомлять его о том, что его процесс уничтожается ради блага всей системы. После уничтожения зловредного процесса, однако, иногда может оказаться, что тот “жив и здоров” и продолжает благополучно перегружать систему в середине рабочего дня. Причиной тому может служить дочерний или подчиненный процесс, продолжающий выполняться даже после уничтожения самого пользователя. Поэтому лучше не лениться и дважды удостовериться в том, что причинявший проблему пользователь действительно устранен раз и навсегда.

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

 

Длина очереди выполнения

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

 

Единицы ЦП, используемые процессами

Определять, какое количество единиц ЦП использует каждый процесс UNIX, можно посредством простой команды ps:

$ ps -ef | grep f60

   UID    PID    PPID     C    STIME    TTY    TIME     CMD
oracle  20108    4768     0 09:11:49     ?     0:28 f60webm
oracle    883    4768     5 17:12:21     ?     0:06 f60webm
oracle   7090    4768    16 09:18:46     ?     1:08 f60webm
oracle  15292    4768   101 15:49:21     ?     1:53 f60webm
oracle  18654    4768     0 14:44:23     ?     1:18 f60webm
oracle  24316    4768     0 15:53:33     ?     0:52 f60webm
$ 

Здесь главный интерес представляет четвертый слева столбец: именно в нем и отображается количество единиц ЦП, которое использует каждый процесс. При наличии 100 единиц у каждого ЦП на сервере, процесс Oracle c PID-номером 15 292 (который в приведенном выше примере занимает четвертую позицию сверху) будет тратить больше всей доступной вычислительной мощи ЦП. Если сервер Oracle оснащен всего лишь двумя процессорами, потребуется обязательно обратить внимание на этот процесс и то, почему он настолько интенсивно использует ресурсы ЦП.

 

Поиск пользователей, которые больше всех используют ресурсы ЦП

Если уровни использования ЦП высоки, необходимо выяснять, какие из пользователей являются самыми интенсивными потребителями ресурсов ЦП. В листинге 6 показано, как можно легко выявлять таких пользователей.


SQL> SELECT n.username,
2 s.sid,
3 s.value
4 FROM v$sesstat s,v$statname t, v$session n
5 WHERE s.statistic# = t.statistic#
6 AND n.sid = s.sid
7 AND t.name='CPU used by this session'
8 ORDER BY s.value desc;

USERNAME            SID      VALUE
---------------   -----   --------
JOHLMAN             152      20745
NROBERTS            103       4944
JOHLMAN             167       4330
LROLLINS             87       3699
JENGMAN             130       3694
JPATEL               66       3344
NALAPATI             73       3286
SQL>

В листинге 6 видно, что ресурсы ЦП распределяются между пользователями неравномерно. Нужно обязательно выяснить, почему один пользователь потребляет столь значительное количество этих ресурсов. При необходимости использованием ресурсов ЦП можно управлять как на уровне одного, так и на уровне группы пользователей, с помощью утилиты Database Resource Manager (Диспетчер ресурсов базы данных). Через представление V$SESSTAT можно получать информацию об использовании ЦП на уровне сеанса, как показано в листинге 7.


SQL> SELECT sid, s.value "Total CPU Used by this Session"
2 FROM V$SESSTAT S
3 WHERE S.statistic# = 12
4* ORDER BY S,value DESC;

    SID     Total CPU Used by this Session
   -----    ------------------------------
    496                 27623
    542                 21325
    111                 20814
    731                 17089
    424                 15228

SQL>

 

На что расходуется время ЦП?

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

  • загрузка SQL-операторов в библиотечный кэш;
  • поиск в разделяемом пуле проанализированных версий SQL-операторов;
  • синтаксический анализ SQL-операторов;
  • выполнение запроса к словарю данных;
  • считывание данных из кэша буферов;
  • обход деревьев индексов для выборки индексных ключей.

Общее время ЦП, используемое экземпляром (или сеансом), может рассматриваться как сумма следующих компонентов:

total CPU time = parsing CPU usage + recursive CPU usage + other CPU usage общее время ЦП = использование ЦП для синтаксического анализа + использование ЦП для рекурсивных вызовов + использование ЦП для других целей

В идеале общие показатели по использованию ЦП в первых двух категориях (для синтаксического анализа и для рекурсивных вызовов) должны быть небольшими. Например, для оценки использования ЦП в масштабах сеанса можно выдать запрос, показанный в листинге 8.


SQL> SELECT name,value FROM V$SYSSTAT
2 WHERE NAME IN ('CPU used by this session',
3 'recursive cpu usage',
4* 'parse time cpu');

NAME                            VALUE
-------------------------   ---------
recursive cpu usage           4713085
CPU used by this session     98196187
parse time cpu                 132947
3 rows selected.
SQL>

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


На заметку! В следующих примерах анализировать показатели использования можно как на уровне экземпляра через представление V$SYSSTAT, так и на уровне отдельного сеанса с помощью представления V$SESSTAT. Главное помнить, что под значением столбца total CPU used by this session (общий показатель использования ЦП в данном сеансе) в представлении V$SYSSTAT подразумевается сумма показателей использования ЦП во всех сеансах вместе.


Настройка системы Oracle: память ОЗУ, процессоры CPU  и диски HDD 

Использование ЦП для синтаксического анализа

Как уже рассказывалось в начале данной статьи, синтаксический анализ является дорогостоящей операцией, которую следует стараться сокращать до минимума. В следующем примере показатель использования ЦП для синтаксического анализа выглядит довольно низким по сравнению с общим процентом использования ЦП. Первый запрос показывает, чтоб общий показатель использования ЦП в экземпляре составляет 49159124

SQL> SELECT name, value FROM V$SYSSTAT
2* WHERE name LIKE '%CPU%';

NAME                           VALUE
---------------------------  --------
CPU used when call started   13220745
CPU used by this session     49159124
2 rows selected.
SQL>

Следующий запрос показывает, что показатель использования ЦП для синтаксического анализа составляет 96431 и потому является незначительным по сравнению с общим показателем использования ЦП в базе данных: 

SQL> SELECT name, value FROM V$SYSSTAT
2 WHERE name LIKE '%parse%';

NAME                     VALUE
--------------------  --------
parse time cpu           96431
parse time elapsed      295451
parse count (total)    3147900
parse count (hard)       29139
4 rows selected.
SQL>

В листинге 5 приведен пример сеанса, в котором общий показатель использования ЦП является высоким из-за высокого показателя по использованию ЦП для синтаксического анализа.

SQL> SELECT a.value " Tot_CPU_Used_This_Session",
2 b.value "Total_Parse_Count",
3 c.value "Hard_Parse_Count",
4 d.value "Parse_Time_CPU"
5 FROM v$sysstat a,
6 v$sysstat b,
7 v$sysstat c,
8 v$sysstat d
9 WHERE a.name = 'CPU used by this session'
10 AND b.name = 'parse count (total)'
11 AND c.name = 'parse count (hard)'
12* AND d.name = 'parse time cpu';

 Tot_CPU_Used  Total_Parse_Count    Hard_Parse_Count   Parse_Time_CPU
 This_Session
-------------  -----------------  ------------------  ---------------
         2240              53286                 281             1486
SQL>​

 


В этом примере время использования ЦП для синтаксического анализа составляет две трети от общего времени использования ЦП. Очевидно, что высокие показатели по синтаксическому анализу должны вызывать беспокойство даже несмотря на то, что большинство из операций синтаксического анализа является частичными (soft parse). В следующем разделе показано, что можно делать для сокращения количества выполняемых в базе данных операций синтаксического анализа.

 

Сокращение времени использования ЦП для синтаксического анализа

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

  1. Используйте переменные связывания и удалите жестко закодированные литеральные значения из кода, как объяснялось ранее в этой статье, в разделе “Оптимизация использования библиотечного кэша”.
  2. Удостоверьтесь, что для разделяемого пула не выделено слишком много памяти. Следует помнить о том, что даже при наличии точной копии нового SQL-оператора в библиотечном кэше, Oracle будет искать его путем сканирования всех содержащихся в кэше операторов. В случае если в кэше содержится несметное количество относительно бесполезных операторов, они будут только замедлять работу экземпляра и увеличивать время, затрачиваемое на выполнение синтаксического анализа.
  3. Проверьте, что в библиотечном кэше не возникает соперничества за получение защелки (latch contention), которое может приводить к увеличению времени использования ЦП для синтаксического анализа.
  4. Если вывод TKPROF или одного из упомянутых выше запросов показывает, что общий показатель времени использования ЦП для синтаксического анализа составляет 90% или больше, удостоверьтесь в том, что все таблицы в запросах недавно подвергались анализу. В случае отсутствия статистических данных по каким-то из таблиц, эти данные будут генерироваться в ходе процесса синтаксического анализа, и время использования ЦП для синтаксического анализа будет из-за этого очень сильно возрастать.

 

Использование ЦП для рекурсивных вызовов

При использовании ЦП для рекурсивных вызовов большая часть времени тратится в основном на поиск в словаре данных и на выполнение программ PL/SQL. Следовательно, если в приложении задействовано много пакетов и процедур, показатель по использованию ЦП для рекурсивных вызовов будет очень высоким.

В следующем примере нет причин для беспокойства, потому что показатель использования ЦП для рекурсивных вызовов составляет только 5% от общего показателя использования ЦП: 

SQL> SELECT name, value FROM V$SYSSTAT
2 WHERE name IN ('CPU used by this session',
3* 'recursive cpu usage');

NAME                            VALUE
--------------------------  ---------
recursive cpu usage           4286925
CPU used by this session     84219625
2 rows selected.
SQL>

Если процент использования ЦП для рекурсивных вызовов составляет значительную часть от общего показателя использования ЦП, может понадобиться проверить, адекватное ли количество памяти выделено для разделяемого пула. У приложений на базе PL/SQL, однако, показатель использования ЦП для рекурсивных запросов всегда будет довольно высоким.


На заметку! Большое количество рекурсивных SQL-операторов также может свидетельствовать о том, что Oracle доводится выполнять операции по управлению пространством, например, выделять экстенты (extents). Это крайне пагубно сказывается на производительности. Избегать подобной проблемы можно, увеличивая размеры экстентов для объектов базы данных. Это является еще одной веской причиной выбирать локально управляемые табличные пространства, которые сокращают количество рекурсивных SQL-операторов.


 

Оценка использования памяти

Физическая память операционной системы удерживает все данные и программы за счет выполнения их загрузки с диска. Системный ЦП выполняет программы только в том случае, если они загружаются в физическую память. При возникновении ситуации с чрезмерным использованием физической памяти, операционная система начинает использовать виртуальную память, которая представляет собой область хранения, находящуюся на вторичном носителе, например, на диске, и предназначена для временного удержания каких-нибудь используемых данных и/или программ. Пространство, занимаемое виртуальной памятью, называется пространством подкачки (swap space). Когда системе требуется место в физической или основной памяти, она “выгружает” некоторые программы в область подкачки и тем самым высвобождает дополнительное пространство в физической памяти для выполнения программы.

Операционная система выгружает данные в область подкачки в виде так называемых страниц (pages), каковые являются наименьшими единицами памяти, которые могут использоваться для передачи памяти туда и обратно между физической памятью и областью подкачки. Когда у операционной системы возникает необходимость в странице, которая была выгружена в область подкачки, происходит так называемая страничная ошибка (page fault). Страничные ошибки часто называют просто “операциями страничного обмена” (paging) и подразумевают передачу данных из виртуальной памяти обратно в физическую. Чрезмерное количество операций страничного обмена приводит к снижению производительности операционной системы и, как следствие, ухудшению также и производительности экземпляра Oracle.

Одним из наилучших способов для проверки производительности памяти операционной системы является применение утилиты vmstat.

 

Оценка использования дисковой подсистемы и операций ввода-вывода

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

  • Выбор конфигурации RAID. О возможных конфигурациях системы RAID подробно рассказывалось в этом блоге. Главное помнить, что конфигурация RAID 5 не обеспечивает идеальной производительности средств ввода-вывода, если в приложении используется большое количество операций записи. Для получения более высокой скорости ввода-вывода нужно применять такую конфигурацию RAID, которая предусматривает расслоение дисков, желательно в соответствии с рекомендациями Oracle.
  • “Чистые” устройства и файловые системы в ОС. В некоторых обстоятельствах можно выигрывать от использования “чистых” (не содержащих никаких файловых систем) устройств, которые работают в обход кэша буферов операционной системы. Эти устройства, однако, имеют и свои недостатки, в том числе очень ограниченные возможности для резервного копирования, поэтому нужно точно знать, что их преимущества будут перевешивать их недостатки. В целом такие устройства предлагают более скоростные возможности для ввода-вывода и обеспечивают более высокую степень производительности для насыщенного операциями записи приложения. Еще можно рассматривать вариант использования альтернативных файловых систем наподобие файловой системы VXFSS от VERITAS, которая помогает выполнять операции ввода-вывода быстрее посредством доступной в ней опции прямого ввода-вывода.
  • Объем ввода-вывода. Объем ввода-вывода исчисляется в размере блоков Oracle. Минимальный объем ввода-вывода зависит размера блоков, а максимальный — от значения параметра инициализации DB_FILE_MULTIBLOCK_READ_COUNT. В приложении OLTP объем ввода-вывода должен быть маленьким, а в приложении DSS — гораздо большим. Начиная с выпуска Oracle 10.2, в случае не установки значения для данного параметра, база данных настраивает его автоматически.
  • Размеры слоев логических томов. Размер слоя (stripe size), также называемый шириной слоя (stripe width), зависит от глубины слоя и количества дисков в расслоенном наборе. В случае применения расслоения в отношении множества дисков производительность средств ввода-вывода в базе данных будет выше и нагрузка будет распределяться лучше. Нужно делать так, чтобы размер слоя был больше размера среднего запроса на выполнение ввода-вывода, иначе для обработки одного запроса на ввод-вывод от Oracle будет выполняться несколько операций ввода-вывода. При большом количестве одновременных запросов на ввод-вывод размер слоя должен быть гораздо больше размера ввода-вывода. Большинство современных утилит LVM умеет производить динамическую реконфигурацию размера слоев.
  • Количество контроллеров и дисков. Количество шпинделей и количество контроллеров играет важную роль в производительности дисков. Даже при наличии большого количества шпинделей, на уровне контроллеров предположительно все равно может возникать состязание.
  • Распределение ввода-вывода. Следует стараться избегать неравномерного распределения ввода-вывода в дисковой системе. В случае использования LVM и применения технологии расслоения на уровне оборудования, по этому поводу можно совершенно не волноваться. Однако в противном случае следует обязательно вручную организовывать файлы данных на дисках так, чтобы количество операций ввода-вывода выглядело в системе относительно равномерно. Обратите внимание на то, что таблицы и индексы обычно требуется размещать в отдельных табличных пространствах, но никакого правила касательно того, что их также нужно размещать и на отдельных дисках, не существует. Поскольку индексы считываются перед таблицами, они могут спокойно сосуществовать на одном и том же диске.

Вас заинтересует / Intresting for you:

Настройка памяти базы данных O...
Настройка памяти базы данных O... 19200 просмотров Stas Belkov Sat, 07 Jul 2018, 15:44:14
Создание БД Oracle 12c с макси...
Создание БД Oracle 12c с макси... 4268 просмотров Андрей Васенин Mon, 20 Aug 2018, 13:43:20
Настройка производительности э...
Настройка производительности э... 4249 просмотров Antoniy Mon, 29 Jan 2018, 17:37:48
Мониторинг Oracle через метрик...
Мониторинг Oracle через метрик... 5060 просмотров sepia Tue, 21 Nov 2017, 13:18:05
Войдите чтобы комментировать

apv аватар
apv ответил в теме #10026 2 года 9 мес. назад
Отлично преподнесены азы настройки СУБД Oracle. Новичку будет понятно с чего начать.
Боба, наша благодарность!