# Thread: how much time consumed

1. Registered User
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. Registered User
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.

3. Registered User
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. Registered User
Join Date
Nov 2003
Location
Bangalore, INDIA
Posts
333
Hi,

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

#### Posting Permissions

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