If you look at v$sql_shared_cursor - do you see queries with a lot of child
cursors and lots of new child cursor being created constantly?
On Wed, Nov 18, 2015 at 7:05 AM, <zhuchao@xxxxxxxxx> wrote:
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
BeginEndBuffer Cache:176,128M176,128MStd Block Size:8KShared Pool Size:
10,240M10,240MLog Buffer:159,300K
Load Profile
Per SecondPer TransactionPer ExecPer CallDB Time(s):15.20.00.000.00DB
CPU(s):14.60.00.000.00Redo size:9,438,745.43,618.0 Logical reads:
347,667.4133.3 Block changes:48,782.018.7 Physical reads:984.00.4 Physical
writes:2,218.50.9 User calls:156,080.159.8 Parses:17,140.06.6 Hard
parses:54.50.0 W/A MB processed:6.60.0 Logons:1.00.0 Executes:49,221.5
18.9 Rollbacks:215.90.1 Transactions:2,608.8
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %:99.98Redo NoWait %:100.00Buffer Hit %:99.72In-memory Sort
%:100.00Library Hit %:99.80Soft Parse %:99.68Execute to Parse %:65.18Latch
Hit %:99.81P*arse CPU to Parse Elapsd %:**24.42**% Non-Parse CPU:**97.8*6
Shared Pool Statistics
BeginEndMemory Usage %:93.0893.02% *SQL with executions>1:**96.38**61.57*%
Memory for SQL w/exec>1:t92.2473.14
Top 5 Timed Foreground Events
EventWaitsTime(s)Avg wait (ms)% DB timeWait ClassDB CPU 26,406 96.26 log
file sync4,280,0331,67606.11Commitdb file sequential read1,767,59838701.41User
I/O*library cache: mutex X**150,453**284**2**1.04**Concurrenc*ySQL*Net
message to client192,796,21120000.73Network
Host CPU (CPUs: 32 Cores: 16 Sockets: 2)
Load Average BeginLoad Average End%User%System%WIO%Idle1.742.2337.79.80.8
49.1Instance CPU
%Total CPU%Busy CPU%DB time waiting for CPU (Resource
Manager)51.6101.20.0Memory
Statistics
BeginEndHost Mem (MB):258,279.4258,279.4SGA use (MB):194,205.7194,205.7PGA
use (MB):2,100.33,752.7% Host Mem used for SGA+PGA:76.0176.65
Statistic NameTime (s)% of DB TimeDB CPU26,406.2296.26sql execute elapsed
time9,622.1135.08parse time elapsed2,509.169.15hard parse elapsed time
125.840.46sequence load elapsed time10.950.04PL/SQL execution elapsed time
9.000.03connection management call elapsed time7.430.03inbound 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
