Description:
Show the results of the latest ADDM run(s)
Script:
prompt
prompt ADDM (Automatic Database Diagnostic Monitor) recommendations and suggestions for possible problems
prompt
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;
/
Example output:
SQL> @addm
ADDM (Automatic Database Diagnostic Monitor) recommendations and suggestions for possible problems
Latest Sets of Recommendations
------------------------------
Task Name: ADDM:2594484505_29821
--------------------------------
Task Started: 01-OCT-2015 23:00:30
For full details connect as SYS and run:
select dbms_advisor.get_task_report('ADDM:2594484505_29821', 'TEXT', 'TYPICAL' ) from dual;
PROBLEM: Individual database segments responsible for significant "User I/O" and "Cluster" waits were found.
------------------------------------------------------------------------------------------------------------
Symptoms:
---------
Wait class "User I/O" was consuming significant database time.
Recommendation:
---------------
Segment Tuning
Recommended action:
-------------------
Run "Segment Advisor" on INDEX "OCEAN.PK_LOCATION_LINKS" with object ID 116737.
Attributes:
-------------------
Investigate application logic involving I/O on INDEX "DEPUTY.ALIAS_INDEX_01" with object ID 116737.
Attributes:
-------------------
Recommendation:
---------------
Segment Tuning
Recommended action:
-------------------
Investigate application logic involving I/O on TABLE "DEPUTY.ALIAS" with object ID 114499.
Attributes:
-------------------
Look at the "Top SQL Statements" finding for SQL statements consuming significant I/O on this segment. For example, the INSERT statement with SQL_ID "8szmfgm7fysa3" is responsible for 100% of "User I/O" and "Cluster" waits for this segment.
Attributes:
-------------------
PROBLEM: PL/SQL execution consumed significant database time.
-------------------------------------------------------------
Symptoms:
---------
Recommendation:
---------------
SQL Tuning
Recommended action:
-------------------
Tune the entry point PL/SQL "SYS.DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC" of type "PACKAGE" and ID 7945. Refer to the PL/SQL documentation for addition information.
Attributes:
-------------------
PROBLEM: SQL statements consuming significant database time were found. These statements offer a good opportunity for performance improvement.
----------------------------------------------------------------------------------------------------------------------------------------------
Symptoms:
---------
Recommendation:
---------------
SQL Tuning
Recommended action:
-------------------
Run SQL Tuning Advisor on the INSERT statement with SQL_ID "8szmwam7fysa3".
Attributes:
-------------------
insert into wri$_adv_objspace_trend_data select timepoint, space_usage, space_alloc, quality from table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL, NULL, 'FALSE', :5, 'FALSE'))
Recommendation:
---------------
SQL Tuning
Recommended action:
-------------------
Run SQL Tuning Advisor on the SELECT statement with SQL_ID "7wgks43wrjtrz".
Attributes:
-------------------
SELECT U.SPACE_USED, U.SPACE_ALLOCATED FROM TABLE(DBMS_SPACE.OBJECT_SPACE_USAGE_TBF( :B1 , :B2 , :B3 , 0, :B4 , 'TRUE', :B5 )) U
No hay comentarios:
Publicar un comentario