Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Significant difference in response times for same query running on Windows client vs database server

user6502667Jan 29 2014 — edited Jan 30 2014

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.
Ideally I would like to get a response time equivalent on the Windows client to what I get when running this on the database server.
In both cases the query plans are the same.
The query and plan is shown below :

{code}

SQL> explain plan
  2  set statement_id = 'SLOW'
  3  for
  4  SELECT DISTINCT /*+ FIRST_ROWS(503) */ objecttype.id_object
  5  FROM documents objecttype WHERE objecttype.id_type_definition = 'duotA9'
  6  ;

Explained.

SQL> select * from table(dbms_xplan.display('PLAN_TABLE','SLOW','TYPICAL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |  2852K|    46M|       | 69851   (1)|
|   1 |  HASH UNIQUE       |           |  2852K|    46M|   153M| 69851   (1)|
|*  2 |   TABLE ACCESS FULL| DOCUMENTS |  2852K|    46M|       | 54063   (1)|
-----------------------------------------------------------------------------
{code}

Are there are configuration changes that can be done on the Oracle client or database to improve the response times for the query when it is running from the client?

The version on the database server is 10.2.0.1.0
The version of the oracle client is also 10.2.0.1.0

I am happy to provide any further information if required.

Thank you in advance.

Comments

jgarry

When you say Oracle client, do you mean sqlplus?  If so, try:

set autotrace on

show arraysize

set arraysize 500

select ...

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?

unknown-7404 
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.

Hemant K Chitale

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

SET ARRAYSIZE

SET PAGESIZE

SET LINESIZE

if using sqlplus as the "client".

If using another client software, check if you can set the arraysize in that client.

Hemant K Chitale

Harmandeep Singh

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.

Thanks,

Harman

1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 27 2014
Added on Jan 29 2014
#general-database-discussions
4 comments
139 views