Path: news.cambrium.nl!textnews.cambrium.nl!feeder1.cambriumusenet.nl!feed.tweaknews.nl!195.62.100.242.MISMATCH!newsfeed.kamp.net!newsfeed.kamp.net!eternal-september.org!feeder.eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail From: Mladen Gogala Newsgroups: comp.databases.oracle.server Subject: 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) Organization: A noiseless patient Spider Lines: 74 Message-ID: References: <752cfcd9-b397-4838-826f-a955be32110b@googlegroups.com> Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Injection-Date: Sun, 2 Feb 2014 17:01:50 +0000 (UTC) Injection-Info: mx05.eternal-september.org; posting-host="9329a606497bb546bacd1416e46e9713"; logging-data="5821"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/1dBI8tmhwRzHkjO8dKr8e" User-Agent: Pan/0.139 (Sexual Chocolate; GIT bf56508 git://git.gnome.org/pan2) Cancel-Lock: sha1:Kmvbjub8pZj0lFsKT/MvfgxHFwo= Xref: news.cambrium.nl 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.com