Re: Increase FREEIST w/o drop objects--sorry for the previous garbage code

> By the way, one of our senior dba said, the freelists needs to be
> prime " Freelists needs to be a prime number. If you cannot use 23,
> use 17", I don't know why. Anyone have experience?

It's because Oracle assigns sessions to specific freelists based on mod function on number of freelists and Oracle (not OS) PID for the session (there are actually several functions which are used under different circumstances).
Normally this isn't a problem, but when there is certain pattern in PID numbers for inserting sessions, inserts might not be distributed to different freelist evenly enough (e.g. I got 2 freelists and every *second* session in database is doing the inserts - so sessions served by even-numbered processes would always use first freelist and the second one would be completely unused).

But if we have a prime number of freelists, then the mod function is more likely to distribute inserts evenly across all freelists even if there is a pattern in inserting session PIDs (e.g. with 3 process freelists, PIDs

10,12,14,16,18,20 would map to list 2,1,3,2,1,3 respectively. With only two
lists, all of those PIDs would map to list 1 (mod(10,2) returns 0 and +1 is
added, because freelist 0 is segment master freelist).

If you're wondering in which situation could you hit this kind of problem - one potential case would be if your application always makes two sessions per user for whatever reason and first of them is always doing the inserts, then we'd have a strong pattern here... There are more complex cases though..

Why didn't your senior DBA answer this question? Is he (or she) making these "must be" recommendations without knowing what he actually recommends?

Tanel.

--
http://www.freelists.org/webpage/oracle-l