ADDM Runs

set serveroutput on;
declare
  -- find latest ADDM finding that actually produced recommendations
  -- can be more than 1
  cursor c_latest_recommends is
    select *
    from dba_advisor_tasks
    where execution_end = ( select max( execution_end )
                            from dba_advisor_tasks dat,
                                 dba_advisor_recommendations dar
                            where dat.task_name = dar.task_name
                          );

  cursor c_problems ( i_task in varchar2 ) is
    select *
    from dba_advisor_findings
    where task_name = i_task
      and type = 'PROBLEM';

  cursor c_symptoms ( i_task in varchar2, i_parent_id in integer) is
    select *
    from dba_advisor_findings
    where task_name = i_task
      and type = 'SYMPTOM'
      and parent = i_parent_id;

  cursor c_recommendations ( i_task in varchar2, i_problem_id in integer) is
    select *
    from dba_advisor_recommendations
    where task_name = i_task
      and finding_id = i_problem_id
    order by rec_id;

  cursor c_actions ( i_task in varchar2, i_rec_id in integer) is
    select *
    from dba_advisor_actions
    where task_name = i_task
      and rec_id = i_rec_id;

  cursor c_objects ( i_task in varchar2, i_obj_id in integer) is
    select *
    from dba_advisor_objects
    where task_name = i_task
      and object_id = i_obj_id;

begin

  dbms_output.put_line( 'Latest Sets of Recommendations' );
  dbms_output.put_line( '------------------------------' || chr(10) );

  for i in c_latest_recommends loop
    dbms_output.put_line( '' );
    dbms_output.put_line( 'Task Name: ' || i.task_name );
    dbms_output.put_line( rpad( '-', length( 'Task Name: ' || i.task_name ), '-' ) );
    dbms_output.put_line( 'Task Started: ' || to_char( i.execution_start, 'DD-MON-YYYY HH24:MI:SS') );

    dbms_output.put_line( '' );

    dbms_output.put_line( 'For full details connect as ' || i.owner || ' and run:' );
    dbms_output.put_line( '' );
    dbms_output.put_line( 'select dbms_advisor.get_task_report(''' || i.task_name || ''', ''TEXT'', ''TYPICAL'' ) from dual;');
    dbms_output.put_line( '' );

    for j in c_problems( i.task_name ) loop
      dbms_output.put_line( ' ' || j.type || ': ' || j.message );
      dbms_output.put_line( ' ' || rpad( '-', length( j.type || ': ' || j.message ), '-' ) );
      -- Symptoms
      dbms_output.put_line( '' );
      dbms_output.put_line( '  Symptoms:' );
      dbms_output.put_line( '  ---------' );
      for k in c_symptoms( i.task_name, j.finding_id) loop
        dbms_output.put_line( '  ' || k.message );
      end loop;
      -- Recommendations
      for l in c_recommendations( i.task_name, j.finding_id ) loop
        dbms_output.put_line( '' );
        dbms_output.put_line( '   Recommendation:' );
        dbms_output.put_line( '   ---------------' );
        dbms_output.put_line( '   ' || l.type );
        -- Actions for recommendation
        dbms_output.put_line( '' );
        dbms_output.put_line( '    Recommended action:' );
        dbms_output.put_line( '    -------------------' );

        for m in c_actions( i.task_name, l.rec_id ) loop
          dbms_output.put_line( '    ' || m.message );
          -- Object attributes
          dbms_output.put_line( '' );
          dbms_output.put_line( '      Attributes:' );
          dbms_output.put_line( '      -------------------' );
          for n in c_objects( i.task_name, m.object_id ) loop
            dbms_output.put_line( '      ' || n.attr3 || ' ' || n.attr4 || ' ' || n.attr5);
          end loop;
        end loop;
      end loop;
      dbms_output.put_line( chr(10) );
    end loop;
    dbms_output.put_line( chr(10) );
  end loop;
end;