Generate multiple ADDM reports and oracleaddm reports in Oracle

Generate multiple ADDM reports and oracleaddm reports in Oracle
I have written an article in Oracle to generate AWR reports in batches. We used to monitor AWR reports every day to eliminate performance risks in a timely manner. Now there is another way to generate an ADDM report and listen to the Oracle suggestions.
Problems that can be identified by ADDM include:
An error occurred while accessing the Memory Page of the operating system.
. CPU bottlenecks caused by Oracle load and non-Oracle Load
. Top SQL statements and objects with different resource loads-CPU consumption, I/O bandwidth usage, potential I/O problems, and RAC internal communication busy
. Top SQL statements ranked according to the execution time of PLSQL and JAVA.
Too many connections (login/logoff ).
Too many hard parsing problems-caused by too small shared pool, writing problems, unsuitable binding size, and resolution failure.
Too many soft resolution Problems
Too many index queries lead to resource contention.
. Excessive waiting time caused by user locks (the lock added through the dbms_lock package)
. Too much wait time due to the DML lock (for example, locking the table)
. Too much wait time due to MPs queue output (for example, MPs queue output through dbms_pipe.put)
. Excessive wait time (Row-Level Lock wait) due to concurrent updates to the same record)
. Too much wait time due to insufficient ITL (a large number of transactions operate on the same data block)
. Excessive commit and rollback (logfile sync events) in the system ).
. I/O performance problems due to disk bandwidth being too small and other potential problems (for example, too many checkpoints, MTTR settings problems, too many undo operations, etc.) I
For data blocks written by the DBWR process, the disk IO throughput is insufficient.
. Because the archiving process cannot keep up with the redo date, the system slows down.
. Redo data files are too small.
. Contention caused by extended disk allocation
. Competition Caused by moving the high water level of an object
. Memory too small-SGA Target, PGA, Buffer Cache, Shared Pool
. There are hot blocks with frequent read/write contention in an instance or a cluster environment
. There are hot objects with frequent read/write contention in an instance or a cluster environment
Internal communication problems in the. RAC Environment
The. LMS process cannot keep up with the lock, causing blocking of lock requests.
. Instance skew caused by blocking and contention in the RAC Environment
IO and CPU problems caused by. RMAN
. Streams and AQ Problems
. Resource Management wait event

Usage: Create an addm directory under the e drive and put the three files into the directory addm. cmd,Addm. SQL,MyAddm. SQL.

The connected user must be granted two permissions.

Grant select any dictionary to user; ----- the user is the account of the production database.

Grant advisor to user; ------- the user is the production database account

1. File Name:Addm. Cmd

 Content:Cmd.exe/c sqlplus username/password @ servername @Addm. SQL

2. File Name:Addm. SQL

 Content:

@ E: \ addm \ myaddm. SQL 0 8
@ E: \ addm \ myaddm. SQL 8 12
@ E: \ addm \ myaddm. SQL 12 14
@ E: \ addm \ myaddm. SQL 14 18
@ E: \ addm \ myaddm. SQL 18 24
Exit

3. File Name:Myaddm. SQL

Content:

Set echo off;
Set veri off;
Set feedback off;
Set termout on;
Set heading off;
Set linesize 300;
Var dbid number;
Var inst_num number;
Var bid number;
Var eid number;
Var bhour varchar2 (10 );
Var ehour varchar2 (10 );
Var task_name varchar2 (40 );
Begin
: Bhour: = & 1;
: Ehour: = & 2;
If (length (: bhour) = 1)
Then: bhour: = '0' |: bhour;
End if;
If (length (: ehour) = 1)
Then: ehour: = '0' |: ehour;
End if;
End;
/
Begin
Select min (snap_id) into: bid from dba_hist_snapshot s
Where (to_char (sysdate-1, 'yyyy-mm-dd') | ''|: bhour) <=
To_char (s. end_interval_time, 'yyyy-mm-dd hh24 ')
And (to_char (sysdate-1, 'yyyy-mm-dd') | ''|: ehour)> =
To_char (s. end_interval_time, 'yyyy-mm-dd hh24 ');
Select max (snap_id) into: eid from dba_hist_snapshot s
Where (to_char (sysdate-1, 'yyyy-mm-dd') | ''|: bhour) <=
To_char (s. end_interval_time, 'yyyy-mm-dd hh24 ')
And (to_char (sysdate-1, 'yyyy-mm-dd') | ''|: ehour)> =
To_char (s. end_interval_time, 'yyyy-mm-dd hh24 ');
If (: ehour = 24)
Then select max (snap_id) into: eid from dba_hist_snapshot s where (to_char (sysdate, 'yyyy-mm-dd') | '00') = to_char (s. end_interval_time, 'yyyy-mm-dd hh24 ');
End if;
Select dbid into: dbid from v $ database;
Select instance_number into: inst_num from v $ instance;
End;
/


Column report_name new_value report_name noprint;
Select 'pms _ '|: inst_num |' _ '| to_char (sysdate-1, 'yyyymmdd') |' _ '|: bhour | '-' |: ehour | '.txt 'report_name from dual;
Begin
Declare
Id number;
Name varchar2 (100 );
Descr varchar2 (500 );
BEGIN
Name: = '';
Descr: = 'addm run: snapshots ['|: bid | ','
|: Eid | '], inst' |: inst_num
| ', Database id' |: dbid;
Dbms_advisor.create_task ('addm', id, name, descr, null );
: Task_name: = name;
Dbms_advisor.set_task_parameter (name, 'start _ SNAPSHOT ',: bid );
Dbms_advisor.set_task_parameter (name, 'end _ SNAPSHOT ',: eid );
Dbms_advisor.set_task_parameter (name, 'instance',: inst_num );
Dbms_advisor.set_task_parameter (name, 'db _ id',: dbid );
Dbms_advisor.execute_task (name );
End;
End;
/


Spool & report_name;
Set long 1000000 pagesize 0 longchunksize 1000
Column get_clob format a80
Select dbms_advisor.get_task_report (: task_name, 'text', 'typical ')
From dual;
Spool off;
Prompt
Prompt End of Report
Prompt Report written to & report_name.


Set termout on;
Clear columns SQL;
Ttitle off;
Btitle off;
Repfooter off;
Undefine report_name