Re: sql_profile

Akimeu wrote:
> Hi group,
>
> I have a recommended sql profile that the DBMS_SQLTUNE suggested
> ("...Oracle found a potentially better exec plan, and it says to
> accept_sql_profile (task_name=>etc.."), but before I execute this
> command - how do I view the details of the recommended profile?
>
> Thanks in advance,
> Al

OEM is the easiest and in my mind most useful interface, but you can also use something like what are below...

SELECT dbms_sqltune.report_tuning_task('your_task_name')   FROM dual;

set long 1000000
set pagesize 50000
column get_clob format a80
select dbms_advisor.get_task_report(
task_name, 'TEXT', 'ALL')
as ADDM_report
from dba_advisor_tasks

	where task_id=(
	select max(t.task_id)
	from dba_advisor_tasks t, 			dba_advisor_log l
	  where t.task_id = l.task_id
	  and t.advisor_name='ADDM'
	  and l.status= 'COMPLETED');

Also, select view_name from dba_views where view_name like 'DBA_ADVIS%' should prove useful.

HTH, Steve Received on Tue Oct 17 2006 - 14:50:28 CDT