When you say Oracle client, do you mean sqlplus? If so, try:
set autotrace on
set arraysize 500
There may be other things to do. Other clients may have similar settings. Have you thought about an appropriate index? Do you really need a distinct?
I have a query which is taking a long time to return the results using the Oracle client.
When I run this query on our database server (Unix/Solaris) it completes in 80 seconds.
When I run the same query on a Windows client it completes in 47 minutes.
There are NO queries that 'run' on a client. Queries ALWAYS run within the database server.
A client can choose when to FETCH query results. In sql developer (or toad) I can choose to get 10 rows at a time. Until I choose to get the next set of 10 rows NO rows will be returned from the server to the client; That query might NEVER complete.
You may get the same results depending on the client you are using. Post your question in a forum for whatever client you are using.
Time spent is on
a) the network to transfer the data (output rows) to the client
b) formatting on the client
You can improve these with
if using sqlplus as the "client".
If using another client software, check if you can set the arraysize in that client.
Hemant K Chitale
As others said, time on the sqlplus window will be large when you are getting huge amount of data. Does it makes any practical sense to fetch huge amount of data on the sql plus window on client side as you will not scan more than few hundred rows....
From performance view, you should be bothered at that timings and execution plan at the server level.