Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Database Performance Question

RE: Database Performance Question

From: Wong, Bing <bing.wong_at_IngramMicro.com>
Date: Tue, 29 Jan 2002 12:14:48 -0800
Message-ID: <F001.003FF66C.20020129120508@fatcity.com>

Bringing back 20,000 rows is not good in my shop. I recently tuned a SQL (returned 14000 rows, and it is web-based appl) from running 15 minutes to less than 15 seconds. The SQL joining only two indexes. The only changed I make was the index. The index had 2 keys before and I added the third one to filter less number of rowid from index before accessing base tables.

For your situation, I would go back to SQL to split the join. Joining 4 big tables is not good.

I agreed with Kirti that this is not the database problem. May look into partitioning on that 18million row table.

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

Typically i bringing back about 20,000 rows and that too after filtering first and joining with bigger tables.
Srini

-----Original Message-----
Sent: Tuesday, January 29, 2002 12:41 PM To: Multiple recipients of list ORACLE-L

How many rows are you bringing back from a typical query, how good is the best filter condition and are you filtering that first before joining to the other tables in the explain plan?

What is the query and explain plan?

Iain Nicoll

-----Original Message-----
Sent: Tuesday, January 29, 2002 4:20 PM
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..

-----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
--------------------------------------------------------------------
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: Nicoll, Iain (Calanais)
  INET: iain.nicoll_at_calanais.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:
  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: Wong, Bing
  INET: bing.wong_at_IngramMicro.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). Received on Tue Jan 29 2002 - 14:14:48 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US