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)
----------------------------------------------------------------------------------------------------------------
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