Hi,
Sorry for late response - of course, such an approach is to use a
sequence is incorrect.
Please understand the situation when the application was written many
years ago by an external company. After migration to Exadata where (by
the definition :) ) all had to run faster, but it is not - and we have
to explain it to customers and this external company.
We check and reduce CPU usage - we know that is too high and for some
of them we successfully reduce CPU usage.
About configuration. YES. Database is installed on 4 nodes but only
one node is active
Pozdrawiam / Regards,
Paweł
On Wed, 15 Jun 2016 06:52:19 +0000
Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx> wrote:
This situation we have after migration from standalone database to
RAC (on EXADATA) for a system that works for many years
That does, at least, tell us that the choice to implement the ORDER
option didn't understand Oracle sequences - it's redundant on non-RAC
(or OPS as it might have been) systems.
I can't remember if I've said this, but the "resmgr:cpu quantum"
doesn't use CPU, it tells you that the session waiting for it is
using too much CPU and has voluntarily gone into a wait state. This
could mean that the session is associated with a particular service
or consumer group rather than it being the session itself that is the
guilty party, and it doesn't even mean that the service/group/session
is using a lot of CPU, only that it's exceeding its quota while the
machine is under stress.
Two points of attack: check out the resource manager settings; and
take a few snapshots of v$sesstat and v$session_event to find out
which sessions are the biggest consumer of CPU and most frequent
waiters for "resmgr: cpu quantum", and "enq: SV contention" (and "DFS
lock handle").
You've said something about only one active node - does this mean
you're running active/passive (or even that you've installed the RAC
s/w but only started one node), or that you've got multiple nodes but
only one of them should be running the SQL ? I did a quick test
running up a two-node RAC, and tried running the code on both (with
v1 set to 10,000) and don't even see any SV enqueues being acquired.
(I haven't added resource manager to the mix, though; nor have I
tried to overload the CPUs with other stuff).
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
