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 <cary.millsap_at_hotsos.com> 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:jonathan@gennick.com
> >
> >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 Oracle-article-request_at_gennick.com 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: jonathan_at_gennick.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).
>
> 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: hkchital_at_singnet.com.sg
>
> 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.net
> --
> Author: Cary Millsap
> INET: cary.millsap_at_hotsos.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).
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: yong321_at_yahoo.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 Wed Nov 05 2003 - 10:24:25 CST
