I have a problem regarding tuning. The reports of Developer 2000 are taking a very long time. I analyzed the tables and indexes. Then I checked the queries and created indexes on those columns that are being accessed. But still, no use. Then I increased the size of shared pool, sort area size and db buffer size, and now the reports have become slower. Can you please tell me what is to be done?
Creating indexes an sometimes cause performance problems, or they can help performance. So just creating an index may not cure your performance problems.
Performance tuning is quite a topic which I simply don't have room to address in this forum. But I can give you some guidelines. First, look at the application. Examine every SQL statement the application makes to the database. Tune each statement individually. Make sure that each statement runs in a short period of time. Also make sure that your application is using bind variables. After you are satisfied with your application, tune the database. Make sure that your SGA is sized correctly. And one of the mistakes people make is to make the Shared Pool too big, causing performance problems, not solving them.
If you need further help with tuning, please refer to the Oracle documentation, or the multitude of tuning books on the market. You can also investigate the many performance tuning tools on the market. Finally, it may be in your best interest to hire a consultant to look into this issue for you.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.