/*
*
*  Author     : Vishal Gupta
*  Purpose    : SQL Profile Commands
*  Parameters : 
*          
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  16-May-12  Vishal Gupta  First cut
*/

-- Requires the ALTER ANY SQL PROFILE privilege.

-- Create a SQL Profile (Use following scripts)
@sql_profile_create.sql 
@sql_profile_create_fromcursor.sql

-- DROP a SQL Profile
exec DBMS_SQLTUNE.DROP_SQL_PROFILE (name => 'PROF_6r0u9km82f9gt_3364382418', ignore => FALSE)

-- Enable/Disable SQL Profile.
exec DBMS_SQLTUNE.ALTER_SQL_PROFILE (name=> 'PROF_8zp316hbs0c45_3180070961', attribute_name => 'STATUS', value => 'DISABLED')
exec DBMS_SQLTUNE.ALTER_SQL_PROFILE (name=> 'PROF_8zp316hbs0c45_3180070961', attribute_name => 'STATUS', value =>'ENABLED')

-- Rename a SQL Profile
exec DBMS_SQLTUNE.ALTER_SQL_PROFILE (name=> 'PROF_8zp316hbs0c45_3180070961', attribute_name =>'NAME', value =>'PROF_ak6yf7a3h5h9n_3180070961')

-- Modify Description
exec DBMS_SQLTUNE.ALTER_SQL_PROFILE (name=> '<profilename>', attribute_name =>'DESCRIPTION', value =>'<desc>')

-- Force matching of SQL statements even if literal values are used. 
-- Its similar to using cursor_sharing=force
exec DBMS_SQLTUNE.ALTER_SQL_PROFILE (name=> '<profilename>',attribute_name =>'FORCE_MATCHING', value =>'TRUE')

-- Change CATEGORY
exec DBMS_SQLTUNE.ALTER_SQL_PROFILE (name=> '<profilename>', attribute_name =>'CATEGORY', value =>'<newcategory>')


-- On Source Side
-- Pack, transfer and UnPack the SQL Profile from one database to another.
drop table TRAF_DBA_OWNER.VG_SQLPROF_STG;
exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF ( table_name => 'VG_SQLPROF_STG' , schema_name => 'TRAF_DBA_OWNER' , tablespace_name => NULL);
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF   ( profile_name => 'PROF_f5mx26ua5khzy_8022806', profile_category => 'DEFAULT', staging_table_name => 'VG_SQLPROF_STG' , staging_schema_owner => 'TRAF_DBA_OWNER');
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF   ( profile_name => '%', profile_category => '%', staging_table_name => 'VG_SQLPROF_STG' , staging_schema_owner => 'TRAF_DBA_OWNER');
expdp "'/ as sysdba'" tables=TRAF_DBA_OWNER.VG_SQLPROF_STG directory=DIR_DUMP dumpfile=TRAF_DBA_OWNER.VG_SQLPROF_STG.dmp logfile=TRAF_DBA_OWNER.VG_SQLPROF_STG.exp.log

-- On Target Side
impdp "'/ as sysdba'" TABLE_EXISTS_ACTION=replace tables=TRAF_DBA_OWNER.VG_SQLPROF_STG dumpfile=TRAF_DBA_OWNER.VG_SQLPROF_STG.dmp logfile=TRAF_DBA_OWNER.VG_SQLPROF_STG.imp.log
exec DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF ( profile_name => '%', profile_category => 'DEFAULT', staging_table_name => 'VG_SQLPROF_STG' , staging_schema_owner => 'TRAF_DBA_OWNER' , replace => false);
exec DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF ( profile_name => 'PROF_f5mx26ua5khzy_8022806', profile_category => '%', staging_table_name => 'VG_SQLPROF_STG' , staging_schema_owner => 'TRAF_DBA_OWNER' , replace => false);


-------------------------------
-- SQL Tuning Advisor
-------------------------------
DECLARE
my_task_name VARCHAR2(30);
my_sqltext   CLOB;
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
  sql_id           => 'f5mx26ua5khzy',
  plan_hash_value  => null,
  scope            => 'COMPREHENSIVE',
  time_limit       => 300,
  task_name        => 'TUNINGTASK_f5mx26ua5khzy',
  description      => 'TUNINGTASK_f5mx26ua5khzy'
  );
END;
/

--After you have created a tuning task, you need to execute the task and start the tuning process. For example:
BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'TUNINGTASK_f5mx26ua5khzy' );
END;
/


SET LONG 80000
SET LONGCHUNKSIZE 40000
set pages 10000
set lines 500
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'TUNINGTASK_f5mx26ua5khzy')
FROM DUAL;


Recommendation (estimated benefit: 91.32%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile to use parallel execution for this statement.

BEGIN
    dbms_sqltune.accept_sql_profile(task_name =>'TUNINGTASK_f5mx26ua5khzy'
    , task_owner => 'SYS'
    , replace =>TRUE
    , profile_type => DBMS_SQLTUNE.PX_PROFILE); 
END;
/