Generate multiple ADDM reports in Oracle

Source: Internet
Author: User

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

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.