Re: real world oracle concurrency limit

1. 216 Rollbacks per seconds, is it "user rollbacks" or "transaction rollbacks"? Does the application issue rollbacks only to validate the connection?
2. To eliminate the soft parse, the best way is to use *one pass*. Cache the open cursor objects in java, which is the returned object from prepareStatement('select...'), then reuse the cursors to avoid prepareStatement calls. Or you can the jdbc statement caching feature.

Real World Performance have a live demo on this topic: RWP #4: Bind Variables and Soft Parsing
https://apexapps.oracle.com/pls/apex/f?p=44785:24:3653991277997:PRODUCT:::P24_CONTENT_ID,P24_PREV_PAGE,P24_PROD_SECTION_GRP_ID:9269,141,1745 RWP #5: Shared Cursors and One Parse
https://apexapps.oracle.com/pls/apex/f?p=44785:24:3653991277997:PRODUCT:::P24_CONTENT_ID,P24_PREV_PAGE,P24_PROD_SECTION_GRP_ID:9270,141,1745

On Wed, Nov 18, 2015 at 8: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
>
>

-- 
Regards
Sidney Chen

--
http://www.freelists.org/webpage/oracle-l