Юрчишин В.М., Клим Б.В. і інші. Організація баз даних

Подождите немного. Документ загружается.

Додаткові обов’язки DBA:

▪ аналіз даних. Іноді DBA просять провести аналіз даних, щоб

одержати рекомендації щодо поліпшення продуктивності чи

ефективності збереження даних. Це може бути пов’язано з

індексацією таблиць або з функціонуванням паралельних

запитів;

▪ розробка БД (попередня). DBA часто залучається до попередньої

стадії розробки структури БД. При включенні DBA у процес

дизайну БД багато проблем можуть бути попереджені;

▪ моделювання та оптимізація даних. При моделюванні даних

можна врахувати особливості системи вводу-виводу і значно

збільшити продуктивність СУБД;

▪ надання допомоги розроблювачам у питаннях мови SQL, про

збережені процедури. DBA повинен бути готовим стати

консультантом для розроблювачів і користувачів. Він досить

часто залучається до усунення проблем SQL-коду і до розробки

(написанню) збережених процедур;

▪ розробка виробничих стандартів і угод по іменах. Оскільки в

розробці і розгортанні додатків можуть брати участь декілька

різних груп програмістів, то DBA часто залучається на роль

розроблювача виробничих стандартів і угод по іменах, щоб

додатки відповідали цим стандартам;

▪ документування середовища. DBA повинен документувати

кожен аспект середовища СУБД, включаючи конфігурацію

устаткування, відновлення та зміни програмного забезпечення і

СУБД, а також усі питання, пов’язані зі зміною системи і її

параметрів;

▪ консультування розробників і кінцевих користувачів;

▪ перевірка і тестування нових програм. DBA може брати участь у

перевірці нових програм, для того, щоб дати рекомендації,

основані на цій перевірці. Це може бути придбання нового

програмного забезпечення чи планове їх відновлення і перехід

на нові версії;

▪ оцінка нового програмного й апаратного забезпечення;

▪ планування завантаження системи і необхідного обсягу пам’яті.

Визначення необхідності в придбанні додаткових серверів,

додаткової дискової й оперативної пам’яті, щоб задовільнити

зрослі потреби користувачів.

141

5.2 Аудит

Засоби аудиту призначені для періодичного слідкування за

діями окремих користувачів з боку АБД. Для активізації служби

аудиту необхідно включити у файлі параметрів ініціалізації бази

