RE: No parsing

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_at_ness-gsg.com] Sent: Monday, March 21, 2005 5:11 PM
To: Paul Drake
Cc: Wolfson Larry - lwolfs; oracle-l_at_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" <bdbafh_at_gmail.com>
To: <brajesh.kj_at_ness-gsg.com>
Cc: <lawrence.wolfson_at_acxiom.com>; <oracle-l_at_freelists.org> 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
> <brajesh.kj_at_ness-gsg.com> wrote:
> > Larry,
> >
> > I dont think there was any major change in the initora.
> >
> > Regards
> > Brajesh
> >
> > ----- Original Message -----
> > From: "Wolfson Larry - lwolfs" <lawrence.wolfson_at_acxiom.com>
> > To: <brajesh.kj_at_ness-gsg.com>; <oracle-l_at_freelists.org>
> > 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]
> > > On Behalf Of Brajesh.Jaiswal
> > > Sent: Monday, March 21, 2005 3:47 PM
> > > To: oracle-l_at_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