Re: oracle sql developer tool - Execution time

Comments embedded.
[Quoted] On Oct 13, 8:35 am, raja <dextersu..._at_gmail.com> wrote:
> Hi,
>
> Anyone who has used oracle sql developer tool, please answer my
> question :
>
> How can view the execution time ?

It's usually displayed atop the Script Runner window.

> The execution time that i getting seems to be wrong.

Define 'wrong'. Simply because it isn't what you expect doesn't make it 'wrong'.

> How can i find the correct execution time of a query....

Use the SQL*Plus functionality by typing:

set timing on

in the query text window. You'll then see output after your query results indicating the elapsed time for that query. Yes, this may differ from what SQL Developer reports in its default timing display, but no one said that the time displayed in that 'window' is the actual query time. It's likely the overall time required to send the query, return the results and then display that information across the network to your PC. Both values are 'correct', even though they don't match, as the criteria for determining each is different.

A proper frame of reference, which doesn't include assumptions, is necessary.

>
> Any other features, that can help in performance tuning. If possible,
> Please explain them too.

The usual array of functions is available:

Explain Plan
Autotrace
set timing on

All contribute to understanding what Oracle is doing to return your results. In addition you can set the following events and generate trace files:

10046 (captures session statistics for all queries executed after the event is set)
10053 (reports what the CBO is doing to determine the query plan) 10032 (reports on sort operations and whether or not any go to disk)

To explain these would take a book, and you can buy your own copy of "Cost-Based Oracle Fundamentals", by Jonathan Lewis and read up on these events and what they do. You should also purchase a copy of Cary Millsap's fine text "Optimizing Oracle Performance".

To assist you in locating these excellent references:

Publisher

Title                                                             ISBN

Apress                          "Cost-Based Oracle
Fundamentals"                1-59059-636-6
O'Reilly Press                "Optimizing Oracle
Performance"                    0-596-00527-X

>
> Thanks in Advance.
> With Regards,
> Raja.

David Fitzjarrell Received on Mon Oct 13 2008 - 18:07:32 CEST