Re: Significant difference in response times for same query running on Windows client vs database server
Date: Sun, 2 Feb 2014 17:01:50 +0000 (UTC)
Message-ID: <pan.2014.02.02.17.01.49_at_gmail.com>
On Wed, 29 Jan 2014 16:15:20 -0800, mjmstud wrote:
> 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.
This will require a trace. The most likely suspect is the single row fetch, combined with slow network. On the other hand, it may be a bug, since your version is the very first version of 10.2 and it is known to be buggy. It also contains the famous 200 days bug:
https://community.oracle.com/thread/405202
It wasn't just the instant client, it was any type of client. This brings back memories of a long lost age.
-- Mladen Gogala The Oracle Whisperer http://mgogala.byethost5.comReceived on Sun Feb 02 2014 - 18:01:50 CET