Home » RDBMS Server » Performance Tuning » want to see the time duration of a statement
want to see the time duration of a statement [message #183521] Fri, 21 July 2006 04:02 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I want to see the time taken for any query. I am running the following script in toad...but this is giving the following error.
ORA-06550: line 5, column2:
PLS-00428: an INTO clause is expected in this SELECT statement.

Please advice.


declare
var1 date;
begin
var1 := sysdate;
 SELECT a.memp_code, a.memp_cmpcd, a.memp_divcd, a.memp_depcd, a.memp_loccd,
	 a.MEmp_CatPCd,a.memp_grdcd,a.memp_grpcd, a.memp_design, b.edcode, c.formula,
	 TRIM(d.edhcode) , d.edlnkmast, e.Memp_ITaxBnk, d.RndType
	 FROM MEMPOFF a,EMPED b,EDINT c,EDHEAD d,MEMPPAY e
	 WHERE
	 a.MEmp_Code(+) = e.MEmp_Code
	 AND a.memp_code = 00020027
	 AND c.EDHCode = d.EDHCode
	 AND b.EDCode = c.EDCode
	 AND a.MEmp_code = b.Emp_Code
	 AND (b.FrmDt <=200508 AND ((b.ToDt >= 200508 AND b.ToDt IS NOT NULL)
	 OR b.ToDt IS NULL))
	 AND (c.FrmDt <=200508 AND ((c.ToDt >= 200508 AND c.ToDt IS NOT NULL)
	 OR c.ToDt IS NULL))
	 AND a.MEmp_active <> 0 AND memp_stp<>1
	 ORDER BY a.MEmp_Code, Level_Id;
dbms_output.put_line (sysdate - var1);
end;

[Updated on: Fri, 21 July 2006 04:04]

Report message to a moderator

Re: want to see the time duration of a statement [message #183530 is a reply to message #183521] Fri, 21 July 2006 04:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, the error message is pretty explicit.
You need to provide a list variables to select the data from this query into.
However, if your query returns more than one row, you'd have to use BULK COLLECT to return the data into collections instead.

If you just want a quick and dirty timing mechanism, you can go to SQL*Plus and type
SET TIMING ON

This will tell you the elapsed time during the execution of this statment, but I beleieve it includes the network time as well.
Re: want to see the time duration of a statement [message #183531 is a reply to message #183521] Fri, 21 July 2006 04:35 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
the statement errors, so you cannot get a time for it.
when you use select in a pl/sql block you must use

select column list
INTO variable list
FROM table(s)
where restrictions;

so the first thing you need to do is get the syntax right.

HTH
Jim
Re: want to see the time duration of a statement [message #183604 is a reply to message #183521] Fri, 21 July 2006 09:11 Go to previous messageGo to next message
amcghie
Messages: 35
Registered: March 2005
Location: Sunny Dubai
Member
Or you could use the
dbms_utility.get_time
supplied PLSQL package. For example:

set serveroutput on
DECLARE
  l_start_time PLS_INTEGER;
  l_end_time   PLS_INTEGER;
  r_emp        emp%ROWTYPE;
BEGIN
  --
  l_start_time := dbms_utility.get_time;
  --
  SELECT *
  INTO   r_emp
  FROM   emp
  WHERE  rownum = 1;
  --
  l_end_time := dbms_utility.get_time;
  --
  dbms_output.put_line('Time to Execute (ms): '||(l_end_time-l_start_time));
  --
END;


Hope this helps

Cheers

Andy
Re: want to see the time duration of a statement [message #195593 is a reply to message #183604] Fri, 29 September 2006 10:20 Go to previous messageGo to next message
skn
Messages: 6
Registered: September 2006
Junior Member
hi mona,

try this one also.

SELECT TO_CHAR(sysDATE, 'DD-MON-YYYY HH:MI:SS') into vtext1 FROM dual;
dbms_output.put('time before= ');
dbms_output.put_line(vtext1);

type your query .................


SELECT TO_CHAR(sysDATE, 'DD-MON-YYYY HH:MI:SS') into vtext1 FROM dual;
dbms_output.put('time after= ');
dbms_output.put_line(vtext1);

Thanks
Sathia
Re: want to see the time duration of a statement [message #195793 is a reply to message #195593] Mon, 02 October 2006 08:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The problems with that are:

1) It only gives you the duration of the statement to the nearest second.
2) It includes the execution time of the second SELECT sysdate FROM dual; in your execution time.

If you can live with the poor timing granularity then a better approach would be:
vtext1 := TO_CHAR(sysDATE, 'DD-MON-YYYY HH:MI:SS'); 

type your query .................


vtext2 TO_CHAR(sysDATE, 'DD-MON-YYYY HH:MI:SS'); 
dbms_output.put_line('time before= '||(vtext1)); 
dbms_output.put_line('time after=  '||(vtext2)); 


But a still better solution would be:
vnum1 := dbms_utility.get_time; 

type your query .................

dbms_output.put_line('time taken = '||to_char(dbms_utility.get_time - vnum1));
as this gives you the time taken in 1/100s of a second.

The best solution of course, is to set Trace on for your session, execute the query, and use TKPROF on the trace file produced.
Re: want to see the time duration of a statement [message #195800 is a reply to message #195793] Mon, 02 October 2006 09:03 Go to previous message
skn
Messages: 6
Registered: September 2006
Junior Member
Thanks JRowbottom for your clarification.

[Updated on: Mon, 02 October 2006 09:04]

Report message to a moderator

Previous Topic: Partitioning In Phases and Impact
Next Topic: Outline help
Goto Forum:
  


Current Time: Sun Dec 04 04:46:59 CST 2016

Total time taken to generate the page: 0.21210 seconds