Sql Profile Notes

select * from dba_sql_profiles;

exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_0129d2c93fe60000');

The current parameter values are displayed using the %_ADVISOR_PARAMETERS views.
COLUMN parameter_value FORMAT A30

SELECT parameter_name, parameter_value
FROM dba_advisor_parameters
WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK'
AND parameter_name IN ('ACCEPT_SQL_PROFILES',
'MAX_SQL_PROFILES_PER_EXEC',
'MAX_AUTO_SQL_PROFILES');

The following code shows how the SET_TUNING_TASK_PARAMETER procedure is used to turn on acceptance of automatically generated SQL profiles.
BEGIN
DBMS_SQLTUNE.set_tuning_task_parameter(
task_name => 'SYS_AUTO_SQL_TUNING_TASK',
parameter => 'ACCEPT_SQL_PROFILES',
value => 'TRUE');
END;
/

The REPORT_AUTO_TUNING_TASK function of the DBMS_SQLTUNE package returns a CLOB containing a report from the specified automatic tuning task. Setting the BEGIN_EXEC and END_EXEC parameters to NULL produces a report from the most recent execution.
VARIABLE l_report CLOB;
BEGIN
:l_report := DBMS_SQLTUNE.report_auto_tuning_task(
begin_exec => NULL,
end_exec => NULL,
type => DBMS_SQLTUNE.type_text, -- 'TEXT'
level => DBMS_SQLTUNE.level_typical, -- 'TYPICAL'
section => DBMS_SQLTUNE.section_all, -- 'ALL'
object_id => NULL,
result_limit => NULL);
END;
/

SET LONG 1000000
PRINT :l_report