Getting time for views

[Hoffman, Tobi K (DYS)]

I am working to try to optimize views, and wondering if there is a way
to see how long a view took to run, other than gluing the eyeballs to
the screen to know about what the timer reached before the data showed
up.

Tobi Hoffman
Dept. of Youth Services
Boston, MA

[Jeff Smith-7]

I can think of 2 ways.

If you're on Toad Standard,
 Write a simple stored procedure that queries the view.
 Execute/Profile the procedure - Oracle will tell you how much time was spent running your query.

If you're on Toad XPert,
 Send the query to the Optimizer.  You can execute your embedded query and we'll tell you exactly how long it took to run, to retrieve the rowd, CPU time, etc.  You can then choose to tune the query if you want.

You can also do a trace on a session from the Session Browser or view the session specific stats from V$Session - that's in base toad too.

________________________________
From: toad@... [mailto:toad@...] On Behalf Of Hoffman, Tobi K (DYS)
Sent: Tuesday, October 30, 2007 9:41 AM
To: toad@...
Subject: [toad] Getting time for views


I am working to try to optimize views, and wondering if there is a way to see how long a view took to run, other than gluing the eyeballs to the screen to know about what the timer reached before the data showed up.

Tobi Hoffman
Dept. of Youth Services
Boston, MA

[Erwin Rollauer]

There is an option to show the time at the bottom of the edit window.
Now it depends on whether you want the time for the first set of rows or
all rows.

 

If you want the time for the first set of rows then just look at the
time at the bottom of the window.

If you want the time for ALL rows then turn AUTO TRACE on. Right click
and choose AUTO TRACE. You will get the grid only when all rows have
been returned.

AUTO TRACE also gives you useful statistics.

NOTE to Toadies. How about including the time to fulfill the query in
the statistics shown. Maybe show it in italics to indicate it is not a
real trace statistic.

 

NOTE HOWEVER. If you repeat a query the time will be a lot less. Oracle
caches. If you use a SAN then the SAN caches. If you want more or less
"consistent" timings then you need your friendly neighborhood DBA to
flush the buffer pool. But I don't think you can flush the SAN cache.

 

Erwin Rollauer, ISR, McGill, 514 398-5023 ex 00626

________________________________

From: toad@... [mailto:toad@...] On Behalf Of
Hoffman, Tobi K (DYS)
Sent: Tuesday, October 30, 2007 9:41 AM
To: toad@...
Subject: [toad] Getting time for views

 

I am working to try to optimize views, and wondering if there is a way
to see how long a view took to run, other than gluing the eyeballs to
the screen to know about what the timer reached before the data showed
up.

Tobi Hoffman
Dept. of Youth Services
Boston, MA

 

[Jeff Smith-7]

The optimzer also allows you to run a statement X times, so you can adjust for 'false' performance gains on multiple executions.

________________________________
From: toad@... [mailto:toad@...] On Behalf Of Erwin Rollauer
Sent: Tuesday, October 30, 2007 10:32 AM
To: toad@...
Subject: RE: [toad] Getting time for views

There is an option to show the time at the bottom of the edit window. Now it depends on whether you want the time for the first set of rows or all rows.
If you want the time for the first set of rows then just look at the time at the bottom of the window.
If you want the time for ALL rows then turn AUTO TRACE on. Right click and choose AUTO TRACE. You will get the grid only when all rows have been returned.
AUTO TRACE also gives you useful statistics.
NOTE to Toadies. How about including the time to fulfill the query in the statistics shown. Maybe show it in italics to indicate it is not a real trace statistic.
NOTE HOWEVER. If you repeat a query the time will be a lot less. Oracle caches. If you use a SAN then the SAN caches. If you want more or less "consistent" timings then you need your friendly neighborhood DBA to flush the buffer pool. But I don't think you can flush the SAN cache.
Erwin Rollauer, ISR, McGill, 514 398-5023 ex 00626
________________________________
From: toad@... [mailto:toad@...] On Behalf Of Hoffman, Tobi K (DYS)
Sent: Tuesday, October 30, 2007 9:41 AM
To: toad@...
Subject: [toad] Getting time for views

