Home -> Usenet -> c.d.o.server -> ACCELERATING STORE PROCEDURE

ACCELERATING STORE PROCEDURE

From: Jorge Reyes <jorg_reyes_at_hotmail.com>
Date: Thu, 19 Feb 2009 11:29:14 -0800 (PST)
Message-ID: <93be6817-3f0f-4282-b74b-312b558e8b6b_at_v15g2000vbb.googlegroups.com> 





Hi everyone,



Hoping you are ok, i need your help to solve this query in order to
make faster an stored procedure, it goes likes this:



Tables Sources -> SP procesing some data -> Tables Destiny (Results)



So my source's tables are


OM_DB.CDRS (86 fields and 30,000 rows average)
OM_DB.COFETEL (8 fields and 62,000 rows average)
OM_DB.CDRS_LISTED_ISDN_CAUSE_CODES (4 fields, 54 rows and this is a


catalog, no index)



Those source tables with this indexes



CREATE INDEX PSA.CDRS_IX



ON OM_DB.CDRS(START_TIME,CALLED_PARTY_ON_DEST,CALL_SOURCE_REGID)


CREATE INDEX PSA.SX_CDRS



ON OM_DB.CDRS(ISDN_CAUSE_CODE,CALL_DURATION_INT)


CREATE INDEX PSA.COFETEL_IX



ON OM_DB.COFETEL(MODALIDAD)


CREATE INDEX PSA.COFETEL_SX



ON OM_DB.COFETEL(NIR_INICIAL,NIR_FINAL)


The destiny's tables are



om_db.CDRS_FINAL1 (9 fields, 5 rows per hour)
om_db.CDRS_ISDN_CAUSE_CODE (5 fields, 50 rows per hour)



Those destiny tables with this indexes



CREATE UNIQUE INDEX PSA.IX_CDRS_FINAL1



ON OM_DB.CDRS_FINAL1(PMM_DATETIME,CALL_SOURCE_REGID)


CREATE UNIQUE INDEX PSA.IX_CDRS_ISDN_CAUSE_CODE

ON OM_DB.CDRS_ISDN_CAUSE_CODE



(PMM_DATETIME,CALL_SOURCE_REGID,ISDN_CAUSE_CODE)



I need run this procedure each 15 minutes and there are 30,000 rows
average on source OM_DB.CDRS, the execution time often exceed 15
minutes so i need to make better querys this is the sp:



Note: The DISTINCT(CALL_SOURCE_REGID) is 5 to 10 rows.



CREATE OR REPLACE PROCEDURE OM_DB.PR_MAKE_CDRS_FINAL1

IS

    vPMM_DATETIME        TIMESTAMP(3);
    vMAX_PMM_DATETIME    TIMESTAMP(3);
    vEND_DATETIME        TIMESTAMP(3);




    vCALL_SOURCE_REGID   VARCHAR2(30);

    vTOTAL_CDRS_CLIENTE  INT;
    vMIN_VALID_COFETEL   INT;
    vMIN_INVALID_COFETEL INT;
    vMOVILES_VALIDOS     INT;
    vMOVILES_INVALIDOS   INT;
    vFIJOS_VALIDOS       INT;
    vFIJOS_INVALIDOS     INT;
    vNUMERIC_REASON_CODE INT;
    vTOTAL_ISDN_CAUSE    INT;
    vCALL_DURATION_INT   INT;
    v_rows_processed     INT;
    count_violated       INT;





    CURSOR CL_CDR IS SELECT DISTINCT(CALL_SOURCE_REGID) FROM

om_db.CDRS WHERE CALL_SOURCE_REGID IS NOT NULL;


    CURSOR L_ISDN IS SELECT NUMERIC_REASON_CODE FROM

om_db.CDRS_LISTED_ISDN_CAUSE_CODES ORDER BY NUMERIC_REASON_CODE;



