Поиск наилучшего плана – SQL Tuning Advisor
Минимально необходимые привилегии
для непривилегированного пользователя:
CONNECT / AS SYSDBA
GRANT ADVISOR TO < USERNAME >;
GRANT SELECTCATALOG_ROLE TO < USERNAME >;
GRANT EXECUTE on DBMS_SQLTUNE TO < USERNAME >;
Дополнительно
Можно запускать помощник с помощью встроенного скрипта:
$ORACLE_HOME/rdbms/admin/sqltrpt.sql
Основные шаги
Убедитесь, что оригинальный запрос находится в памяти (shared pool)
или AWR репозитории
Создать задание для SQL Tuning Advisor одним из 3х способов
/* Анализ исходного текста запроса */
DECLARE
my_task_name varchar2(30);
my_sqltext clob;
BEGIN
my_sqltext := q'!
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (
sql_text => my_sqltext,
user_name => '< SCHEMA_NAME >',
scope => 'COMPREHENSIVE',
time_limit => 600,
task_name => '< TASK_NAME >',
description => '< TASK_DESCRIPTION >');
END;
/
/* Анализ запроса по SQL_ID из Cursor Cache */
DECLARE
my_task_name varchar2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (
sql_id => '< SQL_ID >',
scope => 'COMPREHENSIVE',
time_limit => 600,
task_name => '< TASK_NAME >',
description => '< TASK_DESCRIPTION >');
END;
/
/* Анализ запроса по SQL_ID из AWR репозитория */
DECLARE
my_task_name varchar2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (
begin_snap => < BEGIN_SNAP_ID >,
end_snap => < END_SNAP_ID >,
sql_id => '< SQL_ID >',
scope => 'COMPREHENSIVE',
time_limit => 600,
task_name => '< TASK_NAME >',
description => '< TASK_DESCRIPTION >');
END;
/
Запустить задание на исполнение
exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => '< TASK_NAME >');
Просмотр рекомендаций
SET LONG 100000
SET LONGCHUNKSIZE 100000
SET LINESIZE 1000
SET PAGESIZE 1000
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('< TASK_NAME >') FROM DUAL;
Пример:
exec DBMS_SQLTUNE.DROP_TUNING_TASK('TSK_01');
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := q'!SELECT * FROM DUAL!';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext,
user_name => 'ANGORL',
scope => 'COMPREHENSIVE',
time_limit => 600,
task_name => 'TSK_01',
description => 'test');
END;
/
exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'TSK_01' );
SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS
WHERE task_id = (SELECT task_id FROM USER_ADVISOR_TASKS
WHERE task_name='TSK_01');
SET LONG 100000
SET LONGCHUNKSIZE 100000
SET LINESIZE 10000
SET PAGESIZE 10000
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TSK_01') FROM DUAL;
В рекоммендациях может быть предложено принять profile:
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 92.44%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>'TSK_01', task_owner => 'ANGOR', replace => TRUE);
И выведены для сравнения два плана выполнения запроса:
Оригинального
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 5227986114
И с применённым profile
2- Using SQL Profile
--------------------
Plan hash value: 6245447849
1. Создайте задачу:
Задача создается для стейтманта из AWR:
DECLARE
my_task_name VARCHAR2(100);
BEGIN
my_task_name := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 523,
end_snap => 658,
sql_id => '7h35uxf5uhmm1',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '7h35uxf5uhmm1_AWR_tuning_task',
description => 'Tuning task for statement 7h35uxf5uhmm1 in AWR');
DBMS_OUTPUT.put_line('my_task_name: ' || my_task_name);
END;
/
Задача создается по sql_id курсора из кэша:
DECLARE
my_task_name VARCHAR2(100);
BEGIN
my_task_name := DBMS_SQLTUNE.create_tuning_task (
sql_id => '7h35uxf5uhmm1',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '7h35uxf5uhmm1_tuning_task',
description => 'Tuning task for statement 7h35uxf5uhmm1');
DBMS_OUTPUT.put_line('my_task_name: ' || my_task_name);
END;
/
Задача создается для операторов из STS:
DECLARE
my_task_name VARCHAR2(100);
BEGIN
my_task_name := DBMS_SQLTUNE.create_tuning_task (
sqlset_name => 'test_sql_tuning_set',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'sqlset_tuning_task',
description => 'Tuning task for an SQL tuning set.');
DBMS_OUTPUT.put_line('my_task_name: ' || my_task_name);
END;
/
Можно указать SQL-оператор вручную:
DECLARE
my_sql_text VARCHAR2(500);
my_task_name VARCHAR2(100);
BEGIN
my_sql_text := 'SELECT e.*, d.* ' ||
'FROM emp e JOIN dept d ON e.deptno = d.deptno ' ||
'WHERE NVL(empno, ''0'') = :empno';
my_task_name := DBMS_SQLTUNE.create_tuning_task (
sql_text => my_sql_text,
bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => 'ANGOR',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'emp_dept_tuning_task',
description => 'Tuning task for an EMP to DEPT join query.');
DBMS_OUTPUT.put_line('my_task_name: ' || my_task_name);
END;
/
общее время на анализ ограничили до 60 секунд.
2. Выполните задачу:
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task' );
END;
/
3. Получите отчет по настройке:
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SET PAGESIZE 24
select DBMS_SQLTUNE.REPORT_TUNING_TASK ( 'my_sql_tuning_task' ) as recommendations from dual;
Мониторить выполнение задач по настройке SQL можно так:
SELECT task_name, status FROM dba_advisor_log WHERE owner = 'ANGOR';
Приостановить и продолжить выполнение задачи можно так:
EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => 'my_sql_tuning_task');
EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => 'my_sql_tuning_task');
Завершить выполнение задачи можно так:
EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => 'my_sql_tuning_task');
Сбросить задачу для ее повторного выполнения:
EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => 'my_sql_tuning_task');
Простой пример:
DECLARE
my_task_name VARCHAR2(30);
my_sql_text CLOB;
BEGIN
my_sql_text := 'select sysdate
from dual';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sql_text,
-- bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => 'ANGOR',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'my_sql_tuning_task',
description => 'task to tune sql'
);
END;
/
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task' );
END;
/
select DBMS_SQLTUNE.REPORT_TUNING_TASK ( 'my_sql_tuning_task' ) as recommendations from dual;
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_sql_tuning_task
Tuning Task Owner : ANGOR
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 06/08/2014 16:59:14
Completed at : 06/08/2014 16:59:16
-------------------------------------------------------------------------------
Schema Name: ANGOR
SQL ID : 7ahprz34d71wv
SQL Text : select sysdate
from dual
-------------------------------------------------------------------------------
There are no recommendations to improve the statement.
-------------------------------------------------------------------------------
Использование инструмента SQLT (Doc ID 215187.1)
Закрепление существующего плана
Основные шаги
Если исходный запрос находится в памяти (shared pool)
и оптимизированный запрос находится в памяти (shared pool)
Перейдите в директорию sqlt/utl где установлен инструмент SQLT
Запустите скрипт coe_xfr_sql_profile.sql с ролью SYSDBA и предоставьте исходные данные:
SQL_ID исходного запроса [SQL_ID]
PLAN_HASH_VALUE оптимизированного запроса [OPT_PLAN_HASH_VALUE]
SQL> connect / as sysdba
SQL> @coe_xfr_sql_profile.sql SQL_ID OPT_PLAN_HASH_VALUE
Будет создан скрипт coe_xfr_sql_profile_< SQL_ID >_< OPT_PLAN_HASH_VALUE >.sql
Если запрос использует литералы то можно модифицировать параметр
force=>TRUE (в скрипте) для применения плана ко всем запросам с литералами
Запустить скрипт coe_xfr_sql_profile_< SQL_ID >_< OPT_PLAN_HASH_VALUE >.sql
из под пользователя с ролью SYSDBA для окончательной фиксации плана
SQL> connect / as sysdba
SQL> @coe_xfr_sql_profile_< SQL_ID >_< OPT_PLAN_HASH_VALUE >.sql
Проверить закрепление нового плана для исходного SQL_ID
select distinct p.name sql_profile_name, s.sql_id, p.category, p.status
from DBA_SQL_PROFILES p,
DBA_HIST_SQLSTAT s
where p.name = s.sql_profile
and s.sql_id in ('&SQL_ID');
Проверить использование нового SQL Profile для исходного SQL_ID:
col sql_profile format a30
col sql_patch format a28
col sql_plan_baseline format a30
col last_load_time format a20
col last_active_time format a20
select INST_ID,
SQL_ID,
CHILD_NUMBER,
PLAN_HASH_VALUE,
SQL_PROFILE,
SQL_PATCH,
SQL_PLAN_BASELINE,
LAST_LOAD_TIME,
LAST_ACTIVE_TIME
from GV$SQL where SQL_ID in ('&SQL_ID');
Пример:
Создаем SQL Profile
Связываем sql_id с лучшим plan_hash_value
@coe_xfr_sql_profile.sql
SQL> @coe_xfr_sql_profile.sql try7b95kv87a3 4809245315
SQL> @coe_xfr_sql_profile_try7b95kv87a3_4809245315.sql
Смотрим план:
select SQL_ID, CHILD_NUMBER, PLAN_HASH_VALUE, SQL_PROFILE FROM V$SQL where sql_id='&SQL_ID';
select * from table(dbms_xplan.display_cursor('try7b95kv87a3',null,'ADVANCED ALLSTATS LAST'));
Убеждаемся, что он использует новый PLAN_HASH_VALUE.
Если нет, то делаем flush the HASH VALUE:
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like 'try7b95kv87a3';
ADDRESS HASH_VALUE
---------------- ----------
00000000A5D6BEE7 8756491057
SQL> exec DBMS_SHARED_POOL.PURGE ('00000000A5D6BEE7, 8756491057', 'C');
PL/SQL procedure successfully completed.
SQL>
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like 'try7b95kv87a3';
select name from dba_sql_profiles;
