That's it. If you didn't use the cache, then it would cause the same problem as with normal table-managed sequence numbers. But with cached sequence numbers, an application can get a sequence number without touching the database (SEQ$) at all.
Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -----Original Message----- Yong Huang Sent: Wednesday, November 05, 2003 10:24 AM To: Multiple recipients of list ORACLE-L Cary, If hitting a table that keeps a counter causes so many performance problems, I wonder why hitting sys.seq$ is much faster. I'd like to have some education on this Oracle magic. The only thing I can think of is that Oracle keeps some numbers in library cache as seen in sys.v$_sequences. Your own table doesn't do that. Yong Huang --- Cary Millsap <[EMAIL PROTECTED]> wrote: > "Hit a table that keeps a counter" will not scale (will not perform at > high concurrency). It will cause you no end of "buffer busy waits" > waits, "latch free" waits for a cache buffers chains latch (even if > db_block_buffers, _db_block_hash_buckets, and _db_block_hash_latches > could be set to infinity), lots of unnecessary CPU service consumption > due to the spinning (especially if you try to tinker with _spin_count), > and possibly a wide range of side effects including "write complete > waits" waits and others. > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - Performance Diagnosis 101: 11/19 Sydney > - SQL Optimization 101: 12/8-12 Dallas > - Hotsos Symposium 2004: March 7-10 Dallas > - Visit www.hotsos.com for schedule details... > > > -----Original Message----- > Hemant K Chitale > Sent: Wednesday, November 05, 2003 8:25 AM > To: Multiple recipients of list ORACLE-L > > > My comments [probably off-the-cuff without spending much time > thinking the issues through .....?] > > 1. Hit a table that keeps a counter. > Used to be a mechanism in the Oracle5 days [If I remember correctly, > Sequences came in Oracle6]. Issues were with locking the single > record used as the generator or scanning for the max(value) of the > key. > Not quite sure I understand how you encountered concurrency issues, > though. > > > 2. Stored sequences. > Although I prefer not to use a Sequence as a PK in itself [preferring > natural column/s which are Unique keys, with the NOT NULL, of course], > I have used a Sequence in an Advanced Replication implementation that > had no Primary Key and I needed a PK for Conflict Resolution [this was > years > ago and, if you ask me, I can't remember all the details] > > 3. SYS_GUID > SYS_GUID I've never used. It doesn't generate a NUMBER value > so it is not really similar to a Sequence. > Can user's key in a SYS_GUID-generated value ? Is it really > "human readable" or "recallable" as a plain NUMBER, Security Security > Number, > ZIP Code ?? > > 4. Similar to SYS_GUID .. > You hit on a fortuitous combination of columns. > > > Hemant > > At 05:19 AM 05-11-03 -0800, you wrote: > >The recent article that mentioned sequences got me to > >thinking. I might pitch a more detailed article on sequences > >to Builder.com. But a more interesting article might be one > >that explored various ways to automatically generate primary > >keys. So, in the name of research, let me throw out the > >following questions: > > > >What mechanisms have you used to generate primary keys? > >Which ones worked well, and why? Which mechanisms worked > >poorly? > > > >I've run up against the following approaches: > > > >* Hit a table that keeps a counter. This is the "roll your > >own sequence method". The one time I recall encountering > >this approach, I helped convert it over to using stored > >sequences. This was because of concurrency problems: with > >careful timing, two users could end up with the same ID > >number for different records. Is there ever a case when this > >roll-your-own approach makes sense, and is workable? > > > >* Stored sequences. I worked on one app that used a separate > >sequence for each automatically generated primary key. I > >worked on another app, a smaller one, that used the same > >sequence for more than one table. The only issue that I > >recall is that sometimes numbers would be skipped. But end > >users really didn't care, or even notice. > > > >* The SYS_GUID approach. I've never used SYS_GUID as a > >primary key generator. I wonder, was that Oracle's > >motivation for creating the function? Has anyone used it for > >primary keys in a production app? What's the real reason > >Oracle created this function? > > > >* Similar to SYS_GUID, I once worked on an obituary-tracking > >application that built up a primary key from, as best I can > >recall now: date of death, part of surname, part of first > >name, and a sequence number used only to resolve collisions, > >of which there were few. The approached worked well, > >actually, because whatever fields we munged together to > >generate a primary key gave us a unique key the vast > >majority of the time. > > > >The SYS_GUID approach is interesting, but if you need an ID > >number that users will see, and that users might type in > >themselves (e.g. social security number), is SYS_GUID really > >all that viable? > > > >Best regards, > > > >Jonathan Gennick --- Brighten the corner where you are > >http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] > > > >Join the Oracle-article list and receive one > >article on Oracle technologies per month by > >email. To join, visit > >http://four.pairlist.net/mailman/listinfo/oracle-article, > >or send email to [EMAIL PROTECTED] and > >include the word "subscribe" in either the subject or body. > > > >-- > >Please see the official ORACLE-L FAQ: http://www.orafaq.net > >-- > >Author: Jonathan Gennick > > INET: [EMAIL PROTECTED] > > > >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: [EMAIL PROTECTED] (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). > > Hemant K Chitale > Oracle 9i Database Administrator Certified Professional > My personal web site is : http://hkchital.tripod.com > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Hemant K Chitale > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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.net > -- > Author: Cary Millsap > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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). __________________________________ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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.net -- Author: Cary Millsap INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
