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
Received on Mon Mar 21 2005 - 16:46:31 CST
