Home » RDBMS Server » Performance Tuning » oracle sequence is causing high cpu usage? (oracle sequence is causing high cpu usage?)
oracle sequence is causing high cpu usage? [message #277726] Wed, 31 October 2007 05:34 Go to next message
dba4bank
Messages: 49
Registered: October 2007
Location: TURKEY
Member
I have a oracle 10G database on windows server 2003
oracle sequence is causing high cpu usage.it is sometime % 86 percent.


I am issuing this command from forms "SELECT seqno.NEXTVAL FROM DUAL"

[Updated on: Wed, 31 October 2007 06:36]

Report message to a moderator

Re: oracle sequence causing high cpu usage [message #277729 is a reply to message #277726] Wed, 31 October 2007 05:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Prove what you say.

Read and follow OraFAQ Forum Guide and How to Identify Performance Problem and Bottleneck and provide the requested information.

Regards
Michel
Re: oracle sequence is causing high cpu usage? [message #278183 is a reply to message #277726] Fri, 02 November 2007 05:21 Go to previous messageGo to next message
zoltanp
Messages: 58
Registered: March 2005
Location: Hungary
Member
Have you set the cache parameter of the sequence?

The nocache or low cache parameter may cause high cpu usage.

Zoltán Patalenszki
Re: oracle sequence is causing high cpu usage? [message #278195 is a reply to message #278183] Fri, 02 November 2007 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

The nocache or low cache parameter may cause high cpu usage.

It may cause waits but I was not aware it may cause high cpu usage.
Can you provide where you get this information or how to show it?

Regards
Michel
Re: oracle sequence is causing high cpu usage? [message #278206 is a reply to message #278195] Fri, 02 November 2007 06:48 Go to previous messageGo to next message
zoltanp
Messages: 58
Registered: March 2005
Location: Hungary
Member
I think that it may cause.
The nocache parameter caused bottleneck in our application
some years ago and the cache parameter help it.
I think that generating sequence element is not only I/O operation.

Sorry, but I can't prove but
Dba4bank will probe it, if they want.

Regards
Zoltán Patalenszki
Re: oracle sequence is causing high cpu usage? [message #278229 is a reply to message #278206] Fri, 02 November 2007 07:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

The nocache parameter caused bottleneck in our application

I concur but this is because of locks and not cpu usage.

Regards
Michel
Re: oracle sequence is causing high cpu usage? [message #283794 is a reply to message #278229] Wed, 28 November 2007 03:00 Go to previous messageGo to next message
dba4bank
Messages: 49
Registered: October 2007
Location: TURKEY
Member
ı already used high cache parameter.

thx for interested

regards
Re: oracle sequence is causing high cpu usage? [message #284088 is a reply to message #277726] Wed, 28 November 2007 16:23 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I have seen two situations where sequences have cause increased resource consumption and delays in processing. for what it is worth:

1) as was indicated, nocache, or low cache value. Consider that a cache size of zero means that for each value selected from the sequence, an recursive update must be made to the underlying sequence table. You can see this update as sql in v$sqlarea. If you do lots of sequence number gets, you will have lots of updating. this is in fact one of the four problems of a current system I am working on that is having CPU issues and spikes.

2) .NET or 3GL or front end tool doing sequence number gets across the network. Not necessarily a CPU cost, but can really slow down an app because each sequence number get may be coded in that app as a network round trip.

good luck. Kevin
Previous Topic: Statspack frequency
Next Topic: Tuning look-up tables : Suggestions needed
Goto Forum:
  


Current Time: Sat Dec 03 09:46:17 CST 2016

Total time taken to generate the page: 0.07320 seconds