Faster Inserts

Lets consider an application which has large number of inserts into a table having primary key.
Generally primary key values are generated by sequences.
When we have thousands of concurrent inserts (or parallel inserts) using sequences,  all the values goto the right hand side of the Unique Index
causing massive contention for right hand side block, hence slowing down the whole process.

Previously the solution for this was:
1. Reverse Key Indexes : Good for small primary key values but it is noticed that performance decreases for large numbers.
2. HASH Partitioning : Performance is not upto expectation in RAC clustered environment.

Best solution for the above problem was suggested by Tom Kyte (https://www.facebook.com/pages/Tom-Kyte-Ask-Tom-fan-page/189391211108914)
in Real World Performance India (Sangam12)
Which is to code the sequence to ensure there is no index contention.
We shall create a new sequence structure as Instance# || SessionsId || Sequence
The above will be inserted into Primary Key column instead of just sequence value.


Below is a demo:

create table TEST_INSERTS( COL1 number primary key, DESCR VARchar2(200) );
CREATE SEQUENCE SEQINS;

--First lets do the old way using only sequences

begin
for i in 1..10000000
loop
INSERT INTO TEST_INSERTS VALUES (SEQINS.nextval,'Using SEQINS only');
end loop;
commit;
end;
/

-- New Method for optimal scaling and response time characteristics
begin
for i in 1..10000000
loop
/*
iF YOUR DATABASE is RAC clustered then use as below
INSERT INTO TEST_INSERTS VALUES (sys_context('USER_ENV','INSTANCE')|| sys_context('USER_ENV','SID')|| SEQINS.nextval,'Using SEQINS only');
*/
INSERT INTO TEST_INSERTS VALUES (sys_context('USER_ENV','SID')|| SEQINS.nextval,'Using Composite SEQINS');
end loop;
commit;
end;
/

 More Information below

http://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:6077843300346542181