Skip to content

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

 

[Post Views: 107]

Read Offline: PDF Download

Brijesh Gogia

I’m an experienced Oracle Applications DBA with more than a decade of full-time DBA experience. I have gained a wide knowledge of the Oracle software stack and have worked on several big projects for multi-national companies. I enjoy working with the leading-edge technology and have passion for database performance and stability. Thankfully my work allows me time for researching new technologies (and to write about them).
  
You can connect with me on LinkedIn.

Be First to Comment

Leave a Reply

Required fields are marked *