Версія Oracle 10g: фахівці з налаштування запитів більше не потрібні

Моя поезія тут більше не потрібна,
Та й, мабуть, сам я теж тут не потрібен.
С. Єсенін. Русь радянська.

Піч затопить, всі заготовить, закупить,
Яєчко спече та сам і облупиться.
Попадя Балдо не нахвалиться …
А. С. Пушкін. Казка про попа і про працівника його Балду.

Зміст



Анотація


У версії 10 Oracle з'явився спеціальний режим роботи оптимізатора витрат (cost optimizer), що дозволяє провести поглиблений аналіз запиту і запропонувати для подальшого виконання більш якісний, ніж зазвичай, план. У статті розглядається приклад програмного виконання поглибленого аналізу запитів з допомогою нового у версії 10 пакету DBMS_SQLTUNE.

Введення


У версії 10 Oracle з'явився спеціальний режим роботи оптимізатора витрат (cost optimizer), що дозволяє провести поглиблений аналіз запиту і запропонувати для подальшого виконання більш якісний, ніж зазвичай, план. Застосувати такий аналіз можна за допомогою пакету DBMS_SQLTUNE, який дозволяє отримати від СУБД рекомендації з налаштування конкретних запитів і навіть отримати сценарій внесення змін. Рекомендації будуються на основі додаткового, понад звичайний, вивчення даних, перевірочних прогонів частин плану та накопичених фахівцями з налаштування запитів знань.

Пакетом можна користуватися як вручну (програмно), так і через графічні засоби OEM. Далі розглядається приклад програмного виконання поглибленого аналізу запитів.

Поглиблений аналіз запитів і його можливості


У звичайній роботі оптимізатор витрат будує план обробки "на швидку руку": спираючись на статистику використовуваних в запиті таблиць та індексів, значення деяких параметрів СУБД і, можливо, наявні в запиті підказки. У версії 10 можна попросити оптимізатор обробити запит у спеціальному настроечно режимі (Automated Tuning Optimizer), при якому оптимізатор здатний виконати наступні види аналізу:


Поглиблений аналіз запиту вимагає часу, в тому числі за рахунок перевірочних прогонів частин плану для отримання більш точних оцінок витрат. Аналіз без побудови профілю називається вимагає менше часу і називається обмеженим (limited). Аналіз з побудовою профілю вимагає більше часу і називається повним (comprehensive).

На відміну від статистик об'єктів запиту, профіль є властивістю конкретного запиту і здатний враховувати співвідношення даних, що проявляють себе саме в конкретному запиті. Застосування профілю здатне дати більш якісний план, не вимагаючи переформулювання запиту, що дозволяє підвищувати ефективність обробки запитів у готових додатках.

Налаштування окремих запитів


Розглянемо приклад налаштування за допомогою пакету DBMS_SQLTUNE конкретного запиту.

Відкриємо в SQL * Plus сеанс зв'язку з СУБД від імені SCOTT і зробимо необхідні приготування:

CONNECT scott/tiger

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT ename, loc, sal, hiredate
FROM emp, dept
WHERE emp.deptno = dept.deptno
;


Відповідь на запит може виглядати приблизно так:

Execution Plan
———————————————————-
Plan hash value: 615168685

