Руководство по диагностике и устранению проблем в Oracle

Тарик Фарук, Майк Олт, Пауло Португал, Мохамед Хури,  
Сайед Джаффар Хуссейн, Джим Чупрински, Гай Хэррисон

Руководство  
по диагностике и устранению  
проблем в Oracle

Tariq Farooq, Mike Ault, Paulo Portugal, Mohamed Houri, 
Syed Jaffar Hussain, Jim Czuprynski, Guy Harrison

Oracle Database Problem 
Solving and Troubleshooting 
Handbook

 
Boston • Columbus • Indianapolis • New York • San Francisco • Amsterdam • Cape Town 
Dubai • London • Madrid • Milan • Munich • Paris • Montreal • Toronto • Delhi • Mexico City  
São Paulo • Sidney • Hong Kong • Seoul • Singapore • Taipei • Tokyo

Тарик Фарук, Майк Олт, Пауло Португал, Мохамед Хури,  
Сайед Джаффар Хуссейн, Джим Чупрински, Гай Хэррисон

Руководство  
по диагностике  
и устранению проблем  
в Oracle

Москва, 2018

УДК004.65:004.451OracleDatabase
ББК32.973.34
Ф25

ФарукТ.,ОлтМ.,ПортугалП.,ХуриМ.,ХуссейнС.Дж.,Чупрински Дж.,ХэррисонГ.
Ф25 Руководство по диагностике и устранению проблем в Oracle / пер. с англ. А. 

В. Снастина. – М.: ДМК Пресс, 2018. – 498 с.: ил.

ISBN978-5-97060-448-9

Книга предоставляет исчерпывающие и актуальные рекомендации по обеспечению 
надежной и эффективной работы СУБД Oracle в сложных производственных корпоративных средах. Семь ведущих экспертов по Oracle объединили свои усилия, чтобы 
создать не имеющий аналогов сборник испытанных решений, практических примеров 
и пошаговых описаний процедур для Oracle версий 12c, 11g и более поздних. Каждое 
решение тщательно подготовлено, для того чтобы помочь опытным администраторам 
понять и устранить серьезные проблемы как можно быстрее.
Рассмотрены сегменты LOB, пространства таблиц UNDO, циклы ожидания высоконагруженных буферов сборщика мусора, время задержек при ответах на запросы, конкуренция при блокировках, индексация, распределенные XA-транзакции, резервное 
копирование/восстановление RMAN и др. Описаны оптимизация языка определения 
данных, настройка механизма VLDB, исследование и тестирование баз данных, гибкая 
настройка совместного использования курсоров, извлечение больших объемов данных, 
перемещение данных, SSD, индексация, а также способы решения проблем с Oracle RAC.
Издание предназначено администраторам баз данных, а также программистам, 
работающим с Oracle.

УДК 004.65:004.451Oracle Database
ББК 32.973.34

Authorized translation from the English language edition, entitled Oracle Database Problem 
Solving and Troubleshooting Handbook; ISBN 0134429206; by Farooq, Tariq; and by Ault, Mike; and 
by Portugal, Paulo; and by Houri, Mohamed; and by Hussain, Syed Jaffar; and by Czuprinski, Jim; and 
by Harrison, Guy; published by Pearson Education, Inc., publishing as Addison-Wesley professional. 
Copyright © 2016 by Pearson Education, Inc.
All rights reserved. No part of this book may be reproduced or transmitted in any form or by any 
means, electronic or mechanical, including photocopying, recording or by any information storage 
retrieval system, without permission from Pearson Education, Inc. RUSSIAN language edition 
published by DMK PUBLISHERS, Copyright © 2017.
Все права защищены. Любая часть этой книги не может быть воспроизведена в какой 
бы то ни было форме и какими бы то ни было средствами без письменного разрешения владельцев авторских прав.

ISBN 978-0-13-442920-5 (анг.) 
ISBN 978-5-97060-448-9 (рус.) 

Copyright © 2016 Pearson Education, Inc.
© Оформление, издание, перевод, ДМК Пресс, 2018

Содержание

Предисловие ..........................................................................................................................14

Благодарности .......................................................................................................................16

Об авторах ..............................................................................................................................19

О технических рецензентах и прочих участниках проекта .....................................23

Глава 1. Диагностика и настройка производительности сегментов LOB .............25
Краткое описание типа данных LOB .......................................................................................25

