4

I'm running queries in PL/SQL Developer. How to find out the running time of sql query in PL/SQL. I am querying specific tables. Like

select * from table_name where customer_id=1;

select * from movie_table where movie_id=8;

While i am using PL/SQL, i want to know the query running time.

Thanks, your help is very much appreciated.

careem
  • 57
  • 1
  • 1
  • 9
  • There is no PL/SQL in your examples –  Apr 16 '14 at 12:09
  • Select sysdate from dual before and after each query. Make sure you run each query twice if you want compile time removed from the equation. – Dan Bracuk Apr 16 '14 at 12:14
  • 1
    Take a look at http://dbaforums.org/oracle/index.php?showtopic=2213 – Hamidreza Apr 16 '14 at 12:18
  • well it works on only count. It's not working on select * from table_name where customer_id=1; and it works only on select count (*) from table_name; – careem Apr 16 '14 at 12:21
  • Good link. I didn't know about that command that outputs the execution time. While the link specifies sql plus, I just verified that it works in sql developer. Hopefully @careem reads it. – Dan Bracuk Apr 16 '14 at 12:26
  • What works only on count(*)? – Dan Bracuk Apr 16 '14 at 12:26
  • This works only on count. `declare i number:=dbms_utility.get_time; j number; begin select max(rownum) into j from table_work; i:=round((dbms_utility.get_time-i)/100,2); dbms_output.put_line(i || ' Sec'); end;` – careem Apr 16 '14 at 12:29
  • 1
    If you read the entire page that @Hamidreza mentions you'll see a really simple way to solve your problem. – Dan Bracuk Apr 16 '14 at 12:35

2 Answers2

10

The simplest way to do this, courtesy of @Hamidreza's link, is like this:

set timing on;
select * from table_name where customer_id=1;

The execution time will appear below the records selected.

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
0

I think you can use dbms_utility.get_time function. 

l_start := dbms_utility.get_time;
select * from table_name where customer_id=1;
select * from movie_table where movie_id=8;
l_end := dbms_utility.get_time;
l_diff := (l_end-l_start)/100;
dbms_output.put_line('Overall Time: '|| l_diff);

Something like this, in brief.

Guneli
  • 1,691
  • 1
  • 11
  • 17