| How to improve performance ? [message #64776] |
Tue, 27 January 2004 13:45  |
Anil
Messages: 80
Registered: September 2001
|
Member |
|
|
Hi,
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.
Anil
|
|
|
|
| Re: How to improve performance ? [message #64785 is a reply to message #64776] |
Fri, 30 January 2004 13:05  |
ilver
Messages: 50
Registered: January 2004
|
Member |
|
|
Hi,
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.
/ilver
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
