{"id":352,"date":"2019-11-27T07:39:00","date_gmt":"2019-11-26T21:39:00","guid":{"rendered":"http:\/\/www.mrmarkyoung.com\/oracle\/?p=352"},"modified":"2019-11-27T07:40:10","modified_gmt":"2019-11-26T21:40:10","slug":"addm-runs","status":"publish","type":"post","link":"http:\/\/www.mrmarkyoung.com\/oracle\/2019\/11\/27\/addm-runs\/","title":{"rendered":"ADDM Runs"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code>set serveroutput on;\ndeclare\n  -- find latest ADDM finding that actually produced recommendations\n  -- can be more than 1\n  cursor c_latest_recommends is\n    select *\n    from dba_advisor_tasks\n    where execution_end = ( select max( execution_end )\n                            from dba_advisor_tasks dat,\n                                 dba_advisor_recommendations dar\n                            where dat.task_name = dar.task_name\n                          );\n\n  cursor c_problems ( i_task in varchar2 ) is\n    select *\n    from dba_advisor_findings\n    where task_name = i_task\n      and type = 'PROBLEM';\n\n  cursor c_symptoms ( i_task in varchar2, i_parent_id in integer) is\n    select *\n    from dba_advisor_findings\n    where task_name = i_task\n      and type = 'SYMPTOM'\n      and parent = i_parent_id;\n\n  cursor c_recommendations ( i_task in varchar2, i_problem_id in integer) is\n    select *\n    from dba_advisor_recommendations\n    where task_name = i_task\n      and finding_id = i_problem_id\n    order by rec_id;\n\n  cursor c_actions ( i_task in varchar2, i_rec_id in integer) is\n    select *\n    from dba_advisor_actions\n    where task_name = i_task\n      and rec_id = i_rec_id;\n\n  cursor c_objects ( i_task in varchar2, i_obj_id in integer) is\n    select *\n    from dba_advisor_objects\n    where task_name = i_task\n      and object_id = i_obj_id;\n\nbegin\n\n  dbms_output.put_line( 'Latest Sets of Recommendations' );\n  dbms_output.put_line( '------------------------------' || chr(10) );\n\n  for i in c_latest_recommends loop\n    dbms_output.put_line( '' );\n    dbms_output.put_line( 'Task Name: ' || i.task_name );\n    dbms_output.put_line( rpad( '-', length( 'Task Name: ' || i.task_name ), '-' ) );\n    dbms_output.put_line( 'Task Started: ' || to_char( i.execution_start, 'DD-MON-YYYY HH24:MI:SS') );\n\n    dbms_output.put_line( '' );\n\n    dbms_output.put_line( 'For full details connect as ' || i.owner || ' and run:' );\n    dbms_output.put_line( '' );\n    dbms_output.put_line( 'select dbms_advisor.get_task_report(''' || i.task_name || ''', ''TEXT'', ''TYPICAL'' ) from dual;');\n    dbms_output.put_line( '' );\n\n    for j in c_problems( i.task_name ) loop\n      dbms_output.put_line( ' ' || j.type || ': ' || j.message );\n      dbms_output.put_line( ' ' || rpad( '-', length( j.type || ': ' || j.message ), '-' ) );\n      -- Symptoms\n      dbms_output.put_line( '' );\n      dbms_output.put_line( '  Symptoms:' );\n      dbms_output.put_line( '  ---------' );\n      for k in c_symptoms( i.task_name, j.finding_id) loop\n        dbms_output.put_line( '  ' || k.message );\n      end loop;\n      -- Recommendations\n      for l in c_recommendations( i.task_name, j.finding_id ) loop\n        dbms_output.put_line( '' );\n        dbms_output.put_line( '   Recommendation:' );\n        dbms_output.put_line( '   ---------------' );\n        dbms_output.put_line( '   ' || l.type );\n        -- Actions for recommendation\n        dbms_output.put_line( '' );\n        dbms_output.put_line( '    Recommended action:' );\n        dbms_output.put_line( '    -------------------' );\n\n        for m in c_actions( i.task_name, l.rec_id ) loop\n          dbms_output.put_line( '    ' || m.message );\n          -- Object attributes\n          dbms_output.put_line( '' );\n          dbms_output.put_line( '      Attributes:' );\n          dbms_output.put_line( '      -------------------' );\n          for n in c_objects( i.task_name, m.object_id ) loop\n            dbms_output.put_line( '      ' || n.attr3 || ' ' || n.attr4 || ' ' || n.attr5);\n          end loop;\n        end loop;\n      end loop;\n      dbms_output.put_line( chr(10) );\n    end loop;\n    dbms_output.put_line( chr(10) );\n  end loop;\nend;\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-352","post","type-post","status-publish","format-standard","hentry","category-oracle-database"],"_links":{"self":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/352","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/comments?post=352"}],"version-history":[{"count":1,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/352\/revisions"}],"predecessor-version":[{"id":353,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/352\/revisions\/353"}],"wp:attachment":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/media?parent=352"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/categories?post=352"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/tags?post=352"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}