Hi,
We have big problems on daily basis with the infrastructure and I need help, or some suggestion what to do.
We have a application server with two nodes, reports server and a database server. The applications makes requests to the server, and the queries are execute on one node or the other. The whole infrastructure is oracle based, the apps are build with oracle forms and reports with oracle reports, the server is Linux.
Now I will try to describe the problem from the point what really is happening every day.
Different workers use the application and the application have different type of modules(forms), every worker has his own sets of of modules depending on his/her role is and what he/she needs to work. So they serve different type of customers and when that's happens different types of queries works behind.
Mostly all the tables are indexed and where they are execute from TOAD editor, they work pretty fast, but when they are executed from the application are very very slow. The problem is sometimes some query can execute for 3 sec lets say and next moment the same query is working 1 minute. We indexed all other tables which we think that they we're making problems and was little slowly, but not so bad to break the performance on the system as whole, but after that, again we have same problems with the system and big bottleneck. The users has difficulties when serve the customer, the applications is very slow on responding.
Another type of problem is with the the reports which is very weird. For example, the forms which the worker use, have a button and when the worker click on the button for around 3 sec it has to create reports. Again, the reports from oracle reports when we test it works very well, but from the application server, works very slow. The button is very stupid and just pass parameters and then from the button is called the report, from the report server and that operations sometimes knows to be stuck for about a minute.
We do some simple login inside stored pl sql procedures with start time and end time, but that log won't help us because what really is going on.
if anyone have suggestions i like to hear.
Tnx