Home » SQL & PL/SQL » Client Tools » How to get Query execution time in seconds using "Show Timing" (oracle sql plus 10g)
How to get Query execution time in seconds using "Show Timing" [message #614699] Mon, 26 May 2014 04:08 Go to next message
jgjeetu
Messages: 243
Registered: July 2013
Location: India Delhi Rohini Sec-1
Senior Member

Show Timing gives query execution time in miliseconds , is there any way to get Query execution time in seconds using "Show Timing" in SQL plus.
Thanks
Note :- I do not want any pl/sql code .

[Updated on: Mon, 26 May 2014 04:09]

Report message to a moderator

Re: How to get Query execution time in seconds using "Show Timing" [message #614700 is a reply to message #614699] Mon, 26 May 2014 04:18 Go to previous messageGo to next message
Littlefoot
Messages: 19890
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:

Show Timing gives query execution time in miliseconds ...


It does?
SQL> select count(*) From dual connect by level < 1E6;

  COUNT(*)
----------
    999999

Elapsed: 00:00:01.56
SQL>

These are hours:minutes:seconds.hudreds_of_seconds. Could you post your example which shows miliseconds?

As of your question: multiply miliseconds with 1000 and you'll get seconds.
Re: How to get Query execution time in seconds using "Show Timing" [message #614701 is a reply to message #614699] Mon, 26 May 2014 04:20 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No way.
It is not millisecond but centisecond.
And it is not "show timing" but "set timing on".


Re: How to get Query execution time in seconds using "Show Timing" [message #614702 is a reply to message #614699] Mon, 26 May 2014 04:36 Go to previous messageGo to next message
jgjeetu
Messages: 243
Registered: July 2013
Location: India Delhi Rohini Sec-1
Senior Member

actually i saw one link over internet in that i read about show timing , n whatever i read i mentioned here and now i am getting confused , my requirement is only to get query execution time , please tell how to do that using sql query.
and i ran your command in sql plus , see what i got:-
./fa/11895/0/
  • Attachment: timing.JPG
    (Size: 10.77KB, Downloaded 206 times)
Re: How to get Query execution time in seconds using "Show Timing" [message #614703 is a reply to message #614702] Mon, 26 May 2014 04:40 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
Are you talking about "Timing show" or "Set timing on"?

in either case, it is hours:minutes:seconds.hundredth_of_a_second

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 26 15:06:14 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning and Automatic Storage Management options

SQL> timing show;
SP2-0325: no timing elements to show
SQL> timing start t_start;
SQL> timing show;
timing for: t_start
Elapsed: 00:00:06.94
SQL> timing start t_start_2;
SQL> timing show;
timing for: t_start_2
Elapsed: 00:00:03.86
SQL> timing stop;
timing for: t_start_2
Elapsed: 00:00:10.77
SQL> timing show;
timing for: t_start
Elapsed: 00:01:04.20
SQL> timing stop;
timing for: t_start
Elapsed: 00:01:08.96
SQL> timing show;
SP2-0325: no timing elements to show
SQL>

[Updated on: Mon, 26 May 2014 04:48]

Report message to a moderator

Re: How to get Query execution time in seconds using "Show Timing" [message #614705 is a reply to message #614703] Mon, 26 May 2014 04:47 Go to previous messageGo to next message
jgjeetu
Messages: 243
Registered: July 2013
Location: India Delhi Rohini Sec-1
Senior Member

i think i read few wrong stuff over internet and i posted here so dont concetrate on what i said earlier. Right now my requirement is only getting query execution timing using sql query.
Thanks
Re: How to get Query execution time in seconds using "Show Timing" [message #614706 is a reply to message #614702] Mon, 26 May 2014 04:48 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please post text in text not in image.
Which OS do you use?
Windows:
SQL> set timing on
SQL> select count(*) from dual connect by level < 600000;
  COUNT(*)
----------
    599999

1 row selected.

Elapsed: 00:00:00.26

Linux:
SQL> set timing on
SQL> select count(*) from dual connect by level < 600000;
  COUNT(*)
----------
    599999

1 row selected.

Elapsed: 00:00:00.26


Re: How to get Query execution time in seconds using "Show Timing" [message #614708 is a reply to message #614705] Mon, 26 May 2014 04:57 Go to previous messageGo to next message
jgjeetu
Messages: 243
Registered: July 2013
Location: India Delhi Rohini Sec-1
Senior Member

But i am getting different output Sad
and I am using windows xp oracle 8i
Note:- At office i have oracle 8i and at home i have oracle 10g , both are at windows xp.
Re: How to get Query execution time in seconds using "Show Timing" [message #614712 is a reply to message #614708] Mon, 26 May 2014 05:09 Go to previous message
jgjeetu
Messages: 243
Registered: July 2013
Location: India Delhi Rohini Sec-1
Senior Member

i ran same comamnd in oracle 10g on other pc and
now i got the output Smile
thanks
Previous Topic: ER Diagram to pdf
Next Topic: Run Log in SQLDeveloper still going even if DBMS_SCHEDULER.LOGGING_OFF is set
Goto Forum:
  


Current Time: Sun Dec 21 00:38:35 CST 2014

Total time taken to generate the page: 0.12659 seconds