RE: No parsing

Brajesh,

                Have you tried the CBO 10053 trace?
That might give you a clue. Especially if you run it on the 9.2.0.4 DB and see a difference.

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