Home

18 Sept 2013

Explain plan and how to take AWR,ASH,ADDM Reports in Oracle

Explain paln for long running query and how to take AWRRPT,ADDMRRPT,ASHRPT.sql to know the performance of the DB
----------------------------------------------------------------------------------------------------------------
Taking AWR,ADDM,ADR Reports in oracle db to know the status or performance of DB.

Awr:  It contain the whole performance of the db(Automatic workload Repository.
Addm: It will verify or gives about AWR Report performance.(Automatic DB Diagnostic monitor)
Adr:  ADR is the diagnostic repository which is meant for storage of the error related information , useful for debugging and troubleshooting.(Automatic Diagnostic Repository)
Login to system:
oracle@nabdcdb/#cd $ORACLE_HOME
oracle@nabdcdb/oracle/orahome#cd rdbms/admin/ls awr*
Go to sql prompt with sysdba and run the script as follows:

Sql>@/oracle/orahome/rdbms/admin/awrrpt.sql
Sql>@/oracle/orahome/rdbms/admin/addmrrpt.sql
Sql>@/oracle/orahome/rdbms/admin/ashrpt.sql

After running this scripts it will ask :
Report Name:rajutodaydate
Start date:12118
End date:13191       Ex: select for one day.O/P will store in the below path

D:\app\oracle\product\11.2.0\dbhome_1\RDBMS\Admin\ ------------In windows
/oracle/orahome/rdbms/admin/  ---------------------------------In linux
---------------------------------------------------------------------------------------
Checking the performance of long running queries using explain
--------------------------------------------------------------------------------------

Sql>explain plan for (copy the long running query and press enter)
Sql>@/oracle/orahome/rdbms/admin/utlxpls.sql;  (It will display the performance of the query)
SQL> @E:\ORACLE\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlxpls.sql(this in windows)

No comments:

Post a Comment