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: sequence numbers

Re: sequence numbers

From: Anjo Kolk <anjo_at_oraperf.com>
Date: Thu, 10 Oct 2002 15:33:23 -0800
Message-ID: <F001.004E6564.20021010153323@fatcity.com>


There is no read penalty, but there is a indirect write penalty. Every nextval operation will start a recursive transaction that will generate redo and that needs to be flushed to disk.

Anjo.

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>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: dgoulet_at_vicr.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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Anjo Kolk
  INET: anjo_at_oraperf.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 Thu Oct 10 2002 - 18:33:23 CDT

Original text of this message

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