Устранение проблемы с LOB-объектом: пример из практики .........................................26
Еще один пример из реальной практики: HW Resolution .................................................28
Проблемы BASICFILE LOB: более эффективное решение .................................................32

Сравнение LOB-объектов BASICFILE с SECUREFILE LOB .......................................................32

Различия между новыми и старыми типами LOB .............................................................33
Преобразование BASICFILE LOB-объектов в SECUREFILE LOB-объекты .........................36

Воздействие параметра PCTFREE на LOB-объекты ................................................................38
Улучшение производительности операции вставки данных INSERT ...................................41
Резюме .......................................................................................................................................42

Глава 2. Восстановление табличного пространства, поврежденного  
при выполнении операции UNDO ...................................................................................43
Общий обзор процедуры отмены изменения данных...........................................................43

Важность параметра UNDO_RETENTION ............................................................................44
Настройка параметра UNDO_RETENTION ..........................................................................45

Сегменты DTP, XA и сегмент отката (rollback) ........................................................................46

Прочие нестандартные ситуации, возникающие в сегментах отката  
и сегментах Undo ..................................................................................................................48

Восстановление поврежденного табличного пространства Undo ........................................48

Профилактика, обнаружение и восстановление повреждений ........................................49
Обработка повреждений памяти ........................................................................................50
Обработка логических повреждений..................................................................................53
Устранение повреждений носителей информации ...........................................................53

Резюме .......................................................................................................................................56

Глава 3. Обработка событий ожидания освобождения буферов  
глобального кэша .................................................................................................................58
Обзор событий ожидания освобождения буферов ................................................................58
Практическое применение утилиты ORAchk  .........................................................................59

Установка утилиты ORAchk  .................................................................................................59
Результаты выполнения ORAchk : пример выводимой информации ..............................61

Устранение событий gc buffer busy wait ..................................................................................61

Использование ADDM для поиска информации о событии .............................................63
Использование AWR для поиска информации о событии ................................................64
Использование ASH для поиска информации о событиях ................................................66

Определение проблем, связанных с событием gc buffer busy wait .......................................67

Использование представлений ASH для поиска ожидающих сеансов .............................68
Быстрое определение узких мест, снижающих производительность ..............................69

 
Содержание

Методики устранения событий gc buffer busy wait ................................................................72
Резюме .......................................................................................................................................73

Глава 4. Адаптивное разделение курсора .....................................................................74
Алгоритм работы механизма ACS ...........................................................................................75

Чувствительность к связыванию с определением диапазона избирательности.............75
Чувствительность к связыванию с предикатом равенства и гистограммой ...................78
Чувствительность к связыванию при секционировании по диапазонам ключей ..........79

Работа механизма адаптивного разделения курсора (ACS) ..................................................81
Мониторинг осведомленности о связывании в механизме ACS ...........................................85

Связь BUCKET_ID и COUNT ..................................................................................................86
Как сделать курсор осведомленным о связывании ...........................................................89

Курсор, осведомленный о связывании ...................................................................................96
Практический пример ..............................................................................................................99
Резюме ..................................................................................................................................... 105

Глава 5. Стабилизация времени ответа на запрос  
с помощью механизма управления планами SQL.................................................... 106
Общие положения .................................................................................................................. 107
Создание базовой линии плана выполнения SQL ................................................................ 110

Автоматический захват плана выполнения..................................................................... 110
Загрузка планов из кэша курсора ..................................................................................... 113

Имитация базовых линий ...................................................................................................... 115
Оптимизатор Oracle и его взаимодействие с механизмом управления планами SQL ..... 119

План стоимостного оптимизатора не соответствует базовой линии  
плана выполнения SQL ...................................................................................................... 122
Базовая линия плана выполнения SQL не является воспроизводимой ......................... 128

Воспроизводимость базовой линии плана выполнения SQL .............................................. 132

Переименование индекса .................................................................................................. 133
Изменение типа индекса ................................................................................................... 135
Добавление в индекс замыкающих столбцов .................................................................. 136
Реверсирование индекса ................................................................................................... 137

Параметр NLS_SORT и воспроизводимость базовой линии плана выполнения SQL ........ 138
Сравнение параметров ALL_ROWS и FIRST_ROWS ............................................................... 142
Адаптивное разделение курсора и механизм управления планами SQL ........................... 146

Взаимодействие механизмов ACS и SPM в версии Oracle 11g Release 11.2.0.3.0 ........... 147
Взаимодействие механизмов ACS и SPM в версии Oracle 12c Release 12.1.0.1.0 ........... 152

