AWR report from sqlplus:
oracle@jupiter:~$ cd $ORACLE_HOME
oracle@jupiter:/oracle/app$ cd rdbms/admin
oracle@jupiter:/oracle/app/rdbms/admin$ sqlplus s as sysdba
sql>@awrrpt.sql
Enter value for report_type: html
Enter value for num_days: 1
Enter value for begin_snap: 9014
Enter value for end_snap: 9017
Enter value for report_name: awr_nahar.html
SQL> host
bash-3.00$ ls
bash-3.00$ scp awr_nahar.html nahar@nahar:
-----------------------------------------------------------------------------------------
Example:
I was trying to spool but I had not spooling permission there. So -
Then I run the awrrpt.sql file from my home.
nahar@db01:~$ sqlplus s as sysdba
SQL> @/d00/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/awrrpt.sql
scp mf01.therap.net:/u/nahar/nahar-20111127.html /home/nahar/AWR/
-----------------------------------------------------------------------------------------
AWR (Automatic Workload Repository):
What is AWR?
AWR reports collects database performance statistics and metrics for analysis and tuning, shows the exact time spent in the database, and even saves session information.
What is default interval period between two awr report ?
By default, AWR automatically generates snapshots of the performance data once every hour and retains the statistics in the workload repository for 7 days.
GUI ----> OEM (Oracle Enterprise Manager)
Login to OEM ---> Administration Tag ----> Workload Section --->AUTOMATIC WORKLOAD REPOSITORY
The data in the snapshot interval is then analyzed by the Automatic Database Diagnostic Monitor (ADDM).
How to Manage & change interval period for awr report ?
There is two to modify or changes in AWR report.
1. Manually ---> through DBMS_WORKLOAD_REPOSITORY plsql package
2. GUI ----> OEM (Oracle Enterprise Manager)
Login to OEM ---> Administration Tag ----> Workload Section --->AUTOMATIC WORKLOAD REPOSIT
How to Interpreting with AWR report ?
1. Load Profile
Here we know about Logical Read, Physical Read, Soft Parse, Hard Parse
2. Instance Efficiency Percentages
If your instance percentage show BUFFER CAHCE HIT RATIO is 100% it is not sign for your database is very fast and running smootly.
3. Top 5 Timed Events
It is very important section in AWR report.
through this we can know most five wait event is effecting database performance.
4. SQL Statistics
It this section we will know about Execution time, cpu used, logical & physical read for top most sql statement.
5.Advisory Statistics
In this section we will get advice for PGA, BUFFER CACHE, SHARED POOL, JAVA POOL size for better performance.
