So what happens?
If nocache is 'correct'... and we actually cache, then will inserts
start failing with unique constraint violations as the separate
instances range of values diverge? -- (assuming the sequence is the
primary key)? I can only think that oracle 'knows' what the real
nextval is regardless of the cache values... which if correct confuses
the issue of why it should be nocache in the first place....?
-----Original Message-----
From: Chamberlain John
[mailto:oracledba-ezmlmshield-x14655722.[Email address protected]
Sent: Thursday, December 29, 2005 2:44 AM
To: LazyDBA Discussion
Subject: FW: counting hits in an application
Joel,
You are correct as there will be one cache per instance with a different
range of values.
However, with a frequently used sequence, especially with RAC, you are
asking for severe performance problems if you don't use a decent cache
value. Example worst case: I got a 40% throughput improvement on a large
insert job (7 million rows) by changing a sequence from nocache to cache
50000.
You know it makes sense!
Rgds.
John.
-----Original Message-----
From: Patterson Joel [mailto:[Email address protected]
Sent: 28 December 2005 15:28
To: Chamberlain, John
Subject: RE: counting hits in an application
**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal
**Anything below this line is the original email text
On a slight tangent, isn't nocache required in a RAC setup to keep the
nextval straight?
-----Original Message-----
From: Chamberlain John
[Email address protected]
Sent: Wednesday, December 28, 2005 10:24 AM
To: LazyDBA Discussion
Subject: RE: counting hits in an application
Why not use a sequence?
This assumes you do not need 100% accuracy, but this will be pretty
close:
Create sequence app_counter nocache;
(Note with nocache there could be contention issues if the proc is
heavily
called, so you may want to use a cache. However, with cache, you lose
all
cached values if the database is restarted and also the last_number in
dba_sequences will reflect the highest number cached)
Then insert in procedure (or app code):
Select app_counter.nextval from dual;
Then you can set up a process (dbms_job or cron job) to query
dba_sequences
and record its value:
Select last_number from dba_sequences where sequence_name="APP_COUNTER";
HTH!!
-----Original Message-----
From: Udi [mailto:oracledba-ezmlmshield-x34036238.[Email address
protected]
Sent: 28 December 2005 00:29
To: LazyDBA Discussion
Subject: Fw: counting hits in an application
Thanks! sounds just like what we need. We will explore.
Thanks,
Udi
This message and any files or text attached to it are intended only for
the recipients named above, and contain information that may be
confidential or privileged. If you are not an intended recipient, you
must not read, copy, use, or disclose this communication. Please also
notify the sender by replying to this message, and then delete all
copies
of it from your system. Thank you.
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
-----------------------------------------
Information in this email may be privileged, confidential and is
intended
exclusively for the addressee. The views expressed may not be official
policy, but the personal views of the originator. If you have received
it
in error, please notify the sender by return e-mail and delete it from
your
system. You should not reproduce, distribute, store, retransmit, use or
disclose its contents to anyone. Please note we reserve the right to
monitor all e-mail communication through our internal and external
networks.
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page