Home » RDBMS Server » Performance Tuning » Procedure is running from more than 3 hrs but do not return the results
Procedure is running from more than 3 hrs but do not return the results [message #300378] Fri, 15 February 2008 04:37 Go to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
My user report that the procedure is running from more than 3 hrs but still did not return the results.
It was working fine earlier.

kindly have a look onto the procedure below.



CREATE OR REPLACE PROCEDURE Rp_Alert_Test_Sp
AS
/* Declaration of all the variables used in the Procedure */
V_BillStartDate DATE ;
V_BillStopDate DATE ;
V_BillCycleNo NUMBER(5);

BEGIN

EXECUTE IMMEDIATE 'TRUNCATE TABLE Rp_Alert_TEST' ;
EXECUTE IMMEDIATE 'TRUNCATE TABLE RP_TEMP_ALERTSERVICE_TEST' ;
EXECUTE IMMEDIATE 'TRUNCATE TABLE RP_TEMP2_ALERT_TEST' ;

-- Inserting all the accounts whose status is active or temporary deactivation
/*
CREATE TABLE Rp_Alert_TEST
(
ACCOUNTNUMBER NUMBER(20),
CUSTOMERNAME VARCHAR2(152 BYTE),
IMSI VARCHAR2(500 BYTE),
MSISDN VARCHAR2(50 BYTE),
CELL_CITY_CODE VARCHAR2(50 BYTE),
PLANNAME VARCHAR2(50 BYTE),
STATUS VARCHAR2(10 BYTE),
INVOICEAMOUNT NUMBER(16,2),
PAYMENT NUMBER(20,2),
POSTBILLCREDITADJUSTMENT NUMBER(16,2),
POSTBILLDEBITADJUSTMENT NUMBER(16,2),
REVERSALPAYMENT NUMBER(20,2),
UNBILLED_USAGE NUMBER(20,2),
PAYBLEAMOUNT NUMBER(20,2),
CREDITLIMIT NUMBER(16,2),
SECURITYDEPOSIT NUMBER(16,2),
USAGEPERCENTAGE NUMBER(16,2),a
REPORTDATE DATE,
ADVANCEPAYMENT NUMBER(20,2),
PREBILLCREDITADJUSTMENT NUMBER(16,2),
PREBILLDEBITADJUSTMENT NUMBER(16,2)
***********************RP_TMP_ALERTSERVICE*********************
CREATE TABLE RP_TMP_ALERTSERVICE
(
ACCOUNTNO NUMBER(20),
CUSTOMERNAME VARCHAR2(152 BYTE),
IMSI VARCHAR2(500 BYTE),
MSISDN VARCHAR2(50 BYTE),
CELL_CITY_CODE VARCHAR2(50 BYTE),
PLANNAME VARCHAR2(50 BYTE),
STATUS VARCHAR2(10 BYTE)
)

********************RP_TMP2_ALERT*******************
CREATE TABLE RP_TMP2_ALERT
(
ACCOUNTNUMBER NUMBER(20),
INVOICEAMOUNT NUMBER(16,2),
PAYMENT NUMBER(20,2),
POSTBILLCREDITADJUSTMENT NUMBER(16,2),
POSTBILLDEBITADJUSTMENT NUMBER(16,2),
REVERSALPAYMENT NUMBER(20,2),
UNBILLED_USAGE NUMBER(20,2),
CREDITLIMIT NUMBER(16,2),
SECURITYDEPOSIT NUMBER(16,2),
ADVANCEPAYMENT NUMBER(20,2),
PREBILLCREDITADJUSTMENT NUMBER(16,2),
PREBILLDEBITADJUSTMENT NUMBER(16,2)
)


*/
INSERT INTO RP_TEMP_ALERTSERVICE_TEST
(
ACCOUNTNO,MSISDN, PLANNAME, IMSI , CELL_CITY_CODE, CUSTOMERNAME, STATUS,MSISDNSTARTDATE
)
SELECT X.ACCOUNTNO,X.MSISDN,X.PLANNAME,X.IMSI,Y.CELL_CITY_CODE,Y.CUSTOMERNAME,DECODE(X.STATUS,'1','Active','3','TD')STATUS,X.MSISDNSTARTDATE
FROM
(
/*
SELECT A.ACCOUNTNO ACCOUNTNO,A.SERVICENO MSISDN,B.OFFERINGNAME PLANNAME,A.NETWORKIDENTIFIERNO IMSI,A.STATUS STATUS ,A.MSISDNSTARTDATE FROM
(
SELECT ACCOUNTNO,
CASE WHEN NOC = 1 THEN SERVICENO ELSE
LPAD(NOC,10,'0') END SERVICENO,NETWORKIDENTIFIERNO,STATUS,DECODE(NOC,'1',startdate)msisdnstartdate
FROM (
SELECT ACCOUNTNO,MAX(SERVICENO)SERVICENO,COUNT(*) NOC,MAX(NETWORKIDENTIFIERNO)NETWORKIDENTIFIERNO,MAX(STATUS)STATUS,startdate
FROM
(SELECT ACCOUNTNO,SERVICENO,NETWORKIDENTIFIERNO,STATUS,startdate FROM CC_ACCOUNTSERVICE
WHERE PRODUCTLINEID=1 AND STOPDATE IS NULL AND STATUS IN (1,3)
)
GROUP BY ACCOUNTNO,startdate)
) A ,*/
SELECT A.ACCOUNTNO ACCOUNTNO,A.SERVICENO MSISDN,B.OFFERINGNAME PLANNAME,A.NETWORKIDENTIFIERNO IMSI,A.STATUS STATUS ,A.MSISDNSTARTDATE FROM
(
SELECT ACCOUNTNO,
CASE WHEN NOC = 1 THEN SERVICENO ELSE
LPAD(NOC,10,'0') END SERVICENO,NETWORKIDENTIFIERNO,STATUS,DECODE(NOC,'1',startdate)msisdnstartdate
FROM (
SELECT ACCOUNTNO,SERVICENO, NOC,NETWORKIDENTIFIERNO,STATUS,startdate
FROM
(SELECT DISTINCT A.ACCOUNTNO,DECODE(B.NOC,1,A.SERVICENO,NULL) SERVICENO,B.NOC,
DECODE(B.NOC,1,A.NETWORKIDENTIFIERNO,NULL) NETWORKIDENTIFIERNO,
DECODE(B.NOC,1,A.STATUS,NULL) STATUS,DECODE(B.NOC,1,A.startdate,NULL) startdate
FROM CC_ACCOUNTSERVICE A,
(SELECT ACCOUNTNO,COUNT(*) NOC FROM CC_ACCOUNTSERVICE WHERE PRODUCTLINEID=1 AND STOPDATE IS NULL AND STATUS IN (1,3) GROUP BY ACCOUNTNO) B
WHERE A.PRODUCTLINEID=1 AND A.STOPDATE IS NULL AND A.STATUS IN (1,3) AND A.ACCOUNTNO = B.ACCOUNTNO
))
) A,
(SELECT ACCOUNTNO,SERVICENO,OFFERINGNAME FROM (
SELECT ACCOUNTNO,SERVICENO,OFFERINGNAME FROM CC_ACCOUNTSERVICEOFFERING
WHERE OFFERINGTYPEID = 2 AND PRODUCTLINEID = 1 AND STATUS IN (1,3) AND STOPDATE IS NULL
))B
WHERE A.ACCOUNTNO = B.ACCOUNTNO (+)
AND A.SERVICENO = B.SERVICENO (+)
ORDER BY A.ACCOUNTNO
)X,
(
SELECT DISTINCT /* PARALLEL (A, 2) NOLOGGING */ SYSDATE REPORTDATE ,
A.ACCOUNTNO ACCOUNTNUMBER,
B.ACCOUNTNAME CUSTOMERNAME,
E.BILLABLEACCOUNTNO BILLABLEACCOUNTNUMBER,
F.BILLGROUPID BILL_GROUP ,
E.SALESAGENTCD CELL_CITY_CODE
FROM
(SELECT A.ACCOUNTNO FROM CC_ACCOUNTSERVICE A GROUP BY A.ACCOUNTNO HAVING COUNT(*)>=1)A,
CC_ACCOUNTSERVICE B, CC_ACCOUNT E , SA_BILLGROUP F
WHERE
A.ACCOUNTNO=B.ACCOUNTNO
AND A.ACCOUNTNO=E.ACCOUNTNO AND
B.STARTDATE IS NOT NULL
AND B.STOPDATE IS NULL
AND B.ACCOUNTNO = E.ACCOUNTNO
AND E.BILLGROUPID = F.BILLGROUPID
AND B.PRODUCTLINEID = 1
AND E.BILLGROUPID =105
AND B.STATUS IN (1,3)
)Y
WHERE X.ACCOUNTNO=Y.ACCOUNTNUMBER;

-- Find out new Bill Start Date
SELECT d.STOPDATE+1
INTO V_BillStartDate
FROM SA_BILLGROUP a, SA_BILLGROUPCYCLE b , SA_BILLCYCLEPERIOD c, SA_BILLPERIOD d
WHERE a.BILLGROUPID = b.BILLGROUPID
AND a.BILLGROUPCYCLENO = c.BILLCYCLENO
AND b.BILLCYCLEID = c.BILLCYCLEID
AND c.BILLPERIODID = d.BILLPERIODID
AND a.BILLGROUPID = 105;

-- Bill Stop Date
SELECT TRUNC(SYSDATE)
INTO V_BillStopDate
FROM dual;


INSERT INTO RP_TEMP2_ALERT_TEST
(
ACCOUNTNUMBER, INVOICEAMOUNT, PAYMENT, POSTBILLCREDITADJUSTMENT, POSTBILLDEBITADJUSTMENT, REVERSALPAYMENT, CREDITLIMIT, SECURITYDEPOSIT,CREDITPROPERTY,
ADVANCEPAYMENT, PREBILLCREDITADJUSTMENT, PREBILLDEBITADJUSTMENT,
UNBILLED_USAGE, WAIVEDAMOUNT,PAYBLEAMOUNT
)

SELECT Z.ACCOUNTNO,G.INVOICEAMOUNT ,A.PAYMENT,D.POSTCRDADJ,C.POSTDEBITADJ, H.REVERSALPAYMENT,
I.CREDITLIMIT,J.SECURITYDEPOSIT,Y.CREDITPROPERTY,
B.ADVANCEPAYMENT ,E.PRECRDADJ,F.PREDEBADJ,K.UNBILLEDUSAGE,S.WAIVEDAMOUNT,
( NVL(G.INVOICEAMOUNT,0) - NVL(A.PAYMENT,0)
- NVL(E.PRECRDADJ,0)+ NVL(F.PREDEBADJ,0)
- NVL(D.POSTCRDADJ,0)+ NVL(C.POSTDEBITADJ,0)
+ NVL(K.UNBILLEDUSAGE,0) - NVL(S.WAIVEDAMOUNT,0)) PAYBLEAMOUNT
FROM
--G.INVOICEOUTSTANDINGAMOUNT,G.ALLOCATEDAMOUNT , ((G.INVOICEAMOUNT - NVL(G.ALLOCATEDAMOUNT,0) + NVL(C.POSTDEBITADJ,0) - NVL(D.POSTCRDADJ,0)) - G.INVOICEOUTSTANDINGAMOUNT ) FROM
(SELECT ACCOUNTNO, ROUND(SUM(PAYMENTAMOUNT),2) PAYMENT FROM AP_PAYMENT
WHERE PAYMENTSTATUS =1 AND ALLOCATIONTYPE!=0
GROUP BY ACCOUNTNO )A,
(SELECT ACCOUNTNO, ROUND(SUM( PAYMENTBALANCE),2) ADVANCEPAYMENT FROM AP_ADVANCEPAYMENT
GROUP BY ACCOUNTNO ) B,
(SELECT ACCOUNTNO, ROUND(SUM(ADJUSTMENTAMOUNT),2) POSTDEBITADJ FROM AP_ACCOUNTADJUSTMENT
WHERE ADJUSTMENTTYPE = 2 AND ADJUSTMENTCATEGORY = 2
GROUP BY ACCOUNTNO )C,
(SELECT ACCOUNTNO, ROUND(SUM(ADJUSTMENTAMOUNT),2) POSTCRDADJ FROM AP_ACCOUNTADJUSTMENT
WHERE ADJUSTMENTTYPE = 1 AND ADJUSTMENTCATEGORY = 2
GROUP BY ACCOUNTNO )D,
(SELECT ACCOUNTNO, ROUND(SUM(ADJUSTMENTAMOUNT),2) PRECRDADJ FROM AP_ACCOUNTADJUSTMENT
WHERE ADJUSTMENTTYPE = 1 AND ADJUSTMENTCATEGORY = 1 AND ADJUSTMENTDATE BETWEEN V_BillStartDate AND V_BillStopDate
GROUP BY ACCOUNTNO )E,
(SELECT ACCOUNTNO, ROUND(SUM(ADJUSTMENTAMOUNT),2) PREDEBADJ FROM AP_ACCOUNTADJUSTMENT
WHERE ADJUSTMENTTYPE = 2 AND ADJUSTMENTCATEGORY = 1 AND ADJUSTMENTDATE BETWEEN V_BillStartDate AND V_BillStopDate
GROUP BY ACCOUNTNO )F,
( SELECT X.ACCOUNTNO,ROUND(SUM(X.INVOICEAMOUNT),2) INVOICEAMOUNT FROM AP_INVOICE X
WHERE invoicetypeid !=3 GROUP BY X.ACCOUNTNO ) G,
(SELECT ACCOUNTNO, ROUND(SUM(PAYMENTAMOUNT),2) REVERSALPAYMENT FROM AP_PAYMENT
WHERE PAYMENTSTATUS IN (3,4) AND ALLOCATIONTYPE!=0
GROUP BY ACCOUNTNO ) H,
(SELECT b.accountno,q.CREDITTHRESHOLD CREDITLIMIT FROM cr_riskcategorymaster q , CC_ACCOUNT b
WHERE b.CREDITRATING BETWEEN q.LOWERSCORE AND q.UPPERSCORE AND b.billgroupid= 105 ) I,
(SELECT ACCOUNTNO,ROUND(SUM(PAIDDEPOSITAMOUNT),2) SECURITYDEPOSIT FROM AP_COLLECTEDDEPOSIT
WHERE REFUNDSTATUS=0
GROUP BY ACCOUNTNO) J,
( SELECT accountno, PROPERTYVALUE CREDITPROPERTY FROM CC_ACCOUNTPROPERTY WHERE propertyid=18 )Y,
(SELECT ACCOUNTNO,NVL(SUM(WAIVEDAMOUNT),0) WAIVEDAMOUNT FROM AP_INVOICEWAIVER WHERE TRUNC(WAIVEDDATE) BETWEEN V_BillStartDate AND V_BillStopDate
GROUP BY ACCOUNTNO)S,
RP_TEMP_ALERTSERVICE_TEST Z,
/* (SELECT ACCOUNTNO,ROUND(SUM(USAGECHARGE),2) UNBILLEDUSAGE FROM AC_BILLINGPOSTED
WHERE UOMID = 100 AND ACCUMULATEDDATE BETWEEN V_BillStartDate AND V_BillStopDate
GROUP BY ACCOUNTNO) K*/
( SELECT ACCOUNTNO,SUM(BILLABLEAMOUNT) UNBILLEDUSAGE FROM
(SELECT ACCOUNTNO,NVL(ROUND(SUM(BILLABLEAMOUNT),2),0) BILLABLEAMOUNT FROM AC_GSMPOSTED
WHERE TRUNC(ACCUMULATEDDATE) BETWEEN V_BillStartDate AND V_BillStopDate
GROUP BY ACCOUNTNO
UNION ALL
SELECT ACCOUNTNO,NVL(ROUND(SUM(BILLABLEAMOUNT),2),0) BILLABLEAMOUNT FROM AC_GSMSMSPOSTED
WHERE TRUNC(ACCUMULATEDDATE) BETWEEN V_BillStartDate AND V_BillStopDate
GROUP BY ACCOUNTNO
UNION ALL
SELECT ACCOUNTNO,NVL(ROUND(SUM(BILLABLEAMOUNT),2),0) BILLABLEAMOUNT FROM AC_GSMROAMPOSTED
WHERE TRUNC(ACCUMULATEDDATE) BETWEEN V_BillStartDate AND V_BillStopDate
GROUP BY ACCOUNTNO
UNION ALL
SELECT ACCOUNTNO,NVL(ROUND(SUM(BILLABLEAMOUNT),2),0) BILLABLEAMOUNT FROM AC_GPRSPOSTED
WHERE TRUNC(ACCUMULATEDDATE) BETWEEN V_BillStartDate AND V_BillStopDate
GROUP BY ACCOUNTNO)
GROUP BY ACCOUNTNO)K
WHERE Z.ACCOUNTNO = A.ACCOUNTNO (+)
AND Z.ACCOUNTNO = B.ACCOUNTNO (+)
AND Z.ACCOUNTNO = C.ACCOUNTNO (+)
AND Z.ACCOUNTNO = D.ACCOUNTNO (+)
AND Z.ACCOUNTNO = E.ACCOUNTNO (+)
AND Z.ACCOUNTNO = F.ACCOUNTNO (+)
AND Z.ACCOUNTNO = G.ACCOUNTNO (+)
AND Z.ACCOUNTNO = H.ACCOUNTNO (+)
AND Z.ACCOUNTNO= I.ACCOUNTNO(+)
AND Z.ACCOUNTNO = J.ACCOUNTNO (+)
AND Z.ACCOUNTNO = S.ACCOUNTNO (+)
AND Z.ACCOUNTNO = K.ACCOUNTNO (+)
AND Z.ACCOUNTNO = Y.ACCOUNTNO (+);

INSERT INTO RP_ALERT_TEST
(
REPORTDATE,ACCOUNTNUMBER, MSISDN,PLANNAME,IMSI,CELL_CITY_CODE, CUSTOMERNAME,STATUS,MSISDNSTARTDATE,
INVOICEAMOUNT, PAYMENT, POSTBILLCREDITADJUSTMENT, POSTBILLDEBITADJUSTMENT, REVERSALPAYMENT,
CREDITLIMIT, SECURITYDEPOSIT,CREDITPROPERTY, ADVANCEPAYMENT,PREBILLCREDITADJUSTMENT, PREBILLDEBITADJUSTMENT,
UNBILLED_USAGE, WAIVEDAMOUNT,PAYBLEAMOUNT

)
SELECT DISTINCT /* parallel (a, 2) nologging */
SYSDATE ReportDate,M.ACCOUNTNO,M.MSISDN,M.PLANNAME,M.IMSI,M.CELL_CITY_CODE,M.CUSTOMERNAME,M.STATUS,M.MSISDNSTARTDATE,
N.INVOICEAMOUNT,N.PAYMENT,N.POSTBILLCREDITADJUSTMENT,N.POSTBILLDEBITADJUSTMENT,N.REVERSALPAYMENT,
N.CREDITLIMIT,N.SECURITYDEPOSIT,N.CREDITPROPERTY,N.ADVANCEPAYMENT,N.PREBILLCREDITADJUSTMENT,N.PREBILLDEBITADJUSTMENT,
NVL(N.UNBILLED_USAGE,0),N. WAIVEDAMOUNT,NVL(N.PAYBLEAMOUNT,0)
FROM RP_TEMP_ALERTSERVICE_TEST M,RP_TEMP2_ALERT_TEST N
WHERE M.ACCOUNTNO=N.ACCOUNTNUMBER(+);

--------CALCULATE PREBILLADJUSTMENT--------------------------------------------
/* UPDATE Rp_Alert_TEST a
SET a.PREBILLCREDITADJUSTMENT = (SELECT ROUND(SUM(b.ADJUSTMENTAMOUNT),2)
FROM AP_ACCOUNTADJUSTMENT b
WHERE b.ACCOUNTNO = a.ACCOUNTNUMBER
AND b.ADJUSTMENTTYPE = 1
AND b.ADJUSTMENTCATEGORY = 1
AND b.ADJUSTMENTDATE BETWEEN V_BillStartDate AND V_BillStopDate ),
a.PREBILLDEBITADJUSTMENT = (SELECT ROUND(SUM(b.ADJUSTMENTAMOUNT),2)
FROM AP_ACCOUNTADJUSTMENT b
WHERE b.ACCOUNTNO = a.ACCOUNTNUMBER
AND b.ADJUSTMENTTYPE = 2
AND b.ADJUSTMENTCATEGORY = 1
AND b.ADJUSTMENTDATE BETWEEN V_BillStartDate AND V_BillStopDate )
WHERE (ACCOUNTNUMBER) IN (SELECT ACCOUNTNO
FROM AP_ACCOUNTADJUSTMENT
WHERE ADJUSTMENTTYPE IN (1,2)
AND ADJUSTMENTCATEGORY = 1
AND ADJUSTMENTDATE BETWEEN V_BillStartDate AND V_BillStopDate );*/

--CALCULATE UNBILLEDUSAGE---------------------------------------
/* UPDATE Rp_Alert_TEST a
SET a.UNBILLED_USAGE=(SELECT ROUND(SUM(USAGECHARGE),2) UNBILLEDUSAGE FROM AC_BILLINGPOSTED b
WHERE a.accountnumber=b.ACCOUNTNO AND UOMID = 100 AND ACCUMULATEDDATE BETWEEN V_BillStartDate AND V_BillStopDate)
WHERE (ACCOUNTNUMBER) IN (SELECT ACCOUNTNO
FROM AC_BILLINGPOSTED b
WHERE UOMID = 100
AND ACCUMULATEDDATE BETWEEN V_BillStartDate AND V_BillStopDate );
-- Calculate Payable Amount
UPDATE Rp_Alert_TEST a
SET PAYBLEAMOUNT= NVL(INVOICEAMOUNT,0) - NVL(PAYMENT,0) - NVL(ADVANCEPAYMENT,0)
- NVL(PREBILLCREDITADJUSTMENT,0)+ NVL(PREBILLDEBITADJUSTMENT,0)
- NVL(POSTBILLCREDITADJUSTMENT,0)+ NVL(POSTBILLDEBITADJUSTMENT,0)
+ NVL(REVERSALPAYMENT,0)+ NVL(UNBILLED_USAGE,0) ;*/


UPDATE RP_ALERT_TEST a
SET UsagePercentage = (NVL(PaybleAmount,0) / NVL(CREDITLIMIT,0)) *100
WHERE NVL(CREDITLIMIT,0) > 0;


COMMIT;

END Rp_Alert_Test_Sp;
/


Highly Appreciate your valuable time spent on the issue.

Thank you.

Regards.
MN
Re: Procedure is running from more than 3 hrs but do not return the results [message #300386 is a reply to message #300378] Fri, 15 February 2008 04:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code and post with line numbers.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Also read How to Identify Performance Problem and Bottleneck .

Regards
Michel
Re: Procedure is running from more than 3 hrs but do not return the results [message #300431 is a reply to message #300378] Fri, 15 February 2008 06:46 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
Thanks for your response.

The query is finally returning the results in 2 hrs during peek hours, and the result is 30,000 rows.
And during off peek hours,it is returning the same results in 1 hour.


please suggest!

Thanks
Re: Procedure is running from more than 3 hrs but do not return the results [message #300444 is a reply to message #300431] Fri, 15 February 2008 07:29 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please follow the rules.
./fa/1707/0/

Regards
Michel

[Updated on: Fri, 15 February 2008 07:30]

Report message to a moderator

Previous Topic: A query with one additional condition slows down
Next Topic: sql tuning
Goto Forum:
  


Current Time: Thu Dec 08 22:00:54 CST 2016

Total time taken to generate the page: 0.14945 seconds