Kirti: Thanks for the insight. Here are my Answers:
1. I feel database is the problem because when only a subset of the total data is used the performance is excellent (with one tenth of the total data). The no. of users are the same. Only amount data that is retrieved is enormous (almost 10 times) when we have the performance problem. 2. Partioning may/may not help as it still has to traverse through all of the partitions to get the data required in order to satisfy the join condition. May improve response time but not significantly. 3. I will look into this as well. 4. Connections on the database side are DEDICATED and on the web side they are shared.(Using Microsoft COM objects, have certain set of database connections as POOL that are shared by different users.) Please let me know what you think... Srini -----Original Message----- Sent: Tuesday, January 29, 2002 11:20 AM To: Multiple recipients of list ORACLE-L You have done very nicely so far in tuning those joins... but... More questions to you : 1. Why do you think this is still a database problem? 2. Will partitioning those larger tables help the queries? 3. What have you checked to make sure that the bottleneck is not the web-server? 4. Are these connections persistent or the app connects/disconnects when accessing the database? Looking for answers to these questions may help you locate other opportunities to improve upon.. - Kirti -----Original Message----- Sent: Tuesday, January 29, 2002 9:25 AM To: Multiple recipients of list ORACLE-L Hello all: We have an application that is having slow response time against an 8i database, I would like to improve the response time. This is a web based application accessing the database with about 2000 users. Most of the application queries are based on complex joins on 4 big tables with each having over 5 million rows( biggest table has 18 million rows). I have tuned the Package queries for the best explain plan possible, but still do not seem to make dramatic change in the response time. Though, I was able to make significant headway tuning these packages by bringing down response times from 7 minutes to under 3 minutes. But this is not an acceptable response time from a web-application perspective. I am considering creating data cubes based on the most frequently used join conditions and pre-populate them on a nightly basis or use triggers to update the cube simultaneously. I am hoping that this enhance the response times. I would greatly appreciate your suggestions or opinions on this idea. If you have an alternative/better way of achiving this please enlighten me. Thank very much. Srini Rajendran. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
