Базы данных Oracle - статьи

         

Настройка отдельных запросов


Рассмотрим пример настройки с помощью пакета 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;

Получим примерно .

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

Ту же информацию можно извлечь из словаря-справочника, например:

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

Хотя профиль и имеется, но в этом запросе не учитывается.

См. следующую статью - "".


Содержание раздела