————————————————– ————————-
/ Id / Operation / Name / Rows / Bytes / Cost (%CPU)/ Time /
————————————————– ————————-
/ 0 / SELECT STATEMENT / / 14 / 532 / 7 (15)/ 00:00:01 /
/* 1 / HASH JOIN / / 14 / 532 / 7 (15)/ 00:00:01 /
/ 2 / TABLE ACCESS FULL/ DEPT / 4 / 84 / 3 (0)/ 00:00:01 /
/ 3 / TABLE ACCESS FULL/ EMP / 14 / 238 / 3 (0)/ 00:00:01 /
————————————————– ————————-

Predicate Information (identified by operation id):
—————————————————

1 – access(“EMP”.”DEPTNO”=”DEPT”.”DEPTNO”)

Note
—–
– dynamic sampling used for this statement


Перемкнемося на інший сеанс від імені SYS, наприклад, так:

HOST sqlplus / AS SYSDBA

Створимо завдання на автоматичне налаштування запиту з боку користувача SCOTT (на поглиблений аналіз):

DECLARE
my_task_name VARCHAR2 ( 30 );
my_sqltext CLOB;

BEGIN
my_sqltext :=
“SELECT ename, loc, sal, hiredate FROM emp, dept ”
// “WHERE emp.deptno = dept.deptno”
;

my_task_name :=
DBMS_SQLTUNE.CREATE_TUNING_TASK (
sql_text => my_sqltext
, user_name => “SCOTT”
, task_name => “my_sql_tuning_task”
);
END;
/


Дізнатися стан завдання можна зі словника-довідника:

SELECT status, execution_start start_time, execution_end end_time
FROM dba_advisor_log
WHERE owner = “SYS” AND task_name = “my_sql_tuning_task”
;

Воно буде "INITIAL".

Запустимо завдання для налаштування запиту:

EXECUTE –
DBMS_SQLTUNE.EXECUTE_TUNING_TASK ( “my_sql_tuning_task” );

Повторний запит до DBA_ADVISOR_LOG дасть STATUS = "COMPLETED". (Для серйозних запитів завдання могло б виконуватися набагато довше).

Завдання виконав поглиблений аналіз із застосуванням контрольних прогонів фрагментів плану і підготувало рекомендації. Запитаємо їх:

SET LONG 10000
SET LONGCHUNKSIZE 1000
SET LINESIZE 200

SELECT
DBMS_SQLTUNE.REPORT_TUNING_TASK ( “my_sql_tuning_task” )
FROM dual;


Отримаємо приблизно такий розгорнутий результат:

DBMS_SQLTUNE.REPORT_TUNING_TASK(“MY_SQL_TUNING_TASK”)
————————————————– ———————-
GENERAL INFORMATION SECTION
————————————————– ———————-
Tuning Task Name : my_sql_tuning_task
Tuning Task Owner : SYS
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 03/14/2006 20:57:05
Completed at : 03/14/2006 20:57:05
Number of Statistic Findings : 1
Number of SQL Profile Findings : 1

————————————————– ———————–
Schema Name: SCOTT
SQL ID : 3dcfttkf1kwmn
SQL Text : SELECT ename, loc, sal, hiredate FROM emp, dept
WHERE emp.deptno = dept.deptno

————————————————– ———————–
FINDINGS SECTION (2 findings)
————————————————– ———————–

1- Statistics Finding
———————
Table “SCOTT”.”DEPT” and its indices were not analyzed.

Recommendation
————–
– Consider collecting optimizer statistics for this table and its indices.
execute dbms_stats.gather_table_stats (ownname => "SCOTT", tabname =>
“DEPT”, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => "FOR ALL COLUMNS SIZE AUTO", cascade => TRUE);

Rationale
———
The optimizer requires up-to-date statistics for the table and its indices
in order to select a good execution plan.

DBMS_SQLTUNE.REPORT_TUNING_TASK(“MY_SQL_TUNING_TASK”)
————————————————– ———————–

2- SQL Profile Finding (see explain plans section below)
——————————————————–
A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 38.11%)
——————————————
– Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
“my_sql_tuning_task”, replace => TRUE);

————————————————– ———————-
EXPLAIN PLANS SECTION
————————————————– ———————-

1- Original With Adjusted Cost
——————————
Plan hash value: 615168685

————————————————– ————————-
/ Id / Operation / Name / Rows / Bytes / Cost (%CPU)/ Time /
————————————————– ————————-
/ 0 / SELECT STATEMENT / / 14 / 364 / 7 (15)/ 00:00:01 /
/* 1 / HASH JOIN / / 14 / 364 / 7 (15)/ 00:00:01 /
/ 2 / TABLE ACCESS FULL/ DEPT / 4 / 36 / 3 (0)/ 00:00:01 /
/ 3 / TABLE ACCESS FULL/ EMP / 14 / 238 / 3 (0)/ 00:00:01 /
————————————————– ————————-

Predicate Information (identified by operation id):
—————————————————

1 – access(“EMP”.”DEPTNO”=”DEPT”.”DEPTNO”)

2- Using SQL Profile
——————–
Plan hash value: 351108634

DBMS_SQLTUNE.REPORT_TUNING_TASK(“MY_SQL_TUNING_TASK”)
————————————————– ———————-
————————————————– ————————————
/Id / Operation / Name / Rows / Bytes / Cost (%CPU)/ Time /
————————————————– ————————————
/ 0 / SELECT STATEMENT / / 14 / 364 / 4 (0)/ 00:00:01 /
/ 1 / NESTED LOOPS / / 14 / 364 / 4 (0)/ 00:00:01 /
/ 2 / TABLE ACCESS FULL / EMP / 14 / 238 / 3 (0)/ 00:00:01 /
/ 3 / TABLE ACCESS BY INDEX ROWID/ DEPT / 1 / 9 / 1 (0)/ 00:00:01 /
/*4 / INDEX UNIQUE SCAN / PK_DEPT / 1 / / 0 (0)/ 00:00:01 /
————————————————– ————————————

Predicate Information (identified by operation id):
—————————————————

4 – access(“EMP”.”DEPTNO”=”DEPT”.”DEPTNO”)

————————————————– ———————-


Оптимізатор зробив два спостереження: (а) відсутня статистика по одній з таблиць (і запропонував готову команду збору статистики) і (б) можна отримати більш вигідний план, застосувавши профіль (і вказав для порівняння старий план і план із застосуванням профілю).

Ту ж саму інформацію можна отримати з словника-довідника, наприклад:

SELECT type, message
FROM dba_advisor_findings
WHERE owner = “SYS” AND task_name = “my_sql_tuning_task”
;

Застосуємо створений профіль до запиту, правда трохи в іншій формі, ніж чим рекомендує оптимізатор, але по суті так само:

BEGIN
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => “my_sql_tuning_task”
, name => “my_sql_profile”
);
END;
/

Уточнити властивості (і наявність) створеного профілю запиту можна за словником-довідником так:

SELECT category, type, status
FROM dba_sql_profiles
WHERE name = “my_sql_profile”
;

Повернемося у вихідний сеанс, знову видамо запит і поспостерігаємо план:

EXIT
/

Отримаємо приблизно такий результат:

Execution Plan
———————————————————-
Plan hash value: 351108634

————————————————– ————————————
/Id / Operation / Name / Rows / Bytes / Cost (%CPU)/ Time /
————————————————– ————————————
/ 0 / SELECT STATEMENT / / 14 / 364 / 4 (0)/ 00:00:01 /
/ 1 / NESTED LOOPS / / 14 / 364 / 4 (0)/ 00:00:01 /
/ 2 / TABLE ACCESS FULL / EMP / 14 / 238 / 3 (0)/ 00:00:01 /
/ 3 / TABLE ACCESS BY INDEX ROWID / DEPT / 1 / 9 / 1 (0) / 00:00:01 /
/*4 / INDEX UNIQUE SCAN / PK_DEPT / 1 / / 0 (0)/ 00:00:01 /
————————————————– ————————————

Predicate Information (identified by operation id):
—————————————————

4 – access(“EMP”.”DEPTNO”=”DEPT”.”DEPTNO”)

Note
—–
– SQL profile “my_sql_profile” used for this statement


Зауважимо, що від тексту запиту не потрібно повного посимвольного збіги. Видамо:

select ename, LOC,SAL, hiredate
from emp, dept WHERE emp.deptno = dept.deptno
;

Отримаємо знову:

Execution Plan
———————————————————-
Plan hash value: 351108634

————————————————– ————————————
/Id / Operation / Name / Rows / Bytes / Cost (%CPU)/ Time /
————————————————– ————————————
/ 0 / SELECT STATEMENT / / 14 / 364 / 4 (0)/ 00:00:01 /
/ 1 / NESTED LOOPS / / 14 / 364 / 4 (0)/ 00:00:01 /
/ 2 / TABLE ACCESS FULL / EMP / 14 / 238 / 3 (0)/ 00:00:01 /
/ 3 / TABLE ACCESS BY INDEX ROWID / DEPT / 1 / 9 / 1 (0) / 00:00:01 /
/*4 / INDEX UNIQUE SCAN / PK_DEPT / 1 / / 0 (0)/ 00:00:01 /
————————————————– ————————————

Predicate Information (identified by operation id):
—————————————————

4 – access(“EMP”.”DEPTNO”=”DEPT”.”DEPTNO”)

Note
—–
– SQL profile “my_sql_profile” used for this statement


Тим не менш, досвід показує, що розбіжність текстів обмежується терпимістю до відмінностей в регістрі букв і кількостях прогалин. Наприклад, наступний вид запиту не викличе підключення профілю:

select ename, LOC,SAL, hiredate
from emp, scott.dept WHERE emp.deptno = dept.deptno
;

Хоча профіль і приписаний запиту, окремий сеанс може відмовитися від його використання. Профіль було створено для "категорії" (поле CATEGORY.DBA_SQL_PROFILES) DEFAULT і буде застосований лише за умови, що запит видається з встановленим у DEFAULT значенням параметра СУБД SQLTUNE_CATEGORY. Це значення умолчательную. Замінимо його на інше:

ALTER SESSION SET SQLTUNE_CATEGORY = test;

SELECT ename, loc, sal, hiredate
FROM emp, dept
WHERE emp.deptno = dept.deptno;


План для цього (і тільки!) Сеансу знову стане тим самим:

Execution Plan
———————————————————-
Plan hash value: 615168685

————————————————– ————————-
/ Id / Operation / Name / Rows / Bytes / Cost (%CPU)/ Time /
————————————————– ————————-
/ 0 / SELECT STATEMENT / / 14 / 532 / 7 (15)/ 00:00:01 /
/* 1 / HASH JOIN / / 14 / 532 / 7 (15)/ 00:00:01 /
/ 2 / TABLE ACCESS FULL/ DEPT / 4 / 84 / 3 (0)/ 00:00:01 /
/ 3 / TABLE ACCESS FULL/ EMP / 14 / 238 / 3 (0)/ 00:00:01 /
————————————————– ————————-

Predicate Information (identified by operation id):
—————————————————

1 – access(“EMP”.”DEPTNO”=”DEPT”.”DEPTNO”)

Note
—–
– dynamic sampling used for this statement


Хоча профіль і є, але в цьому запиті не враховується.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*