Re: Indicators of potential scaling issues
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: kevin.lidh@xxxxxxxxx
- Date: Mon, 6 Feb 2006 16:33:49 +0000
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:
