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

On Wednesday, January 29, 2014 7:15:20 PM UTC-5, mjm..._at_gmail.com 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.

mjm, you should capture the session that is running from the client and compare the plan in v$sql_plan to what you posted. Why is the SQL using a First_Rows hint?

The fact the client has a slower response time can be a sign the problem is on the client machine: how many applications are running on the client, how busy are the machine cpu's, how much free memory is there?, and what is the speed of the machine cpu's? Checking to see if other end users have better, worse, or equal performance may be worthwhile.

HTH -- Mark D Powell -- Received on Sat Feb 01 2014 - 18:00:42 CET