We have a table in PROD
CREATE TABLE address
address_id NUMBER(12) NOT NULL,
with Primary Key on address_id.
There are no any other Keys (unique or foreign) on this table.
No any triggers also.
table has about 2 500 000 records.
we use sequence to generate address_id for this table. Now the next sequense value is about 4 500 000.
there are a lot of DELETE and INSERT statements running (online txns) on this table - about 10 000 of each per day.
We got a DELETE performance problem for this table.
WHERE address_id = 12345;
it takes about 5.5 sec to complete it (before we rebuilt index it was 10-12 sec per DELETE statement)
it's too much for us.
we can afford only 0.5 sec per statement.
What I cannot understand is that the same INSERT/UPDATE/SELECT statements complete during 0.3 - 0.5 sec.
The next we going to do is to partition this table (range partition on address_id).
But first I'd like to find out why it is only DELETE course us such problems and INSERT/UPDATE/SELECY work just fine?
Does anybody have any ideas/suggestions?
Write a procedure which collects the
session statistics (redo generated ,roll back ghenerated) before
the start of delete and then run delete and then collect the statistics
after that .
Also run a trace to find if there are any waits.