Re: Slow Views...

From: Jared Still <jkstill_at_cybcon.com>
Date: Fri, 18 Jun 2004 04:38:55 -0700
Message-Id: <1087558735.19682.272.camel@poirot>

As an experiment, try this:

alter session set optimizer_max_permutations=2000;

I have found this to be useful in 8.1.7 when dealing with complex views.

Jared

On Fri, 2004-06-18 at 04:26, Mitchell Baldwin wrote:
> 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 - 06:36:36 CDT