RE: Database Performance Question

From: Deshpande, Kirti <kirti.deshpande_at_verizon.com>
Date: Tue, 29 Jan 2002 12:41:12 -0800
Message-ID: <F001.003FF747.20020129124525@fatcity.com>

Srini,
1. You don't have to feel... go after v$system_event, v$session_event and v$session_wait to find out what events and SQLs are contributing to unnecessary waits and try to find out what can be done to resolve it. If you think data retrieval is the problem, then info from these views will help tell you why. Also, check v$filestat for 'hot' files. If this is I/O related problem, then get Quest's StorageXpert to find out I/O contention by disks and by DB segments and SQLs causing it. I think you can get an eval copy from them.

2. Is appl written for Web interaction, or this a retro to get something looking nice as a front-end? May be this is something you may want to check with Developers/Users. Do they, the end users, go through thousands of rows returning through the web browsers. IMO, you have some design issues to deal with..

3. ??

4. How many dedicated connections to the database? How much memory is available on the box?

Doing some more data collection as mentioned above (in 1) will get you on the right track

Hope this helps.. some...

Kirti

-----Original Message-----
Sent: Tuesday, January 29, 2002 11:36 AM To: Multiple recipients of list ORACLE-L

Kirti:
Thanks for the insight.

Here are my Answers:

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.
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.
I will look into this as well.
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: SRAJENDRAN_at_nlfs.com

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: ListGuru_at_fatcity.com (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: kirti.deshpande_at_verizon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: SRAJENDRAN_at_nlfs.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------