Tuning with Response Time
One of the critical activities in Database Performance Tuning is Response Time Analysis which consists of finding out where time is being spent in the database. TIME is the most important property (event) in Performance Tuning.
Users perceive time as the overall indicator of performance and how responsive their application is will be what they base their opinion on.
Response Time Analysis for an Oracle Database consist of performing the following calculation:
Response Time = Service Time + Wait Time
Service Time is measured using the statistic ‘CPU used by this session’
Wait Time is measured by summing up time spent on Wait Events
Performance Tuning methods based on tools such as Statspack and the DBA module for TOad work by evaluating the relative impact of the various components of overall Response Time and use that to drive the tuning effort towards the components.
Oracle 10g introduced the Automatic Database Diagnostic Monitor (ADDM). In order to use ADDM you must have the license for the Oracle Diagnostic Pack. The ADDM consists of functionality built into the Oracle kernel which it uses to assist in tuning the Oracle instance.
ADDM uses the Automatic Workload Repository (AWR) as its repository in which it stores database wide usage statistics that are populated at fixed intervals of 60 minutes. To make use of ADDM, a PL/SQL interface called DBMS_ADVISOR is used and is either called directly or via the OEM application. $ORACLE_HOME/rdbms/admin/addmrpt.sql is the PL/SQL DBMS_ADVISOR code. Besides, this package there are a number of views (with names beginning with the DBA_ADVISOR_ prefix) that perform retrieval of the results of any actions performed by the DBMS_ADVISOR API. The preferred method of accessing ADDM is through the use of OEM, as it shows a complete performance overview including recommendations on how to resolve bottlenecks in a web based interface. When accessing ADDM manually, you should consider using the ADDMRPT.SQL script provided in your ORACLE_HOME release directory, as it hides the complexities involved in accessing the DBMS_ADVISOR package.
ADDM and Statspack produce listings of the most significant Wait Events in a section called “Top 5 Wait Events”. When initially reviewing this list it becomes easy to simply start with these first and forget about evaluating their overall impact on Response Time first.
In situations where ‘Service Time’, i.e. CPU usage is significantly greater than ‘Wait Time’, it is very likely that investigating Wait Events will not produce significant savings in ‘Response Time’. Therefore, you should always compare the time taken by the top wait events to the ‘CPU used by this session’ and direct the tuning effort to the biggest consumers.
0 responses so far ↓
There are no comments yet...Kick things off by filling out the form below.