Re: No parsing

  • From: Paul Drake <bdbafh@xxxxxxxxx>
  • To: brajesh.kj@xxxxxxxxxxxx
  • Date: Mon, 21 Mar 2005 17:42:13 -0500

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@xxxxxxxxxxxx> wrote:
> Larry,
> 
> I dont think there was any major change in the initora.
> 
> Regards
> Brajesh
> 
> ----- Original Message -----
> From: "Wolfson Larry - lwolfs" <lawrence.wolfson@xxxxxxxxxx>
> To: <brajesh.kj@xxxxxxxxxxxx>; <oracle-l@xxxxxxxxxxxxx>
> 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> > On Behalf Of Brajesh.Jaiswal
> > Sent: Monday, March 21, 2005 3:47 PM
> > To: oracle-l@xxxxxxxxxxxxx
> > 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
> >
> >
> > --
> > //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.
> >
> > --
> > //www.freelists.org/webpage/oracle-l
> >
> 
> --
> //www.freelists.org/webpage/oracle-l
> 


-- 
#/etc/init.d/init.cssd stop
# f=ma, divide by 1, convert to moles.
--
//www.freelists.org/webpage/oracle-l

Other related posts: