ADDM Report
ADDM for RAC
DBMS_ADDM for RAC
SELECT MIN(snap_id), MAX(snap_id) FROM dba_hist_snapshot;
All instance in RAC ...
_________________________
VAR tname VARCHAR2(30);
VAR start_snap_id number;
VAR end_snap_id number;
BEGIN
:tname := 'DB_TEST_ANUJ';
:start_snap_id := 884 ;
:end_snap_id := 1052 ;
DBMS_ADDM.ANALYZE_DB(:tname, :start_snap_id, :end_snap_id);
END;
DBMS_ADDM for particulate Instance
_________________________________
VAR tname VARCHAR2(30);
VAR start_snap_id number;
VAR end_snap_id number;
VAR INST_NUM number;
BEGIN
:tname := 'INST_TEST_ANUJ';
:start_snap_id := 884 ;
:end_snap_id := 1052 ;
:INST_NUM := 1;
DBMS_ADDM.ANALYZE_INST(:tname,:start_snap_id,:end_snap_id, :INST_NUM );
END;
/
DBMS_ADDM for partial mode
___________________________
DBMS_ADDM
analyze instance 1 and 3 out of four node RAC
VAR tname VARCHAR2(30);
VAR start_snap_id number;
VAR end_snap_id number;
BEGIN
:tname := 'PART_MODE_TEST_ANUJ';
:start_snap_id :=884;
:end_snap_id := 1052 ;
DBMS_ADDM.ANALYZE_PARTIAL(:tname,'1,3', :start_snap_id, :end_snap_id);
END;
/
ADDM report output
__________________
SET LONG 500000 PAGESIZE 0;
SELECT DBMS_ADDM.GET_REPORT(:tname) FROM DUAL;
or
SELECT DBMS_ADDM.GET_REPORT('INST_TEST_ANUJ') FROM DUAL;
===========
SELECT MIN(snap_id), MAX(snap_id) FROM dba_hist_snapshot;
BEGIN
--
DBMS_ADVISOR.create_task (advisor_name => 'ADDM',task_name => '33033_33035_AWR_SNAPSHOT',task_desc => 'Advisor for snapshots 33033 to 33035');
DBMS_ADVISOR.set_task_parameter (task_name => '33033_33035_AWR_SNAPSHOT',parameter => 'START_SNAPSHOT',value => 33033);
DBMS_ADVISOR.set_task_parameter ( task_name => '33033_33035_AWR_SNAPSHOT',parameter => 'END_SNAPSHOT',value => 33035);
DBMS_ADVISOR.execute_task(task_name => '33033_33035_AWR_SNAPSHOT');
END;
/
SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_report('33033_33035_AWR_SNAPSHOT') AS report FROM dual;
=============
some sql !!!
set linesize 500 pagesize 300
col ACTION_MESSAGE for a70 wrap
col MESSAGE for a50 wrap
Select a.execution_end, b.type, b.impact, d.rank, d.type,
'Message:'|| b.message MESSAGE,
'Command To correct:'||c.command COMMAND,
'Action Message:'|| c.message ACTION_MESSAGE
From dba_advisor_tasks a, dba_advisor_findings b,Dba_advisor_actions c, dba_advisor_recommendations d
Where a.owner=b.owner
and a.task_id=b.task_id
And b.task_id=d.task_id
and b.finding_id=d.finding_id
And a.task_id=c.task_id
and d.rec_id=c.rec_Id
And a.task_name like 'ADDM%'
and a.status='COMPLETED'
and to_char(execution_end,'dd/mm/yyyy')='06/05/2021'
Order by 3 desc;
set linesize 500 pagesize 300
col ACTION_MESSAGE for a70 wrap
col MESSAGE for a50 wrap
Select a.execution_end, b.type, b.impact, d.rank, d.type,
'Message:'|| b.message MESSAGE,
'Command To correct:'||c.command COMMAND,
'Action Message:'|| c.message ACTION_MESSAGE
From dba_advisor_tasks a, dba_advisor_findings b,Dba_advisor_actions c, dba_advisor_recommendations d
Where a.owner=b.owner
and a.task_id=b.task_id
And b.task_id=d.task_id
and b.finding_id=d.finding_id
And a.task_id=c.task_id
and d.rec_id=c.rec_Id
And a.task_name like 'ADDM%'
and a.status='COMPLETED'
and to_char(execution_end,'dd/mm/yyyy')='06/05/2021'
Order by 3 desc;
EXECUTION TYPE IMPACT RANK TYPE MESSAGE COMMAND ACTION_MESSAGE
--------- ----------- ---------- ---------- ------------------------------ -------------------------------------------------- ----------------------------------------------------------------------------------- ----------------------------------------------------------------------
06-MAY-21 PROBLEM 338002056 2 SQL Tuning Message:SQL statements consuming significant datab Command To correct:UNDEFINED Action Message:Investigate the PL/SQL statement with SQL_ID "b6usrg82h
ase time were found. These statements offer a good wsa3" for possible performance improvements. You can supplement the in
opportunity for performance improvement. formation given here with an ASH report for this SQL_ID.
06-MAY-21 PROBLEM 338002056 4 SQL Tuning Message:SQL statements consuming significant datab Command To correct:UNDEFINED Action
set linesize 300 pagesize 300
col ACTION_MESSAGE for a150 wrap
Select distinct c.message ACTION_MESSAGE
From dba_advisor_tasks a, dba_advisor_findings b,Dba_advisor_actions c,
dba_advisor_recommendations d
Where a.owner=b.owner and a.task_id=b.task_id
And b.task_id=d.task_id and b.finding_id=d.finding_id
And a.task_id=c.task_id and d.rec_id=c.rec_Id
And a.task_name like 'ADDM%' and a.status = 'COMPLETED'
-- And c.message like '%involving I/O on TABLE%'
--And c.message like 'Investigate application logic involving I/O on TABLE%'
Order by 1;
ACTION_MESSAGE
------------------------------------------------------------------------------------------------------------------------------------------------------
Consider not using the compression option for RMAN jobs. Alternatively, consider slowing down RMAN activity, or scheduling RMAN jobs when user activit
y is lower.
Increase the size of the SGA by setting the parameter "sga_target" to 4608 M.
Increase the size of the SGA by setting the parameter "sga_target" to 5120 M.
Increase the size of the SGA by setting the parameter "sga_target" to 5632 M.
Increase the size of the SGA by setting the parameter "sga_target" to 6144 M.
Increase the size of the SGA by setting the parameter "sga_target" to 6656 M.
Increase the size of the SGA by setting the parameter "sga_target" to 7168 M.
Increase the size of the SGA on affected instances. Check the ADDM analysis of affected instances for more details.
Increase throughput of the Global Cache Service (LMSn) processes. Increase the number of Global Cache Service processes by increasing the value of the
parameter "gcs_server_processes". Alternatively, if the host is CPU bound consider increasing the OS priority of the Global Cache Service processes.
Investigate application logic involving I/O on database object with ID 82.
Investigate application logic to eliminate parse errors.
Investigate appropriateness of DDL operations.
Investigate the PL/SQL statement with SQL_ID "b6usrg82hwsa3" for possible performance improvements. You can supplement the information given here with
an ASH report for this SQL_ID.
Set pages 1000 lines 300
col ACTION_MESSAGE for a70
col MESSAGE for a20
col COMMAND for a15
Select a.execution_end, b.type, b.impact, d.rank, d.type,
'Message : '||b.message MESSAGE,
'Command To correct: '||c.command COMMAND,
'Action Message : '||c.message ACTION_MESSAGE
from dba_advisor_tasks a, dba_advisor_findings b,dba_advisor_actions c, dba_advisor_recommendations d
where a.owner=b.owner and a.task_id=b.task_id
and b.task_id=d.task_id and b.finding_id=d.finding_id
and a.task_id=c.task_id and d.rec_id=c.rec_Id
and a.task_name like 'ADDM%'
and a.status='COMPLETED'
and a.description like '%53996%' --- <<<< from above sql
and d.type='SQL Tuning'
-- and b.owner not in ('SYS','SYSTEM')
-- and a.description like (select snap_id from (select snap_id from dba_hist_snapshot order by begin_interval_time desc) where rownum < 3)
Order by b.impact, d.rank;
Set pages 1000 lines 300
col ACTION_MESSAGE for a70
col MESSAGE for a20
col COMMAND for a15
Select a.execution_end, b.type, b.impact, d.rank, d.type,
'Message : '||b.message MESSAGE,
'Command To correct: '||c.command COMMAND,
'Action Message : '||c.message ACTION_MESSAGE
from dba_advisor_tasks a, dba_advisor_findings b,dba_advisor_actions c, dba_advisor_recommendations d
where a.owner=b.owner and a.task_id=b.task_id
and b.task_id=d.task_id and b.finding_id=d.finding_id
and a.task_id=c.task_id and d.rec_id=c.rec_Id
and a.task_name like 'ADDM%'
and a.status='COMPLETED'
--and a.description like '%b742waswg25n7_tuning_task11%' --- <<<< from above sql
and d.type='SQL Tuning'
-- and b.owner not in ('SYS','SYSTEM')
-- and a.description like (select snap_id from (select snap_id from dba_hist_snapshot order by begin_interval_time desc) where rownum < 3)
--and a.execution_end > sysdate -1
Order by b.impact, d.rank;===
select round((ratio_to_report(max(Benefit)) over () *100)) as overall_benefit_pct
, type
, min(benefit) min_benefit
, max(Benefit) max_benefit
, count(*) cnt
from dba_Advisor_recommendations
where type is not null
group by type
order by 1 desc
;
col command for a15
col message for a100
select command, message , count(*) from dba_advisor_Actions
group by command, message
;
col TASK_NAME for a27
col description for a100
select task_name, description, created from dba_advisor_tasks
where advisor_name = 'ADDM'
and created > sysdate -7
order by created desc;
SELECT type, count(*) FROM dba_advisor_findings NATURAL JOIN dba_advisor_tasks WHERE created between sysdate -1 and sysdate GROUP BY type
SELECT distinct message FROM dba_advisor_recommendations JOIN dba_advisor_findings USING (finding_id, task_id) WHERE rank = 0;
SET LONG 500000 PAGESIZE 0
SELECT DBMS_ADDM.GET_REPORT('ADDM:1825264339_1_19982') FROM DUAL;
1 comment:
http://anuj-singh.blogspot.com/2011/06/oracle-11g-addm-report-for-oracle-rac.html
Post a Comment