oracle sta demo

CONNECT / AS SYSDBA
GRANT ADVISOR TO SH;
GRANT SELECT_CATALOG_ROLE TO SH;
GRANT EXECUTE ON DBMS_SQLTUNE TO SH;

select *
from (
select a.snap_id
       ,a.sql_id
       ,decode(sum(a.executions_delta),0,1,sum(a.executions_delta))                         execs
       ,sum(a.disk_reads_delta)                                                             pios
       ,sum(sum(a.disk_reads_delta)) over (partition by a.snap_id)                          total_pios
       ,dense_rank () over (partition by a.snap_id order by sum(a.disk_reads_delta) desc)   pio_rank
       ,sum(a.buffer_gets_delta)                                                            lios
       ,sum(sum(a.buffer_gets_delta)) over (partition by a.snap_id)                         total_lios
       ,dense_rank () over (partition by a.snap_id order by sum(a.buffer_gets_delta) desc)  lio_rank
       ,sum(a.cpu_time_delta)                                                               cput
       ,sum(sum(a.cpu_time_delta)) over (partition by a.snap_id)                            total_cput
       ,dense_rank () over (partition by a.snap_id order by sum(a.cpu_time_delta) desc)     cpu_rank
       ,sum(a.elapsed_time_delta)                                                           elat
       ,sum(sum(a.elapsed_time_delta)) over (partition by a.snap_id)                        total_elat
       ,dense_rank () over (partition by a.snap_id order by sum(a.elapsed_time_delta) desc) ela_rank
from dba_hist_sqlstat  a,
     dba_hist_snapshot b
where a.snap_id=b.snap_id
and   b.begin_interval_time >= trunc(sysdate-5)
and   b.begin_interval_time <= trunc(sysdate)
group by a.snap_id,a.sql_id)
where   pio_rank <= 5
or      lio_rank <= 5
or      cpu_rank <= 5
or      ela_rank <= 5
;

DECLARE
my_task_name VARCHAR2(30);
sql_id VARCHAR2(30);
BEGIN
sql_id := '&sqlid';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => sql_id,
scope => 'comprehensive',
time_limit => 12000,
task_name => 'perf_tuning_' || sql_id,
description => 'sql performence tunning');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK('perf_tuning_' || sql_id);
END;
/

select snap_id,count(*) from dba_hist_active_sess_history where sql_id='&sql_id' group by snap_id order by 1;

DECLARE
my_task_name VARCHAR2(30);
sql_id VARCHAR2(30);
BEGIN
sql_id := '&sqlid';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => sql_id,
begin_snap =>&bid,
end_snap =>&eid,
scope => 'comprehensive',
time_limit => 1200,
task_name => 'perf_tuning_' || sql_id,
description => 'sql performence tunning');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK('perf_tuning_' || sql_id);
END;
/

set long 999999
set LONGCHUNKSIZE 999999
set serveroutput on size 999999
set linesize 200
set pagesize 3000
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('perf_tuning_&sqlid') FROM DUAL;

BEGIN
dbms_sqltune.drop_tuning_task('perf_tuning_&sqlid');
END;
/