BEGIN



    dbms_output.enable(40000);


    dbms_output.put_line('Inicio de procedimiento: ' || TO_CHAR


(sysdate, 'YYYY-MM-DD HH24:MI:SS'));




    
  
  •   Valida si existe informacion en la tabla de origen
    count_violated := 0;
    SELECT COUNT(*) INTO count_violated FROM om_db.CDRS;
    IF (count_violated=0) THEN
        dbms_output.put_line('No hay informacion en la tabla de
origen');
    ELSE

        SELECT TO_DATE(TO_CHAR(MIN(START_TIME),'YYYY-MM-DD HH24') || ':

00:00','YYYY-MM-DD HH24:MI:SS')

        INTO vPMM_DATETIME FROM om_db.CDRS;
        vEND_DATETIME := vPMM_DATETIME + 1/24;
        SELECT TO_DATE(TO_CHAR(MAX(START_TIME),'YYYY-MM-DD HH24') || ':

59:59','YYYY-MM-DD HH24:MI:SS')

        INTO vMAX_PMM_DATETIME FROM om_db.CDRS;


  
      
    
    •   ********************* PARA REPROCESAMIENTO UNICAMENTE


      
    
    •   ESPECIFICAR FECHA INICIAL Y FECHA FINAL

        --vPMM_DATETIME := '2009-01-01 00:53:00';
        --vEND_DATETIME := '2009-01-20 15:53:00';
        --

      
        WHILE (vPMM_DATETIME <= vMAX_PMM_DATETIME) LOOP
            OPEN CL_CDR;

	        FETCH CL_CDR INTO vCALL_SOURCE_REGID;
            WHILE (CL_CDR%FOUND) LOOP

                vTOTAL_CDRS_CLIENTE := 0;
                vMIN_VALID_COFETEL := 0;
                vMIN_INVALID_COFETEL := 0;
                vMOVILES_VALIDOS := 0;
                vMOVILES_INVALIDOS := 0;
                vFIJOS_VALIDOS := 0;
                vFIJOS_INVALIDOS := 0;
                vTOTAL_ISDN_CAUSE := 0;
                vCALL_DURATION_INT := 0;

    
        
      
      •   Obtener Total de CDRS por cliente
                SELECT COUNT(CALL_SOURCE_REGID) INTO

vTOTAL_CDRS_CLIENTE
                FROM om_db.CDRS
                WHERE START_TIME BETWEEN vPMM_DATETIME AND
vEND_DATETIME
                AND CALL_SOURCE_REGID = vCALL_SOURCE_REGID;


    
      

  
    




                v_rows_processed := 0;
                DELETE om_db.CDRS
                WHERE START_TIME BETWEEN vPMM_DATETIME AND
vEND_DATETIME
                AND CALLED_PARTY_ON_DEST LIKE '%*%'
                AND CALL_SOURCE_REGID = vCALL_SOURCE_REGID;
                v_rows_processed := sql%rowcount;

                vMIN_INVALID_COFETEL := v_rows_processed;

                -- Obtener MIN_VALID_COFETEL
                SELECT COUNT(*) INTO vMIN_VALID_COFETEL FROM (
                SELECT A.*, B.*
                FROM om_db.CDRS A, om_db.cofetel B
                WHERE A.START_TIME BETWEEN vPMM_DATETIME AND
vEND_DATETIME
                AND CAST(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10) AS
NUMBER(*,0)) between B.NIR_INICIAL AND B.NIR_FINAL
                AND A.CALL_SOURCE_REGID = vCALL_SOURCE_REGID);

                -- Calcular el MIN_INVALID_COFETEL
                vMIN_INVALID_COFETEL := vTOTAL_CDRS_CLIENTE -


vMIN_VALID_COFETEL;



    
  
  •   Obtener los MOVILES_VALIDOS
                SELECT COUNT(*) INTO vMOVILES_VALIDOS FROM (
                SELECT A.*, B.*

                FROM om_db.CDRS A, om_db.cofetel B
                WHERE A.START_TIME BETWEEN vPMM_DATETIME AND
vEND_DATETIME
                AND CAST(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10) AS

NUMBER(*,0)) between B.NIR_INICIAL AND B.NIR_FINAL
                AND SUBSTR(A.CALLED_PARTY_ON_DEST,0,6) = '140521'

                AND A.CALL_SOURCE_REGID = vCALL_SOURCE_REGID
                AND B.MODALIDAD = 'CPP');


  
  •   Obtener los MOVILES_INVALIDOS
                SELECT COUNT(*) INTO vMOVILES_INVALIDOS FROM (
                SELECT A.*, B.*

                FROM om_db.CDRS A, om_db.cofetel B
                WHERE A.START_TIME BETWEEN vPMM_DATETIME AND
vEND_DATETIME
                AND CAST(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10) AS

NUMBER(*,0)) between B.NIR_INICIAL AND B.NIR_FINAL
                AND SUBSTR(A.CALLED_PARTY_ON_DEST,0,6) <> '140521'
                AND A.CALL_SOURCE_REGID = vCALL_SOURCE_REGID
                AND B.MODALIDAD = 'CPP');


  
  •   Obtener los FIJOS_VALIDOS
                SELECT COUNT(*) INTO vFIJOS_VALIDOS FROM (
                SELECT A.*, B.*

                FROM om_db.CDRS A, om_db.cofetel B
                WHERE A.START_TIME BETWEEN vPMM_DATETIME AND
vEND_DATETIME
                AND CAST(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10) AS

NUMBER(*,0)) between B.NIR_INICIAL AND B.NIR_FINAL
                AND SUBSTR(A.CALLED_PARTY_ON_DEST,0,5) = '14052'

                AND A.CALL_SOURCE_REGID = vCALL_SOURCE_REGID
                AND B.MODALIDAD IN('FIJO','MPP'));


  
  •   Obtener los FIJOS_INVALIDOS
                SELECT COUNT(*) INTO vFIJOS_INVALIDOS FROM (
                SELECT A.*, B.*

                FROM om_db.CDRS A, om_db.cofetel B
                WHERE A.START_TIME BETWEEN vPMM_DATETIME AND
vEND_DATETIME
                AND CAST(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10) AS

NUMBER(*,0)) between B.NIR_INICIAL AND B.NIR_FINAL
                AND SUBSTR(A.CALLED_PARTY_ON_DEST,0,5) <> '14052'
                AND A.CALL_SOURCE_REGID = vCALL_SOURCE_REGID
                AND B.MODALIDAD IN('FIJO','MPP'));






                v_rows_processed := 0;
                UPDATE om_db.CDRS_FINAL1 SET
                    TOTAL_CDRS_CLIENTE = TOTAL_CDRS_CLIENTE + COALESCE


