ASSM vs MANUAL - freelists + freelist groups

Hi all,

I've been working on trying to improve performance of our busiest tables by tuning freelists. The results are not earth-shattering, however, and wondered if I could get some feedback from the list.

I ran two tests. The tests involved creating 25 parallel sessions using dbms_jobs. Each of those sessions did 10 seconds of inserts and then one commit at the end. The only difference between the two were that the tables were created differently.

I created two tablespaces with different options:

    SEGMENT SPACE MANAGEMENT MANUAL;
and for the other:

    SEGMENT SPACE MANAGEMENT AUTO; Then I created a table in each of these two tablespaces. The table in the manual tablespace had a storage clause like this:

storage (freelists 5 freelist groups 3)

Then I submitted the pl/sql that does 10 seconds of INSERTs 25 times using dbms_jobs. I managed to insert 1672489 rows into the manual table and 1596116 rows into the auto table. That's an improvement of 76373 rows. I'm guessing that gap would probably widen with more concurrent sessions.

Does this seem like the right order of magnitude improvement I should expect on INSERTs? Should I ever get say 2x or 3x as many inserts on manual segment space management table versus an ASSM?

These results I found seem rather small. Should I be setting storage parameters like this:
(freelists x freelist groups) = (number of expected sessions)

Thanks for any comments,
Sean

--

Sean Hull, Senior Consultant
Heavyweight Internet Group
Rockefeller Center, Box 5352
New York, NY 10185
http://www.iheavy.com
voice: 646.827.9877x23 fax: 646.827.3434

Sean Hull, founder and senior consultant of Heavyweight Internet Group is the author of O'Reilly and Associates "Oracle and Open Source" bridging Open Source software and integration with the world's best performing database, Oracle. http://www.oreilly.com/catalog/oracleopen/
--

http://www.freelists.org/webpage/oracle-l Received on Mon Apr 25 2005 - 02:13:32 CDT