Re: Increase FREEIST w/o drop objects--sorry for the previous garbage code
- From: Tanel Põder <tanel.poder.003@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 3 Oct 2004 00:37:12 +0300
> 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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
