четверг, 23 июля 2009 г.

DBMS_SQLTUNE (настройка SQL)


Поиск наилучшего плана – 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;