Резюме ..................................................................................................................................... 155

Глава 6. Советы, методики и особые приемы оптимизации  
для языка определения данных (DDL) ........................................................................ 156
Основы оптимизации операций языка определения данных ............................................ 156
Механизм оптимизации операций языка определения данных (DDL) .............................. 159

Оценка мощности таблицы ............................................................................................... 160
Столбец C_DDL в виртуальном столбце ............................................................................ 162
Столбец C_DDL в расширении группы столбцов ............................................................. 163
Что происходит при изменении значения по умолчанию для столбца C_DDL ............. 165
Столбец C_DDL и индексы ................................................................................................. 168

Оптимизация операций языка определения данных для столбцов с атрибутом NULL ..... 170
Резюме ..................................................................................................................................... 175

Содержание  7

Глава 7. Управление, оптимизация и настройка очень больших  
баз данных ........................................................................................................................... 176
Общие сведения об очень больших базах данных ............................................................... 176
Оптимальная базовая конфигурация ................................................................................... 177

Шаблон конфигурации хранилища данных ..................................................................... 178
Выбор оптимального размера блока данных ................................................................... 178
Табличные пространства большого файла ....................................................................... 179
Выбор правильных размеров системной глобальной области (SGA)  
и программной глобальной области (PGA) ...................................................................... 180
Группы временных табличных пространств .................................................................... 181
Секционирование данных ................................................................................................. 181
Секционирование по индексу: сравнение локального и глобального индексов .............. 182
Сжатие данных ................................................................................................................... 183
Сжатие таблицы ................................................................................................................. 183
Инструменты Heat Map и Automatic Data Optimization (ADO)........................................ 184
Расширенная функция сжатия секционированного индекса ............................................ 185

Основные правила настройки производительности очень большой БД ........................... 185

Пример из реальной жизни ............................................................................................... 186
Ограничение воздействия индексов на операции загрузки данных ............................. 187
Максимальное использование ресурса ............................................................................ 188

Сбор статистических данных оптимизатора ........................................................................ 189

Краткий обзор постепенно накапливаемых статистических данных ........................... 189
Сбор статистических данных в параллельном режиме ................................................... 191
Установка значения параметра ESTIMATE_PERCENT ...................................................... 193

Наилучшие практические методики резервного копирования и восстановления  
данных ..................................................................................................................................... 193

Решения на основе Exadata ............................................................................................... 195
Использование среды Data Guard ..................................................................................... 195

Резюме ..................................................................................................................................... 195

Глава 8. Эффективные практические методики резервного  
копирования и восстановления данных с использованием  
диспетчера восстановления ............................................................................................ 196
Идеальный план резервного копирования и восстановления ............................................ 196
Общий обзор диспетчера восстановления ........................................................................... 197
Рекомендации по проектированию стратегий резервного копирования баз данных ...... 198

Процедуры полного и инкрементального резервного копирования ............................. 199
Резервное копирование со сжатием данных ................................................................... 199
Инкрементальное резервное копирование ..................................................................... 200
Ускоренные операции инкрементального резервного копирования ............................ 201
Операции отката к предыдущему состоянию в технологии Oracle Flashback ............... 202
Резервное копирование с использованием дисковой памяти ....................................... 203
Стратегия Recover Forward Forever .................................................................................... 203

Проверка корректности резервных копий диспетчера восстановления ........................... 211
Оптимизация и настройка операций резервного копирования ......................................... 212

Настройка производительности операций резервного копирования  
с использованием дисковых накопителей ....................................................................... 213

Использование диспетчера восстановления в кластерных (RAC) базах данных ............... 214
Хранение данных в каталоге восстановления ...................................................................... 216
Надежная стратегия восстановления .................................................................................... 216
Использование консультанта по восстановлению данных DRA ......................................... 218
Резюме ..................................................................................................................................... 219

 
Содержание

Глава 9. Методики тестирования и настройки базы данных  
с использованием анализа автоматического репозитория  
рабочей нагрузки: часть 1 ............................................................................................... 221
Общий обзор автоматического репозитория рабочей нагрузки ........................................ 222
Что нужно искать .................................................................................................................... 223
Раздел заголовка ..................................................................................................................... 224

