RE: Re[2]: sequence numbers

April,

What can I say? Ouch! I feel your pain. I've been trapped in some pretty ridiculous situations too. (Though, I think you have me beat! A 37 column primary key?? Really??) Well, you at least seem to have the proper attitude. ;-) Without a sense of humor, I'm afraid you'd go insane in short order! ;-)

The only other thing I can think of when people shut you down like that is: document. "At meeting X, on such and such a date, I identified this problem, and Mr. Z told me to not to worry about it." It may not help, but from a sanity point of view, there is a certain amount of satisfaction in "I told you so!", even if you never verbalize it....;-)

Hang in there,

-Mark

On Fri, 2002-10-11 at 08:43, April Wells wrote:
> Mark...
>
> If this were the MOST serious design flaw in the whole mess, I wouldn't care
> so much. There is a point where you just shut up (gee, I have been TOLD to
> do that in meetings) and wait till it breaks (or worse, one of our clients
> buys it and we have to TRY to implement). I am the funny one... the one to
> laugh at and make fun of because I keep trying to tell them that you can't
> do things. You can't have a totally denormalized Oracle table if there 1500
> columns in it... yes queries will fly on a table that can't be built. You
> can't have 37 columns in a primary key. Date really isn't an acceptable
> name for a column.
>
> April Wells
> Oracle DBA
> Keep yourself well oiled with life, laughter, new ideas and action.
> Otherwise you will rust out. _Anonymous
>
>
> -----Original Message-----
> Sent: Thursday, October 10, 2002 7:34 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi Dick,
>
> I have to disagree with you here. Particularly in the case where this
> sequence will see any sort of concurrency, from multiple concurrent
> sessions accessing it. This is due to the serialization on the SQ
> enqueue. This will cause far worse scalability issues than any I/O.
> Not that I/O is insignificant, but in this situation, serialization on
> the enqueue will be the real showstopper for scalability.
>
> As to losing the cached values, well, so what? If your design is such
> that it's important to have an unbroken contiguous sequence of numbers
> with no gaps, then I would argue that is a serious design flaw. Also,
> if that's your requirement, then a sequence is not appropriate, since it
> can and will end up causing gaps, the first time you roll back a
> transaction.
>
> Finally, as to sequences losing cached values, unless your instance
> crashes or does a shutdown abort, Oracle should not loose any sequence
> values.
>
> -Mark
>
>
>
> On Thu, 2002-10-10 at 18:18, dgoulet_at_vicr.com wrote:
> > Actually there is no IO penalty since Oracle has to treat the sequence
> just like
> > any table with the old LRU algorithm. I have several sequences with a
> cache of
> > 0 and they perform as well as those with a cache value. The big
> difference is
> > when you shut down the database and all of those cached values end up in
> the
> > trash.
> >
> > Dick Goulet
> >
> > ____________________Reply Separator____________________
> > Author: "Yechiel Adar" <adar76_at_inter.net.il>
> > Date: 10/10/2002 1:38 PM
> >
> > I think that you will have an update to the sequence number EVERY time
> instead
> > of every 20 times. That's mean I/o for every nextval.
> >
> > Yechiel Adar
> > Mehish
> > ----- Original Message -----
> > From: Tim Gorman
> > To: Multiple recipients of list ORACLE-L
> > Sent: Thursday, October 10, 2002 7:43 PM
> > Subject: Re: sequence numbers
> >
> >
> > CACHE 20 is the default, so if you remove the clause, it will have
> absolutely
> > no impact on performance or anything else...
> >
> > ...of course, I get the feeling that that wasn't the gist of your
> question,
> > was it?
> > ----- Original Message -----
> > From: April Wells
> > To: Multiple recipients of list ORACLE-L
> > Sent: Wednesday, October 09, 2002 8:54 AM
> > Subject: sequence numbers
> >
> >
> > I have been given create scripts for sequences to be used in tables
> that
> > will be loaded via bulk loads. How huge is the potential performance hit
> if I
> > take out the cache 20?
> >
> > April Wells
> > Oracle DBA
> > There is neither good nor bad, but thinking makes it so. -Shakespeare
> >
> >
> > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> > <HTML><HEAD>
> > <META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>
> > <META content="MSHTML 5.00.2314.1000" name=GENERATOR>
> > <STYLE></STYLE>
> > </HEAD>
> > <BODY bgColor=#ffffff
> > style="FONT: 10pt Times New Roman; MARGIN-LEFT: 2px; MARGIN-TOP: 2px">
> > <DIV><FONT face=Arial size=3>I think that you will have an update to the
> > sequence number EVERY time instead of every 20 times. That's mean I/o for
> every
> > nextval.</FONT></DIV>
> > <DIV>&nbsp;</DIV>
> > <DIV>Yechiel Adar<BR>Mehish</DIV>
> > <BLOCKQUOTE dir=ltr
> > style="BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT:
> 0px;
> > PADDING-LEFT: 5px; PADDING-RIGHT: 0px">
> > <DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
> > <DIV
> > style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color:
> black"><B>From:</B>
> > <A href="mailto:Tim_at_SageLogix.com" title=Tim_at_SageLogix.com>Tim
> Gorman</A>
> > </DIV>
> > <DIV style="FONT: 10pt arial"><B>To:</B> <A
> href="mailto:ORACLE-L_at_fatcity.com"
> >
> > title=ORACLE-L_at_fatcity.com>Multiple recipients of list ORACLE-L</A>
> </DIV>
> > <DIV style="FONT: 10pt arial"><B>Sent:</B> Thursday, October 10, 2002
> 7:43
> > PM</DIV>
> > <DIV style="FONT: 10pt arial"><B>Subject:</B> Re: sequence numbers</DIV>
> > <DIV><BR></DIV>
> > <DIV><FONT face=Arial>CACHE 20 is the default, so if you remove the
> clause, it
> >
> > will have absolutely no impact on performance or anything
> else...</FONT></DIV>
> > <DIV><FONT face=Arial></FONT>&nbsp;</DIV>
> > <DIV><FONT face=Arial>...of course, I get the feeling that that wasn't
> the
> > gist of your question, was it?</FONT></DIV>
> > <BLOCKQUOTE dir=ltr
> > style="BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT:
> 0px;
> > PADDING-LEFT: 5px; PADDING-RIGHT: 0px">
> > <DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
> > <DIV
> > style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color:
> > black"><B>From:</B>
> > <A href="mailto:awells_at_csedge.com" title=awells_at_csedge.com>April
> Wells</A>
> > </DIV>
> > <DIV style="FONT: 10pt arial"><B>To:</B> <A
> > href="mailto:ORACLE-L_at_fatcity.com" title=ORACLE-L_at_fatcity.com>Multiple
>
> > recipients of list ORACLE-L</A> </DIV>
> > <DIV style="FONT: 10pt arial"><B>Sent:</B> Wednesday, October 09, 2002
> 8:54
> > AM</DIV>
> > <DIV style="FONT: 10pt arial"><B>Subject:</B> sequence numbers</DIV>
> > <DIV><BR></DIV>
> > <DIV><SPAN class=841194713-09102002>I have been given create scripts
> for
> > sequences to be used in tables that will be loaded via bulk
> loads.&nbsp; How
> >
> > huge is the potential performance&nbsp;hit if I take out the cache
> > 20?</SPAN></DIV>
> > <DIV>&nbsp;</DIV>
> > <P><FONT face="Courier New">April Wells</FONT> <BR><FONT
> > face="Courier New">Oracle DBA&nbsp;</FONT><BR><SPAN
> > class=841194713-09102002><FONT face="Courier New">T<SPAN
> > class=841194713-09102002>here is neither good nor bad, but thinking
> makes it
> >
> > so.
> >
> -Shakespeare</SPAN></FONT></SPAN></P></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML
> >
> >

-- 
--
Mark J. Bobak
Oracle DBA
mark_at_bobak.net
"It is not enough to have a good mind.  The main thing is to use it
well."
 						-- Rene Descartes
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mark J. Bobak
  INET: mark_at_bobak.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).