Hiya Friends, Topic: AWR (Automatic Workload Repository) understanding…
$ORACLE_HOME/rdbms/admin/awrrpt.sql ( for single instance)
When we need AWR report:
We need the AWR when the performance is slow or degraded over time due to increased user activity.
When the performance of the database over time has been slow-down or degraded due to the changing or increased content in the database.
AWR helps in the performance of DB has become slower if the either patchset applied or app server version has changed.
Also, it helps to when the performance of DB has become slower after either database patchset applied or the database version has changes.
We need to check in AWR:
4. Buffer Cache Section (data block should access from local cache, % should be high. ie. cache fusion)
5. Global Cache and Enqueue Services (global cache operation should be less (milisec) -- if more than 5ms then we need to check cluster interconnections.
6. GCS and GES messages across the private network. (It should be less.)
Regards,
Damn sure you all are doing well in your life… If you have any
problem means you are doing something big in your life.
Today, we would like to share regarding AWR (Automatic Workload
Repository) which is a very important in oracle DBA’s life. And also, it is
designed especially for oracle internal purpose.
Actually, we are collecting main points and trying to make it easy
to understand.
Hope it may useful also we will put some more points here as days
goes…
AWR:
In 11.2 spawrrac.sql is replaced by the AWR Global RAC report
(awrgrpt.sql) report, a Server Performance AWR RAC report.
$ORACLE_HOME/rdbms/admin/awrgrpt.sql (for RAC)
$ORACLE_HOME/rdbms/admin/awrrpt.sql ( for single instance)
We need the AWR when the performance is slow or degraded over time due to increased user activity.
When the performance of the database over time has been slow-down or degraded due to the changing or increased content in the database.
AWR helps in the performance of DB has become slower if the either patchset applied or app server version has changed.
Also, it helps to when the performance of DB has become slower after either database patchset applied or the database version has changes.
We need to check in AWR:
At top 3rd line, we will see
Begin Snap, End snap, Elapsed and DB time
So, here if the “DB Time” value is higher than “Elapsed” value ,
it means session is waiting for something…
DB Time > Elapsed = Session is waiting for something.
Sessions: Here value is 00:47 start and End at 00:31, it means before
running AWR, Database was busy. So we need to calculate after the 10 min of
start time and before 10 min end time.
2. You will see Load Profile:
It shows the brief description about the database uses and
workload.
For example if the values of “Redo Size” per second is 194 K and
per Transaction is 45 K, it means the average transaction generates about 45 K
of redo data and the Database produces 194K redo per second.
3. Cache load profile (Interconnect traffic in kb = 5 mb)
4. Buffer Cache Section (data block should access from local cache, % should be high. ie. cache fusion)
5. Global Cache and Enqueue Services (global cache operation should be less (milisec) -- if more than 5ms then we need to check cluster interconnections.
6. GCS and GES messages across the private network. (It should be less.)
To be continued…
Some more useful links:
Regards,