In this section we discuss briefly the reasons that would lead us to look for the most resource intensive SQL statements while investigating a database performance problem.
Response Time Analysis shows that heaviest time consumer is CPU-related e.g. CPU Other or CPU Parse time or an I/O-related Wait Event e.g. db file sequential read or db file scattered read:
This is the method followed by tools such as Statspack.Extensive information is gathered from the database which shows in detail in what type of activities time is being spent.It is based on the following equation:
Response Time = Service Time + Wait Time
where Service Time is time spent on the CPUand Wait Time is the sum of time spent on Wait Events i.e. non-idle time spent waiting for an event to complete or for a resource to become available.Service Time is comprised of time spent on the CPU for Parsing, Recursive CPU usage (for PLSQL and recursive SQL) and CPU used for execution of SQL statements (CPU Other).
Service Time = CPU Parse + CPU Recursive + CPU Other
The above components of Service Time can be found from the following statistics:
Service Time from CPU used by this session
CPU Parse from parse time cpu
CPU Recursive from recursive cpu usage
From these, CPU Other can be calculated as follows:
CPU other = CPU used by this session - parse time cpu - recursive cpu usage
When CPU Other is a significant component of total Response Time the next step is to find the SQL statements that access the most blocks. Block accesses are also known as Buffer Gets and Logical I/Os.Statspack lists such SQL statements in section SQL ordered by Gets.When CPU Parse is a significant component of total Response Time the next step is to find the SQL statements that have the most parses.Statspack lists such SQL statements in section SQL ordered by Parse Calls.Note: this is available in Oracle9i and above.Wait Time is the sum of time waited for non-idle Wait Events. These include I/O waits for reading blocks from disk as measured by the Wait Events db file sequential read for single-block reads and db file scattered read for multi-block reads.When such Wait Events are found to be significant components of Response Time the next step is to find the SQL statements that read the most blocks from disk.Statspack lists such SQL statements in section SQL ordered by Reads.Example from Statspack: (pre-Oracle9i Release 2)Here is an example where CPU Other was found to be a significant component of total Response Time:
Top 5 Wait Events
~~~~~~~~~~~~~~~~~
Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
direct path read 4,232 10,827 52.01
db file scattered read 6,105 6,264 30.09
direct path write 1,992 3,268 15.70
control file parallel write 893 198 .95
db file parallel write 40 131 .63
------------------------------------------------------------------------------
Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
CPU used by this session 358,806 130.5 12,372.6
parse time cpu 38 0.0 1.3
recursive cpu usage 186,636 67.9 6,435.7
From these figures we can obtain:
- Wait Time = 10,827 x 100% / 52,01% = 20,817 cs
- Service Time = 358,806 cs
- Response Time = 358,806 + 20,817 = 379,623 cs
- CPU Other = 358,806 - 38 - 186,636 = 172,132 cs
If we now calculate percentages for the top Response Time components:
- CPU Other = 45.34%
- CPU Recursive = 49.16%
- direct path read = 2.85%
- etc. etc.
CPU Other is a significant component of Response Time, so a possible next step is to look at the SQL ordered by Gets section.
Example from Statspack: (Oracle9i Release 2 & above)
Starting with Oracle9i Release 2, Statspack presents Service Time (obtained from the statistic CPU used by this session ) together with the top Wait Events in a section called Top 5 Timed Events, which replaces the section Top 5 Wait Events of previous releases.
Here is an example:
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~
% TotalEvent Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- ----------
db file scattered read 3,367 96 17.40
CPU time 32 5.79
db file sequential read 161 1 .18
control file parallel write 245 0 .05
-------------------------------------------------------------------------------
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 3,211 4.3 1,605.5
parse time cpu 59 0.1 29.5
recursive cpu usage 232 0.3 116.0
These figures give us directly the percentages of the Wait Events against the total Response Time so no further calculations are necessary to assess the impact of Wait Events. Service Time is presented as CPU time in this section and corresponds to the total CPU utilisation. We can drill down to the various components of Service Time as follows:
- CPU Other = 3,211 - 59 - 232 = 2,920 cs
- CPU Other = 2,920 / 3,211 x 5.79% = 5.26%
- CPU Parse = 59 / 3,211 x 5.79% = 0.11%
- CPU Recursive = 232 / 3,211 x 5.79% = 0.42%
In this example, the main performance problem was an issue related to the Library Cache.The second most important time consumer was waiting for physical I/O due to multiblock reads (db file scattered read).In this case a possible approach would be to look at the SQL ordered by Reads section of Statspack.
Operating System resource analysis shows that excessive CPU or Disk I/O usage is caused by one or more Oracle processes:
If Operating System utilities show that most CPU usage is due to a small number of Oracle processes then typically they will be SQL Traced and have TKPROF reports generated from their tracefiles. Analysis of the TKPROF reports will lead to the most time consuming SQL statements which will be the subject of subsequent tuning.
If CPU usage is excessive but spread out fairly evenly among all the Oracle processes on the system then typically a tool such as Statspack will be used to perform Response Time analysis. The components of Service Time will be evaluated and if this shows CPU Other as being significant the next step will be to look at the SQL performing most block accesses in the SQL by Gets section of the Statspack report.
If Disk I/O is highly utilized on the system then a Response Time analysis using Statspack can be done to confirm that the Disk I/O usage is due to Oracle processes and I/O-related Wait Events can be expected to be significant components of overall Response Time. SQL statements performing the most physical reads can then be found in the section SQL ordered by Reads .
Investigation of the performance of a batch job or other session which performs a number of SQL statements and has slow performance:
This will be done with SQL Trace and TKPROF as descrived above and the most important SQL statements in terms of time spent will be identified for further tuning.Examination of statistics stored for SQL statements in V$ dynamic views:
Part of the information stored with a SQL statement (Shared Cursor) in the Library Cache portion of the Shared Pool are a number of statistics related to its execution. These are available through the V$SQLAREA dynamic view and can be queried to monitor the most expensive SQL statements in each category.
This is the approach used by the graphical tool SQL Analyze in the Oracle Enterprise Manager Tuning Pack.
