Re: get time end of a process can capture(Stored Procedure)

On Aug 19, 12:18 pm, EliasFigueroa <Eliasf..._at_gmail.com> wrote:
> As I get time end of a process
> can capture? cpu time consumed (%)
> find time elapsed (Oracle 8i)
> I have this SP ,I catch the same time
> Thank you
>
> CREATE OR REPLACE PROCEDURE SPG_EXAMPLE
>  ( p_fecha       IN VARCHAR2,
>    p_descripcion OUT VARCHAR2,
>    p_estado      OUT NUMBER,
>    P_registros   OUT NUMBER,
>    p_message     OUT VARCHAR2,
>    p_inicio      OUT DATE
>    p_fin         OUT DATE
>
>  )
> IS
>     TYPE T_numero  IS TABLE OF AGENCIAS.NUMERO%type;
>     TYPE T_indicador IS TABLE OF AGENCIAS.INDICADOR%type;
>     v_numero      T_numero;
>     v_indicador   T_indicador;
>     ld_inicio     DATE;
>     ld_fin        DATE;
>     ld_diff       DATE;
>     lv_SQL2       VARCHAR2(2000);
>     lv_Count      NUMBER;
>     v_errornumber CHAR(10);
>     v_errortext   CHAR(100);
>     v_descripción VARCHAR2(2000) :='CARGA AGENCIAS';
> BEGIN
>     ld_inicio := sysdate;===> STARTTIME
>     p_inicio  := ld_inicio;
>     p_estado:= 0;
>     p_message:='';
>     p_descripcion:=v_descripción;
>     lv_SQL2:='select count(ROWID) from agencias2 ' ;
>
> select numero,indicador
>
> BULK COLLECT INTO v_numero ,v_indicador
> FROM agencias;
>
>         FORALL i IN v_numero.FIRST..v_numero.LAST
>          INSERT  INTO  agencias2
>                     (numero, indicador,
>                      usuariocreacion, fechacreacion,
>                      usuarioactualizacion, fechaactualizacion)
>
> VALUES( v_numero(i) ,v_indicador(i),'PRUEBA',SYSDATE,'PRUEBA',SYSDATE);
>                   COMMIT;
>         p_estado := 1;
>
>          EXECUTE IMMEDIATE lv_SQL2 INTO lv_Count;
>          P_registros:=lv_Count;
>           p_message:=('Se Insertaron un Total de :'|| lv_Count||'
> Registros');
>           ld_fin := sysdate; --- end time
>
> EXCEPTION
>   WHEN others THEN
>          ROLLBACK;
>          v_errornumber := SQLCODE;
>          v_errortext   := substr(SQLERRM,1,100);
>          Raise_application_error(-20002, 'Error al insertar:' ||
> v_errornumber || v_errortext);
> END SPGT147_CARGAAGENCIAS;
1 - if you can run the stored procedure from SQLPlus would the set timing on command be good enough?
2 - Have you looked at the SQL trace facility? 3 - You could modify the code to grap the timestamp at the start of the procedure and again at the end of the procedure where you could either display the information or store it to a table.

HTH -- Mark D Powell -- Received on Tue Aug 19 2008 - 12:05:08 CDT