|
Real Application Clusters
|
Hello everyone.
Last time, I talked about what Oracle statistics indicated when RAC
provided poor scalability.
Here is a brief summary:
----------------------
1. enqueue occurs frequently.
2. buffer busy waits occur frequently.
3. buffer busy global cache occurs frequently.
4. TX enqueue among enqueues occurs frequently.
5. Most of buffer busy waits is log-related index.
6. ITL waits occur in log-related index.
7. It takes time to process operations related to global cache convert.
8. It takes time to process operations related to global lock get.
----------------------
What I'm going to do first based on this summary is to reduce contentions among instances
regarding log-related index with heavy buffer busy waits.
Following is table and index configurations.
**********************************************************
SQL> desc logtbl1
Name NULL? Type
-------------------- -------- ----------------------------
INS_DATE NOT NULL TIMESTAMP(6)
CONNECT_ID NOT NULL CHAR(64)
ACTION_CD NUMBER(1)
USER_ID CHAR(50)
NOTE VARCHAR2(200)
SQL> select INDEX_NAME,TABLE_NAME,COLUMN_NAME,COLUMN_POSITION
2 from user_ind_columns where table_name = 'LOGTBL1';
INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION
------------ ------------ ------------ ---------------
IDX_LOGTBL1 LOGTBL1 INS_DATE 1
**********************************************************
|
INS_DATE columns is indexed.
Now, I try to reduce the contention among instances.
Before I start, I briefly review a fundamental behavior of index. If sequential
value is inserted to ascending index, it will be inserted to leaf blocks one after another.
Large number of block contentions must occur in an environment having heavy transactions.
ITL waits indicate the status of block contention.
In this case, however, block contention is not actually a big problem.
(See Vol. 138 for details.)
What are we supposed to do to prevent index from being transferred among instances?
I will talk about this next week.
That's it for today.
Tadashi Yamashita
|
|
Ora! Ora! Oracle