oracle tunning sql dbms_sqltune - ghdrako/doc_snipets GitHub Wiki

$cat tuning_task_awr.sql
SET SERVEROUTPUT ON

define sqlid=&1
spool Tuning_&&sqlid..logset timing on
set echo on
set trimout on trimspool on
set linesize 1024
set pagesize 256
COLUMN snap_min new_value begin_snap noprint
COLUMN snap_max new_value end_snap noprint

select min(snap_id) snap_min, max(snap_id) snap_max from dba_hist_snapshot;
exec DBMS_SQLTUNE.drop_tuning_task(task_name => '&&sqlid._tuning_task');
declare
 l_sql_tune_task_id varchar2(100);
begin
 l_sql_tune_task_id := dbms_sqltune.create_tuning_task (
  begin_snap => &begin_snap,
  end_snap => &end_snap,
  sql_id => '&&sqlid',
  scope => dbms_sqltune.scope_comprehensive,
  time_limit => 1800,
  task_name => '&&sqlid._tuning_task',
  description => 'Tuning task1 for statement &&sqlid in AWR');
 dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
end;
/

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '&&sqlid._tuning_task');
set long 65536
set longchunksize 65536
set linesize 200 trimspool on trimout on
select dbms_sqltune.report_tuning_task('&&sqlid._tuning_task') from dual;
exec DBMS_SQLTUNE.drop_tuning_task(task_name => '&&sqlid._tuning_task');
spool off

jako parametr &sqlid zapytania do tunningu define sqlid="zasedufv8kkvdbh"