| A change of heart | - Start your web searches at Ixora |
| Pressed for time | - Understanding the components of response time |
We have made it bigger by adding a large number of documents from about 30 other web sites to our index. However, unlike other search engines, we do not index everything. We only index documents that are both relevant to Oracle performance tuning, and that supplement the Oracle documentation in either content or clarity. That means that you can get good information quickly, without wasting time sifting through a lot of low-value documents.
We've also enhanced the search syntax to support some common Internet search conventions. You can now search for phrases (using quotation marks), or make particular words or phrases mandatory (using a + sign) or exclude documents containing particular words or phrases (using a - sign).
Although our index has a strong performance focus, the new Ixora search facility is also a useful starting point for other Oracle related searches. In addition to the hits against documents in our index, there are a series of links at the foot of each results page that enable you to try the same query at Google, Ask Tom, TechNet and MetaLink. These queries use the full functionality of the respective search engines, and often get better results than if you were to try those searches directly. You'll be amazed what you can find!
So, next time you are looking for advanced Oracle performance tuning information, why not try starting your search at Ixora!
Unfortunately, if you are already pressed for time, proactive tuning may be a luxury that you can scarce afford. If so, you should spend your precious tuning time where the database spends its precious time. If your database spends most of its time doing disk I/O, then you should attempt to tune disk I/O. If CPU usage accounts for most of your database response time, then you should attempt to reduce CPU usage. And if there is a significant resource contention problem, then you should attempt to resolved that. There is little point in doing a major disk reorganization to obtain say a 10% improvement in disk I/O, if that disk I/O only accounts for 10% of the total response time - that would be a lot of effort for an overall gain of only 1%.
So before you start tuning, you should seek to understand your current performance. In particular, you need to know how much of your response time is due to CPU usage, disk I/O, and resource contention (if any). Fortunately, Oracle can maintain timed statistics to give you all the information that you need - in great detail. V$SYSTEM_EVENT and V$SESSION_EVENT record timing information for almost everything that Oracle does, except CPU usage which is accumulated in V$SYSSTAT and V$SESSTAT.
However, to turn these statistics into an accurate response time breakdown, you need to exclude the events that are used to measure the time that sessions have spent waiting for work to do, and all the time accumulated by Oracle's background processes. A query to do that can be downloaded from the Ixora web site. Here is an example of the output.
SQL> @response_time_breakdown
MAJOR MINOR WAIT_EVENT SECONDS PCT
-------- ------------- ---------------------------------------- -------- ------
CPU time parsing n/a 497 .57%
reloads n/a 13 .01%
execution n/a 52618 59.99%
disk I/O normal I/O db file sequential read 21980 25.06%
full scans db file scattered read 9192 10.48%
direct I/O direct path read 1484 1.69%
direct path write 354 .40%
other I/O log file sequential read 9 .01%
db file parallel read 0 .00%
control file sequential read 0 .00%
control file parallel write 0 .00%
waits DBWn writes rdbms ipc reply 143 .16%
free buffer waits 36 .04%
checkpoint completed 31 .04%
LGWR writes log file switch completion 698 .80%
other locks latch free 496 .57%
sort segment request 108 .12%
latency commits log file sync 6 .01%
network SQL*Net break/reset to client 18 .02%
SQL*Net more data to client 8 .01%
SQL*Net message to client 7 .01%
SQL*Net more data from client 3 .00%
file ops file open 4 .01%
file identify 1 .00%
misc instance state change 0 .00%
With this type of information it is easy to ensure that you devote your precious tuning time
to the aspects of database performance that have the biggest potential for improvement.
And when planning a tuning change, you will be able to accurately predict the impact on overall performance.
| Copyright © Ixora Pty Ltd |
|