Category Archives: Performance Tuning

Generating ADDM report Automatically

Database tuning is always ongoing effort and it never stops. Similar doing health checks are like gold mine. You never know what you will get it. From Oracle DB 10g onwards, we have a facility to generate ADDM reports which often is neglected. With growing databases under my hood, I was bit negligent reviewing performance of some least used database or those which were not coming in picture very often. I thought of automating the tuning and reviewing which may help me to efficiently monitor the database performance and keep a track of what is going on in my database.

So here is my workflow.

1. Automate the ADDM report of last day for each database.

2. Send an email report to yourself and team

3. Ask team members to have a quick look every day on these reports

4. Run tuning advisor and segment advisor and review the result

5. Take actions

Hopefully, we will rip this gold mine of data for performance tuning.

Here is the script to generate ADDM report of last day.

/*
 * Purpose: To generate ADDM report manually
 * 
 * Author: Anjul Sahu
 * 
 * History: 
 * 25-SEP-2014	anjul	initial version
 * 
 */
 

SET LONG 1000000
SET LINES 1000 PAGES 0
SET FEEDBACK ON TIMING OFF VERIFY OFF
SPOOL last_day_addm_report.txt

DECLARE
   dbid           NUMBER;
   bid            NUMBER;
   eid            NUMBER;
   db_unique_name VARCHAR2(30);  
   host_name      VARCHAR2(64);
   status         VARCHAR2(11);
   starttime      CHAR (5);
   endtime        CHAR (5);
   output         VARCHAR2 (32000);
   tname varchar2(50);
   tid   number;  
BEGIN
   --starttime := '00:00';
   --endtime := '23:00';
	dbms_output.enable(100000);
   SELECT MIN (snap_id)-1, MAX (snap_id)+1
     INTO bid, eid
     FROM dba_hist_snapshot
    WHERE 
      --TO_CHAR (begin_interval_time, 'hh24:mi') >= starttime
      --AND TO_CHAR (end_interval_time, 'hh24:mi') <= endtime
      --AND 
      TRUNC (begin_interval_time) = TRUNC (SYSDATE-1)
      AND TRUNC (end_interval_time) = TRUNC (SYSDATE-1);
 
   SELECT dbid, db_unique_name
     INTO dbid, db_unique_name
     FROM v$database;
 
   SELECT host_name INTO host_name
     FROM v$instance;
    
    DBMS_ADVISOR.CREATE_TASK('ADDM',tid,tname,'ADDM Report( '
    || bid || ',' || eid || ' )');
    DBMS_ADVISOR.SET_TASK_PARAMETER( tname,'START_SNAPSHOT',bid );
    DBMS_ADVISOR.SET_TASK_PARAMETER( tname,'END_SNAPSHOT',eid );
    DBMS_ADVISOR.EXECUTE_TASK( tname );    
    
    status := 0;
    
    while status  'COMPLETED' loop
    select status into status from dba_advisor_tasks where task_id = tid;
    dbms_lock.sleep(5);    
    end loop;

 
 
   SELECT DBMS_ADVISOR.GET_TASK_REPORT( tname) INTO output FROM DUAL;
   DBMS_OUTPUT.PUT_LINE(output); 

EXCEPTION
   WHEN OTHERS THEN
    dbms_output.put_line('Error Occurred...'||SQLCODE);
    NULL;
END;
/
SPOOL OFF
exit
Advertisements

SQL Tuning: Where to START?

Found following Oracle support notes to be good starting point while tuning SQL –

  • TROUBLESHOOTING: Tuning a New Query (Doc ID 372431.1)
  • Tuning Queries: ‘Quick and Dirty’ Solutions (Doc ID 207434.1)
  • TROUBLESHOOTING: Advanced Query Tuning (Doc ID 163563.1)
  • Troubleshooting: High CPU Utilization (Doc ID 164768.1)
  • Potentially Expensive Query Operations (Doc ID 162142.1)

E-Business Suite Performance Tuning

I was going through Steven Chan’s blog and have got a very good presentation on EBS performance tuning. It has given the approach, note references and tools to be used during investigation. It covers all the aspects of the system – client browser,¬†middle-ware, network and database. It is a must reference for all the Oracle Apps DBAs.

Apart from the tools mentioned – I strongly recommend to use ASH Viewer and Tanel Poder’s Snapper when tuning database.

Presentation (pdf 1.9M)

Source: http://blogs.oracle.com/stevenChan/entry/oaug_collaborate_recap_best_practices