Slow generation of batch reports

[TBTL]

Hi,

I have a database running one database running Oracle 9i Enterprise edition, i have a problem of report generation as it takes to much time to give out a report, especially at the end of the month. I have about 1.3 million transactions a day.I have put on Range partition on my table and also sorted out the problem with my application's sql statement used to the access database but still it gives me some sort of problem. A new table partition is created date wise.

How can i improve the report generation time as it some times takes about 2-3 hours or more depending on the transaction load on the database.

Help Me Guys

 

[sem]

You may consider using aggregating materialized views instead of generating report on live data. Without knowing details it's impossible to optimize your query. Do you have appropriate indexes? Are they used? etc.

Regards, Dima

http://seminihin.narod.ru

 

[TBTL]

I have already rebuilt the indexes and index usage is nearly 100% as my database is used specifically for report generation only.Data is loaded frequently by an automatic shell script.It is loaded via SQL Loader from the primary database. This is actually my secondary database used only for report generation.

I have optimized the query well enough using hints too. there is some improvement by using materialized views which has helped me a lot but still i need to make my system efficient to the peak level.

 

[sem]

Not to be offencive, but your question with information provided is like "what should I do to make my Oracle run faster?" I may answer that you should buy more memory, more and better processors etc. But you may get the same answer from Oracle support as well
How can one optimize your query with no details about its nature? Utilizing indexes doesn't guarantee efficient plan. Index may have low selectivity or huge size. Besides the order of joining your tables matters. Can you gather statistics at the time your database is full? In this case the plan may be optimized especially for the most complex case. But IMO the most complex cases reuire manual management (hints).

Can you provide more details? Current execution plan, tables involved with indexes and approximate sizes? Waste 2-3 more hours to run your query with SET AUTOTRACE TRACEONLY

Regards, Dima

http://seminihin.narod.ru