Create Tuning Task Manually

Tuning task for specific SQL_ID can be created by SQLTRPT very easily.

Manual way is also documented below:

CREATE TUNING TASK

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '0uuczutvk6jqj',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 4000,
                          task_name   => '0uuczutvk6jqj'||'_manual_tuning',
                          description => 'Manual Tuning on 06 SEP 16');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

OR BY GIVING THE SQL DIRECTLY IF SQL IS SMALL:

variable sqltext varchar2(4000);
BEGIN
 :sqltext := q'#select count(*) from fnd_user;#';
END;
/
variable spmtune   varchar2(30);
exec :spmtune := dbms_sqltune.create_tuning_task(sql_text => :sqltext);
PRINT SPMTUNE

EXECUTE TUNING TASK

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '0uuczutvk6jqj'||'_manual_tuning');

CHECK STATUS OF TASK

SELECT task_name, status FROM dba_advisor_log WHERE task_name = '0uuczutvk6jqj'||'_manual_tuning';

CHECK RECOMMENDATION

SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 1000
SELECT DBMS_SQLTUNE.report_tuning_task('0uuczutvk6jqj'||'_manual_tuning') AS recommendations FROM dual;
SET PAGESIZE 24

Author
Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

Create Tuning Task Manually

Related posts: