RE: counting hits in an application

RE: counting hits in an application

 

  


The problem with sequences is that you cant directly tie them to a table
outside the app code as there is no physical link, so one sequence could be
used as the key for multiple tables (in theory). Basically you can ignore
the ones that get used little, just pick up the ones with a big last_number
and track them. Those will give you the most potential gain.

Also worth checking "sequence cache" from statspack.

Rgds,
John...

-----Original Message-----
From: Patterson Joel
[mailto:oracledba-ezmlmshield-x22087535.[Email address protected]
Sent: 29 December 2005 14:39
To: LazyDBA Discussion
Subject: RE: counting hits in an application

Good advice. Appears to be an effort to track the data for each table,
(we got more than 120 sequences easy).

I like the 5% rule... as for bulk loading, just give it a lot at first
eh? And move up from there.

-----Original Message-----
From: Chamberlain John
[mailto:oracledba-ezmlmshield-x20271725.[Email address protected]
Sent: Thursday, December 29, 2005 9:27 AM
To: LazyDBA Discussion
Subject: RE: counting hits in an application


The cache 20 is the default value that Oracle has been using for years.

To adjust, you need to consider in particular concurrency and growth
rate
over time. I try to have at least 5% of the expected values per day
cached
if possible for OLTP, so a daily check of last_number in dba_sequences
is
worthwhile until you get a feel for the growth. Also, if most of the
data is
bulk-loaded, then a big cache will speed that up a lot. For tables where
new
inserts are very low, then nocache will be appropriate. Most of the
time,
the default of 20 should be more than enough.

John...

-----Original Message-----
From: Patterson Joel
[mailto:oracledba-ezmlmshield-x15578546.[Email address protected]
Sent: 29 December 2005 14:03
To: LazyDBA Discussion
Subject: RE: counting hits in an application

Yes, thank you John, very nice explanation.

Do you have a query or way to tell if the sequence needs a larger cache?
A cache of 20 is typical to start... so when to change?

-----Original Message-----
From: Chamberlain John
[mailto:oracledba-ezmlmshield-x57470119.[Email address protected]
Sent: Thursday, December 29, 2005 8:40 AM
To: LazyDBA Discussion
Subject: RE: counting hits in an application

With no cache, you get contention on the sequence as it reads one out
and
resets the next value. Basically with no cache it single threads. Plus
with
a heavy stream of requests, there is overhead on the sequence for every
access. The cache sets up a set of available values in memory, and pulls
off
once when it needs to. Once all values are used up, it resets the cache
with
a new set of values. If you have a set of heavily inserted tables with a
sequence key (quite common!) then with a high cache, the inserts will be
consistently much faster. The bigger the cache the better the overall
performance as it only needs to reset the sequence/cache periodically.

I got 40% insert speed improvement just by going from no cache to 50k
cache
for a batch of about 7 million rows.

For RAC, the cache range for each instance is different, for example,
node a
will get values 1 to 1000, node b will get 1001 to 2000. The last_number
will be set top 2000. This will allow each node to operate the sequence
without any need to check the other node (independent). When one node
uses
up all its available cached sequences, it grabs a new set and updates
the
last_number to the highest sequence cached. This is why a database
restart
loses any unused sequence values.

With nocache, Oracle has to check the other RAC nodes to ensure no other
session has got it. This is quite heavy processing and has the potential
to
slow the whole application down. You will never get duplicate keys on a
sequence because of this check.

Nocache in RAC is only of value where every number in the sequence has
to be
allocated sequentially and/or a new value is pulled only occasionally,
and
is to be avoided like the plague where large number of inserts are
expected.

Hope that's clear (not too mud like!!)

John.

-----Original Message-----
From: Patterson Joel
[mailto:oracledba-ezmlmshield-x91264501.[Email address protected]
Sent: 29 December 2005 13:15
To: LazyDBA Discussion
Subject: RE: counting hits in an application

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.



Oracle LazyDBA home page