(vTOTAL_CDRS_CLIENTE,0),

                    MIN_VALID_COFETEL = MIN_VALID_COFETEL + COALESCE


(vMIN_VALID_COFETEL,0),

                    MIN_INVALID_COFETEL = MIN_INVALID_COFETEL +
COALESCE(vMIN_INVALID_COFETEL,0),
                    MOVILES_VALIDOS = MOVILES_VALIDOS + COALESCE


(vMOVILES_VALIDOS,0),

                    MOVILES_INVALIDOS = MOVILES_INVALIDOS + COALESCE


(vMOVILES_INVALIDOS,0),

                    FIJOS_VALIDOS = FIJOS_VALIDOS + COALESCE


(vFIJOS_VALIDOS,0),

                    FIJOS_INVALIDOS = FIJOS_INVALIDOS + COALESCE


(vFIJOS_INVALIDOS,0)

                WHERE PMM_DATETIME = vPMM_DATETIME
                AND CALL_SOURCE_REGID = vCALL_SOURCE_REGID;

                v_rows_processed := sql%rowcount;
			    IF v_rows_processed = 0 THEN
				    count_violated := 0;
                    SELECT COUNT(PMM_DATETIME) INTO count_violated
                    FROM om_db.CDRS_FINAL1
                    WHERE PMM_DATETIME = vPMM_DATETIME
                    AND CALL_SOURCE_REGID = vCALL_SOURCE_REGID;

                    IF (count_violated=0) THEN
                        INSERT INTO om_db.CDRS_FINAL1 VALUES(
                            vPMM_DATETIME,
                            vCALL_SOURCE_REGID,
                            COALESCE(vTOTAL_CDRS_CLIENTE,0),
                            COALESCE(vMIN_VALID_COFETEL,0),
                            COALESCE(vMIN_INVALID_COFETEL,0),
                            COALESCE(vMOVILES_VALIDOS,0),
                            COALESCE(vMOVILES_INVALIDOS,0),
                            COALESCE(vFIJOS_VALIDOS,0),
                            COALESCE(vFIJOS_INVALIDOS,0)
                        );
                    END IF;
                END IF;

                OPEN L_ISDN;
	            FETCH L_ISDN INTO vNUMERIC_REASON_CODE;
                WHILE (L_ISDN%FOUND) LOOP
                    -- Obtener vTOTAL_ISDN_CAUSE
                    SELECT COUNT(ISDN_CAUSE_CODE) INTO
vTOTAL_ISDN_CAUSE
                    FROM om_db.CDRS
                    WHERE START_TIME BETWEEN vPMM_DATETIME AND
vEND_DATETIME
                    AND CALL_SOURCE_REGID = vCALL_SOURCE_REGID
                    AND ISDN_CAUSE_CODE = vNUMERIC_REASON_CODE;

                    -- Obtener vCALL_DURATION_INT de aquellos
registros que sean mayor a 5 seg.
                    SELECT SUM(CALL_DURATION_INT) INTO
vCALL_DURATION_INT
                    FROM om_db.CDRS
                    WHERE START_TIME BETWEEN vPMM_DATETIME AND
vEND_DATETIME
                    AND CALL_SOURCE_REGID = vCALL_SOURCE_REGID
                    AND ISDN_CAUSE_CODE = vNUMERIC_REASON_CODE
                    AND CALL_DURATION_INT >= 5;

                    UPDATE om_db.CDRS_ISDN_CAUSE_CODE SET
                        TOTAL_ISDN_CAUSE = TOTAL_ISDN_CAUSE + COALESCE


(vTOTAL_ISDN_CAUSE,0),

                        CALL_DURATION_INT = CALL_DURATION_INT +
COALESCE(vCALL_DURATION_INT,0)
                    WHERE PMM_DATETIME = vPMM_DATETIME
                    AND CALL_SOURCE_REGID = vCALL_SOURCE_REGID
                    AND ISDN_CAUSE_CODE = vNUMERIC_REASON_CODE;

                    v_rows_processed := sql%rowcount;
			        IF v_rows_processed = 0 THEN
				        count_violated := 0;
                        SELECT COUNT(PMM_DATETIME) INTO count_violated
                        FROM om_db.CDRS_ISDN_CAUSE_CODE
                        WHERE PMM_DATETIME = vPMM_DATETIME
                        AND CALL_SOURCE_REGID = vCALL_SOURCE_REGID
                        AND ISDN_CAUSE_CODE = vNUMERIC_REASON_CODE;

                        IF (count_violated=0) THEN
                            INSERT INTO om_db.CDRS_ISDN_CAUSE_CODE
VALUES(
                                vPMM_DATETIME,
                                vCALL_SOURCE_REGID,
                                vNUMERIC_REASON_CODE,
                                COALESCE(vTOTAL_ISDN_CAUSE,0),
                                COALESCE(vCALL_DURATION_INT,0)
                            );
                        END IF;
                    END IF;

                    FETCH L_ISDN INTO vNUMERIC_REASON_CODE;
                END LOOP;
                CLOSE L_ISDN;

                FETCH CL_CDR INTO vCALL_SOURCE_REGID;
            END LOOP;
            CLOSE CL_CDR;

            DELETE om_db.CDRS
            WHERE START_TIME BETWEEN vPMM_DATETIME AND vEND_DATETIME;

            COMMIT;
            vPMM_DATETIME := vPMM_DATETIME + 1/24;
            vEND_DATETIME := vPMM_DATETIME + 1/24;
        END LOOP;

        EXECUTE IMMEDIATE 'TRUNCATE TABLE om_db.CDRS';
        COMMIT;




    END IF;



    dbms_output.put_line('Fin de procedimiento: ' || TO_CHAR(sysdate,
'YYYY-MM-DD HH24:MI:SS'));



END PR_MAKE_CDRS_FINAL1;



Thanks in advanced
Received on Thu Feb 19 2009 - 13:29:14 CST












Original text of this message