Note: Burleson Oracle Consulting is the leader in Oracle Emergency Support and is available to assist with any Oracle database problem, anywhere in the world, anytime.

 

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: alter table customer_order storage (freelists 5); alter index cust_pk        storage (freelists 5); (Note:  I did not know the length of the enqueues on the segment header, so I added the additional freelists, one at a time, until the buffer busy waits disappeared). He additional freelists did the trick and the segment header contention disappeared. However, I knew that this was only a stop-gap fix and as soon as they ran their weekly purge (a single process) that only one of the five freelists would get the released blocks, causing the table to extend unnecessarily.   From MOSC note: 1029850.6: As can be seen from the algorithms above, using multiple free lists may cause some empty blocks to go unused, causing the segment to extend.  If performance is critical, multiple free lists can be used to improve concurrent access, possibly at the expense of additional space used.        Guarantee your Success! Oracle is the world's most complex, robust and flexible database, considered impossible to master without a mentor. That's why all BC Oracle trainers are working professionals, experts in Oracle who share their tips and secrets.