Re: Indicators of potential scaling issues

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