Oracle 8i performance question - a cry for help!

[Pyrophos]

RedRed,

Hopefully all the tips are of any use and you are able to fix the problem.

Whatever the reason for the performance, when you are able to fix it, please let us know what was the reason and how you fixed it (professional interest).

[agallag]

If you're looking to replace the hardware, I highly recommend you look at HP/Compaq. I've found them to be much more reliable than IBM or *shudder* Dell, and the price isn't that much different.

The Proliant line has amazing disk performance if you go with the SmartArray 5300 controller. You can put up 256MB of cache on the disk controller, and if you throw in some 15K Ultra320 disks, you won't have any disk related performance problems. It also has either 2 or 4 channels, so you can split things up in a variety of ways.

What kind of price range are you considering? The ML530 is a dual P4 Xeon (up to 2.8GHz) that is quite reasonably priced. If you need more CPUs, the ML570 can take up to 4 Xeons (up to 2.0GHz). They also have the DL line, which take up less rack space, but hold fewer disks internally.

[cjolley]

Yuppers
We use Proliant 8000s for your size database :
quad Xeon 2 meg cache (700?) processors
2 gigs of ram
14 count of 18 gig HDs
2 controlers (We have noticed that it helps a lot to put data and index tablespaces on different controlers.)

(I guess the 8000s are retired now though)
They are very reliable.

Also, with the way HD prices are now, you might want to consider raid 0+1 instead of 5.
Though, we use raid 5 and have very good disk performance.
chuck

[edit] Only 2 gigs of ram!

[cjolley]

Red,
I had never seen anyone set sort_area_retained_size to anything but 0.
Look at http://asktom.oracle.com again and search for "sort_area_retained_size"
Read the first 4 posts. (especialy #4 about Sort Area Size)

The gist is that setting sort_area_retained_size > 0 is something you do if you have lots of extra memory, not the reverse.

chuck

[agallag]

The Proliant 8000 is 2 generations old now, replaced by the ML570, and now by the ML570G2. We still have some 8000 boxes in production, and they're certainly workhorses. They just go forever. The new G2 is the same idea, but wickedly fast as well.

[cjolley]

<a href="http://asktom.oracle.com/pls/ask/f?p=4950:8:6782715184354712">ask Tom shared_pool_size</a>

[RedRed]

Thanks Guys

I havent forgotten - I will post my findings... My dev box was offline for a while - I will run as soon as I can.... There is a newer version of my app, which I will get the dev box, and test that. I did notice that the 'Parse to exec ratio' was OVER 100%! in live....

With regards to the box - I am tied into a contract with IBM - the currently suggested model is a 630? Its at RISC box - 2 processors (option to go for 4)

http://www-1.ibm.com/servers/eserver...ries/9330.html

I have no experience of these boxes.... does anyone?.....
I have asked for the 1.45 GHz box - but I might have to get the 1 GHz... 2 procs & 4 GB Ram (8 Mb Cache)..... This is what the boffins are telling me, anyway.........

RedRed

[cjolley]

Originally posted by ex RedRed
...I did notice that the 'Parse to exec ratio' was OVER 100%! in live....

http://asktom.oracle.com/pls/ask/f?p=4950:8:58554607287471031

What language/system do they do thier developement in?
Our EP ratio is < 4%

Possibly even more importantly, our hard parses are < 10% of our total parses.

Chuck

[RedRed]

Yeah I know.... It seems that they use a presentation layer (middle teir) written in Delphi.... This is currently sending a stream of sql to the Oracle system.

There is no 'application' as such on the database server - byond Oracle. There are about 100-150 pre-compiled procedures on their box - but much of the SQL seems to be sent completely generated upon the middle teirs..... The procedures are mostly fired from triggers in the database - (THOUSANDS of those!)

I used to be able to get a 'stream' of SQL in an earlier DEV version of the software.... Not any more... .... There was a COMPLETE lack of bound variables being used in the in the presentation streams.... I have raised this with the software company. They are a bit cagey - 'as long as we deliver to spec - dont you get too concerned'..... blagh....blagh... 'This functionality is still being developed, not yet optimised... blagh blagh.....')

I am trying to help with this - I wont let them bluff me....

RedRed

[cjolley]

Originally posted by ex RedRed
... 'This functionality is still being developed, not yet optimised...

Well Duh!

Does "spec" include any kind of performance target?

Ask them if they are willing to spring for the super computer it will need to run on if you get a couple of hundred more users hitting it.
chuck

[cjolley]

Here is a possible work around for you.

set CURSOR_SHARING=FORCE in init.ora

Here is an explanation with cavats:

http://asktom.oracle.com/pls/ask/f?p=4950:8:59827428841858574

and the reference in doc_id 62143.1 on metalink

chuck

[RedRed]

Thanks CJ

The project wasnt being run by me originally (I came in in year 3 of the three year project - and I still reckon that it has 2 years to run!) Health service - dont you know! (Most of the delay is our fault - though a significant percentage is probably theirs! (I am not playing a 'blame game' with them yet.....)

Performance is in the contract - but it is explicitly for a completed product.... The original contract specs are SO far out from reality though....

The Contracts agency who wrote the original hadnt got a clue - literrally!

It does pay the mortgage though!

RedRed

[RedRed]

In projects I worked on before though we always put some optimisations in as we discovered them - it wasnt left to the end.... (best not to break code through rewrites!)

John J

[cjolley]

Unfortunately, fixing an app that doesn't use bind variables can involve something very close to a re-write.
Binds are something that need to be planed for in the design stage.
chuck

[Pyrophos]

Beginning from Oracle 9i or 9i SE (can't remember which) there is option in the SQL-engine, which can automatically convert literals to bind variables thus helping with some of the parse problems.