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