Add Freelists
A
client called from Michigan once with a complaint that the
company order processing center was unable keep up with adding
new orders into Oracle. The client had just expanded its
telephone order processing department and had doubled the
order processing staff to meet a surge in market interest. The
VP was frantic, saying that 400 order-entry clerks were
getting 30-second response time and they were forced to
manually write-down order information.
I
checked v$session and found 450 connected users, and a
quick review of v$sql revealed that at virtually all
the DML were inserts into a customer_order table. The
top timed event was buffer busy wait and it was clear
that there were enqueues on the segment header blocks for the
table and its indexes.
The “proper” fix for this issue is to create a new tablespace
for the table and index using Automatic Segment Space
Management (ASSM), also known as bitmap freelists. I could
then reorganize the table online with the dbms_redefinition
utility and alter index cust_pk rebuild the index into
the new tablespace. However, it would take me several hours to
build and execute the jobs and the VP said that he was losing
over $500 per minute.
The system was on release 9.2.0.4, so I was able to
immediately relieve the segment header contention with these
commands:
