Re: ACCELERATING STORE PROCEDURE
Date: Fri, 20 Feb 2009 13:07:46 +0000 (UTC)
Message-ID: <gnm9v2$vlo$1_at_solani.org>
On Thu, 19 Feb 2009 11:29:14 -0800, Jorge Reyes wrote:
> Hi everyone,
Hi. Please do not use all capitals in your title. It's an equivalent to yelling and is considered to be extremely rude.
>
> 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, you're copying from source to destination. It's not that unusual.
>
> 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)
That is not enough. The best thing to do would be to execute the procedure with the event 10046 turned on to level 12 and then analyze the output with Orasrp.
>
> 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;
Why do you have "select distinct"? The "distinct" clause will always cause sorting. CDR=="Call Detail Record", this is some kind of telecommunication service? If so, there are no two CDR's that are exactly the same, there are no duplicates. PABX or switch will simply not produce any duplicates. No "distinct" clause is necessary. If it is, you have problems with processing your data, even before it comes to your procedure.
> CURSOR L_ISDN IS SELECT NUMERIC_REASON_CODE FROM
> om_db.CDRS_LISTED_ISDN_CAUSE_CODES ORDER BY NUMERIC_REASON_CODE;
Why would you have "order by" in a cursor declaration?
>
> 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');
This is one of the 7 deadly sins: thou shalt not use "select count(*)" to establish existence or the lack of it.
> ELSE
> SELECT TO_DATE(TO_CHAR(MIN(START_TIME),'YYYY-MM-DD HH24') || ':
> 00:00','YYYY-MM-DD HH24:MI:SS')
Why are you converting DATE column to CHAR and then back again to the DATE
value?
> 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);
This is going to be a Cartesian join. Are you sure that you want that?
>
> -- 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');
CDR should be split and processed before it's inserted into the database.
RDBMS is not a good tool for text processing. You should split
CALLED_PARTY_ON_DEST using some general purpose language like Perl, C or
Java. Expressions like
AND CAST(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10) AS NUMBER(*,0))
between B.NIR_INICIAL AND B.NIR_FINAL
are extremely hard to process. You should add a separate numeric column and insert the following value into it by writing a trigger.
CAST(SUBSTR(A.CALLED_PARTY_ON_DEST,-10,10) AS NUMBER(*,0)) You could also use some Perl to extract it from the CDR during the initial processing of the PABX files. That would be a less costly option, in terms of machine resources.
Do you know Juan Pacheco Reyes?
-- http://mgogala.freehostia.comReceived on Fri Feb 20 2009 - 07:07:46 CST