Профиль нагрузки .............................................................................................................. 226
Характеристики производительности экземпляра БД ................................................... 227
Совместно используемый пул оперативной памяти ....................................................... 228
Ожидание событий ............................................................................................................ 228
Средняя нагрузка ............................................................................................................... 231
Использование процессоров экземпляром БД ................................................................ 232
Статистические характеристики оперативной памяти .................................................. 232

Специальные разделы отчета по кластерным (RAC) базам данных ................................... 233

Статистические данные по использованию процессоров в кластерной  
базе данных ........................................................................................................................ 234
Статистические данные по нагрузке глобального кэша ................................................. 234
Глобальный кэш и сервисы формирования очереди ....................................................... 234
Сетевые соединения в кластере ........................................................................................ 236

Статистические характеристики временной модели .......................................................... 236
Статистические характеристики операционной системы .................................................. 237

Ожидание событий в интерактивном режиме................................................................. 238
Ожидание событий в фоновом режиме ............................................................................ 240
Гистограммы времени ожидания событий ...................................................................... 241
Статистические данные по сервисам ............................................................................... 242

Раздел команд SQL ................................................................................................................. 243

Общее затраченное время ................................................................................................. 243
Общее процессорное время .............................................................................................. 244
Общее время использования буфера ................................................................................ 244
Общее время операций чтения с диска ............................................................................ 245
Общее количество выполнения команд ........................................................................... 245
Общее количество операций (вызовов) синтаксического анализа ................................ 245
Совместно используемая память ...................................................................................... 246
Счетчик версий команд ..................................................................................................... 246
Общее время ожидания событий в кластере ................................................................... 247

Статистические данные по операциям в экземпляре базы данных ................................... 247

Статистические данные для согласованных операций чтения ...................................... 250
Статистические характеристики операций получения блоков базы данных ............... 251
Статистические данные по «грязным» блокам ................................................................ 251
Статистические данные по очередям ............................................................................... 251
Счетчик выполнения ......................................................................................................... 251
Статистические данные по свободным буферам ............................................................ 251
Статистические данные по глобальному кэшу (GC) ........................................................ 252
Статистические данные для поиска по индексу .............................................................. 252
Статистические данные по листьям-узлам в B-деревьях ............................................... 253
Статистические данные по открытым курсорам ............................................................. 253
Статистические данные по операциям синтаксического анализа ................................. 253
Статистические данные по физическим операциям чтения и записи .......................... 253
Статистические данные по рекурсивным операциям .................................................... 256
Статистические данные о повторно выполняемых командах ....................................... 256
Статистические данные по курсорам сеанса ................................................................... 257

Содержание  9

Статистические данные по операциям сортировки ........................................................ 257
Общая длина «грязной» очереди ...................................................................................... 257
Статистические данные по выборкам из таблиц ............................................................. 258
Откаты транзакций ............................................................................................................ 258
Статистические данные по вектору изменений отмены операций ............................... 259
Статистические данные пользователей ........................................................................... 259
Статистические данные по рабочей области ................................................................... 260
Статистические данные по операциям экземпляра БД – абсолютные значения ......... 260
Статистические данные по операциям экземпляра БД – потоковые операции ........... 260

Резюме ..................................................................................................................................... 260

Глава 10. Методики тестирования и настройки базы данных  
с использованием анализа автоматического репозитория  
рабочей нагрузки: часть 2 ............................................................................................... 261
Статистические данные по вводу/выводу в табличном пространстве ............................... 261
Статистические данные по пулу буферов ............................................................................. 263

Статистические данные по пулу буферов ........................................................................ 265
Статистические данные по восстановлению экземпляра ............................................... 265
Раздел рекомендаций по пулу буферов ............................................................................ 266

Статистические данные по программной глобальной области .......................................... 266

Общие показатели по программной глобальной области .............................................. 268
Сводные статистические показатели цели PGA .............................................................. 268
Сводная гистограмма цели PGA ........................................................................................ 268
Рекомендации по использованию памяти в PGA ............................................................ 270

Статистические данные по совместно используемому пулу .............................................. 271
Прочие рекомендации ........................................................................................................... 271

Рекомендации по параметру SGA_TARGET ...................................................................... 273
Рекомендации по пулу потоков (streams) ........................................................................ 273
Рекомендации по пулу Java ............................................................................................... 273

Статистические данные по ожиданиям, связанным с буферами ....................................... 273
Статистические данные по очередям ................................................................................... 275
Статистические данные по сегментам отмены действий (откатов) ................................... 276
Статистические данные по защелкам ................................................................................... 278

