Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Re[2]: sequence numbers

RE: Re[2]: sequence numbers

From: April Wells <awells_at_csedge.com>
Date: Fri, 11 Oct 2002 04:43:26 -0800
Message-ID: <F001.004E6984.20021011044326@fatcity.com> 





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).

begin 666 InterScan_Disclaimer.txt
M5&AE(&EN9F]R;6%T:6]N(&-O;G1A:6YE9"!I;B!T:&ES(&4M;6%I;"!I<R!S
M=')I8W1L>2!C;VYF:61E;G1I86P_at_86YD(&9O<B!T:&4@:6YT96YD960@=7-E
M(&]F('1H92!A9&1R97-S964@;VYL>3L@:70@;6%Y(&%L<V\@8F4@;&5G86QL
M>2!P<FEV:6QE9V5D(&%N9"]O<B!P<FEC92!S96YS:71I=F4N("!.;W1I8V4@
M:7,@:&5R96)Y(&=I=F5N('1H870_at_86YY(&1I<V-L;W-U<F4L('5S92!O<B!C
M;W!Y:6YG(&]F('1H92!I;F9O<FUA=&EO;B!B>2!A;GEO;F4@;W1H97(@=&AA
M;B!T:&4@:6YT96YD960@<F5C:7!I96YT(&ES('!R;VAI8FET960_at_86YD(&UA
M>2!B92!I;&QE9V%L+B @268@>6]U(&AA=F4@<F5C96EV960@=&AI<R!M97-S
M86=E(&EN(&5R<F]R+"!P;&5A<V4@;F]T:69Y('1H92!S96YD97(@:6UM961I
M871E;'D_at_8GD@<F5T=7)N(&4M;6%I;"X*"D-O<G!O<F%T92!3>7-T96US+"!)
M;F,N(&AA<R!T86ME;B!E=F5R>2!R96%S;VYA8FQE('!R96-A=71I;VX@=&\@
M96YS=7)E('1H870_at_86YY(&%T=&%C:&UE;G0@=&\@=&AI<R!E+6UA:6P@:&%S
M(&)E96X@<W=E<'0_at_9F]R('9I<G5S97,N("!792!A8V-E<'0@;F\@;&EA8FEL
M:71Y(&9O<B!A;GD_at_9&%M86=E('-U<W1A:6YE9"!A<R!A(')E<W5L="!O9B!S
M;V9T=V%R92!V:7)U<V5S(&%N9"!A9'9I<V4@>6]U(&-A<G)Y(&]U="!Y;W5R
M(&]W;B!V:7)U<R!C:&5C:W,@8F5F;W)E(&]P96YI;F<@86YY(&%T=&%C:&UE
+;G0N#0H-"@T*#0H 
end

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: April Wells
  INET: awells_at_csedge.com

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).


Received on Fri Oct 11 2002 - 07:43:26 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US