Create an Oracle SQL TUNING TASK manually with the sql_id ..
Create Oracle Task
save this on task.sql
set long 100000000 pagesize 1000 linesize 300 verify off
col recommendations for a200
accept task_name -
prompt 'Task_Name: '
declare
ret_val varchar2(32700);
begin
ret_val := dbms_sqltune.create_tuning_task(task_name=>'&&Task_name', sql_id=>'&sql_id', time_limit=>&time_limit);
dbms_sqltune.execute_tuning_task('&&Task_name');
end;
/
==============
@task.sql
Task_Name: SQLT
Enter value for sql_id: 40ww88ggjtx4y
Enter value for time_limit: 3200
SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
select dbms_sqltune.report_tuning_task('&&task_name') as recommendations from dual ;
==========================================================
STEP 1: Create tuning task for the specific SQL_ID:
declare
task_nm varchar2(100);
begin
task_nm := dbms_sqltune.create_tuning_task(SQL_ID=> '&SQL_ID',TASK_NAME => 'SQL_TUNNING_TASK_SQL_ID');
end;
/
STEP 2: Check the status of newly created task:
SELECT task_name, status FROM dba_advisor_log WHERE task_name = '&TASK_NAME';
STEP 3: Execute the newly created task:
exec dbms_sqltune.execute_tuning_task (TASK_NAME => '&TASK_NAME');
STEP 4: Check the status after executing the task:
SELECT task_name, status FROM dba_advisor_log WHERE task_name = '&TASK_NAME';
STEP 5: Execute the Below Query to get the Advisory Report:
SET LONG 10000 PAGESIZE 1000 LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('&TASK_NAME') AS recommendations FROM dual;
SET PAGESIZE 24
old 1: SELECT DBMS_SQLTUNE.report_tuning_task('&TASK_NAME') AS recommendations FROM dual
new 1: SELECT DBMS_SQLTUNE.report_tuning_task('SQL_TUNNING_TASK_SQL_ID') AS recommendations FROM dual
NOTE: Replace task name in above query
RECOMMENDATIONS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : SQL_TUNNING_TASK_SQL_ID
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 05/06/2021 09:27:26
Completed at : 05/06/2021 09:27:27
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : 3kywng531fcxu
SQL Text : delete from tab_stats$ where obj#=:1
Bind Variables :
1 - (NUMBER):7231
-------------------------------------------------------------------------------
There are no recommendations to improve the statement.
-------------------------------------------------------------------------------
STEP 6: To Drop the Tuning Task:
execute dbms_sqltune.drop_tuning_task('&TASK_NAME');
====================================================
define p_inst=1
column dt heading 'Date/Hour' format a11
set linesize 500 pages 100
select * from (
select min(snap_id) as snap_id,
to_char(start_time,'MM/DD/YY') as dt, to_char(start_time,'HH24') as hr
from (
select snap_id, s.instance_number, begin_interval_time start_time,
end_interval_time end_time, snap_level, flush_elapsed,
lag(s.startup_time) over (partition by s.dbid, s.instance_number order by s.snap_id) prev_startup_time,
s.startup_time
from dba_hist_snapshot s, gv$instance i
where 1=1
-- and begin_interval_time between trunc(sysdate)- p_days and sysdate
and begin_interval_time > sysdate -4
and s.instance_number = i.instance_number
and s.instance_number = &p_inst
order by snap_id
)
group by to_char(start_time,'MM/DD/YY') , to_char(start_time,'HH24')
order by snap_id, start_time )
pivot
(sum(snap_id)
for hr in ('00','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23')
)
order by dt;
Date/Hour '00' '01' '02' '03' '04' '05' '06' '07' '08' '09' '10' '11' '12' '13' '14' '15' '16' '17' '18' '19' '20' '21' '22' '23'
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
05/01/21 32911 32912 32913 32914 32915 32916 32917 32918 32919 32920 32921 32922 32923 32924 32925 32926 32927 32928 32929 32930
05/02/21 32931 32932 32933 32934 32935 32936 32937 32938 32939 32940 32941 32942 32943 32944 32945 32946 32947 32948 32949 32950 32951 32952 32953 32954
05/03/21 32955 32956 32957 32958 32959 32960 32961 32962 32963 32964 32965 32966 32967 32968 32969 32970 32971 32972 32973 32974 32975 32976 32977 32978
05/04/21 32979 32980 32981 32982 32983 32984 32985 32986 32987 32988 32989 32990 32991 32992 32993 32994 32995 32996 32997 32998 32999 33000 33001 33002
05/05/21 33003 33004 33005
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 32911 ,
end_snap => 32926,
sql_id => '1j6tnz8fcm4c3',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 560,
task_name => '1j6tnz8fcm4c3_AWR_tuning_task',
description => 'Tuning task for statement 1j6tnz8fcm4c3 in AWR.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '1j6tnz8fcm4c3',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 560,
task_name => '1j6tnz8fcm4c3_AWR_tuning_task',
description => 'Tuning task for statement 1j6tnz8fcm4c3');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '1j6tnz8fcm4c3_AWR_tuning_task');
PL/SQL procedure successfully completed.
SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( '1j6tnz8fcm4c3_AWR_tuning_task' ) FROM DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('1J6TNZ8FCM4C3_AWR_TUNING_TASK')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : 1j6tnz8fcm4c3_AWR_tuning_task
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 560
Completion Status : COMPLETED
Started at : 05/05/2021 03:16:45
Completed at : 05/05/2021 03:16:46
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : 1j6tnz8fcm4c3
SQL Text : delete /* QOSH:PURGE_OLD_STS *//*+ dynamic_sampling(4) */ from
sys.wri$_optstat_histgrm_history where savtime < :1 and
savtime > timestamp '1900-01-01 00:00:00 -0:0' and rownum <=
:2
Bind Variables :
2 - (NUMBER):10000
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
EXEC DBMS_SQLTUNE.drop_tuning_task (task_name => '1j6tnz8fcm4c3_AWR_tuning_task');
==============
