Re: Indicators of potential scaling issues

  • From: Kevin Lidh <kevin.lidh@xxxxxxxxx>
  • To: Niall Litchfield <niall.litchfield@xxxxxxxxx>
  • Date: Mon, 6 Feb 2006 15:36:46 -0700

Thank you all for your suggestions.  I had gotten a private e-mail
suggesting the scalability PDF and I'm well into reading it.  I've been
looking at parsing but they seem to be doing a decent job with bind
variables and enforcing reuse.  The rest of the suggestions are definitely
worth exploring.  The production system is a beast.  Over 6000 attaches
during the on hours portion of the day and many GB of RAM in use.  We don't
have the ability to duplicate load even with the LoadRunner at full tilt.

Thank you again,


On 2/6/06, Niall Litchfield <niall.litchfield@xxxxxxxxx> wrote:
> 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 
>  (ok it isn't online - I lied - it is free to download though).
> --
> Niall Litchfield
> Oracle DBA

Other related posts: