Dear All,

We are having :

1. BaaN IV
2. Oracle 8.0.5
3. Windows NT 4.0
4. Three-tier architecture
5. Four different plants at different location

Sometimes my both servers or one of them goes slow-down, I think the reason is large From..to..To ranges. During report execution I have to face lot of calls, but I coudn't do anything except wait for completion of that report. My queries are:
1. How to find who has given big reports?
2. How to stop/kill/pause?
3. Why it is happening sometimes at database and sometimes at application side?
4. How to solve this performance issue except small From..to..To range?

-Suhas

There are so many things to consider but here are some of the options.

1. Given that you are three-tier - Do you have a dedicated network between the two systems and are you running Net8/SQL*NET or Baan Net?

2. What are your db_resource settings? Some of these will impact large reports in a three-tier environment.

3. You can trace back the report to a specific user -- if you are using a 1:1 Baan User:Db User it is fairly straight-forward. If not, then you will have to run some queries from the Oracle V$ tables to trace the user back to the client system user and PID. This will answer the "Who?" question.

4. Trying killing the bshell process first. Then you should see the driver (oracle8) and back-end process go away. If the back-end process stays running, you may need to kill this one manually.

5. You may see the load shift depending on which process is performing the unit of work at a given moment. If it is the bshell, then the app server will be busy. If the DB process, then obviously the DB server will be carrying the load.

6. Are you running the combo mode driver (ipc_info for oracle8 set to "d" in column before the executable path)?

Many times queries with larger range scans will pick an incorrect index. Or, if statistics have not been updated recently, you will not use an optimal execution plan. Hard telling with this level of detail, but keep posting and we will see what we can help with.

-- Jim

Some more things:

* Check what is your bottleneck. Your taskmanager will be your first friend here. Maybe you are disk bound and not CPU bound. Try to find out what your real bottleneck is.

* Oracle delivers some monitoring tools that show you heavy users. When having not all users logged on as the same database user you can see what users are consuming your CPU


Good luck,
Dick