Re: Sequence cache parameter

This message was emailed to Doug Smith at <doug.smith_at_fmr.com>

First, how dare you question a DBA! Remember that the DBA is always right especially when he is wrong.

Basically Oracle hands out the default number of numbers at a time. If your default is 20 Oracle gives you the next 20 numbers. If you don't use them, they are gone and lost forever. From a performance point of view it is more efficient to go to the database fewer times in order to get sequences since it reduces overall database activity.

 If you are trying to control trivial stuff like check numbers you don't want lost numbers (actually you may not care -- but the auditors will!) so you might even set the cache default to 1. You will still lose a number in the event of a rollback, but if having your numbers be monotonic (wow, first chance I've had of using that word in months) is critical you will figure out how to code so that your check writing program, or whatever, won't rollback.

regards
Jerry Gitomer

Doug Smith wrote in message ...
>Whoops - we're actually using 2, which I thought was the
default. The
>developer who came up with 2 no longer works here, so I'm not
sure how that
>number was orignally derived.
>
>Bottom line is I'd like to learn enough to have some context to
weigh
>recommendations for changing the cache parameter.
>
>--Doug
>
>Philippe <parnaud_at_yahoo.com> wrote in message
>news:7nn9v5$9ph$1_at_concorde.ctp.com...
>> tell your DBA that 20 is the default value for cache
parameter.
>>
>>
>> Doug Smith wrote in message ...
>> >Greetings,
>> >
>> >I work with an application that uses sequences numbers to
generate
>primary
>> >key values for the tables in the database.
>> >
>> >Recently, the DBA I work with told me that using the default
cache
>> parameter
>> >on the sequences was evil, and that we should use 20 instead
of the
>> default.
>> >
>> >When I asked "why 20?", I was informed that he was the DBA,
and I should
>> not
>> >question his judgement.
>> >
>> >My question is what criteria is used to size the sequence
cache
>parameter,
>> >and how do we measure the effects of changing the parameter?
Also, what
>are
>> >the issues/tradeoffs with changing the cache parameter.
>> >
>> >Note that when profiling our app, the code that accesses the
sequence
>> >numbers does not register as taking a significant amount of
time.
>> >
>> >Thanks
>> >
>> >--Doug
>> >
>> >
>>
>>
>
>
Received on Wed Jul 28 1999 - 12:52:58 CDT