Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Posts
    34

    Unanswered: how much time consumed

    Hi All

    We have a stored procedures which has a lot of inserts , updates & deletes & selects.

    We want to actually know how much time is consumed by all the above DML's & selects.

    What is the best & easy way to obtain such statistical info


    Rgrds

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    If you want to get total tioming of your procedure execution, then SET TIMING ON or SET TIME ON (on SQL * PLUS).

    But if you want to get individual timing for each of these statements, then start TRACE on session level for the duration of this procedure's execution and then turn it off after it finishes. Use tkprof utiliy to see the details of each of these SQLs.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  3. #3
    Join Date
    Dec 2003
    Location
    India
    Posts
    12

    Re: how much time consumed

    You can use dbms_utility.get_time to calucate the time upto 1/100 of a second.
    If you want to calculate the time in the format HH24:MIS:FF3 (i.e upto millisecond level) and you database is 9i then you can use the following process in you stored procedure

    declare
    I NUMBER(10);
    time1 timestamp;
    time2 timestamp;
    diff timestamp;
    counter number(10);
    C NUMBER;
    D NUMBER;
    E NUMBER;
    F NUMBER;
    TOTAL VARCHAR2(14);
    begin

    select SYSTIMESTAMP into time1 from DUAL;
    FOR I IN 1..15
    LOOP
    SELECT COUNT(*) INTO COUNTER FROM NAD_ADDRESS;
    END LOOP;
    select SYSTIMESTAMP into time2 from DUAL;
    SELECT
    substr((time2-time1),instr((time2-time1),' ')+10,3) MILLIseconds, substr((time2-time1),instr((time2-time1),' ')+7,2) seconds, substr((time2-time1),instr((time2-time1),' ')+4,2) minutes,
    substr((time2-time1),instr((time2-time1),' ')+1,2) hours
    INTO C,D,E,F FROM DUAL ;
    D:=to_char(D);
    E:=to_char(E);
    F:=to_char(F);
    C:=to_char(C);
    TOTAL:= (F||':'||E||':'||D||':'||C);
    dbms_output.put_line('TOTAL IS '||TOTAL);
    end;

    You can make TOTAL as a out parameter of you stored procedure .






    Originally posted by zulu99
    Hi All

    We have a stored procedures which has a lot of inserts , updates & deletes & selects.

    We want to actually know how much time is consumed by all the above DML's & selects.

    What is the best & easy way to obtain such statistical info


    Rgrds

  4. #4
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    You can use TOAD. It wil show all the basic statistic details.
    SATHISH .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •