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

From: <mjmstud_at_gmail.com>
Date: Wed, 29 Jan 2014 16:15:20 -0800 (PST)
Message-ID: <752cfcd9-b397-4838-826f-a955be32110b_at_googlegroups.com>

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. Received on Thu Jan 30 2014 - 01:15:20 CET