Re: Query Execution Time

On Nov 14, 7:29 am, raja <dextersu..._at_gmail.com> wrote:
> Hi,
>
> I am using both 9i/10g.
> I am checking the performance of a query, so i have 2 queries, one
> before change and one after changing the query.
> Let the testing environment be any of these sql*plus / sqltools /
> toad / oracle sql developer.
>
> The problem here is that:
> when i executed the query the second time, the query execution time
> decreases than before, which means that it has been taken in cache.
>
> I tried no cache hint, but it didnt look to work good.
>
> Is there any way to clear the cache, so that we can check the query
> execution properly ?
>
> Also, how can view the exection time, fetch time, parse time
> separately for a particular query ...
>
> Thanks in Advance.
>
> With Regards,
> Raja.

ALTER SYSTEM FLUSH BUFFER_CACHE is the answer to the first question; googling for "extended sql trace", "event 10046", "tkprof" will provide pointers to answers to the second question. Reading through Performance Tuning Guide for your Oracle release(s) (all Oracle documentation is available at http://tahiti.oracle.com in searchable and downloadable form) is also highly recommended.

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Fri Nov 14 2008 - 05:21:23 CST