A tune on the RAC.

The database is slower sometimes and can not handle the application timely.

Some business application data was queued.

Did not find any exception from alert log or even trace files.

Generated the AWR report and find the top events are as below.

Top User Events

Event	Event Class	% Activity	Avg Active Sessions
enq: TX - index contention	Concurrency	43.67	5.10
gc buffer busy	Cluster	21.97	2.56
enq: TX - contention	Other	17.16	2.00
CPU + Wait for CPU	CPU	4.43	0.52
log file sync	Commit	3.14	0.37

Top SQL Statements

SQL ID	Planhash	% Activity	Event	% Event	SQL Text
dcbst5pv6w8b9		87.51	enq: TX - index contention	43.07	INSERT INTO METRIC_DATA (DATET...
		87.51	gc buffer busy	20.18	INSERT INTO METRIC_DATA (DATET...
		87.51	enq: TX - contention	17.16	INSERT INTO METRIC_DATA (DATET...
	174834596	2.38	gc buffer busy	1.78	INSERT INTO METRIC_DATA (DATET...

The report shows that there are too many insert operation causing the index contention.

In this particular situation, I have heard that reverse key indexes distribute values across an index, and are particularly useful in RAC because if the index is on a sequence and lots of inserts are happening, the RAC instances will be accessing the index leaf block at the right most side then when split needed, the situation will be worse and therefore by using a reverse key index, we can stop this contention.

The reverse key index will reduce the contention at the index block level, which can be the more serious contention issue as more rows were involved with a single index block.

And also If we have multiple inserts happening in the table where the PK is a sequence that is incremented by one (and nothing else is happening on that block), then the number of inserts that can happen at any one time is determined by MAXTRANS (and let us assume the block has enough space for the ITL to grow). This is true for both the index data block and table data block, and the transactions doing the insert will be getting a row exclusive table lock.

These locks caused the database performance worse.

On the other side the pctfree default value is 10 for the index and table.

If we can increase it the contention will be reduced when the too many DML happens
But anyway to rebuild the index or increase the pctfree parameter need much more effort than you expect.

Also to avoid this we can point the application to a certain instance.Through this way we an avoid the cache fusion tranfer through the interconnect.

In order to find more I decide to generate the ADDM report to do further investigation on this.

It give me more hints on the issue.

Extract the following from the reports.

FINDING 1: 85% impact (119271 seconds)

--------------------------------------

Database writers (DBWR) were unable to keep up with the demand for free

buffers.

RECOMMENDATION 1: DB Configuration, 85% benefit (119271 seconds)

ACTION: Consider increasing the number of database writers (DBWR) by

setting the parameter "db_writer_processes". Also consider if

asynchronous I/O is appropriate for your architecture.

RATIONALE: The value of parameter "db_writer_processes" was "4" during

the analysis period.

RATIONALE: The value of parameter "disk_asynch_io" was "TRUE" during the

analysis period.

RECOMMENDATION 2: Host Configuration, 85% benefit (119271 seconds)

ACTION: Investigate the I/O subsystem's write performance.

RATIONALE: During the analysis period, the average data files' I/O

throughput was 428 K per second for reads and 149 K per second for

writes. The average response time for single block reads was 1.9

milliseconds.

RECOMMENDATION 3: Application Analysis, 85% benefit (119271 seconds)

ACTION: Investigate application logic for possible use of direct path

inserts as an alternative for multiple INSERT operations.

SYMPTOMS THAT LED TO THE FINDING:

SYMPTOM: Wait class "Configuration" was consuming significant database

time. (85% impact [119272 seconds])

the database buffer in the database is 10G .

Obviously the 4 db writers can not keep up with the database change so that it is hard to find the free database buffer.

Hence the database performance is worse when waiting for the database buffer free.

Per the report. 85% benefit (119271 seconds) would gain if modification the db writer parameter or consider if asynchronous I/O

Also the index contention issue can be relieved through the adding more db writers.

I found the performance back and enq: TX - index contention waits events disappear from the top list after db writers parameter modification.

Of course,The pctfree modification or index rebuild also gain for the index contention.

But the cause and effect is not one to one relationship always.

We can solve an issue from other perspective if focusing on the issue itself can not help.

Ref:

Consider Multiple Database Writer (DBWR)Processes or I/O Slaves
Configuring multiple database writer processes, or using I/O slaves, is useful when the transaction rates are high or when the buffer cache size is so large that a single DBWn process cannot keep up with the load.

DB_WRITER_PROCESSES
The DB_WRITER_PROCESSES initialization parameter lets you configure multiple database writer processes (from DBW0 to DBW9 and from DBWa to DBWj). Configuring multiple DBWR processes distributes the work required to identify buffers to be written, and it also distributes the I/O load over these processes. Multiple db writer processes are highly recommended for systems with multiple CPUs (at least one db writer for every 8 CPUs) or multiple processor groups (at least as many db writers as processor groups).

DBWR_IO_SLAVES
If it is not practical to use multiple DBWR processes, then Oracle provides a facility whereby the I/O load can be distributed over multiple slave processes. The DBWR process is the only process that scans the buffer cache LRU list for blocks to be written out. However, the I/O for those blocks is performed by the I/O slaves. The number of I/O slaves is determined by the parameter DBWR_IO_SLAVES.

DBWR_IO_SLAVES is intended for scenarios where you cannot use multiple DB_WRITER_PROCESSES (for example, where you have a single CPU). I/O slaves are also useful when asynchronous I/O is not available, because the multiple I/O slaves simulate nonblocking, asynchronous requests by freeing DBWR to continue identifying blocks in the cache to be written. Asynchronous I/O at the operating system level, if you have it, is generally preferred.