Real query execution time without network transfer in Oracle

Question

is it possible to determine SELECT execution time without data transfer across network ?

I am trying to optimize our queries on an remote server, which is actualy about 1000 miles away from me, and fetching even small amounts of data takes minutes to get here.

SQL Developer tells me execution time with network trasnfer time included. Same situation with 'set timing on'.

And I'm really confused of result from SELECT * FROM v$sql(area). Results from this one are still the same, even when changing executed queries. Oracle documentation says about ELAPSED_TIME column that it includes parsing/executing/fetching times, and also that these results are somehow accumulated.

So here comes the question: Is there any straightforward way to get the real execution time of query on the server ? Without network transfer time, no matter if the query was cached or wasn't. No averages, no estimations. Just time of the concrete one execution of query.

Thx in advance

Answer 1

Oracle query execution works as producer-consumer design pattern. If network connection is "stuck" then Oracle session process gets blocked by OS. In order to avoid influence of network latency, you must execute SQL queries on the DB server directly (either from sqlplus or via PL/SQL).

Suggestion: forget about time as a metrics to optimize - look at buffer gets and disk reads, this will tell you how many memory pages had to be processed while evaluating the query. This metrics is invariant (independent) to current DB server load.

If network round-trips make significant overhead, try to change an interface. By using collections, nested tables or complex types you can reduce amount network waits.

Answer 2

You can use

SELECT COUNT(*) FROM (SELECT * FROM MY_LARGE_TABLE WHERE ...)

Well, you still transfer one single number over the network but this should be negligible.