Re: How to test query execution time

On Sat, 29 Jun 2002, jAf9_at_ar.com.au wrote:

[...]

> Is there any way to properly test the *time* a particular query takes?
> Meaning, is there a way to tell Oracle to disable all caches and
> whatnot (I don't know every single thing that I'd need to disable), so
> that if I run a query 10 times, it'll take about the same time every
> time?

Take the tablespace offline and then bring it back online. Oracle will then go to disk on the next read.

[...]

> I've been searching for documentation, but can't find anything so
> far.

Whew!

Mother of all documentation.
http://technet.oracle.com/index.html

Great search engine for the documentation http://tahiti.oracle.com/pls/tahiti/tahiti.homepage

A specific tuning URL.
http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76992/toc.htm

> Yes, it's obvious I'm a newbie at tuning, so don't assume I know much,
> because I obviously don't!
>
> I'm aware of EXPLAIN PLAN which shows the execution path, but that
> doesn't have much to do with the time.

One thing that you should do is set up your SQLPATH environmental variable. Then, save a file called login.sql in that path. Then, put the following line in that login.sql file.

set timing on

The other line to get used to (but not put in your login.sql file) is set autotrace on.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.