I am working to try to optimize views, and wondering if there is a way to see how long a view took to run, other than gluing the eyeballs to the screen to know about what the timer reached before the data showed up.

Tobi Hoffman
Dept. of Youth Services
Boston, MA

[Dunbar, Norman-2]

I can add a simple one to Jeff's suggestions.

set timing ON
SELECT * FROM view_name;
set timing off

Run the above as a script with F5 and the elapsed time will be shown.

Alternatively, turn on autotrace, but unfortunately, that doesn't give
you timing information.


The big daddy of them all is a 10046 level 12 trace. But you really
don't want to go there, unless you are having severe problems and love
trace files (like I do!)


Cheers,
Norm. [TeamT]


Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.   We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation.  Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.  If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506.  Find out more about the Environment Agency at www.environment-agency.gov.uk

[hcco_roch]

I am reviewed this thread and was wondering if there is a way to time
a sql script in the edit window, i do not want to turn Auto Trace on.
I am looking for an option in TOAD with navigation path.

Thx

PS.  Nice to see Quest at Open World 2008




--- In toad@..., Jeff Smith <jeff.smith@...> wrote:
>
> The optimzer also allows you to run a statement X times, so you can
adjust for 'false' performance gains on multiple executions.
>
> ________________________________
> From: toad@... [mailto:toad@...] On Behalf
Of Erwin Rollauer
> Sent: Tuesday, October 30, 2007 10:32 AM
> To: toad@...
> Subject: RE: [toad] Getting time for views
>
> There is an option to show the time at the bottom of the edit
window. Now it depends on whether you want the time for the first set
of rows or all rows.
> If you want the time for the first set of rows then just look at the
time at the bottom of the window.
> If you want the time for ALL rows then turn AUTO TRACE on. Right
click and choose AUTO TRACE. You will get the grid only when all rows
have been returned.
> AUTO TRACE also gives you useful statistics.
> NOTE to Toadies. How about including the time to fulfill the query
in the statistics shown. Maybe show it in italics to indicate it is
not a real trace statistic.
> NOTE HOWEVER. If you repeat a query the time will be a lot less.
Oracle caches. If you use a SAN then the SAN caches. If you want more
or less "consistent" timings then you need your friendly neighborhood
DBA to flush the buffer pool. But I don't think you can flush the SAN
cache.
> Erwin Rollauer, ISR, McGill, 514 398-5023 ex 00626
> ________________________________
> From: toad@... [mailto:toad@...] On Behalf
Of Hoffman, Tobi K (DYS)
> Sent: Tuesday, October 30, 2007 9:41 AM
> To: toad@...
> Subject: [toad] Getting time for views
>
> I am working to try to optimize views, and wondering if there is a
way to see how long a view took to run, other than gluing the eyeballs
to the screen to know about what the timer reached before the data
showed up.
>
> Tobi Hoffman
> Dept. of Youth Services
> Boston, MA
>

[Jeff Smith-7]

Run your script (F5)

At the end, you'll see total execution time.  As it's running, you'll also see an execution timer running.

Jeff


[cid:image001.png@...]

From: toad@... [mailto:toad@...] On Behalf Of hcco_roch
Sent: Tuesday, September 30, 2008 1:49 PM
To: toad@...
Subject: Re: [toad] Getting time for views


I am reviewed this thread and was wondering if there is a way to time
a sql script in the edit window, i do not want to turn Auto Trace on.
I am looking for an option in TOAD with navigation path.

Thx

PS. Nice to see Quest at Open World 2008