Операции с защелками ...................................................................................................... 280
Нарушения состояния «сна» защелок ............................................................................... 280
Счетчик защелок и циклов ожиданий .............................................................................. 280
Защелки как источники промахов .................................................................................... 281
Обзор спящих мьютексов .................................................................................................. 281
Защелки-предки и защелки-потомки .............................................................................. 282

Области доступа к сегментам ................................................................................................ 282
Разделы операций с библиотечным кэшем .......................................................................... 284
Разделы компонентов динамической памяти ..................................................................... 287
Разделы памяти процессов .................................................................................................... 289

Общая сводка по памяти процессов ................................................................................. 290
Общая сводка по памяти системной глобальной области .............................................. 291
Различия в сегментации системной глобальной области ............................................... 291

Разделы компонентов потоков .............................................................................................. 291
Статистические данные по ограничениям ресурсов ........................................................... 293
Изменения параметров инициализации .............................................................................. 294
Статистические данные по глобальной очереди и другие разделы по кластерной БД ..... 295

Статистические данные по глобальной очереди ............................................................. 298
Статистические данные по службе глобального согласованного чтения ...................... 298

 
Содержание

Статистические данные по обслуживанию текущих глобальных объектов .................. 299
Статистические данные по обмену с глобальным кэшем ............................................... 299
Статистические данные по временам обмена с глобальным кэшем ............................. 299
Статистические данные по прямому обмену с глобальным кэшем ............................... 299
Статистические данные по времени прямого обмена с глобальным кэшем ................ 300
Статистические данные по задержкам пингования сетевого соединения .................... 300
Пропускная способность сетевого соединения на стороне клиента .............................. 300
Статистические данные об устройстве сетевого соединения ......................................... 300

Резюме ..................................................................................................................................... 301

Глава 11. Сценарии устранения проблем в кластерных базах данных ............. 302
Устранение проблем и настройка кластерной базы данных ............................................... 303

Первая проверка с помощью ORAchk  ............................................................................... 303
Использование утилиты TFA Collector ............................................................................. 303
Использование репозитория автоматической диагностики .......................................... 303
Проверка журнальных файлов предупреждений и трассировки ................................... 304
Применение трех «А» ......................................................................................................... 304
Проверка защищенного соединения в кластерной БД.................................................... 304
Установка режима трассировки и отслеживание журналов трассировки ...................... 304
Использование монитора работоспособности кластера ................................................. 305
Прочие инструменты и утилиты ....................................................................................... 305
Полезные ресурсы My Oracle Support (MOS) .................................................................... 305

Бесперебойно работающая экосистема RAC ........................................................................ 305

Архитектура, обеспечивающая максимальную доступность ......................................... 306
Оптимизированные и эффективные базы данных в кластере RAC ............................... 307

Устранение проблем в кластере RAC с помощью Диспетчера предприятия Oracle 12c ...... 309
Утилиты и команды для устранения проблем ..................................................................... 309
Резюме ..................................................................................................................................... 315

Глава 12. Использование консультантов по командам SQL  
для анализа и устранения проблем с языком SQL .................................................. 316
OEM 12c – SQL Advisors Home ................................................................................................ 317
Консультант по настройке команд SQL ................................................................................. 317

Запуск консультанта по настройке команд SQL в среде OEM 12c .................................. 318
Запуск консультанта по настройке команд SQL вручную в среде SQL*Plus .................. 321

Консультант по оптимизации доступа SQL  ......................................................................... 322

Запуск консультанта по оптимизации доступа SQL в среде OEM 12c ............................ 322
Запуск консультанта по оптимизации доступа SQL вручную в среде SQL*Plus ............ 325

Консультант по исправлению кода SQL ................................................................................ 326
Анализатор производительности SQL .................................................................................. 327
Резюме ..................................................................................................................................... 328

Глава 13. Применение утилиты Data Pump для перемещения  
данных и объектов............................................................................................................. 329
Использование механизма Data Pump .................................................................................. 329

Копирование объектов ...................................................................................................... 330
Режимы механизма Data Pump ......................................................................................... 331

Работа с частными и общедоступными объектами ............................................................. 332

Сохранение и восстановление связей БД ......................................................................... 332
Экспорт общедоступных связей БД и синонимов ........................................................... 333
Проверка корректности содержимого файла дампа экспорта ....................................... 334