Home » RDBMS Server » Performance Tuning » How to improve performance ?
How to improve performance ? [message #64776] Tue, 27 January 2004 13:45 Go to next message
Messages: 80
Registered: September 2001

I have a warehouse database and I'm having issues with the following :

   1.   When I try to create a normal B*tree index on 3 columns, it takes 35 minutes and the table is having 10 million records with the record width of 1500.

   2.  When I count with merging 2 tables(table 1 having 10 million recs. and table 2 having 7 million recs) with 4 where condition, I'm getting the count after 20 minutes.

   3. even the analyze with the dbms_stats.gather.... takes 1hr 30 minutes.

What will be the problems for the above? Let me know if you need any more information.

Re: How to improve performance ? [message #64785 is a reply to message #64776] Fri, 30 January 2004 13:05 Go to previous message
Messages: 50
Registered: January 2004

Creating an index for 10 mill recs takes time, depending on your hardware capability.

When you join big tables you get spanked if the indexes doesn't support you query.
Or your statistics isn't updated (using cost based optimizer)
- You need to know how Oracle is spending the time (resources actually) by tracing the query, using explain plan and extended SQL traces.

Previous Topic: How long each commit took?
Next Topic: statspack
Goto Forum:

Current Time: Tue Feb 27 10:08:23 CST 2024