--- In toad@...<mailto:toad%40yahoogroups.com>, Jeff Smith <jeff.smith@...> wrote:
>
> The optimzer also allows you to run a statement X times, so you can
adjust for 'false' performance gains on multiple executions.
>
> ________________________________
> From: toad@...<mailto:toad%40yahoogroups.com> [mailto:toad@...<mailto:toad%40yahoogroups.com>] On Behalf
Of Erwin Rollauer
> Sent: Tuesday, October 30, 2007 10:32 AM
> To: toad@...<mailto:toad%40yahoogroups.com>
> Subject: RE: [toad] Getting time for views
>
> There is an option to show the time at the bottom of the edit
window. Now it depends on whether you want the time for the first set
of rows or all rows.
> If you want the time for the first set of rows then just look at the
time at the bottom of the window.
> If you want the time for ALL rows then turn AUTO TRACE on. Right
click and choose AUTO TRACE. You will get the grid only when all rows
have been returned.
> AUTO TRACE also gives you useful statistics.
> NOTE to Toadies. How about including the time to fulfill the query
in the statistics shown. Maybe show it in italics to indicate it is
not a real trace statistic.
> NOTE HOWEVER. If you repeat a query the time will be a lot less.
Oracle caches. If you use a SAN then the SAN caches. If you want more
or less "consistent" timings then you need your friendly neighborhood
DBA to flush the buffer pool. But I don't think you can flush the SAN
cache.
> Erwin Rollauer, ISR, McGill, 514 398-5023 ex 00626
> ________________________________
> From: toad@...<mailto:toad%40yahoogroups.com> [mailto:toad@...<mailto:toad%40yahoogroups.com>] On Behalf
Of Hoffman, Tobi K (DYS)
> Sent: Tuesday, October 30, 2007 9:41 AM
> To: toad@...<mailto:toad%40yahoogroups.com>
> Subject: [toad] Getting time for views
>
> I am working to try to optimize views, and wondering if there is a
way to see how long a view took to run, other than gluing the eyeballs
to the screen to know about what the timer reached before the data
showed up.
>
> Tobi Hoffman
> Dept. of Youth Services
> Boston, MA
>


 

[hcco_roch]

Everyone has a differernt solution

--- On Tue, 9/30/08, Jeff Smith <jeff.smith@...> wrote:
From: Jeff Smith <jeff.smith@...>
Subject: RE: [toad] Getting time for views
To: "toad@..." <toad@...>
Date: Tuesday, September 30, 2008, 2:02 PM










   
           







  

Run your script (F5)

  

At the end, you’ll see total execution time.  As it’s running,
you’ll also see an execution timer running.

  

Jeff

  

  

 

  





From:
toad@yahoogroups. com [mailto:toad@ yahoogroups. com] On Behalf Of hcco_roch

Sent: Tuesday, September 30, 2008 1:49 PM

To: toad@yahoogroups. com

Subject: Re: [toad] Getting time for views





  







I am reviewed this thread and was wondering if
there is a way to time

a sql script in the edit window, i do not want to turn Auto Trace on.

I am looking for an option in TOAD with navigation path.



Thx



PS. Nice to see Quest at Open World 2008



--- In toad@yahoogroups. com, Jeff
Smith <jeff.smith@. ..> wrote:

>

> The optimzer also allows you to run a statement X times, so you can

adjust for 'false' performance gains on multiple executions.

>

> ____________ _________ _________ __

> From: toad@yahoogroups. com
[mailto:toad@yahoogroups. com] On
Behalf

Of Erwin Rollauer

> Sent: Tuesday, October 30, 2007 10:32 AM

> To: toad@yahoogroups. com

> Subject: RE: [toad] Getting time for views

>

> There is an option to show the time at the bottom of the edit

window. Now it depends on whether you want the time for the first set

of rows or all rows.

> If you want the time for the first set of rows then just look at the

time at the bottom of the window.

> If you want the time for ALL rows then turn AUTO TRACE on. Right

click and choose AUTO TRACE. You will get the grid only when all rows

have been returned.

> AUTO TRACE also gives you useful statistics.

> NOTE to Toadies. How about including the time to fulfill the query

in the statistics shown. Maybe show it in italics to indicate it is

not a real trace statistic.

> NOTE HOWEVER. If you repeat a query the time will be a lot less.

Oracle caches. If you use a SAN then the SAN caches. If you want more

or less "consistent" timings then you need your friendly neighborhood

DBA to flush the buffer pool. But I don't think you can flush the SAN

cache.

> Erwin Rollauer, ISR, McGill, 514 398-5023 ex 00626

> ____________ _________ _________ __

> From: toad@yahoogroups. com
[mailto:toad@yahoogroups. com] On
Behalf

Of Hoffman, Tobi K (DYS)

> Sent: Tuesday, October 30, 2007 9:41 AM

> To: toad@yahoogroups. com

> Subject: [toad] Getting time for views

>

> I am working to try to optimize views, and wondering if there is a

way to see how long a view took to run, other than gluing the eyeballs

to the screen to know about what the timer reached before the data

showed up.

>

> Tobi Hoffman

> Dept. of Youth Services

> Boston, MA

>



 







     

   
   
       
         
       
       








       


       
       


     

[Jeff Smith-7]

There about 6 ways to attack a problem in Oracle.  Add Toad to the mix and you have the ability to do just about anything you want.

If you can provide some feedback on the things you've tried based on the advice of the list, maybe we can help you some more.

Or, you can open an official case with Quest Support.

From: toad@... [mailto:toad@...] On Behalf Of HCCO, Inc.
Sent: Tuesday, September 30, 2008 3:41 PM
To: toad@...
Subject: RE: [toad] Getting time for views

Everyone has a differernt solution

--- On Tue, 9/30/08, Jeff Smith <jeff.smith@...> wrote:
From: Jeff Smith <jeff.smith@...>
Subject: RE: [toad] Getting time for views
To: "toad@..." <toad@...>
Date: Tuesday, September 30, 2008, 2:02 PM

Run your script (F5)

At the end, you’ll see total execution time.  As it’s running, you’ll also see an execution timer running.

Jeff


[cid:image001.png@...]

From: toad@yahoogroups. com [mailto:toad@ yahoogroups. com] On Behalf Of hcco_roch
Sent: Tuesday, September 30, 2008 1:49 PM
To: toad@yahoogroups. com
Subject: Re: [toad] Getting time for views


I am reviewed this thread and was wondering if there is a way to time
a sql script in the edit window, i do not want to turn Auto Trace on.
I am looking for an option in TOAD with navigation path.

Thx

PS. Nice to see Quest at Open World 2008

--- In toad@yahoogroups. com<mailto:toad%40yahoogroups.com>, Jeff Smith <jeff.smith@. ..> wrote:
>
> The optimzer also allows you to run a statement X times, so you can
adjust for 'false' performance gains on multiple executions.
>
> ____________ _________ _________ __
> From: toad@yahoogroups. com<mailto:toad%40yahoogroups.com> [mailto:toad@yahoogroups. com<mailto:toad%40yahoogroups.com>] On Behalf
Of Erwin Rollauer
> Sent: Tuesday, October 30, 2007 10:32 AM
> To: toad@yahoogroups. com<mailto:toad%40yahoogroups.com>
> Subject: RE: [toad] Getting time for views
>
> There is an option to show the time at the bottom of the edit
window. Now it depends on whether you want the time for the first set
of rows or all rows.
> If you want the time for the first set of rows then just look at the
time at the bottom of the window.
> If you want the time for ALL rows then turn AUTO TRACE on. Right
click and choose AUTO TRACE. You will get the grid only when all rows
have been returned.
> AUTO TRACE also gives you useful statistics.
> NOTE to Toadies. How about including the time to fulfill the query
in the statistics shown. Maybe show it in italics to indicate it is
not a real trace statistic.
> NOTE HOWEVER. If you repeat a query the time will be a lot less.
Oracle caches. If you use a SAN then the SAN caches. If you want more
or less "consistent" timings then you need your friendly neighborhood
DBA to flush the buffer pool. But I don't think you can flush the SAN
cache.
> Erwin Rollauer, ISR, McGill, 514 398-5023 ex 00626
> ____________ _________ _________ __
> From: toad@yahoogroups. com<mailto:toad%40yahoogroups.com> [mailto:toad@yahoogroups. com<mailto:toad%40yahoogroups.com>] On Behalf
Of Hoffman, Tobi K (DYS)
> Sent: Tuesday, October 30, 2007 9:41 AM
> To: toad@yahoogroups. com<mailto:toad%40yahoogroups.com>
> Subject: [toad] Getting time for views
>
> I am working to try to optimize views, and wondering if there is a
way to see how long a view took to run, other than gluing the eyeballs
to the screen to know about what the timer reached before the data
showed up.
>
> Tobi Hoffman
> Dept. of Youth Services
> Boston, MA
>



 

[Bert Scalzo]

As my favorite line from the old Lost in Space program, Doctor Smith says there is more than one way to skin a cat - and the robot says affirmative, exactly 36 ways :)

________________________________
From: toad@... [mailto:toad@...] On Behalf Of Jeff Smith
Sent: Tuesday, September 30, 2008 3:11 PM
To: toad@...
Subject: RE: [toad] Getting time for views

There about 6 ways to attack a problem in Oracle.  Add Toad to the mix and you have the ability to do just about anything you want.
If you can provide some feedback on the things you've tried based on the advice of the list, maybe we can help you some more.
Or, you can open an official case with Quest Support.
From: toad@... [mailto:toad@...] On Behalf Of HCCO, Inc.
Sent: Tuesday, September 30, 2008 3:41 PM
To: toad@...
Subject: RE: [toad] Getting time for views
Everyone has a differernt solution

--- On Tue, 9/30/08, Jeff Smith <jeff.smith@...> wrote:
From: Jeff Smith <jeff.smith@...>
Subject: RE: [toad] Getting time for views
To: "toad@..." <toad@...>
Date: Tuesday, September 30, 2008, 2:02 PM
Run your script (F5)
At the end, you’ll see total execution time.  As it’s running, you’ll also see an execution timer running.
Jeff
[cid:343402721@30092008-32A8]
From: toad@yahoogroups. com [mailto:toad@ yahoogroups. com] On Behalf Of hcco_roch
Sent: Tuesday, September 30, 2008 1:49 PM
To: toad@yahoogroups. com
Subject: Re: [toad] Getting time for views

I am reviewed this thread and was wondering if there is a way to time
a sql script in the edit window, i do not want to turn Auto Trace on.
I am looking for an option in TOAD with navigation path.

Thx

PS. Nice to see Quest at Open World 2008

--- In toad@yahoogroups. com<mailto:toad%40yahoogroups.com>, Jeff Smith <jeff.smith@. ..> wrote:
>
> The optimzer also allows you to run a statement X times, so you can
adjust for 'false' performance gains on multiple executions.
>
> ____________ _________ _________ __
> From: toad@yahoogroups. com<mailto:toad%40yahoogroups.com> [mailto:toad@yahoogroups. com<mailto:toad%40yahoogroups.com>] On Behalf
Of Erwin Rollauer
> Sent: Tuesday, October 30, 2007 10:32 AM
> To: toad@yahoogroups. com<mailto:toad%40yahoogroups.com>
> Subject: RE: [toad] Getting time for views
>
> There is an option to show the time at the bottom of the edit
window. Now it depends on whether you want the time for the first set
of rows or all rows.
> If you want the time for the first set of rows then just look at the
time at the bottom of the window.
> If you want the time for ALL rows then turn AUTO TRACE on. Right
click and choose AUTO TRACE. You will get the grid only when all rows
have been returned.
> AUTO TRACE also gives you useful statistics.
> NOTE to Toadies. How about including the time to fulfill the query
in the statistics shown. Maybe show it in italics to indicate it is
not a real trace statistic.
> NOTE HOWEVER. If you repeat a query the time will be a lot less.
Oracle caches. If you use a SAN then the SAN caches. If you want more
or less "consistent" timings then you need your friendly neighborhood
DBA to flush the buffer pool. But I don't think you can flush the SAN
cache.
> Erwin Rollauer, ISR, McGill, 514 398-5023 ex 00626
> ____________ _________ _________ __
> From: toad@yahoogroups. com<mailto:toad%40yahoogroups.com> [mailto:toad@yahoogroups. com<mailto:toad%40yahoogroups.com>] On Behalf
Of Hoffman, Tobi K (DYS)
> Sent: Tuesday, October 30, 2007 9:41 AM
> To: toad@yahoogroups. com<mailto:toad%40yahoogroups.com>
> Subject: [toad] Getting time for views
>
> I am working to try to optimize views, and wondering if there is a
way to see how long a view took to run, other than gluing the eyeballs
to the screen to know about what the timer reached before the data
showed up.
>
> Tobi Hoffman
> Dept. of Youth Services
> Boston, MA
>