|
Real Application Clusters
|
In this issue, I briefly review topics which I covered in previous issues.
1. First, I check the operation status of an application in a development environment
(single instance), and then install this application on a production environment
(RAC) to test it. The result proves that performance in the development
environment is faster than that in the production environment.
Scalability is 0.67.
2. What causes difference in performance scalability between development and production environments?
V$SEGMENT_STATISTICS and V$SYSTEM_EVENT indicate that index
in a log table encounters queues. I assume that queue occurs while data are inserted to an index
because V$SEGMENT_STATISTICS indicates ITL waits.
ITL waits do not occur in an environment with single instance but occur
when multiple instances operate at a time in RAC.
I assume that a problem occurs when a process is transferred among instances.
(ITL = Interested Transaction List)
3. What is a possible index tuning to improve performance?
I add an instance number to the header of the column of the index.
With this instance number, it is possible to prevent contention that occurs at insertion process.
Index entries are normally stored in ascending order. Thus, by adding the instance number,
contention of leaf block among instances can be avoided.
There is another index tuning, reverse key index which operates effectively
when columns (such as date column) are not sorted in order.
4. At first, scalability is less than 1. After the index tuning, scalability
increases to 1.4.
Combination of tuning methods is important for index tuning. If you have a wrong combination,
tuning does not succeed.
I take buffer busy waits as an example. This time, index is reported, but table may
be reported in other environments.
When table is reported, waits are not related to the index. Thus, I need to have
different combination.
If the table is in locally-managed tablespace, Automatic Segment Space Management
is useful (which is highly recommended by Oracle.)
If the table is in dictionary-managed tablespace, datablock contention can be avoided
by defining free lists and freelist groups.
When large number of full scans occur, the corresponding table is reported as an object where
buffer busy waits occur. In this case, it is actually necessary to determine if full scan
is really necessary. If not, we need to take a second look at an application rather than RAC.
Scalability increases to 1.4. In actual environment, I continue performing index tuning and
scalability increases to 1.9. Scalability does not improve without application tuning.
Do you know scalability of your currently operating system? I assume scalability
is not a big issue if you manage your daily operation smoothly.
Scalability may be less than 1. It is very ironic if performance improves when a problem
occurs an instance.
Scalability may not be important in actual environment, but everyone wants to use resources
efficiently to improve performance.
That's it for today.
Tadashi Yamashita
|
|
Ora! Ora! Oracle