Query performance very slow on Oracle 10g

JohnV · Post by **JohnV** » Wed 26 Oct 2011 06:16

When I execute a query on a table of 5 records, it takes about 6 seconds (e.g. [0.032s exec, 6.445s fetch]) :

SELECT * FROM

I’m using :
- dbForge Studio Version : 3.0.87 -Professional Edition
- OS Client : Windows XP SP3
- Server OS : Windows Server 2003
- Server Database : Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Prod

Any suggestions of the cause ? I have tried to connect via TNS and also via direct mode to no avail.

When I execute the same query on a database Oracle 10g Express Edition on the same computer (Windows 7), it works blazing fast (0.17 seconds on table of 350 records)

Kind regards,
JohnV

Viktor · Post by **Viktor** » Wed 26 Oct 2011 08:28

When I execute a query on a table of 5 records, it takes about 6 seconds (e.g. [0.032s exec, 6.445s fetch])
SELECT * FROM ...

Please try to execute the query using sqlplus. How long does it take to execute the query in sqlplus and dbForge Studio for Oracle on the same Oracle server?

JohnV · Post by **JohnV** » Wed 26 Oct 2011 08:51

I have no timings for SQL Plus - it responds immediately.
When I perform the same query with Oracle SQL Developer 1.5 it takes 0.061 seconds.

JohnV · Post by **JohnV** » Thu 27 Oct 2011 10:56

Any suggestions are welcome.
For now the application is not usable for me, it's a pity for such a nice product

Viktor · Post by **Viktor** » Thu 27 Oct 2011 12:57

We were able to reproduce such an issue on one of test servers. We are currently investigating it and will update you on the issue later.

JohnV · Post by **JohnV** » Mon 07 Nov 2011 12:05

Dear Viktor,

Is there any progress on this issue ?

Viktor · Post by **Viktor** » Mon 07 Nov 2011 14:33

JohnV wrote:Dear Viktor,

Is there any progress on this issue ?

We've revealed the issue and will fix it in one of our future builds of dbForge Studio for Oracle. We will notify you as soon as the next build with the fix of this problem is available for download.

JohnV · Post by **JohnV** » Thu 01 Dec 2011 06:23

Dear Viktor,

When can we expect a new version ?

alexa · Post by **alexa** » Thu 01 Dec 2011 09:45

Hello John.

A new version is due to be released next week.

bobjase · Post by **bobjase** » Fri 13 Jan 2012 02:16

i am having the same problems on the newest version.

Is there any way to set the fetch size?

alexa · Post by **alexa** » Mon 16 Jan 2012 13:01

i am having the same problems on the newest version.

Is there any way to set the fetch size?

If there are too many records in the grid, you can use Paginal Mode. In this mode you can set the number of rows that will be displayed on page. To switch Paginal Mode on, press the Paginal Mode button and set Page Row Count. If Paginal Mode is on, the warning icon with the corresponding information is shown on the status bar. After that you can list pages with the corresponding buttons on the toolbar.

By default, Paginal Mode is enabled in data view with 1000 records on page, so only 1000 records are displayed at once. You may change default settings on the Data Editor->General tab of the Options window.

DominicMason · Post by **DominicMason** » Mon 20 Feb 2012 05:55

The likely cause of this is because the execution plan has changed. Generate a current explain plan of the offending query and compare it to a previous one that was taken when the query was performing well. Usually the previous plan is not available.

Some factors that can cause a plan to change are:

Which tables are currently analyzed? Were they previously analyzed? (ie. Was the query using RBO and now CBO?)
Has OPTIMIZER_MODE been changed in INIT.ORA?
Has the DEGREE of parallelism been defined/changed on any table?
Have the tables been re-analyzed? Were the tables analyzed using estimate or compute? If estimate, what percentage was used?
Have the statistics changed?
Has the SPFILE/ INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT been changed?
Has the INIT.ORA parameter SORT_AREA_SIZE been changed?
Have any other INIT.ORA parameters been changed?

What do you think the plan should be? Run the query with hints to see if this produces the required performance.

It can also happen because of a very high high water mark. Typically when a table was big, but now only contains a couple of records. Oracle still needs to scan through all the blocks to see if they contain data.

jamesbarclay · Post by **jamesbarclay** » Tue 21 Feb 2012 10:20

If we assume the query did make sense then you might want to try using EXISTS as some suggest and avoid IN. IN is not always bad and there are likely cases which one could show it actually performs better than EXISTS.

The question title is not very helpful. I could set this query up in one Oracle database and make it run slow and make it run fast in another. There are many factors that determine how the database resolves the query, object statistics, SYS schema statistics, and parameters, as well as server performance. Sqlserver vs. Oracle isn't the problem here.

For those interested in query tuning and performance and want to learn more some of the google terms to search are "oak table oracle" and "oracle jonathan lewis".

bobjase · Post by **bobjase** » Thu 18 Apr 2013 14:33

Ditto here on the "fetch size". That has nothing to do with Paginal Mode. It is a parameter of the Oracle Connection which determines how much data you get with each request.

alexa · Post by **alexa** » Wed 15 May 2013 11:59

Thank you for the comments on this.

Devart Forums

Query performance very slow on Oracle 10g

Query performance very slow on Oracle 10g

Re: Query performance very slow on Oracle 10g

Re: Query performance very slow on Oracle 10g