Re: Specify Oracle System

On 16 Feb, 15:15, "hpuxrac" <johnbhur..._at_sbcglobal.net> wrote:
> On Feb 16, 9:29 am, f1259..._at_hotmail.co.uk wrote:
>
>
>
> > Hi, this is my first post to this newsgroup so if this isn't the
> > appropriate forum please accept I didn't know rather ignorant of
> > netiquette.
>
> > My company has is about to contract out some PL/SQL work for table
> > data manipulation and reporting and need to specify the performance we
> > require. Basically a requirement might read "A call to procedure xxx
> > on 'the specified system' with no other workload must execute within 8
> > seconds".
>
> > My question is what do I need to specify for 'the specified system'.
> > Specifying the hardware CPU, memory and operating system is easy.
> > Specifying the Oracle parameters is not. I do not need to specify the
> > exact Oracle instance details (include all the .ora files etc) but
> > loosely what is enough so that the during testing and recreation of
> > the environment we can write "the procedure execution requirement was
> > that it should complete within 8 seconds - it executed in 8
> > minutes" (whether it's 8 seconds or 6 seconds or 12 seconds, it is
> > realistically a pass - at 8 minutes it is a fail).
>
> > Jonathan Lewis writes many times in "Cost Based Oracle
> > Fundamentals" ......."As ever, we stick with an 8KB block size in
> > locally managed tablespaces, with 1MB uniform extent size, avoid ASSM,
> > and disable system statistics to make the test case
> > reproducible ....". These writings are valid for what he is
> > discussing.
>
> > What do I need to write in specifying the system for a requirement
> > about a PL/SQL procedure in a package manipulating data completing in
> > 8 seconds ? In addition to the Oracle version and install options, I
> > need to specify other things but I don't know what. SGA memory
> > allocation ? This isn't a treatise about every exact parameter I need
> > to specify, just the top half dozen things so that we get a system
> > gives the expected result in about 8 seconds and not 8 minutes.
>
> > Thank you
> > Ryan
>
> If you have supplied relevant details on what kind of hardware your
> current production system ( and test maybe also ) is running ...
> that's all that I would supply. Also supply oracle database version.
> Give the people some contact information if they require more details.
>
> What you may want to spend some more attention on is the "volume" and
> "type" of data that is required that the procedure process against in
> your "under 8 seconds" constraint.
>
> It's easy for something to run on the test system against 100 rows and
> come back quickly. What happens if the same thing runs against 100
> million rows in production.
>
> How are you going to monitor and supply relevant test data for the
> outsourced development effort. Or are you requiring the contractors
> to create their own test data?
>
> That's what I would concentrate on if I were you.

Thanks very much for the feedback. We don't want to go and specify every parameter and the line of thinking is to provide loose information like a default install of Oracle 10.2 on Solaris 10 as the exact performance isn't that important. The 'ballparks' required for the IT consultants is to give them a target to meet (to meet 8 seconds response time is okay, 2 seconds would be better, but 8 minutes or an hour would ruin the business so we want these things contractually within the requirements document). If they came back to us and said because of the complex nature of the data, they could guarantee 30 seconds then it wouldn't be ideal but we could live with it. We don't want to play hard-ball on this, just to let them know of the type of response we need and avoid nasty surprises later.

As for the test data, this will be provided by us. Actually it isn't test data so much as anonymised production data. The "big" tables are 30,000 rows containing patient data, a few procedures and follow-up tables containing 4-5 million rows, and 30-40 other smaller tables. A modest system. I take onboard what you are saying about having a system being fast for 100 rows and also fast for 100,000,000 rows. I will include a description of the test system specifying table cardinality and data distribution to associate with the required performance metrics.

Thanks you!
Ryan Received on Fri Feb 16 2007 - 10:39:27 CST