Hi,
We have developed interfaces which automates entire sales & purchase pocess. We have made heavy use of AFS. The performance of these interfaces on the production server has never been able to match that on the development server.
On analysis there were lot of database level setting which were not optimal causing lot of I/O waits and contention. We did some tuning related to SGA and other parameters but with not much improvement. We also created ora_storage2.
We also noticed thru TKPROF and other tools the hints passed to oracle driver is misleading. How can we deal with this. Point to note is the production server is a higher end server.


We are on Baan ERP (5.0b)
Service Pack 8
Porting Set 6.2a.03.03

Database
Oracle 8.0.5

Any comments on this.

Nancy

Did you try tweaking the SQL statements in your Baan program to get better hints passed to Oracle?

well the wrong hints being passed are of the standard baan dlls (no scripts) :( Though we tried to suppress the hints passed by Baan using the MT_OPTIMIZER_MODE and ST_OPTIMIZER_MODE parameters it din't seem to help either.

Nancy,

If the wrong hints are being passed on to Oracle on your production server it should also happen on your development server, that is if both environments are more or less identical where software/object releases and configuration is concerned. Are there any notable differences in these areas thay could make a difference?

How long since you updated stats in production?

I would suggest tracing the SQL in both environments and compare the SQL statement to see if it is exactly the same and then compare the execution plans to see if both instances are using the same path to retrieve the data.

We did think on those lines and did analyze tables but they seem to be having a negative impact on the sql.

Nancy,

What OS and server are you using?

Assuming *nix, can you give us the contents of your /etc/system file?