| measuring execution time of queries [message #9149] |
Wed, 22 October 2003 08:22  |
catherine
Messages: 5
Registered: January 2003
|
Junior Member |
|
|
hello
i have twenty select statements already written. i would like to measure the response time of each query on a rapid growing database over a period of time.
what would be a reliable parameter to measure? is there a predefined package or tool already existing from oracle?
any help is welcome, thanx a lot
catherine
|
|
|
|
| Re: measuring execution time of queries [message #9150 is a reply to message #9149] |
Wed, 22 October 2003 08:55  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
A number of ways..
thiru@9.2.0:SQL>set timing on
thiru@9.2.0:SQL>select count(*) from all_objects;
COUNT(*)
----------
29629
Elapsed: 00:00:10.01
-- gives you the elapsed run time.
Also you can get the cpu,elapsed time using TKPROF. This is more reliable .
Have a look at this link for setting up & using TKPROF.
The other parameters you would look here are number of parses,QUERY(consistent reads),array fetches etc..
-Thiru
|
|
|
|
| Re: measuring execution time of queries [message #9152 is a reply to message #9149] |
Wed, 22 October 2003 10:44 |
catherine
Messages: 5
Registered: January 2003
|
Junior Member |
|
|
hello thiru
thanks for your input! if i use the set timing on, how can i save the elapsed time to a parameter and save it in a file? where does oracle store this temporary information?
(as i see it here, i will have no privileges to install tkprof nor accessing generated files on the server)
thanx again for your patience.
greets
catherine
|
|
|
|
|
|
| Re: measuring execution time of queries [message #9154 is a reply to message #9152] |
Wed, 22 October 2003 11:52 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
You can spool in sqlplus and save those files. But if you want to store those values in a table you can use dbms_utility.get_time . It gives the time in 100ths of a sec.
a simple example)
thiru@9.2.0:SQL>variable time1 number;
thiru@9.2.0:SQL>variable time2 number;
thiru@9.2.0:SQL>exec :time1 := dbms_utility.get_time;
PL/SQL procedure successfully completed.
thiru@9.2.0:SQL>select count(*) from dual;
COUNT(*)
----------
1
thiru@9.2.0:SQL>exec :time2 := dbms_utility.get_time;
PL/SQL procedure successfully completed.
thiru@9.2.0:SQL>exec dbms_output.put_line(:time2 - :time1) ;
1218
PL/SQL procedure successfully completed.
thiru@9.2.0:SQL>create table time_t(run_id number,time1 number,time2 number);
Table created.
thiru@9.2.0:SQL>exec insert into time_t select 1,:time1,:time2 from dual;
PL/SQL procedure successfully completed.
thiru@9.2.0:SQL>select * from time_t;
RUN_ID TIME1 TIME2
---------- ---------- ----------
1 37945083 37946301
thiru@9.2.0:SQL>drop table time_t;
Table dropped.
thiru@9.2.0:SQL>create table time_t (run_id number,ela_time number);
Table created.
thiru@9.2.0:SQL>exec insert into time_t select 1,:time2 - :time1 from dual;
PL/SQL procedure successfully completed.
thiru@9.2.0:SQL>select * from time_t;
RUN_ID ELA_TIME
---------- ----------
1 1218
thiru@9.2.0:SQL>
-Thiru
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
