Oracle10g AWR Report Analysis

 

Oracle 10 Gb provides a new performance collection and analysis tool awr (automaticworkload repository ).

Awr exists in the sysaux tablespace and is one of the main users of sysaux.

Snapshot, a set of performance statistics captured at a specific time, used to calculate the change rate of statistics. Each snapshot is identified by snap_id.

The default snapshot is generated every 60 minutes. Retained for 7 days.

 

Awr snapshot set, a mechanism used to mark and retain snapshot set data in important periods. A snapshot set defines a snapshot (two snapshots ). A snapshot set is used to retain snapshot data. Before deleting a snapshot set, snapshots that belong to the snapshot set will always exist.

Generally, you can set a representative time period as a snapshot set for comparison with the current system behavior.

 

Awr compare periods is used to compare two periods in awr. Awr displays awr data between two snapshots (two time points), while awr compare periods shows the differences between two time periods, namely, two awr reports (equivalent to four snapshots. You can accurately diagnose the causes of performance degradation based on the changes reported between two time periods.

 

Generate an awr report and run $ ORACLE_HOME/rdbms/admin/awrrpt. SQL.

For Awr snapshot settings, use dbms_workload_repository.modify_snapshot_settings ().

Generate an awr snapshot set. You can use dbms_workload_repository.create_baseline ().

Generate awr compare periods and run $ ORACLE_HOME/rdbms/admin/awrddrpt. SQL.

 

 

Awr report analysis-CPU busy degree and CPU usage

 

Sessons: Number of sessions connected to the instance during collection.

Cursors/Session: the average number of opened Cursors for each Session.

Elapsed: Sampling Time.

DB Time: represents the instance workload, which is very important in the Time model statistics.

DB Time indicates the Time spent on user operations, including cpu Time (non-background process Time (such as PMON) and waiting Time (non-idle wait Time ).

If the DB Time is close to the Elapsed Time * Number of CPUs, it indicates that the database is busy and the cpu load may be large. At this time, it is likely that resource competition leads to waiting for the event results. You can go to top 5 to wait for the event analysis reason.

You can obtain the DB Time Value and db cpu value in the Time Model Statistics Section. Db cpu refers to the total CPU time of user operations, and does not include the CPU time (such as PMON) consumed by background processes ). So should DB Time be the waiting Time to remove db cpu? Yes, so the formula DB Time = db cpu + wait Time.

V $ SESS_TIME_MODEL

Http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2087.htm#REFRN30340

 

Take a look at the system Statistics Section:

 

From this information, we can see the CPU and memory information of our system.

The sum of SYS_TIME and USER_TIME is 1776 + 4834 = 6610, which is exactly the same as BUSY_TIME. In addition, (6610 + 719387)/100/60/2 = 60.49975. Isn't this our sampling time? 2 represents two CPUs (NUM_CPUS ). The db cpu mentioned above does not include the Time spent by background processes (such as PMON), but the backgroundcpu time is displayed in Time Model Statistics ). So the overall cpu usage of the database can be calculated in this way. cpu usage = (db cpu + background cpu time)/(Elapsed * NUM_CPUS) * 100% = (19.45 + 7.08) /(60.45 mins * 2) * 100% = 0.00366%. It seems that our system is very idle. I should take a report for analysis when the system is a little busy.

V $ OSSTAT

Http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2010.htm#REFRN30321

 

From Laughing