Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Indicators of potential scaling issues

Re: Indicators of potential scaling issues

From: Stephen Andert <andert_at_gmail.com>
Date: Mon, 6 Feb 2006 08:12:36 -0700
Message-ID: <6d45e210602060712w63bcbafdm642f9be1668fba5e@mail.gmail.com>


Kevin,

You can take the statistics from production and import them into the test environment. This will make the CBO make the same choices as it would with the same amount of data as production. Examining the explain plans would then reveal which SQL would be the most expensive. Combine that with expected run frequency (1/day, /hour, /sec,etc) and you will see some of the problems that will surface when promoting the code.

I would agree with you that concurrency and frequency also come into play, but unless you get an actual stress test, you won't know how till it gets to production. A stress test can be done by hand with enough users or a script to simulate user activity or with a third-party load test tool.

Good luck.
Stephen

On 2/4/06, Kevin Lidh <kevin.lidh_at_gmail.com> wrote:
>
> One of the systems I help maintain is a very large (5 TB) implementation
> of a CRM product. My primary charge, though, is to assist our customer with
> testing of new functionality on a very scaled-down (830 GB) version. We
> have the typical problems of the CBO not making the same decisions because
> of differing statistics (which I'm working on) but the point of this e-mail
> was a meeting we had yesterday afternoon. 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?" My question
> was, "Isn't that like saying height is a leading factor for describing a
> human?" He countered with the obvious question which I don't have the
> answer to: what else would you look at?. I've read papers about the Tailor
> atomic modelling method for capacity planning and I'm in the middle of Tom
> Kyte's and Jonathan Lewis's new books but I think I'm still missing some
> critical pieces. I apologize for the lack of brevity of this e-mail (and if
> this isn't the right forum for this question) but I wanted to frame my
> question so there isn't any ambiguity. My question is, are there
> combinations of statistics and/or information I can get from the database
> during the execution of a test on a scaled-down system which would indicate
> that an SQL (or combination) won't scale up even if it performs fine in the
> test? I have no problems doing the reading and research on my own if you
> just have general ideas.
>
> Thank you for your consideration of this question,
>
> Kevin Lidh
> kevin.lidh_at_gmail.com
>
>

--
Stephen Andert
http://spaces.msn.com/members/andert-news

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Feb 06 2006 - 09:12:36 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US