Re: real world oracle concurrency limit
Date: Thu, 19 Nov 2015 19:17:38 +0700
Message-ID: <CAP50yQ9YJUVe4GsPZvzJpuQ269Gu8P2pSgRPe8A5qmG1PNiPwA_at_mail.gmail.com>
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_at_gmail.com> 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_at_gmail.com
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> 发自我的iPhone6
>
> 在 2015年11月14日，下午6:35，Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> 写道：
>
>
> 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
> _at_jloracle
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on
> behalf of zhuchao_at_gmail.com [zhuchao_at_gmail.com]
> *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_at_gmail.com
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> 发自我的iPhone6
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Nov 19 2015 - 13:17:38 CET