даних E:\ORANTA\DATABASE\init.ora параметр AUDIT TRAIL = TRUE (він

присутній, але закоментарений символом #) і перезапустити

сервер. Сама по собі, активізація служби аудиту лише дозволяє

його проводити. Як правило, вона виконується один раз після

іністаляції сервера БД. Зрозуміло, що в окремих версіях Oracle,

служба аудиту може виявитися вже активізованою під час

інсталяції сервера, в чому можна впевнитися, прочитавши текст

файлу init.ora .

Після запуску команди аудиту його результати автоматично

заносяться у системну таблицю sys.aud$. На її базі створено

декілька системних представлень, серед яких найбільше вживаним

є dba_audit_trail.

Аудит системних подій можна запустити за допомогою

команди:

AUDIT список_імен_подій або системних_привілеїв

[BY список_користувачів]

[WHENEVER [NOT] SUCCESSFUL];

Параметр список_імен_подій може включати такі дії, як фіксація

входження користувача в систему або неуспішної спроби виконати

SQL-речення, якщо об’єкт бази даних не існує, CREATE, DROP,

ALTER та ін.

Конструкція BY користувач означає, що фіксуються дії тільки

конкретного користувача чи перерахованих у списку користувачів.

Якщо вона не зазначена, то фіксуються дії всіх користувачів.

Конструкція WHENEVER SUCCESSFUL означає, що в таблицю

аудиту заноситься запис про дію тільки у випадку успішного (NOT

– неуспішного) виконання операції. Якщо конструкція WHENEVER

не зазначена, то аудиторський запис вноситься незалежно від

успішності виконання операції.

Запровадимо, наприклад, автоматичний аудит процесу

реєстрації (з’єднань) користувачів до системи. Це може зробити

користувач system або інший, який має необхідні привілеї. Він

запустив команду аудиту, яка має такий вигляд:

142

SQL> AUDIT CONNECT;

Audit succeeded.

Після того, як послали свої команди на з’єднання з БД

користувачі: user1, user2, livak і sys, аудит був припинений.

SQL> CONNECT user1/stud;

Connected.

SQL> CONNECT user2/stud;

Connected.

SQL> CONNECT livak/parol;

ERROR: ORA-01017: invalid username/password;

logon denied

Warning: You are no longer connected to ORACLE.

SQL> CONNECT sys/change_on_install;

Connected.

SQL> NOAUDIT CONNECT;

Noaudit succeeded.

Нижче представлена вибірка даних з результатами аудиту

системи, ініційованого вищеописаними діями (автоматичної

реєстрації процесів підключення користувачів до сервера Oracle).

SQL> SELECT username, timestamp, returncode

2 FROM dba_audit_trail;

USERNAME TIMESTAMP RETURNCODE

userl 04-11-2009 0

user2 04-11-2009 0

livak 04-11-2009 1017

sys 04-11-2009 0

Реєстрація подій, пов’язаних з доступом до об’єктів,

відбується за допомогою команди:

AUDIT перелік_дій_для_аудиту

ON [схема.]об’єкт або DEFAULT

[WHENEVER [NOT] SUCCESSFUL]

Параметр перелік_дій_для_аудиту залежить від типу об’єкта,

окремі його значення приведені нижче в таблиці 4.4.

143

Таблиця 4.4 Значення параметра перелік_дій_для_аудиту.

Ім’я дії Допустимі об’єкти Oracle

SELECT

Таблиця, представлення, знімок, послідовність

INSERT, UPDATE,

DELETE

Таблиця, представлення, знімок

EXECUTE

Процедура, функція, пакет

GRANT, AUDIT,

RENAME

Таблиця, представлення, знімок,

функція, послідовність, процедура, пакет

Розглянемо приклад аудиту таблиці Gazpr користувача system.

Нагадаємо, що за внесенням записів у цю таблицю слідкує тригер

TrigKilnyt, створений у розділі 4.6.5, який не дозволяє вводити дані,

якщо число ниток газопроводу більші за 15. Наприкінці прикладу

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

SQL> connect system/manager

Connected.

SQL> audit insert on Gazpr

2 whenever not successful;

Audit succeeded.

SQL> insert into Gazpr values(100);

insert into Gazpr values(100)

ERROR at line 1:

ORA-20002: Число ниток не може бути >15

ORA-06512: at "system.TrigKilnyt", line 2

ORA-04088: error during execution of trigger 'system.TrigKilnyt'

SQL> insert into Gazpr(Kilnyt) values(5);

1 row created.

SQL> noaudit insert on Gazpr;

Noaudit succeeded.

SQL> select username, action, timestamp, ses_actions, returncode

2 from user_audit_trail;

USERNAME ACTION TIMESTAMP SES_ACTIONS RETURNCODE

––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––

SYSTEM 103 03-NOV-09 ----B---- 0

144

Переглянувши результати аудиту і проаналізувавши їх,

знаходимо одну зафіксовану операцію, яку забезпечив параметр

команди аудиту WHENEVER NOT SUCCESSFUL. Успішна операція

внесення запису в таблицю Gazpr не зафіксована.

Командою SELECT були виведені такі поля представлення

user_audit_trail:

username – ім’я користувача;

action – числовий код дії користувача (в даному випадку – код

команди INSERT);

timestamp – дата створення реєстраційного запису;

ses_actions – рядок символів, успішне або неуспішне виконання

команди;

returncode – код операції (у випадку помилки цей код дорівнює 0).

5.3 Моніторинг екземпляра Oracle

Питання моніторингу екземпляра вже частково розглядалося в

розділі 4.2 про його архітектуру.

Для того щоб настроїти екземпляр, необхідно встановити

потрібні значення параметрів, збережені у файлі init.ora. (Крім

файлу ініціалізації init.ora, Oracle 9і має ще й серверний файл

параметрів spfile, який дозволяє динамічно змінювати параметри

екземпляра без перезавантаження БД). Настроювання екземпляра

найчастіше являє собою ітераційний процес, коли спочатку

встановлюються орієнтовані наближені значення параметрів, а

потім вони уточнюються в міру нагромадження досвіду

експлуатації.

Окремі питання моніторингу екземпляра були вже частково

розглянені нами в розділі 4.2 про структуру екземпляра Oracle.

Файл ініціалізації має велике число параметрів, багато з яких не

документовані. Щоб довідатися, які параметри необхідні для

ініціалізації БД, можна запросити вміст внутрішньої таблиці БД

x$ksppi, у якій перераховані всі параметри БД. Її поля ksppinm і

ksppdesc містять найменування параметрів і короткий опис їхнього

призначення.

Недокументовані параметри відрізняються за найменуванням,

вони починаються із символу підкреслення. Такі параметри краще

не модифікувати, не знаючи їхнього призначення, це може мати

жалюгідні наслідки. Існує безліч параметрів суто для цілей

діагностики, зміна яких може спричинити розлад механізму

145

синхронізації даних та навіть їхню втрату. Тому краще не

модифікувати параметри у файлі init.ora доти, поки адміністратор не

буде мати чіткого уявлення про всі можливі наслідки.

Адміністратор рідко втручається в роботу фонових процесів.

Однак це стається, якщо в роботі системи виникли які-небудь

проблеми, причину яких необхідно діагностувати й усунути. Існує

декілька способів, які дають можливість проаналізувати динаміку

виконання фонових процесів. Розглянемо їх.

Використання файлу трасування. Найпростіший і

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

– звернутися до файлів трасування процесів. Розміщення цих

файлів на диску визначається параметрами USER_DUMP_DEST і

BACKGROUPJD_DUMP_DEST. Якщо відбувається переривання

фонового процесу чи якась операція закінчилася невдачею, то

відповідний фоновий процес формує файл трасування. У цей файл

записується:

– повідомлення про помилку, яка спричинила переривання

процесу;

– дамп поточного стану стека процесу;

– імена поточних курсорів;

– інша інформація про виниклу проблему.

Крім того, певна інформація про неполадки записується у файл

alert.log, який знаходиться в каталозі BDUMP даного екземпляра.

Максимальний розмір кожного файлу трасування (за винятком

файлу alert.log) можна контролювати через параметр

MAX_DUMP_FILE_SIZE файлу ініціалізації. Це обмеження задається

як число блоків операційної системи. За замовчуванням цей

параметр установлюється як UNLIMITED. Для контролю розміру

файлу alert.log необхідно періодично вручну вилучати цей файл,

коли він стає непотрібним, у протилежному випадку Oracle

продовжує вести запис у кінець цього файлу. Файл трасування

можна без усякої небезпеки вилучати під час роботи екземпляра.

Трасування засобами операційної системи. Крім файлів

трасування, одержати інформацію про роботу фонових процесів

можна й за допомогою команд операційної системи, проте в різних

з них трасування фонових процесів має свої особливості.

Часто буває корисним переглянути інформацію, яку надає ОС,

про використання задачею пам’яті і процесора. Іноді виявляється,

що єдиним способом вивести “завислий” процес є його

переривання на рівні операційної системи. Але тут варто виявляти

146

велику обережність. Насильницьке переривання більшості фонових

процесів Oracle може спричинити непередбачені наслідки.

Використання v$-таблиць для моніторингу. Адміністратор

має можливість одержати інформацію про процеси, які

виконуються під час роботи екземпляра за допомогою

представлень (view), які належать користувачу sys. Звернення до

них зручніше здійснювати через синоніми, назва яких має такий

вигляд: v$ім’я_синоніма. За їх допомогою можна простежити як за

процесами користувача, так і за фоновими процесами, які

підключені до екземпляра.

Для відстеження процесів екземпляра Oracle можна

використовувати такі таблиці:

v$circuit – з’єднання користувачів до бази даних через диспетчери і

сервери;

v$dispatcher – інформація про диспетчерські процеси;

v$shared_server – інформація про розподілювані серверні процеси;

v$sqlarea – інформація про виконувані вирази SQL;

v$sess_io – статистика вводу-виводу для кожної сесії користувача;

v$latch – інформація про кожен тип засуву;

v$sysstat і v$sesstat – статистика про кожну поточну сесію.

Моніторинг підключень до БД. Представлення v$process

відображає інформацію про всі підключені до БД процеси,

включаючи фонові користувача. Цікавими є такі його поля:

addr – адреса процесу Oracle;

pid – ідентифікатор (ID) процесу ОС;

spid – ідентифікатор системного процесу ОС;

username – власник процесу ОС;

serial# – номер процесу Oracle;

terminal – ідентифікатор терміналу ОС;

program – підключення до програми ОС;

background – дорівнює 1 для фонового процесу, NULL – для процесу

користувача.

Це представлення містить теж поля з інформацією про засуви.

Для одержання списку можливих фонових процесів необхідно

запросити представлення v$bgprocess. Найбільш важливими є такі

його поля:

paddr – адреса процесу Oracle (та ж адреса, що й у полі ADDR

представлення v$process);

name – найменування фонового процесу;

description – опис фонового процесу;

147

error – код помилки (при відсутності помилки повертає 0).

Об’єднавши представлення v$process і v$bgprocess у запиті SQL:

SELECT spid, name

FROM v$process, v$bgprocess

WHERE paddr (+) = addr;

можна одержати адреси й ідентифікатори запущених на виконання

фонових процесів.

Представлення v$session містить інформацію про поточні сеанси

користувачів, підключених до БД, і має такі поля:

sid – ідентифікатор сеансу;

serial# – номер сеансу;

paddr – адреса сеансу-батька;

user* – ідентифікатор користувача (вибирається з таблиці sys.user$);

username – ім’я користувача в Oracle;

command – поточна команда, яка виконується в цьому сеансі.

Відповідність номерів сеансів і команд задається таблицею

sys.audit_actions;

status – статус сеансу (ACTIVE, INACTIVE, KILLED);

server – тип підключення до сервера (DEDICATED, SHARED,

PSEUDO, NONE);

osuser – ім’я користувача в ОС;

program – програма ОС, яка виконала підключення;

terminal – тип терміналу, з якого було виконано підключення;

type – тип сеансу (USER чи BACKGROUND);

sql_hash_value –унікальна ідентифікація поточного виразу SQL;

sql_address – те ж.

Інформація про об’єкти БД, до яких у поточний момент

звертаються користувачі, міститься в представленні v$access.

Особливо воно корисне для виявлення сторонніх додатків або

недокументованих процедур, які, можливо, звертаються до БД, та

при вирішенні проблем безпеки даних.

Інформацію про розподілювані процеси сервера містить

представлення v$mts. Окремі поля цього представлення містять звіт

про максимальну кількість підключень, запущені сервери,

перервані і завантажені сервери.

Сценарій utllockt.sql видає інформацію у вигляді дерева про

сеанси у системі, які очікують блокувань

Моніторинг розподілюваної глобальний системної області.

Можна умовно виокремити такі чотири групи механізмів

148

настроювання ГСО:

– механізми, які відповідають за настройку кешів, з яких

складається ГСО (кеш бібліотеки, кеш словника даних, кеш

буфер даних). Відповідні параметри надають можливість

оцінити ефективність використання кешів при роботі сервера;

– параметри, що відповідають за виділення і розподіл пам’яті

для вставки, збереження і відновлення записів. До цієї групи

відносяться параметри PCTFREE і PCTUSED. За допомогою їх

настройки можна уникнути нераціональної витрати дискового

простору і тимчасових затримок під час запису інформації в

блоки даних;

– параметри, настройка яких впливає на продуктивність буфера

журналу відкоту, механізму засувів, сегментів відкоту. Усі ці

механізми відповідають за коректне виконання і збереження

результатів транзакцій. Основною метою настройки цих

параметрів є скорочення часу виконання транзакцій;

– механізм сортувань, який необхідно настроювати, якщо в

роботі з базою даних часто виникає потреба у виконанні

різних сортувань великих обсягів даних.

Настройка кеша бібліотеки. СУБД під час своєї роботи збирає

різноманітну статистику, яку зберігає в системних таблицях.

Статистика про те, наскільки ефективно використовується кеш

бібліотек, зберігається в системній таблиці v$librarycache, де інтерес

представляють такі поля:

pins – кількість разів, коли елемент у кеші виконувався вдало;

reloads – число промахів, тобто елемент не був знайдений. Чим

менше значення цього параметра, тим вищий ступінь використання

кеша бібліотеки. Якщо відношення числа reloads до числа pins

більше, ніж 1%, то це означає, що кеш бібліотеки необхідно

налаштовувати. Для цього треба переписати додаток так, щоб у

ньому використовувалися в міру можливості ідентичні SQL-

речення або збільшити параметр SHARED_POOL_SIZE.

Настройка кеша словника даних. При розгляді кеша

бібліотеки завжди розглядають і кеш словника даних. По-перше,

разом вони утворюють розподілюваний пул. По-друге, обидва кеші

налаштовуються одним і тим же параметром.

Для настроювання кеша словника даних аналізується

інформація, яка знаходиться в системному представленні

v$rowcache. Він має такі важливі поля: gets – загальне число запитів

для відповідного елемента і getmisses – загальна кількість запитів –

149

промахів у кеші. Якщо відношення числа getmisses до gets

перевершує 10-15 %, то треба збільшити значення параметра

SHARED_POOL_SIZE.

Перш ніж змінювати параметр SHARED_POOL_SIZE, слід

переконатися в тому, що в розподілюваному пулі дійсно немає

вільного місця, інакше його збільшення не дасть ніякого

результату.

Інформацію про процеси, які відбуваються в ГСО надають

такі два представлення:

v$sga – про розміри кожного компонента ГСО в кеші журналу

реєстрації транзакцій, кеші-буфері даних і розподілюваному пулі;

v$sgastat – про розміри всіх компонентів, навіть найдрібніших,

включаючи розміри стекових областей різного призначення.

Розмір вільної пам’яті в ГСО можна визначити, виконавши

такий запит:

SELECT pool, bytes

FROM v$sgastat

WHERE Name = 'free memory';

Дуже часто для налагодження роботи БД і додатків виникає

необхідність одержати інформацію про виконання команди SQL.

Таку інформацію містить представлення v$sqlarea, воно містить

інформацію про вирази SQL, які знаходяться в розподілюваній

ГСО, їхній текст, кількість користувачів, які використовували

кожний вираз та кількість блоків на диску й у оперативній пам’яті,

до яких відбулося звертання в процесі виконання команд. За

допомогою його полів disk_reads і buffer_gets можна відстежити

кількість блоків, зчитаних з диска і з кеш-буфера. Ці два поля

дозволяють швидко відшукати запити, які найбільш інтенсивно

використовують ресурси БД.

Представлення v$open_cursor допомагає знайти відкриті

курсори.

Представлення v$sqltext дозволяє визначити вирази SQL, які

були передані на виконання. Ці вирази SQL розбиваються на

окремі фрагменти. Для того щоб витягти їх цілком, потрібно

вибирати фрагменти в тому порядку, в якому вони знаходяться в

полі piece. На відміну від v$sqlarea, яке зберігає лише перші 80

символів виразу SQL, воно показує його цілком.

Настроювання параметрів сортування. Під час роботи з БД

часто виникає потреба в сортуванні даних, яка виникає не тільки

150