Generate multiple ADDM reports in Oracle

Generate multiple ADDM 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;/beginselect min(snap_id) into :bid from dba_hist_snapshot swhere (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 swhere (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;begindeclareid number;name varchar2(100);descr varchar2(500);BEGINname := '';descr := 'ADDM run: snapshots [' || :bid || ', '|| :eid || '], instance ' || :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 1000column get_clob format a80select dbms_advisor.get_task_report(:task_name, 'TEXT', 'TYPICAL')from dual;spool off;promptprompt End of Reportprompt Report written to &report_name.set termout on;clear columns sql;ttitle off;btitle off;repfooter off;undefine report_name