Creating SQL Tuning Task

DECLARE
my_task_name VARCHAR2(30);
my_sqltext   CLOB;
BEGIN
my_sqltext := ‘SELECT a16.mkt_oid mkt_oid,’||
‘a16.mkt_nm mkt_nm,’||
‘ a16.mkt_cd mkt_cd,’||’a15.consolidated_brand_nm consolidated_brand_nm,’||
‘SUM (a11.xp_trx) wjxbfs3’ ||
‘FROM  prescrip_sales a11 JOIN month a12  ON (a11.mo_id = a12.mo_id)’||
‘JOIN zzmq00 pa13 ON (a12.mo_oid = pa13.mo_oid)  JOIN  d_product a14 ON (a11.src_prod_oid = a14.src_prod_oid) 
‘GROUP BY a16.mkt_oid,         a16.mkt_nm, a16.mkt_cd, a15.consolidated_brand_nm, a18.spec_group_oid, a18.spec_group_nm, pa13.mo_oid, pa13.mo_disp’;

 

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
         sql_text    => my_sqltext,
         bind_list   => sql_binds(anydata.ConvertNumber(100)),
         user_name   => ‘SYS’,
         scope       => ‘COMPREHENSIVE’,
         time_limit  => 60,
         task_name   => ‘bi_sql_tuning_test’,
         description => ‘Task to tune a query generated by  Microstrategy ‘);
END;

Executing a Tuning Task

After you have created a tuning task, you need to execute the task and start the tuning process.

BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'bi_sql_tuning_test’ );
END;
 
Check status of the task.
 
SELECT status FROM DBA_ADVISOR_LOG WHERE task_name = 'bi_sql_tuning_test'
 

Displaying the Results of a Tuning Task

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'bi_sql_tuning_test')
  FROM DUAL;
 

Dropping a Tuning task

BEGIN 
DBMS_SQLTUNE.DROP_TUNING_TASK( task_name => 'bi_sql_tuning_test’ );
END;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: