Sql Tuning Advisor-Manual Steps

SET SERVEROUTPUT ON


--For a SQL statement
exec DBMS_SQLTUNE.CREATE_SQLSET('STS_AUG18_5KR');

declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(655,658,
'sql_id='||CHR(39)||'5kr1m2rx3ns2g'||CHR(39)||' and plan_hash_value=287817245',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('STS_AUG18_5KR', baseline_ref_cursor);
end;

-----------------------------------------

--For a ALL SQL statement from snapshot range
exec DBMS_SQLTUNE.CREATE_SQLSET('&STS_NAME');

declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(655,658,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('&STS_NAME', baseline_ref_cursor);
end;

-----------------------------------------------


--For a top 10 SQL statement from snapshot range

exec DBMS_SQLTUNE.CREATE_SQLSET('&STS_NAME');

declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(655,658,
NULL,NULL,'ELAPSED_TIME',NULL,NULL,1,10,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('&STS_NAME', baseline_ref_cursor);
end;

-----------------------------------------------


--Note := Last parameter "ALL" is required to capture plan in STS . By default its BASIC, which dont capture plan. This is
-- required for STS which in turn used for SPM load. OEM by default set this as BASIC.




-- Tuning task created for specific a statement from the AWR.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 764,
end_snap => 938,
sql_id => '19v5guvsgcd1v',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '19v5guvsgcd1v_AWR_tuning_task',
description => 'Tuning task for statement 19v5guvsgcd1v in AWR.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Tuning task created for specific a statement from the cursor cache.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '&SQL_ID',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '&TASK_NAME',
description => 'Tuning task for statement 19v5guvsgcd1v.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Tuning task created from an SQL tuning set.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sqlset_name => '&STS_NAME',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '&STA_NAME',
description => 'Tuning task for an SQL tuning set.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Tuning task created for a manually specified statement.
DECLARE
l_sql VARCHAR2(500);
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql := 'SELECT e.*, d.* ' ||
'FROM emp e JOIN dept d ON e.deptno = d.deptno ' ||
'WHERE NVL(empno, ''0'') = :empno';

l_sql := 'Select receiver_fund, nvl(arf,0), price_date, price, last_update_date '||
'FROM VALU_PEN_UV_CHANGES_14 WHERE last_update_date > '':SYS_B_1'' '||
'AND receiver_fund = '':SYS_B_2'' ';

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_text => l_sql,
bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => 'scott',
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('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/


SELECT NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET where name='&STS_NAME';

select sql_id, substr(sql_text,1, 15) text
from dba_sqlset_statements
where sqlset_name = 'STS_AUG18_5KR'
order by sql_id;


Run Tuning Task
===========

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '&TASK_NAME');
During the execution phase you may wish to pause and restart the task, cancel it or reset the task to allow it to be re-executed:
-- Interrupt and resume a tuning task.
EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => 'emp_dept_tuning_task');
EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => 'emp_dept_tuning_task');

-- Cancel a tuning task.
EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => 'emp_dept_tuning_task');

-- Reset a tuning task allowing it to be re-executed.
EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => 'emp_dept_tuning_task');


The status of the tuning task can be monitored using the DBA_ADVISOR_LOG view:
SELECT task_name, status FROM dba_advisor_log WHERE owner = 'SCOTT';


Once the tuning task has executed successfully the recommendations can be displayed using the REPORT_TUNING_TASK function:
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('&TASK_NAME') AS recommendations FROM dual;
SET PAGESIZE 24


If the recommendations of the SQL tuning advisor include a suggested profile you can choose to accept it using the ACCEPT_SQL_PROFILE procedure:
SET SERVEROUTPUT ON
DECLARE
l_sql_tune_task_id VARCHAR2(20);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.accept_sql_profile (
task_name => '&TASK_NAME',
name => '&Profile_name');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
The NAME parameter is used to specify a name for the profile. If it is not specified a system generated name will be used.


The STATUS, NAME, DESCRIPTION, and CATEGORY attributes of an SQL profile can be altered using the ALTER_SQL_PROFILE procedure:
BEGIN
DBMS_SQLTUNE.alter_sql_profile (
name => '&Profile_Name',
attribute_name => 'STATUS',
value => 'DISABLED');
END;
/
Existing SQL profiles can be dropped using the DROP_SQL_PROFILE procedure:
BEGIN
DBMS_SQLTUNE.drop_sql_profile (
name => 'emp_dept_profile',
ignore => TRUE);
END;
/