Оптимизация запроса с помощью DBMS_SQLTUNE : Oracle

OlegON · █ 28.09.2011 15:18

Иногда бывает очень лениво разбираться, где косячит запрос. В данном случае сначала можно проверить возможности автомата. Как это делается:
1)
для начала грантуем пакет супермагу или тому пользователю, кем хотите тестировать проблемный запрос

Код:

GRANT ADVISOR TO SUPERMAG;
GRANT SELECT_CATALOG_ROLE TO SUPERMAG;
GRANT EXECUTE ON DBMS_SQLTUNE TO SUPERMAG;

2) очень простая последовательность

Код:

DECLARE
task_name VARCHAR2(30);
sqltext CLOB;
BEGIN
sqltext := 'проблемный запрос';
task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => sqltext,
user_name => 'SUPERMAG',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'sql_tuning_task',
description => 'some test');
END;
/

select task_name from dba_advisor_log where task_name not like 'ADDM%';

exec dbms_sqltune.execute_tuning_task (task_name => 'sql_tuning_task');

select status from dba_advisor_log where task_name='sql_tuning_task';

set linesize 1000
set long 10000
set longchunksize 10000
select dbms_sqltune.report_tuning_task('sql_tuning_task') from dual;

execute dbms_sqltune.accept_sql_profile(task_name => 'sql_tuning_task', task_owner => 'SYS', replace => TRUE);

exec dbms_sqltune.drop_tuning_task('sql_tuning_task');

SET SERVEROUTPUT ON
set linesize 1000
set long 10000
set longchunksize 10000
declare
stmt_task VARCHAR2(40);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '83r7gk2gtn5hj');
DBMS_OUTPUT.put_line('task_id: ' || stmt_task );
dbms_sqltune.execute_tuning_task (task_name => stmt_task);
DBMS_OUTPUT.put_line('report: ' || dbms_sqltune.report_tuning_task(stmt_task) );
end;
/

если честно, сей агрегат больше похож на астролога. Иногда выдает абсолютное "пальцем в небо", но в любом случае стОит присмотреться к тому, что оно выдает.

OlegON · █ 13.09.2016 16:16

Собственно, могу добавить только еще один параметр CREATE_TUNING_TASK с биндами

Цитата:

bind_list => sql_binds(anydata.ConvertNumber(480645062))

SQL код:

 SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE"
FROM   USER_ADVISOR_PARAMETERS
WHERE  TASK_NAME = 'sql_tuning_task'
AND    PARAMETER_VALUE != 'UNUSED'
ORDER BY PARAMETER_NAME;

просмотр профилей

SQL код:

 SELECT NAME, SQL_TEXT, CATEGORY, STATUS
FROM DBA_SQL_PROFILES;

выключение профиля

SQL код:

 begin
DBMS_SQLTUNE.ALTER_SQL_PROFILE('SYS_SQLPROF_0157231daab80000','STATUS','DISABLED');
end;
/

удаление профиля

SQL код:

 exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_0157231daab80000');

можно создать задачу тюнинга, без вылавливания забинденных переменных, по SQL_ID:

SQL код:

 SET SERVEROUTPUT ON
declare
stmt_task VARCHAR2(40);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '3pyb5312540d7');
DBMS_OUTPUT.put_line('task_id: ' || stmt_task );
end;
/

Просмотр baselines

SQL код:

 select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

Создание снапшота

SQL код:

 exec dbms_workload_repository.create_snapshot;

вот итоговое, пример...

SQL код:

 SET SERVEROUTPUT ON
SET LONG 10000
SET LINESIZE 1000
declare
stmt_task VARCHAR2(40);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '5yhw2zq025y8d');
DBMS_OUTPUT.put_line('task_id: ' || stmt_task );
dbms_sqltune.execute_tuning_task (task_name => stmt_task);
end;
/  

select dbms_sqltune.report_tuning_task('TASK_211888') from dual;