ASSM vs MANUAL - freelists + freelist groups
- From: Sean Hull <shull@xxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 25 Apr 2005 02:09:17 -0400 (EDT)
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/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
