Re: Indicators of potential scaling issues

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,

Kevin

On 2/6/06, Niall Litchfield <niall.litchfield_at_gmail.com> wrote:
>
> On 2/4/06, Kevin Lidh <kevin.lidh_at_gmail.com> 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
>
>

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