Re: No parsing

It would be unusual, but you may have a statement that is making the optimizer run up huge amounts of CPU time in optimizing. (I am assuming that the four things in the main from clause are views, making the query more complex than it seems).

Can you check whether the shadow process is really hung, or whether it is using lots of CPU.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Public Appearances - schedule updated March 9th 2005

• Original Message ----- From: "Brajesh.Jaiswal" <brajesh.kj_at_ness-gsg.com> To: <oracle-l_at_freelists.org> Sent: Monday, March 21, 2005 9:46 PM 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 


--
http://www.freelists.org/webpage/oracle-l