From oracle-l-bounce@freelists.org Mon Mar 21 18:12:18 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j2M0CIto008584 for ; Mon, 21 Mar 2005 18:12:18 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j2M0CGem008580 for ; Mon, 21 Mar 2005 18:12:17 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7F6EB8851E; Mon, 21 Mar 2005 18:10:29 -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 23940-07; Mon, 21 Mar 2005 18:10:29 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id F063F8838C; Mon, 21 Mar 2005 18:10:28 -0500 (EST) Message-ID: From: Wolfson Larry - lwolfs To: "Brajesh.Jaiswal" , Paul Drake Cc: oracle-l@freelists.org Subject: RE: No parsing Date: Mon, 21 Mar 2005 17:08:37 -0600 MIME-Version: 1.0 Content-type: text/plain Content-Transfer-Encoding: 8bit X-archive-position: 17556 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: lawrence.wolfson@acxiom.com Precedence: normal Reply-To: lawrence.wolfson@acxiom.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Checker-Version: SpamAssassin 2.60 (1.212-2003-09-23-exp) on air891.startdedicated.com X-Spam-Status: No, hits=0.2 required=5.0 tests=AWL,UPPERCASE_25_50 autolearn=no version=2.60 X-Spam-Level: Brajesh, It doesn't always have to be a major change to cause a problem. Are there differences? What about the 10053 CBO trace? That should show you what CBO is thinking? Can you answer Paul's question about the OS? Larry -----Original Message----- From: Brajesh.Jaiswal [mailto:brajesh.kj@ness-gsg.com] Sent: Monday, March 21, 2005 5:11 PM To: Paul Drake Cc: Wolfson Larry - lwolfs; oracle-l@freelists.org Subject: Re: No parsing I am not getting any core file or any file in udump directory. I have opened a TAR for this issue. Waiting for Oracle Support guys to come back. Regards Brajesh ----- Original Message ----- From: "Paul Drake" To: Cc: ; Sent: Tuesday, March 22, 2005 4:12 AM Subject: Re: No parsing > Its usually worthwhile to provide the server OS info. > Bugs have a tendency of being platform-specific. > > check 2 init.ora parameters: > > cursor_sharing > compatible > > it would appear that you have set cursor_sharing=EXACT > (good, this is the most 'safe' setting). > > We've hit some issues with 9.2.0.6 ... which are fixed in 10.1.0.3 > (there's a shocker). > Some were only on lin32, some were on win32. > > Discussing this with an Oracle Support Analyst is the way to go. > Are you getting any core dumps created in the cdump location, trace > files in udump? > > Paul > > > > > On Tue, 22 Mar 2005 04:03:48 +0530, Brajesh.Jaiswal > wrote: > > Larry, > > > > I dont think there was any major change in the initora. > > > > Regards > > Brajesh > > > > ----- Original Message ----- > > From: "Wolfson Larry - lwolfs" > > To: ; > > Sent: Tuesday, March 22, 2005 3:46 AM > > Subject: RE: No parsing > > > > > Brajesh, > > > Any change to initora after upgrade? > > > > > > Larry > > > > > > -----Original Message----- > > > From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] > > > On Behalf Of Brajesh.Jaiswal > > > Sent: Monday, March 21, 2005 3:47 PM > > > To: oracle-l@freelists.org > > > Subject: No parsing > > > > > > Hello All, > > > > > > One of our client have upgraded their database from 9.2.04 to 9.2.0.6. > > They > > > are using a CRM software which is generating the below mentioned SQL. > > Oracle > > > is unable to parse this SQL, infact session starts hanging when we try to > > > execute or generate an explain plan for it. We tried to capture some > > > infiormtion by turning the trace on with even 10046 but no luck. No trace > > > file is getting generated. This application and also the mentioned SQL > > used > > > to work normal in version 9.2.0.4. > > > > > > - udump and bdump directories have nothing so far. > > > > > > - The temp tablespace is normal as per our space check report executed > > every > > > minute. No space exhaustion -- Close to 24GB available all times. > > > > > > - SQL: > > > SELECT > > > SUB_LOOKUP.SUBSCRIBER_LOOKUP_ID > > > ,MIN(SUB_LOOKUP.BAN) > > > ,0 > > > FROM > > > VANTAGE.V_SUBSCRIBER SUBSCRIBER > > > ,VANTAGE.V_SUB_LOOKUP SUB_LOOKUP > > > ,VANTAGE.V_BILLING_ACCOUNT BILLING_ACCOUNT > > > ,VANTAGE.V_PRICE_PLAN PRICE_PLAN > > > WHERE (SUBSCRIBER.PRODUCT_TYPE = 'C' > > > AND SUBSCRIBER.LIVE_NONLIVE = 'Y' > > > AND BILLING_ACCOUNT.CREDIT_CLASS NOT IN ('S','C') > > > AND SUBSCRIBER.PRICE_PLAN NOT IN > > > > > ('100A','109A','120A','120B','130A','140A','150A','170A','180A','180B','909A > > > ','709A') > > > AND PRICE_PLAN.PPLAN_SERIES_CD IN ('CNS','GBM','DOR','COR') > > > AND > > > > > TO_DATE(TO_CHAR(SUBSCRIBER.INIT_ACTIVATION_DATE,'MM/DD/YYYY'),'MM/DD/YYYY') > > > BETWEEN TO_DATE(TO_CHAR((SYSDATE - 10),'MM/DD/YYYY'),'MM/DD/YYYY') > > > AND TO_DATE(TO_CHAR((SYSDATE - 4),'MM/DD/YYYY'),'MM/DD/YYYY') > > > AND BILLING_ACCOUNT.ACCOUNT_SUB_TYPE <> 'M' > > > AND SUBSCRIBER.PRIVACY_IND = 'N' > > > AND (SUBSCRIBER.N_IN_1 IS NULL OR (SUBSCRIBER.N_IN_1 IN ('P','N'))) > > > AND BILLING_ACCOUNT.COL_DELINQ_STATUS = 'N' > > > AND SUBSCRIBER.FUTURE_DEACT_DATE IS NULL > > > AND SUBSCRIBER.STDEXCL_FRIENDS_OF_TED = 'N' > > > AND SUBSCRIBER.STDEXCL_CAMPUS_ADDR = 'N' > > > AND ((SUBSCRIBER.STDEXCL_SENS_COMP_CD = 'Y' AND > > > SUBSCRIBER.STDEXCL_CORP_EPP = 'Y') > > > OR SUBSCRIBER.STDEXCL_SENS_COMP_CD = 'N') > > > AND SUBSCRIBER.STDEXCL_RESELLER = 'N' > > > AND SUBSCRIBER.STDEXCL_GOVT = 'N' > > > AND SUBSCRIBER.STDEXCL_ROGERS_EPP = 'N') > > > AND SUBSCRIBER.SUBSCRIBER_LOOKUP_ID = SUB_LOOKUP.SUBSCRIBER_LOOKUP_ID > > > AND BILLING_ACCOUNT.BAN = SUB_LOOKUP.BAN > > > AND PRICE_PLAN.SOC = SUBSCRIBER.PRICE_PLAN > > > AND EXISTS (SELECT 'X' FROM VANTAGE.CS2701_1582_0_1 CS2701_1582_0_1 > > WHERE > > > CS2701_1582_0_1.A1 = SUB_LOOKUP.SUBSCRIBER_LOOKUP_ID) > > > GROUP BY SUB_LOOKUP.SUBSCRIBER_LOOKUP_ID > > > ; > > > > > > - Explain plan: I tried to obtain one, but my TOAD session ceased > > responding > > > after I pressed Ctrl-E to get the explain plan. Visual inspection of the > > SQL > > > statement suggests no Cartesian product. > > > > > > - Table CS2700_1575_0_1 has 10,901 records. Table SUB_LOOKUP has > > 11,751,635 > > > records. > > > > > > Any help in this regard would be highly appreciated. > > > > > > Regards > > > Brajesh > > > > > > > > > -- > > > http://www.freelists.org/webpage/oracle-l > > > > > > > > > ********************************************************************** > > > The information contained in this communication is > > > confidential, is intended only for the use of the recipient > > > named above, and may be legally privileged. > > > If the reader of this message is not the intended > > > recipient, you are hereby notified that any dissemination, > > > distribution, or copying of this communication is strictly > > > prohibited. > > > If you have received this communication in error, > > > please re-send this communication to the sender and > > > delete the original message or any copy of it from your > > > computer system. Thank You. > > > > > > -- > > > http://www.freelists.org/webpage/oracle-l > > > > > > > -- > > http://www.freelists.org/webpage/oracle-l > > > > > -- > #/etc/init.d/init.cssd stop > # f=ma, divide by 1, convert to moles. -- http://www.freelists.org/webpage/oracle-l