Page: 1 2

Automatic Workload Repository (AWR):

Automatic workload repository (AWR) enables us to retrieve information about workload changes and database usage patterns by analyzing the system performance periodically and storing the information found by default up to 7 days. The information obtained from AWR is very critical for the performance of your database. You can use the information stored in AWR reports to identify the resource intensive SQL statements.

AWR Reports: Operating System Statistics:

Most of the times it happen that it becomes important to review the operating system statistics in AWR Report. Operating system statistics include CPU statistics, virtual memory statistics, disk statistics and network statistics.

CPU Resources consumed by another database/application:

You need to review the operating system statistics of AWR report if there are more than one databases on the server and there exists more then one database or application that compete for CPU and I/O resources.

A number of CPU intensive jobs such as consistent gets, high latch waits and latch spins may be happening on your system. It may be possible that you do not even know that other databases or applications are running on the same server unless you begin reviewing AWR reports. It is important to determine if enough CPU resources are available or not when you review the AWR report for a set of CPU intensive jobs.

CPU Resources consumed by same application:

It sometimes happens that the application tier for the database under consideration is on the same server. In such scenario your CPU resources are consumed by the application as well. It is frustrating when you know that neither network latency nor user think time are causing waits on your system and you are continuously spending time in investigating high SQL*Net message from client waits for batch jobs. In such cases you must ensure if it is the case that the CPU resources are consumed by the application as well.

Statspack:

Prior to Oracle 10g it was not possible to obtain operating system performance statistics from within Oracle. Statspack could show elapsed time and Oracle time but it did not provide any information regarding CPU usage reported against the same database sessions. Continued...

Page: 1 2

 More Database Articles
   Database Security: Step by step guideline
   A Guideline for Oracle Instantiation with RMAN!
   Inside Oracle DETERMINISTIC Clause!!
   SQL FROM Clause - Do not misuse
   Beware of DISTINCT Clause, Important Concerns!!