Page History
...
You can identify a performance problem by doing one or more of the following:
- Examining top 10 wait events
- Examining latches
- About examining logical I/Os
- Examining physical I/Os
- Examining memory
- Examining parse times
| Anchor | ||||
|---|---|---|---|---|
|
We should begin our analysis with the Instance overview. This view displays the Top 10 Wait Events in a pie chart.
...
- In the Time Frame list, choose the period of time you want to analyze.
- In the Instance list, choose the item you want to analyze.
- In the Association area, click Wait Events to observe the wait events that are associated with the selected instance and their wait duration.
| Anchor | ||||
|---|---|---|---|---|
|
After we have examined the top 10 wait events, we now need to determine which latch or latches are contributing to the latch free event.
To examine latches, in the Association area, click Latches from the Association controls. The Statistics tab displays the latches that are associated with this instance.
| Anchor | ||||
|---|---|---|---|---|
|
One of the recommended ways to tune statements is to reduce the number of logical I/Os, because logical I/Os tend to dominate response time. It is important to understand how the number of logical I/Os varies over time. The instance statistics in the Statistics tab re-enforce the resource consumption figures observed in the Activity tab.
Examining consistent gets
We can tune statements by examining the current number of consistent gets for a period of time.
...
- In the Time Frame list, choose the period of time you want to analyze.
- Open the statement you want to analyze in the Statistics tab. For our example, the Statistics tab shows the logical I/O for All Oracle Instances. We can see that the number of logical I/Os remained fairly constant on the evening of September 22nd. The logical I/Os are nearly all made up of Consistent Gets (that is, fetching read-consistent copies of data).
In the Association area, you see that the logical I/O count is dominated by ora920 on linuxi4o2. This is the instance we should look at when considering tuning of logical I/Os. Observe the number of logical/Os per execution. Notice the upward trend. This, together with the total number of logical I/Os, indicates that fewer statements are being run, but each statement is processing more data. This may indicate that the application is I/O bound because it cannot handle more than a certain number of I/Os.
Examining DB block gets
We can also tune statements by examining the number of DB block gets.
...
- In the Time Frame list, choose the period of time you want to analyze.
- Open the statement you want to analyze in the Statistics tab. In our example, we observe two major peaks in I/O activity, one on the 13th of September and one on the 17th. For our example, the Logical I/O Operations graph shows an increase on the 17th and that the logical I/O was split fairly evenly. The logical I/O was split between DB Block Gets, which are fetching the current version of the data, and DB Block Changes, which are updating the current version of the data. This indicates that there was heavy DML activity on these two days.
The Logical I/O Operations per Execution graph shows that the number of logical I/O operations per execution remained roughly constant on these two days. Together, both graphs will imply that approximately twice as many statements were executed on the 17th.
| Anchor | ||||
|---|---|---|---|---|
|
It is usually good practice to examine logical I/Os first because it is a more reliable measure than physical I/Os, due to caching effects that can complicate an analysis of physical I/Os.
...
- First examine logical I/Os. See “Examining consistent gets” on page 176.
- Now examine physical I/Os. For our example, the Statistics tab shows sampled data from the same time period as that shown for consistent gets over time. We can see that the average read and write time follows the number of physical I/O operations reasonably close, particularly the first peak on September 22nd. This indicates that there is some contention between I/O operations, perhaps because they are all reading from different points on the same disk, causing a rise in seek time.
| Anchor | ||||
|---|---|---|---|---|
|
If you are looking to reduce the amount of memory used by Oracle, or looking to increase one of the memory buffers without increasing the overall memory footprint, you can use the memory view.
...
For this example, the Aggregated PGA Target has been set to 24 MB and a maximum of 19 MB has been used. This leaves us with very little scope by which to reduce total PGA memory.
| Anchor | ||||
|---|---|---|---|---|
|
Oracle 10g provides even more statistics than were provided by previous versions. Precise for Oracle displays graphs of some of the more important statistics, such as CPU utilization and parse times.
...