Recently i configured Oracle 8i on Win2000 with clustered nodes.AS a experiment i installed one application.To my surprise the application is running very slow.Matter of fact the same application is running on small server very well ,the database configuration is o. k. since this is the only application running.Even the network is seems to be o.k. Can u tell me what are the areas which i can check to improve its performance as shortly i have to move it into production.All your comments and remarks are appreciated.
Clear the change:
1) Win2000 is new OS u have installed
2) Oracle8i is new version u have installed
3) New hardware
OR u migrated Oracl8i on win2000 from old hardware to new hardware.?????
Both the OS and Oralce 8i are new versions installed on fresh nodes. I created a databae on shared storage and imported the application data from old database 8.0.5 and OS Win NT
Since you are running in a clustered environment I must ask if you are running Parallel Server or FailSafe. If you are using FailSafe then your network speed may be a factor. FailSafe lets only one server have access to the database files at any one time, so it appears to be a 'regular' database configuration until such time as the database-controlling server 'dies'. You may have problems with access time to the clustered storage.
If you are running Parallel Server then you also have rollback segments to worry about, since each instance in a Parallel Server configuration must have its own rollback segments. An insufficient number of rollback segments will cause undo header and undo block waits since transactions are not able to acquire a rollback segment. To check which instance has which rollback segment use this query:
select segment_name, instance_num
order by instance_num, segment_name;
Presuming you have two instances in your Parallel Server you should see rollback segments for both instances. If not then you need to create more rollback segments on the server that is deficient. To see roughly how many rollback segments will be needed you can execute the following query:
(select value from v$parameter where name ='transactions') t,
(select value from v$parameter where name = 'transactions_per_rollback_segment') p;
This will provide a rough idea of how many rollback segments will be needed per instance.
There are other areas of database design and tuning that may need to be addressed, however without seeing the actual configuration it is difficult to ascertain why an application does not perform well.
David D. Fitzjarrell
Oracle Certified DBA
Indeed on my clustered servers i installed Oracle Fail safe.Iam planning to do the diagnise tools and see what it will tell.I will try to put all the information from this shortly so that u will have some information on this to guide me in improving the application performance.
Must have? Have you heard of Public Rollback Segments in OPS?
each instance in a Parallel Server configuration must have its own rollback segments.
Now iam finding something which is very unusual. The clients access the application which is installed on the server through ODBC. Recently i did one experiment by using the system DSN with Microsoft ODBC drivers rather than Oracle ODBC. The application response was terriffic .But i cant use this practically since when i use this it is giving problem from client end. Now i will give u some details on the ODBC which iam using on the servers and clients.It is
Oracle ODBC 8.1.6
Is there any way to improve the performance by updating the drivers.
Another thing you should check that the application is supported by Oracle 8i. It depends on the application as well. I think you should check with application vendors whether it is for Oracle 8i as well.
Finally i found out the problem.I down loaded the upgrade ODBC driver 220.127.116.11.0 from Metalink and replaced the default ODBC driver 18.104.22.168.0 which comes with Oracle Software.Now the application is running smoothly.
Thanks for everyone who participated in this.
Click Here to Expand Forum to Full Width