Re: No parsing

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
>
>
> --
> 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

Other related posts: