Re: Indicators of potential scaling issues

On 2/4/06, Kevin Lidh <kevin.lidh@xxxxxxxxx> wrote:
> My concern was that we aren't
> looking at the right things to identify potentially bad, or worse yet
> dibilitating, SQL before they get into production.  Our customer's Oracle
> consultant said high buffer gets per execution (+3000).  I said there has
to
> be more that would be an indication of an SQL that won't scale when a
> greater load is applied, meaning frequency and concurrency.  He asked,
> "Isn't buffer gets the leading indication of a scaling issue?"
<warning theoretical stuff follows>
I'd certainly agree about frequency of execution. The less you do something
the better it will scale, if you can never do it that would be ideal (or
your a dba or something).

typical scalability inhibitors will include


   - parsing too often (so watch for sql that should be sharable but
   isn't).
   - taking too many resources to do the job (so I like buffer gets per
   execution)
   - redo capacity (so measuring the io throughput of the redo/arch
   devices might be worthwhile)
   - waiting on the damn application server (no suggestions - sorry).
   - cpu capacity (so measure jobs that saturate a single cpu and see how
   often they get executed with increased user load).
   - memory requirements (moving from ram to swap is an nasty experience)
   so look at pga usage/per users

<end of theoretical stuff>

There is a book on this. Its free and online as well, and written by a
scaleability expert :) You can download it at
http://www.scaleabilities.co.uk/book/scalingOracle8i.pdf (ok it isn't online
- I lied - it is free to download though).

--
Niall Litchfield
Oracle DBA
http://www.orawin.info

Other related posts: