From oracle-l-bounce@freelists.org Fri Jun 18 08:25:03 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i5IDOgu22655 for ; Fri, 18 Jun 2004 08:24:55 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i5IDOT622596 for ; Fri, 18 Jun 2004 08:24:42 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8A5BF72C6FB; Fri, 18 Jun 2004 08:08:43 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 01901-31; Fri, 18 Jun 2004 08:08:43 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D035C72C6FA; Fri, 18 Jun 2004 08:08:42 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 18 Jun 2004 08:07:18 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id EB72972C6E0 for ; Fri, 18 Jun 2004 08:07:17 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 01697-21 for ; Fri, 18 Jun 2004 08:07:17 -0500 (EST) Received: from ahmler1.mail.eds.com (ahmler1.mail.eds.com [192.85.154.71]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D048172C4C4 for ; Fri, 18 Jun 2004 08:07:16 -0500 (EST) Received: from ahmlir5.mail.eds.com (ahmlir5-2.mail.eds.com [192.85.154.135]) by ahmler1.mail.eds.com (8.12.10/8.12.10) with ESMTP id i5IDS1R2025698 for ; Fri, 18 Jun 2004 09:28:02 -0400 Received: from ahmlir5.mail.eds.com (localhost [127.0.0.1]) by ahmlir5.mail.eds.com (8.12.10/8.12.10) with ESMTP id i5IDRA1N000796 for ; Fri, 18 Jun 2004 09:27:10 -0400 Received: from USAHM101.amer.corp.eds.com (usahm101.exmi01.exch.eds.com [207.37.138.189]) by ahmlir5.mail.eds.com (8.12.10/8.12.10) with ESMTP id i5IDRAb3000791 for ; Fri, 18 Jun 2004 09:27:10 -0400 Received: by usahm101.exmi01.exch.eds.com with Internet Mail Service (5.5.2657.72) id ; Fri, 18 Jun 2004 09:27:17 -0400 Message-ID: <564DE4477544D411AD2C00508BDF0B6A1CE0AB97@usahm018.exmi01.exch.eds.com> From: "Powell, Mark D" To: "'oracle-l@freelists.org'" Subject: RE: Slow Views... Date: Fri, 18 Jun 2004 09:27:16 -0400 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2657.72) Content-Type: text/plain; charset="iso-8859-1" X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 3016 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: mark.powell@eds.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Mitchell Baldwin Sent: Friday, June 18, 2004 7:26 AM To: oracle-l@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@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@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 -----------------------------------------------------------------