thanks jonathan…
we are now running on 2ge nic bond and outgoing traffic is 500mbps, still looks
good…
Regarding cpu cycle on buffer gets vs contention, from awr it still looks
good(with some library cache mutex ), see awr sample for 30 min report
What concerns me is the hard parse(indicated by 54 parse per second and library
cache mutex),and 17k soft parse. I have requested dev team to fully eliminate
hard parse, and reduce soft parse , and reduce soft parse by increase session
cached cursor from 50 to 200.
regarding user calls vs exec， I hvve requested team member to do a 10046 trace
and feedback with me result will share with you later.
Cache Sizes
Begin End
Buffer Cache: 176,128M 176,128M Std Block Size: 8K
Shared Pool Size: 10,240M 10,240M Log Buffer: 159,300K
Load Profile
Per Second Per Transaction Per Exec Per Call
DB Time(s): 15.2 0.0 0.00 0.00
DB CPU(s): 14.6 0.0 0.00 0.00
Redo size: 9,438,745.4 3,618.0
Logical reads: 347,667.4 133.3
Block changes: 48,782.0 18.7
Physical reads: 984.0 0.4
Physical writes: 2,218.5 0.9
User calls: 156,080.1 59.8
Parses: 17,140.0 6.6
Hard parses: 54.5 0.0
W/A MB processed: 6.6 0.0
Logons: 1.0 0.0
Executes: 49,221.5 18.9
Rollbacks: 215.9 0.1
Transactions: 2,608.8
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 99.98 Redo NoWait %: 100.00
Buffer Hit %: 99.72 In-memory Sort %: 100.00
Library Hit %: 99.80 Soft Parse %: 99.68
Execute to Parse %: 65.18 Latch Hit %: 99.81
Parse CPU to Parse Elapsd %: 24.42 % Non-Parse CPU: 97.86
Shared Pool Statistics
Begin End
Memory Usage %: 93.08 93.02
% SQL with executions>1: 96.38 61.57
% Memory for SQL w/exec>1: t92.24 73.14
Top 5 Timed Foreground Events
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
DB CPU 26,406 96.26
log file sync 4,280,033 1,676 0 6.11 Commit
db file sequential read 1,767,598 387 0 1.41 User I/O
library cache: mutex X 150,453 284 2 1.04 Concurrency
SQL*Net message to client 192,796,211 200 0 0.73 Network
Host CPU (CPUs: 32 Cores: 16 Sockets: 2)
Load Average Begin Load Average End %User %System %WIO %Idle
1.74 2.23 37.7 9.8 0.8 49.1
Instance CPU
%Total CPU %Busy CPU %DB time waiting for CPU (Resource Manager)
51.6 101.2 0.0
Memory Statistics
Begin End
Host Mem (MB): 258,279.4 258,279.4
SGA use (MB): 194,205.7 194,205.7
PGA use (MB): 2,100.3 3,752.7
% Host Mem used for SGA+PGA: 76.01 76.65
Statistic Name Time (s) % of DB Time
DB CPU 26,406.22 96.26
sql execute elapsed time 9,622.11 35.08
parse time elapsed 2,509.16 9.15
hard parse elapsed time 125.84 0.46
sequence load elapsed time 10.95 0.04
PL/SQL execution elapsed time 9.00 0.03
connection management call elapsed time 7.43 0.03
inbound PL/SQL rpc elapsed time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
谢谢
诸超
Mail: zhuchao@xxxxxxxxx
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
发自我的iPhone6
在 2015年11月14日，下午6:35，Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx> 写道：
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
发自我的iPhone6
