1) I don't think it's possible to say anything particularly sensible about the
77K query per second - given your description it's possible that the most
significant detail will be the network latencies involved rather than a
data/library cache threat. (Critically, how many different statements are
executed in that 77K).
Perhaps a helpful indicator would be an estimate of how much CPU is spent on
the data buffer access and data processing and how much is spent on latch or
mutex spinning (checking latch misses, spin gets, and mutex sleep history for
clues). if you're averaging only 9 buffer gets per query that sounds like
700,000 which (to me) makes it sound like a lot of your CPU is spent on
competing to access the data/SQL rather than manipulating the data.
If you've got a system that can be used to produce an accurate test of the
first system then you might like to try loading it like the production system,
then running a single process that does something massively CPU intensive (but
non-database) to see what effect it has on the Oracle side of things.
2) THe user calls / executes thing could be perfectly reasonable - you don't
use pl/sql and the SQL is lightweight, so a rough (though not strictly true)
approximation is: user calls = parse calls + execute calls + fetch calls: user
calls = 3 * executes "fits". You'll probably see that parse calls is roughly
equal to executes, and possibly "cursors opened cumulatively" is much the same.
It's a bit sad that the session stats don't record fetch calls, but you can
get an idea of the match from v$sql and checking the parse, exec, and fetch
columns for a sample of popular statements.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of zhuchao@xxxxxxxxx [zhuchao@xxxxxxxxx]
Sent: 13 November 2015 17:35
To: ORACLE-L
Subject: real world oracle concurrency limit
hi all
i have been away from the oracle world for a few years, and recent growing
traffic caused our only oracle system running into its limit, so pulled me back
to oracle world…
i got two questions
1… whatis the realworld oracle concurrency limit? our 2way x86 server oracle
has reached 77k qps during peak time and ~225k user calls, running the system
at 70pct cpu(on flash card so no io wait)… i am afraid of even if we upgrade
to 4way/48core(now 2way/16core) server, internal concurrency limit(like library
cache mutex etc) will block system from 2x traffic… 5 years ago when i left
oracle world the most busy production system i knew was around 40k qps on a sun
t~3 server… wondering where there is many production system running at
100k~200k qps(a few thousand tps) in real world,if yes whatkind of hw
configirations?
2…i was curious about our system(11203 nom rac) with 225k user calls and 77k
executions… i know user call is usually higher than exec but this kind of gap
does not make sense to me…
we dont use plsql, no big sql fetching tons of rows(sql lio per exec was
just 9). a few hard parse(~60-100 per second) will cause more recursive call
but this does not explain.
anyone has idea?
or i should skip user calls and just read the executions?
thanks a lot
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
best regards
Mail: zhuchao@xxxxxxxxx<mailto:zhuchao@xxxxxxxxx>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
发自我的iPhone6