RE: Slow Views...

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Fri, 18 Jun 2004 09:27:16 -0400
Message-ID: <564DE4477544D411AD2C00508BDF0B6A1CE0AB97@usahm018.exmi01.exch.eds.com>

First I would think that the correct syntax for the hint would be /*+ RULE rather than /* +RULE as posted, but I would stick to a cost based plan if at all possible.

Have you tried to rewrite the query as pure SQL merging all the views and your code into one native SQL statement? Attempting to do this can help determine where the problem lies and expose techniques to eliminate the bottleneck.

IMHO -- Mark D Powell --

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mitchell Baldwin Sent: Friday, June 18, 2004 7:26 AM
To: oracle-l_at_freelists.org
Subject: Slow Views...

Hi All

I have a strange problem with some views on an 8.1.7 database (Solaris 8)

I have a top level view that unions 3 other views which is taking many minutes to return, but the SQL from the sub views is returning in a total of 20secs for all three queries.

Now the strange thing is the sub views are also going incredibly slow compared to the underlying SQL.

I have made sure all the joins are indexed and have analysed the explain plan, and its only doing index range scans.

I've also added the hint /* +RULE */ to stop hash joins.

I know there is an overhead for the views on views, but this seems a bit to much !..

TIA Mitch

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

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/

FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

----------------------------------------------------------------

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


----------------------------------------------------------------

To unsubscribe send email to:  oracle-l-request_at_freelists.org

put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Fri Jun 18 2004 - 08:25:03 CDT