Performance Tuning Question..

Kill the user's process or better yet, just kill the user.

If that's too drastic then you should look to see:

1. Look at cache hit/miss ratio's to see if they are down.

2. Are there any queries that are out there running that are
sucking up resources?

3. Look at the O/S level to see if any processes have started
which might taking up resources such as nightly batch
processes, etc...

Other than that...

You can look to make sure you don't have any invalid objects, check to make sure that your statistics are fresh if using the cost-based optimizer...etc..

I actually had an interesting problem today. We had some users who were very unhappy because some of their monthly reports were taking forever to run. They said it should run in two hours and it had been six and still not completed. It turned out that on the partitioned tables, the september partitions had been created and analyzed once before when they actually had no data loaded into them yet so the row_count was 0. Well after the data load there were some other issues and the tables had not be reanalyzed. Therefore the cost based analyzer was choosing a totally inappropriate path for execution where it should have been using the partition in question. After analyzing the partitions again, the query executed in a little over an hour. Better yet, because of looking at the trace and tkprof output I was able to find some inefficencies in the query and I brought the execution time down from a little over an hour to about 8 minutes. Needless to say, the users were very happy.

The moral of the story:

Make ABSOLUTELY SURE your statistics are up to date. When the statistics are screwed up, the CBO can really screw you *laugh*

[Edited by wjramsey on 11-15-2000 at 07:35 PM]