Specify Oracle System
f125...@hotmail.co.uk
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
bdbafh
Ryan,
Start here with the sample chapter:
http://www.oreilly.com/catalog/optoraclep/chapter/index.html
... but you'll want to read the entire book.
That should do it for you.
-bdbafh
hpuxrac
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.
f125...@hotmail.co.uk
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
joel garry
A contract without specific terms and conditions is not a contract.
Aside from that, contractors will exploit any loophole. A good
contract prevents the need for hardball.
You perhaps want to have separate projects for feasibility
and the actual project.
The default install isn't necessarily the best tuned db. You
pretty much have to tune empirically (though an experienced
person may be able to ballpark pretty close). You may
want to use people who are more experienced with the
business processes involved, and bring in tuning specialists
as necessary, though it would be better to use people who
know how to design for performance. The latter can be
scarce.
(I say these things as a contractor, I'm not a lawyer).
>
> 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.
That all said, I do my best work with the least restrictive contract,
sometimes even just a handshake and pay-by-the-hour-as-long-as-you-are-
happy-with-me.
(I've had those go on for years, but it only works with smallish
sites).
>
> Thanks you!
> Ryan
jg
--
@home.com is bogus.
If you could skip commercials, would you?
http://www.signonsandiego.com/uniontrib/20070216/news_1b16dvr.html