ADDM REPORT IN ORACLE FOR PERFORMANCE TUNING

ADDM REPORT IN ORACLE FOR PERFORMANCE TUNING

---------------------------------------------------------------------------------

Introduction

----------------

ADDM : Aotomatic Database Diagnostic Monitor is a self Diagnostic engine built in oracle database ,ADDM examines and analyze data captured in the AWR to determine possible performance problems

  In oracle database,ADDM uses database time statastics to identify performance problems,ADDM compares the difference between snapshots,by default AWR generates snapshots of performance data ,once every hour,alternatively you can modify the default values of both the interval between snapshots and their retention period,analyze your current database prformance when its hanging or  running slow using ADDM is a server based expert that reviews the database performance every 1 hr by default, its goal is to detect possible bottle necks early and recommended fixes before  system performance degrades noticably.

ADDM Analysis Include following

-------------------------------------------

Cpu load

Memory Usage

I/O Usage

Resources intensive sql

Rseources Intensive pl/sql ,java

RAC issues

Application Issues

Database configuration issues

Concurrency issues

Hardware Changes

Schema Changes

Application Changes

Using other advisors

Sql tuning advisor

Sql acecss advisor

Memory advisor----------> pga advisor----->buffer cache advisor,shared pool advisor,java pool advisor,streams pool advisor

Space advisor-----------> segmemt advisor,undo advisor

Backup -----------------> MTTR advisor

ADDM report can be generated from sql* plus using addmrpt.sql

$ ORACLE_HOME/rdbms/admin/

$ ls -ltr

$ ls -ltr | grep addmrpt*

$ sqlplus '/as sysdba'

connected to database

sql>@?/rdbms/admin/addmrpt.sql

when run the script itshows the current instance like dbid  ,dbname ,instnum, instance . Instance in the workload repository schema  and host name also

we can query in sql also snapshots by using  sql query

sql>select snap_id from dba_hist_snapshot;

specify the begin and end snapshot ids

enter the value for begin_snap :8

begin snapshot id specified:8

             

enter value for end_snap: 11

end snapshot id specified: 11

specify the report name : dccaddmrpt.html 

it will generate the report in dccaddmrpt.html format using winscp we can copy this file 

    

Note : Info on ADDMRPT it may be differ from your environment like production, development and testing 

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

AWR REPORT PERFORMANCE TUNING

 AWR REPORT PERFORMANCE TUNING

-----------------------------------------------------

Introduction

---------------

 Automatic work load Repository report or AWR report collects,processes and maintain performance statistics for problem detection and self tuning process.This gathered data is stored both in memory and in the database,and is dispalyed in both reports and views. 

 AWR was introduced in oracle 10g  and it is still being used in present versions and AWR is comes with enterprise edition

 Performance Tuning in oracle database

 The AWR is a source for information and other oracle feature it includes

 Wait events for identity performance problems

 Time model statstics

 AWR is used to collect performance statastics

 Storing dataabase statastics performance tuning

 Active session history (ASH)

AWR Snapshots performance and dash board database load instanse status and top wait events( snapshot exactly tells the what happening in database in particular time)

AWR snapshot everthing will be recorded in snapshot id like we taking any RMAN backups,blockings,deadlock) for that snapshotid we can take exact time what happening

AWR Report will be genated database should be coninuos running . 

AWR Report comes with enterprsie addition (licencesing that diagnostics and performance)

AWR reports will be stored in sysaux tables when the time of installation.

every hour snapshot will taken

let us go the process

---------------------------------------------

                               whether my database is up and running 

                       

$ ps - ef | grep pmon

let us connect with sql plus

$ sqlplus '/as sysdba'

check my database version

sql>select * from v$ version;

sql >oracle database 12c enterprise edition release 12.1.0.2.0  64 bit production

 parameter statastics_level

sql>show parameter control_management_pack_access;

value -->DIAGNOSTIC+TUNING

$ cd $ /ORACLE_HOME

$ cd /data/oracle/app/oracle/product/12.1.0/db_home/rdbms/admin/

$ ls - ltr

$ ls - lrt | grep awrrpt *

@ awrrpt.sql  lets run the sequel

 

 it will shows the current instance---->DBID 22850381 DBNAME -->prod  instnum-->1

AWR report can be genated in the following formats like  html,txt

enter the value of report type : html

enter the value of num days    :2

                       

specify the begin snapshot  and end snapshot id

enter the value of begin snapshot id :135

enter the value  for end snaphot id :137

enter the value for report _name :dcc.html

exit

$ cd /data/oracle/app/oracle/product/12.1.0/db_home/rdbms/admin/

$ ls -ltr

dcc.html

sql>$ oracle_home/dbms/admin/awrrpt.sql

we cannot see htmll file here in linux platform  so that we can go winscp toool copy that file to desktop you will see the dcc.html and open the report in windows platform

Note :  info on AWR report performance tuning it may be differ from your environment like production,testing,development directory structures

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME